My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
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
Status:  Submitted
Owner:  ----
Closed:  Mar 2014


Sign in to add a comment
 
Reported by szymon.r...@gmail.com, Feb 28, 2012
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
#1 p...@akeo.ie
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

Apr 10, 2012
#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...
Apr 10, 2012
#3 sop@google.com
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
#4 TheLinux...@gmail.com
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
#5 TheLinux...@gmail.com
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
#7 p...@akeo.ie
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
#8 TheLinux...@gmail.com
very much so, as we used to say "MyISAM is where data goes to die"
Apr 11, 2012
#9 p...@akeo.ie
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
#10 dbailey%...@gtempaccount.com
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
#11 sop@google.com
 Issue 785  has been merged into this issue.
May 15, 2012
#12 sop@google.com
 Issue 1058  has been merged into this issue.
May 15, 2012
#13 sop@google.com
 Issue 1138  has been merged into this issue.
May 15, 2012
#14 sop@google.com
 Issue 1082  has been merged into this issue.
May 15, 2012
#15 sop@google.com
 Issue 1330  has been merged into this issue.
Jun 25, 2013
#16 lecaoquochung@gmail.com
 #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
Feb 21, 2014
Project Member #17 david.pu...@sonymobile.com
https://gerrit-review.googlesource.com/#/c/54793/
Status: ChangeUnderReview
Mar 26, 2014
Project Member #19 david.pu...@sonymobile.com
(No comment was entered for this change.)
Status: Submitted
Labels: FixedIn-2.8.4
Sign in to add a comment

Powered by Google Project Hosting