| Issue 1273: | MySQL: Specified key was too long; max key length is 1000 bytes | |
| 16 people starred this issue and may be notified of changes. | Back to list |
Affected Version: 2.3.0-rc0 What steps will reproduce the problem? 1. Gerrit Version 2.2.2.1 upgrading to 2.3.0-rc0: java -jar gerrit-2.3-rc0.war init -d review_site/ 2. Uprading failes on "Upgrading database schema from version" with msg: Exception in thread "main" com.google.gwtorm.client.OrmException: Cannot apply SQL CREATE TABLE submodule_subscriptions ( submodule_project_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, submodule_branch_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, super_project_project_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, super_project_branch_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, submodule_path VARCHAR(255) BINARY DEFAULT '' NOT NULL ,PRIMARY KEY(super_project_project_name,super_project_branch_name,submodule_path) ) at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44) at com.google.gwtorm.jdbc.JdbcSchema.createRelations(JdbcSchema.java:84) at com.google.gwtorm.jdbc.JdbcSchema.updateSchema(JdbcSchema.java:54) at com.google.gerrit.server.schema.SchemaVersion.upgradeFrom(SchemaVersion.java:94) at com.google.gerrit.server.schema.SchemaVersion.check(SchemaVersion.java:77) at com.google.gerrit.server.schema.SchemaVersion.upgradeFrom(SchemaVersion.java:86) at com.google.gerrit.server.schema.SchemaVersion.check(SchemaVersion.java:77) at com.google.gerrit.server.schema.SchemaUpdater.update(SchemaUpdater.java:64) at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:181) at com.google.gerrit.pgm.Init.run(Init.java:79) at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:167) at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:91) at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:49) at Main.main(Main.java:25) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625) at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42) ... 18 more Please provide any additional information below: mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
Apr 10, 2012
Addon: looks like this is bug #291 (https://code.google.com/p/gerrit/issues/detail?id=291). Once DB is recreated and charset is ensured to be latin1, error no longer occurs. UTF-8 support for MySQL would be nice though...
Apr 10, 2012
Perhaps you should refile the bug with MySQL? Other database products like PostgreSQL have no problem with this key structure and UTF-8 strings, because they don't have an artificial limitation of 1000 bytes in a row key, and don't try to encode UTF-8 using UTF-24.
Apr 11, 2012
IIRC this shouldn't be a problem with InnoDB. The 1000 byte total limit is a MyISAM one whereas InnoDB has a 768 byte per column limit (of course UTF8 in MySQL is 3bytes per char).
Apr 11, 2012
Also apparently one of our teams have tried this with InnoDB and 2.3.0 with no issue. Other ex-MySQL members are jogging my memory about that limit being increased recently too but I can't confirm that right now.
Apr 11, 2012
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix <- yep, key prefix limit can be even higher
Apr 11, 2012
Thanks for the input. I'm going to try switching to InnoDB, as it looks like the better choice anyway. I'll report the outcome when I'm done.
Apr 11, 2012
very much so, as we used to say "MyISAM is where data goes to die"
Apr 11, 2012
Switched to InnoDB, and everything looks good for MySQL using an UTF8 reviewdb. The latest Debian (squeeze/6.0.4) makes it fairly easy to ditch MyISAM for InnoDB, as both are available and ready to use. Just edit my.cnf and under [mysqld] add: default-storage-engine = innodb
Apr 24, 2012
Append "sessionVariables=storage_engine=InnoDB" to the database.url to ensure that any new tables created by gerrit use the InnoDB engine even if the default is still MyISAM.
May 15, 2012
Issue 785 has been merged into this issue.
May 15, 2012
Issue 1058 has been merged into this issue.
May 15, 2012
Issue 1138 has been merged into this issue.
May 15, 2012
Issue 1082 has been merged into this issue.
May 15, 2012
Issue 1330 has been merged into this issue.
Jun 25, 2013
#2 p...@akeo.ie Addon: looks like this is bug #291 (https://code.google.com/p/gerrit/issues/detail?id=291). Once DB is recreated and charset is ensured to be latin1, error no longer occurs. UTF-8 support for MySQL would be nice though... ->Fixed with me on CentOS 6
Mar 26, 2014
https://gerrit-review.googlesource.com/#/c/55492/
Mar 26, 2014
(No comment was entered for this change.)
Status:
Submitted
Labels: FixedIn-2.8.4 |
|
| ► Sign in to add a comment |
Also occurs for fresh installation of 2.3.0-rc0 with: mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64) using readline 6.1 CREATE TABLE account_project_watches ( notify_new_changes CHAR(1) DEFAULT 'N' NOT NULL CHECK (notify_new_changes IN ('Y','N')), notify_all_comments CHAR(1) DEFAULT 'N' NOT NULL CHECK (notify_all_comments IN ('Y','N')), notify_submitted_changes CHAR(1) DEFAULT 'N' NOT NULL CHECK (notify_submitted_changes IN ('Y','N')), account_id INT DEFAULT 0 NOT NULL, project_name VARCHAR(255) BINARY DEFAULT '' NOT NULL, filter VARCHAR(255) BINARY DEFAULT '' NOT NULL ,PRIMARY KEY(account_id,project_name,filter) ) at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44) at com.google.gwtorm.jdbc.JdbcSchema.createRelations(JdbcSchema.java:84) at com.google.gwtorm.jdbc.JdbcSchema.updateSchema(JdbcSchema.java:54) at com.google.gerrit.server.schema.SchemaCreator.create(SchemaCreator.java:108) at com.google.gerrit.server.schema.SchemaUpdater.update(SchemaUpdater.java:55) at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:181) at com.google.gerrit.pgm.Init.run(Init.java:79) at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:167) at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:91) at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:49) at Main.main(Main.java:25) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625) at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42) ... 15 more