MySQL error: Specified key was too long; max key length is 767 bytes

[vrozkovec]

Hi, I'm initializing repository this way:

JaversSqlRepository sqlRepository = SqlRepositoryBuilder.sqlRepository()
	.withConnectionProvider(connectionProvider)
	.withDialect(DialectName.MYSQL)
	.build();
Javers javers = JaversBuilder.javers().registerJaversRepository(sqlRepository).build();

however I get an error in the application:

13:22:18,689  INFO SqlRepositoryBuilder:55 - starting up SQL repository module ...
13:22:18,719  INFO Java8AddOns:13 - loading Java8 add-ons ...
13:22:18,738  INFO ScannerModule:28 - using FIELD mappingStyle
13:22:18,753  INFO JaversSchemaManager:244 - creating javers table jv_commit_property ...
13:22:18,753  INFO SchemaManagerImpl:51 - creating entity with name jv_commit_property using ddl:
CREATE TABLE jv_commit_property (
property_name VARCHAR(200),
property_value VARCHAR(200),
commit_fk BIGINT,
CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name),
CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES jv_commit(commit_pk)
) ENGINE = InnoDB
13:22:18,755  WARN RequestCycleExtra:346 - ********************************
13:22:18,755  WARN RequestCycleExtra:347 - Handling the following exception
org.polyjdbc.core.exception.SchemaManagerException: [DDL_ERROR] Failed to run DDL:
CREATE TABLE jv_commit_property (
property_name VARCHAR(200),
property_value VARCHAR(200),
commit_fk BIGINT,
CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name),
CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES jv_commit(commit_pk)
) ENGINE = InnoDB
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:91)
	at org.polyjdbc.core.schema.SchemaManagerImpl.create(SchemaManagerImpl.java:52)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureTable(JaversSchemaManager.java:245)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureSchema(JaversSchemaManager.java:42)
	at org.javers.repository.sql.JaversSqlRepository.ensureSchema(JaversSqlRepository.java:81)
	at org.javers.core.JaversBuilder.build(JaversBuilder.java:107)
	at cz.jaclean.pages.TestPage.onInitialize(TestPage.java:56)
	at org.apache.wicket.Component.fireInitialize(Component.java:891)
	at org.apache.wicket.MarkupContainer.internalInitialize(MarkupContainer.java:1081)
	at org.apache.wicket.Page.isPageStateless(Page.java:465)
	at org.apache.wicket.request.handler.render.WebPageRenderer.isPageStateless(WebPageRenderer.java:287)
	at org.apache.wicket.request.handler.render.WebPageRenderer.shouldRenderPageAndWriteResponse(WebPageRenderer.java:329)
	at org.apache.wicket.request.handler.render.WebPageRenderer.respond(WebPageRenderer.java:193)
	at org.apache.wicket.core.request.handler.RenderPageRequestHandler.respond(RenderPageRequestHandler.java:175)
	at org.apache.wicket.request.cycle.RequestCycle$HandlerExecutor.respond(RequestCycle.java:895)
	at org.apache.wicket.request.RequestHandlerStack.execute(RequestHandlerStack.java:64)
	at org.apache.wicket.request.cycle.RequestCycle.execute(RequestCycle.java:265)
	at org.apache.wicket.request.cycle.RequestCycle.processRequest(RequestCycle.java:222)
	at org.apache.wicket.request.cycle.RequestCycle.processRequestAndDetach(RequestCycle.java:293)
	at org.apache.wicket.protocol.http.WicketFilter.processRequestCycle(WicketFilter.java:261)
	at org.apache.wicket.protocol.http.WicketFilter.processRequest(WicketFilter.java:203)
	at org.apache.wicket.protocol.http.WicketFilter.doFilter(WicketFilter.java:284)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:523)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:560)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:537)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:527)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2011)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1964)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:891)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:795)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:88)
	... 44 more
13:22:18,757  WARN RequestCycleExtra:348 - ********************************
13:22:18,758 ERROR DefaultExceptionMapper:170 - Unexpected error occurred
org.polyjdbc.core.exception.SchemaManagerException: [DDL_ERROR] Failed to run DDL:
CREATE TABLE jv_commit_property (
property_name VARCHAR(200),
property_value VARCHAR(200),
commit_fk BIGINT,
CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name),
CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES jv_commit(commit_pk)
) ENGINE = InnoDB
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:91)
	at org.polyjdbc.core.schema.SchemaManagerImpl.create(SchemaManagerImpl.java:52)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureTable(JaversSchemaManager.java:245)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureSchema(JaversSchemaManager.java:42)
	at org.javers.repository.sql.JaversSqlRepository.ensureSchema(JaversSqlRepository.java:81)
	at org.javers.core.JaversBuilder.build(JaversBuilder.java:107)
	at cz.jaclean.pages.TestPage.onInitialize(TestPage.java:56)
	at org.apache.wicket.Component.fireInitialize(Component.java:891)
	at org.apache.wicket.MarkupContainer.internalInitialize(MarkupContainer.java:1081)
	at org.apache.wicket.Page.isPageStateless(Page.java:465)
	at org.apache.wicket.request.handler.render.WebPageRenderer.isPageStateless(WebPageRenderer.java:287)
	at org.apache.wicket.request.handler.render.WebPageRenderer.shouldRenderPageAndWriteResponse(WebPageRenderer.java:329)
	at org.apache.wicket.request.handler.render.WebPageRenderer.respond(WebPageRenderer.java:193)
	at org.apache.wicket.core.request.handler.RenderPageRequestHandler.respond(RenderPageRequestHandler.java:175)
	at org.apache.wicket.request.cycle.RequestCycle$HandlerExecutor.respond(RequestCycle.java:895)
	at org.apache.wicket.request.RequestHandlerStack.execute(RequestHandlerStack.java:64)
	at org.apache.wicket.request.cycle.RequestCycle.execute(RequestCycle.java:265)
	at org.apache.wicket.request.cycle.RequestCycle.processRequest(RequestCycle.java:222)
	at org.apache.wicket.request.cycle.RequestCycle.processRequestAndDetach(RequestCycle.java:293)
	at org.apache.wicket.protocol.http.WicketFilter.processRequestCycle(WicketFilter.java:261)
	at org.apache.wicket.protocol.http.WicketFilter.processRequest(WicketFilter.java:203)
	at org.apache.wicket.protocol.http.WicketFilter.doFilter(WicketFilter.java:284)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:523)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:560)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:537)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:527)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2011)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1964)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:891)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:795)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:88)
	... 44 more

When I try to run DDL separately in the database:

CREATE TABLE jv_commit_property (
property_name VARCHAR(200),
property_value VARCHAR(200),
commit_fk BIGINT,
CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name),
CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES jv_commit(commit_pk)
) ENGINE = InnoDB

I get the same error..

As you do not specify encoding and my database used utf16_czech_ci collation, this maxed out allowed 767 bytes for a key size. After setting it to utf8_czech_ci, the problem went away.
Either it would be good to specify encoding or check encoding first and then set a limit on a key.

Related discussions:
#1071 - Specified key was too long; max key length is 767 bytes
UTF8, UTF16, and UTF32

[bartoszwalacik]

Im not sure I'm understand, so CREATE TABLE jv_commit_property fails, and db throws Specified key was too long; max key length is 767 bytes.
Which key is 767 long?

[vrozkovec]

I guess it must have been this one:
PRIMARY KEY(commit_fk, property_name)

property_name length is 200 x 4 bytes is 800 bytes for utf16
property_name length is 200 x 3 bytes is 600 bytes for utf8

same goes for other tables. Just try to run it on InnoDB engine and utf16, you'll hit this error.
Also see third list item here

[bartoszwalacik]

but this key
jv_commit_property_pk
has 22 ascii chars ... it not even close to 800

[bartoszwalacik]

ok, i see, its about key value which contains property_name, so why you cant use utf8* encoding?

[bartoszwalacik]

btw, as far as I know, in Postgres, encoding is a db property and javers doesn't create db, you do

[vrozkovec]

Yep, I have switched to utf8 and the problem went away.
When you use MySQL, PolyJDBC creates tables for you.
Anyway, you have the information, and the fix, perhaps just a note somewhere in the FAQ..? It's up to you.
Thanks for the great tool!

[bartoszwalacik]

ok, so in MySql you can specify characterset for individual tables (i didn't know that). And if you don't specify characterset when crating a table, MySql will use db characterset. In your case it is utf16_czech_ci which breaks Javers' ddl's.

Feel free to create a Pull Request to PolyJDBC with a fix

[raupachz]

Not sure if I have the same error or a different one, therefore I just take over this issue. Hope that is ok.

[main] INFO org.javers.repository.sql.SqlRepositoryBuilder - starting up SQL repository module ...
[main] INFO org.javers.java8support.Java8AddOns - loading Java8 add-ons ...
[main] INFO org.javers.core.metamodel.scanner.ScannerModule - using FIELD mappingStyle
[main] INFO org.javers.repository.sql.schema.JaversSchemaManager - creating javers table jv_commit ...
[main] INFO org.polyjdbc.core.schema.SchemaManagerImpl - creating entity with name jv_commit using ddl:
CREATE TABLE jv_commit (
commit_pk BIGINT NOT NULL  AUTO_INCREMENT,
author VARCHAR(200),
commit_date TIMESTAMP,
commit_id NUMERIC(22,2),
CONSTRAINT jv_commit_pk PRIMARY KEY(commit_pk)
) ENGINE = InnoDB
[main] INFO org.polyjdbc.core.schema.SchemaManagerImpl - creating entity with name jv_commit_commit_id_idx using ddl:
CREATE INDEX jv_commit_commit_id_idx ON jv_commit(commit_id)
[main] INFO org.javers.repository.sql.schema.JaversSchemaManager - creating javers table jv_commit_property ...
[main] INFO org.polyjdbc.core.schema.SchemaManagerImpl - creating entity with name jv_commit_property using ddl:
CREATE TABLE jv_commit_property (
property_name VARCHAR(200),
property_value VARCHAR(600),
commit_fk BIGINT,
CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name),
CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES jv_commit(commit_pk)
) ENGINE = InnoDB
[main] INFO org.polyjdbc.core.schema.SchemaManagerImpl - creating entity with name jv_commit_property_commit_fk_idx using ddl:
CREATE INDEX jv_commit_property_commit_fk_idx ON jv_commit_property(commit_fk)
[main] INFO org.polyjdbc.core.schema.SchemaManagerImpl - creating entity with name jv_commit_property_property_name_property_value_idx using ddl:
CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value)
Exception in thread "main" org.polyjdbc.core.exception.SchemaManagerException: [DDL_ERROR] Failed to run DDL:
CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value)
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:91)
	at org.polyjdbc.core.schema.SchemaManagerImpl.create(SchemaManagerImpl.java:52)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureTable(JaversSchemaManager.java:245)
	at org.javers.repository.sql.schema.JaversSchemaManager.ensureSchema(JaversSchemaManager.java:42)
	at org.javers.repository.sql.JaversSqlRepository.ensureSchema(JaversSqlRepository.java:83)
	at org.javers.core.JaversBuilder.build(JaversBuilder.java:107)
	at me.raupach.Main.setupRepo(Main.java:108)
	at me.raupach.Main.main(Main.java:29)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
	at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:88)
	... 7 more

Here is my example program. Very simple. I just wanted to get started and set up a empty database. Therefore the root user. This is just for local development.

public static void main(String [] args) {
  final String url = "jdbc:mysql://localhost/dbvers";
  final MysqlDataSource ds = new MysqlDataSource();
  ds.setUrl(url);
  ds.setUser("root");
  ds.setCharacterEncoding("utf8");
        
  JaversSqlRepository sqlRepository = SqlRepositoryBuilder
    .sqlRepository()
    .withConnectionProvider(() -> ds.getConnection())
    .withDialect(DialectName.MYSQL).build();
}

The default character set is already utf8. So the fix @vrozkovec suggests isn't working for me.

SELECT @@character_set_database, @@collation_database\G
@@character_set_database: utf8
        @@collation_database: utf8_unicode_ci

The tables jv_commit and jv_jv_commit_property are already created. MySQL does not support rollbacks on DDL statements. They are both created with utf8.

MySQL 5.6.34

Edit
The index can be created through mysql client.

CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value);

Must be related to commit 0804fb9. The size of the varchar column was increased from 200 to 600.

[raupachz]

Ok, after some digging I think the problem is with create index and a missing prefix length.

CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value);

If you call this with the mysql command line client it creates an index with a supart of 255. MySQL just assumes 3 bytes with UTF8. 3 times 255 makes 765 which is less than 767.

Unfortunately this does not work with plain JDBC connections. If you use a DDL statement through a jdbc driver it failes with the exception.

final String ddl = "CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value)";
try (Connection con = dataSource.getConnection()) {
  Statement stmt = con.createStatement();
  stmt.executeUpdate(ddl);
} catch (SQLException e) {
  e.printStackTrace();
}

The solution to fix this to add a prefix length.

final String ddl = "CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value(255))";
try (Connection con = dataSource.getConnection()) {
  Statement stmt = con.createStatement();
  stmt.executeUpdate(ddl);
} catch (SQLException e) {
  e.printStackTrace();
}

Unfortunately this is not possible with polyjdbc. I open a ticket and if I find the time PR.

[bartoszwalacik]

@raupachz your issue is fixed in 2.8.2.
Original problem in this issue (db with utf16_czech_ci characterset) still isn't solved.

[raupachz]

@bartoszwalacik I can confirm that 2.8.2 fixes the issue with MySQL and UTF8 encoding.