mysql database upgrade problem

[sss@…]

than i trying to upgrade trac mysql database from 0.12svn to newer 0.12svn i have problem, it looks like mysql database design problem, but i have very limited mysql knowledge to fix it by myself, than i trying to run upgrade i see this:

        trac-admin . upgrade                        
        OperationalError: (1071, 'Specified key was too long; max key length is 1000 bytes')

[sss@…]

software details: dev-lang/python-2.6.4 dev-db/mysql-5.1.44

[sss@…]

i also using git plugin from ​http://trac-hacks.org/svn/gitplugin

[Christian Boos]

Please show us the whole traceback, which maybe will contain more information. If you don't have it and the log doesn't contain it, then enable SQL statement tracing using the TracIni [trac] debug_sql = true setting.

Make sure you have the proper database requirements, notably concerning character sets, as explained in MySqlDb.

[sss@…]

2010-03-20 02:56:08,778 Trac[console] ERROR: Exception in trac-admin command:                                                                                                       
Traceback (most recent call last):                                                                                                                                                  
  File "/usr/lib/python2.6/site-packages/trac/admin/console.py", line 107, in onecmd                                                                                                
    rv = cmd.Cmd.onecmd(self, line) or 0                                                                                                                                            
  File "/usr/lib/python2.6/cmd.py", line 218, in onecmd                                                                                                                             
    return self.default(line)                                                                                                                                                       
  File "/usr/lib/python2.6/site-packages/trac/admin/console.py", line 251, in default                                                                                               
    return cmd_mgr.execute_command(*args)                                                                                                                                           
  File "/usr/lib/python2.6/site-packages/trac/admin/api.py", line 118, in execute_command                                                                                           
    return f(*fargs)                                                                                                                                                                
  File "/usr/lib/python2.6/site-packages/trac/env.py", line 773, in _do_upgrade                                                                                                     
    self.env.upgrade(backup=no_backup is None)                                                                                                                                      
  File "/usr/lib/python2.6/site-packages/trac/env.py", line 507, in upgrade                                                                                                         
    @with_transaction(self)                                                                                                                                                         
  File "/usr/lib/python2.6/site-packages/trac/db/util.py", line 36, in transaction_wrapper                                                                                          
    fn(dbtmp)                                                                                                                                                                       
  File "/usr/lib/python2.6/site-packages/trac/env.py", line 519, in do_upgrade                                                                                                      
    participant.upgrade_environment(db)                                                                                                                                             
  File "/usr/lib/python2.6/site-packages/trac/env.py", line 585, in upgrade_environment                                                                                             
    script.do_upgrade(self.env, i, cursor)                                                                                                                                          
  File "/usr/lib/python2.6/site-packages/trac/upgrades/db23.py", line 36, in do_upgrade                                                                                             
    cursor.execute(stmt)                                                                                                                                                            
  File "/usr/lib/python2.6/site-packages/trac/db/util.py", line 81, in execute                                                                                                      
    r = self.cursor.execute(sql)                                                                                                                                                    
  File "/usr/lib/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute                                                                                                  
    self.errorhandler(self, exc, value)                                                                                                                                             
  File "/usr/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler                                                                                   
    raise errorclass, errorvalue                                                                                                                                                    
OperationalError: (1071, 'Specified key was too long; max key length is 1000 bytes')

[Christian Boos]

source:trunk/trac/upgrades/db23.py@9372#L36​

Ok, the longer key=('repos', 'rev', 'path', 'change_type') is problematic.

And what about the MySQL information? What version/engine/charsets are you using?

[sss@…]

version of mysql bindings is dev-python/mysql-python-1.2.3_rc1, and charset is utf8_unicode_ci

[Christian Boos]

While it should have been utf8_unicode_bin (please people, if you want to use MySQL, conform to the instructions given in MySqlDb!), I think this doesn't make a difference concerning the problem at hand.

I'll try the upgrade again (thought I did?), and if it doesn't work it's indeed a blocker, otherwise I suspect it's probably because an unsupported engine was used (MyISAM?).

[Gluzskiy Alexandr <sss@…>]

i converted db to innodb and charset to utf8_bin and still have this problem

[Remy Blank]

Could you please try the following:

• Create a new Trac environment with the current 0.12svn and a new database (make sure to follow the instructions in MySqlDb exactly).
• Set the repository in trac.ini to the same repository as the environment where you observe the problem.
• Run trac-admin $ENV repository resync "(default)".

If the resync fails, then I would guess that some paths in your repository are too long, and make the primary key for the node_change table too long. Try to find the revision where the failure happens, it should be one above the last revision printed, and see if there are any long paths in that changeset. I don't know if MySQL can be tuned to allow for longer keys; if yes, that might be a solution.

If the resync succeeds, then you are probably still missing something from MySqlDb. You may have to create a new database with the new settings, dump the old database and import it in the new database.

But most likely the resync will fail. ​This ticket seems to be pretty explicit about it, especially ​this comment. Unfortunately, we won't redesign our whole database schema just for MySQL at this point :(

[Christian Boos]

Well, the problem here happens when re-creating the node_change table, in db23.py, before resync.

When doing this, the "hack" in source:trunk/trac/db/mysql_backend.py@9399#L124​ is no longer effective, as repos will take the limit value, of 333 / 4 = 83 characters. So we have 83 + 255 + 20 + 2 = 360, and that's above the maximum 333 allowed for unicode (they use 3 bytes per unicode char, so 360 * 3 > 1000). Now, a quickfix would be to special case repos as well (30 would do fine), but a better fix would be to make this explicit and pass a tuple of key lengths to a key_length parameter, in the Table constructor.

[Remy Blank]

Replying to cboos:

While it should have been utf8_unicode_bin (please people, if you want to use MySQL, conform to the instructions given in MySqlDb!)

MySqlDb only mentions utf8_bin. Now, what's the correct value?

Replying to cboos:

Well, the problem here happens when re-creating the node_change table, in db23.py, before resync.

Isn't the same code also used when doing a trac-admin $ENV initenv? If so, an initenv should also fail on MySQL.

Strangely, I was just able to upgrade my test MySQL environment successfully from 22 to 26. This is with MySQL 5.1.44 on OS X. Same for a new environment created at [8079] (db version 22) and upgraded to current trunk.

Here's what my node_change table looks like:

CREATE TABLE `node_change` (
  `repos` int(11) NOT NULL DEFAULT '0',
  `rev` text COLLATE utf8_bin NOT NULL,
  `path` text COLLATE utf8_bin NOT NULL,
  `node_type` text COLLATE utf8_bin,
  `change_type` text COLLATE utf8_bin NOT NULL,
  `base_path` text COLLATE utf8_bin,
  `base_rev` text COLLATE utf8_bin,
  PRIMARY KEY (`repos`,`rev`(20),`path`(255),`change_type`(2)),
  KEY `node_change_repos_rev_idx` (`repos`,`rev`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I wonder if the issue might be due to older MySQL databases, which may have VARCHAR columns instead of text.

repos will take the limit value, of 333 / 4 = 83 characters

repos is a surrogate key, here an int(11), so it will not set a size for the repos column in the PRIMARY KEY.

[Gluzskiy Alexandr <sss@…>]

now i have revision 9000 installed, my node_change table design:

CREATE TABLE `node_change` (
  `rev` text COLLATE utf8_bin NOT NULL,
  `path` text COLLATE utf8_bin NOT NULL,
  `node_type` text COLLATE utf8_bin,
  `change_type` text COLLATE utf8_bin NOT NULL,
  `base_path` text COLLATE utf8_bin,
  `base_rev` text COLLATE utf8_bin,
  PRIMARY KEY (`rev`(20),`path`(255),`change_type`(2)),
  KEY `node_change_rev_idx` (`rev`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

[Christian Boos]

Replying to Gluzskiy Alexandr <sss@…>:

now i have revision 9000 installed, my node_change table design:

You still didn't report which MySQL version you were using.

[Christian Boos]

Replying to rblank:

Replying to cboos:

While it should have been utf8_unicode_bin (please people, if you want to use MySQL, conform to the instructions given in MySqlDb!)

MySqlDb only mentions utf8_bin. Now, what's the correct value?

Sorry, it's utf8_bin, utf8_unicode_bin doesn't exist, I inferred the latter from the reported value utf8_unicode_ci (see also the ​list of supported unicode collations).

Strangely, I was just able to upgrade my test MySQL environment successfully from 22 to 26.

That's also what I remembered…

Here's what my node_change table looks like:

CREATE TABLE `node_change` (
  `repos` int(11) NOT NULL DEFAULT '0',

Ah of course, sorry again.

… PRIMARY KEY (repos,rev(20),path(255),change_type(2)),

repos is a surrogate key, here an int(11), so it will not set a size for the repos column in the PRIMARY KEY.

But wouldn't the int(11) also take some space in those 1000 bytes? There should be 1000 - (20 + 255 + 2) * 3 = 169 bytes left, but who knows…

For me it works…

mysql> connect trac;
Connection id:    124
Current database: trac

mysql> CREATE TABLE `node_change_test` (
    ->   `repos` int(11) NOT NULL DEFAULT '0',
    ->   `rev` text COLLATE utf8_bin NOT NULL,
    ->   `path` text COLLATE utf8_bin NOT NULL,
    ->   `node_type` text COLLATE utf8_bin,
    ->   `change_type` text COLLATE utf8_bin NOT NULL,
    ->   `base_path` text COLLATE utf8_bin,
    ->   `base_rev` text COLLATE utf8_bin,
    ->   PRIMARY KEY (`repos`,`rev`(20),`path`(255),`change_type`(2)),
    ->   KEY `node_change_repos_rev_idx` (`repos`,`rev`(20))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    ->
    -> ;
Query OK, 0 rows affected (1.08 sec)

mysql> SHOW FULL COLUMNS FROM `node_change_test`;
+-------------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| repos       | int(11) | NULL      | NO   | PRI | 0       |       | select,insert,update,references |         |
| rev         | text    | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| path        | text    | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| node_type   | text    | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
| change_type | text    | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| base_path   | text    | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
| base_rev    | text    | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
+-------------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
7 rows in set (0.26 sec)

Alexandr, does the above CREATE TABLE fail in your case? If not, we should see how the one attempted by the update code is different. Setting [trac] debug_sql = true will put the SQL queries in your log.

[Gluzskiy Alexandr <sss@…>]

Replying to cboos:

Replying to Gluzskiy Alexandr <sss@…>:

now i have revision 9000 installed, my node_change table design:

You still didn't report which MySQL version you were using.

Changed 11 days ago by sss@…

software details: dev-lang/python-2.6.4 dev-db/mysql-5.1.44

[Gluzskiy Alexandr <sss@…>]

Replying to cboos:

Alexandr, does the above CREATE TABLE fail in your case? If not, we should see how the one attempted by the update code is different. Setting [trac] debug_sql = true will put the SQL queries in your log.

yes,

mysql> CREATE TABLE `node_change_test` (
    ->        `repos` int(11) NOT NULL DEFAULT '0',
    ->        `rev` text COLLATE utf8_bin NOT NULL,
    ->        `path` text COLLATE utf8_bin NOT NULL,
    ->        `node_type` text COLLATE utf8_bin,
    ->        `change_type` text COLLATE utf8_bin NOT NULL,
    ->        `base_path` text COLLATE utf8_bin,
    ->        `base_rev` text COLLATE utf8_bin,
    ->        PRIMARY KEY (`repos`,`rev`(20),`path`(255),`change_type`(2)),
    ->        KEY `node_change_repos_rev_idx` (`repos`,`rev`(20))
    ->      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    ->     
    ->      ;
Query OK, 0 rows affected (0.51 sec)

this working.

[Remy Blank]

So the next step would be to set debug_sql = true in the [trac] section of your trac.ini, then attempt the upgrade once again, and to post the part of the log file concerning the upgrade here (please compress the file). This should allow us to see the exact SQL queries run during the upgrade.

[Gluzskiy Alexandr <sss@…>]

trac.log fragment

[Remy Blank]

Thanks, that's exactly what we needed. So the problematic statement is:

CREATE TABLE node_change (
    `repos` text,
    `rev` text,
    `path` text,
    `node_type` text,
    `change_type` text,
    `base_path` text,
    `base_rev` text,
    PRIMARY KEY (`repos`(83),`rev`(20),`path`(255),`change_type`(2))

Christian's explanation in comment:10 was correct, as the upgrade to version 23 introduced the repos column as text, and it's only converted to int(11) in version 24. The suggestion to have an explicit key_length argument sounds good to me, too.

Still, why does it work for me? I'll re-run the upgrade here and log the SQL as well. Maybe we can find the difference.

[Gluzskiy Alexandr <sss@…>]

maybe it's because i using git ass scm system, git revisions it's long checksums like 700e8a5e9fd079396b57a6a24c75bf5dd0ca4673

[Remy Blank]

Heh, funny, the statement from comment:18 completes successfully here, and shows the following for SHOW CREATE TABLE:

CREATE TABLE `node_change` (
  `repos` text COLLATE utf8_bin NOT NULL,
  `rev` text COLLATE utf8_bin NOT NULL,
  `path` text COLLATE utf8_bin NOT NULL,
  `node_type` text COLLATE utf8_bin,
  `change_type` text COLLATE utf8_bin NOT NULL,
  `base_path` text COLLATE utf8_bin,
  `base_rev` text COLLATE utf8_bin,
  PRIMARY KEY (`repos`(83),`rev`(20),`path`(255),`change_type`(2))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

So in theory, my primary key is 1080 bytes. I guess my MySQL installation has some kind of patch applied to work around the primary key limitation (upgraded to 5.1.45).

[Gluzskiy Alexandr <sss@…>]

do you suggest any workaround for me ?

[Remy Blank]

Allows specifying a key size for columns.

[Remy Blank]

The patch above adds an explicit key_size to Column, and fixes db23.py so that the key doesn't exceed 333 bytes for the node_change table.

A few comments:

• There's already a size argument in Column, which isn't used anywhere. Should we use that instead of adding a key_size argument? I think we should keep both arguments separate, as we may want to use size for e.g. creating columns as VARCHAR(n) instead of TEXT.
• Should we keep the special cases in MySQLConnector._collist() for rev, path and change_type (for backward compatibility?), or should I remove them?
• The size of change_type is given as 1, but its key size as 2. Why this difference?
• The patch also removes the optional unique argument of Column, which was never actually used since Column was introduced.

I'm still trying to find why my installation allows keys longer than 1000 bytes. If anyone has a hint…

[Remy Blank]

Looking at the MySQL source code, I see the following limits:

• MAX_KEY_LENGTH is 3072 bytes (sql/unireg.h). This seems to be the maximum key length for any storage backend.
• InnoDB supports keys up to 3500 bytes (see max_supported_key_length() in storage/innobase/handler/ha_innodb.h).
• MyISAM supports keys up to 1000 bytes (MI_MAX_KEY_LENGTH in include/myisam.h).

So it seems that Alexandr still has the MyISAM restriction, despite the table (seemingly) being InnoDB. Maybe this is due to the conversion process from MyISAM (comment:8)? Did you convert the complete DB, or only the tables (does it make a difference?)? Maybe the conversion requires a complete dump, DB re-creation, and load.

Anyway, with the fix, we should be fine even with a 1000 bytes limit, so I'll stop my research here. If Alexandr can confirm that the fix allows him to upgrade his database, and there are no objections, I'll commit the fix. I'm still interested in feedback to comment:22, though.

[Gluzskiy Alexandr <sss@…>]

thx for response, this patch solve upgrade problem, but now i have another problem .. )

[Remy Blank]

Thanks for the feedback!

[Gluzskiy Alexandr <sss@…>]

http://sss.chaoslab.ru:81/git/?p=sss_overlay.git;a=tree;f=www-apps/trac;h=3d58b1770649b0ee2e3d09d76ea4a6aa0b35d6fa;hb=d379fedd1e998a5b92213adf89ec5f2376ab7198 gentoo ebuild

[Remy Blank]

Patch applied in [9406]. I left the per-column name special cases in, in case a plugin was using the functionality.

[Christian Boos]

Sorry for not having commented earlier on the patch earlier, but I don't like so much setting the key size at the level of the column and would have preferred to have it specified next to the key parameter of the Table and in the Index, as you better see the combined size that way and this allows for different trade-offs depending on the key combination.

[Remy Blank]

Replying to cboos:

would have preferred to have it specified next to the key parameter of the Table and in the Index, as you better see the combined size that way and this allows for different trade-offs depending on the key combination.

This was also my first thought. However, does it really make sense to have different key lengths for a column depending on the index? For example, to have 60 significant characters for a column in one key, and 50 in another key. My feeling was that this would lead to strange situations when two values have the same 55-character prefix, and differ only starting from the 56th character. I haven't tested that situation, though.

[Christian Boos]

I'm afraid we risk to clutter the table definition by adding a key_size this way. We don't need a key_size for a column except when that column is part of a compound key and when we're above 1000 bytes. With a single key_length=(x,y,z), we just directly see that we're below 1000 or not, so that would be the main advantage, code clarity and being close to where the need comes from. Having different key lengths when being part of different key combinations might not be that useful, I concede.

[Remy Blank]

Conceptually, if the key size for a column is the same for all indexes, then I rather see it as an attribute of the column and not of the index. But ok, it's not that important, so let's do the key_length thing. Do you want to allow not specifying the length for a component (mabybe with None), or should all key components be specified explicitly?

[Remy Blank]

Set key length at the table and index level.

[Remy Blank]

The patch above sets the key length at the table and index level instead of the column. Frankly, I still think setting it on the column is more readable and makes more sense.