Failure on creating pending segments table in MySQL or MariaDB metadata storage

[sirpkt]

I experienced following exception when I started Druid Overlord.

2016-01-20T11:32:40,984 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.metadata.IndexerSQLMetadataStorageCoordinator.start()] on object[io.druid.metadata.IndexerSQLMetadataStorageCoordinator@2d7a9786].
2016-01-20T11:32:40,986 INFO [main] io.druid.metadata.SQLMetadataConnector - Creating table[druid_pendingSegments]
2016-01-20T11:32:40,997 WARN [main] com.metamx.common.RetryUtils - Failed on try 1, retrying in 1,927ms.
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.BatchUpdateException: Specified key was too long; max key length is 1000 bytes [statement:"null", located:"null", rewritten:"null", arguments:null]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:284) ~[jdbi-2.63.1.jar:2.63.1]
        at io.druid.metadata.SQLMetadataConnector$2.call(SQLMetadataConnector.java:110) ~[druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at com.metamx.common.RetryUtils.retry(RetryUtils.java:38) [java-util-0.27.6.jar:?]
        at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:115) [druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:160) [druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.SQLMetadataConnector.createPendingSegmentsTable(SQLMetadataConnector.java:188) [druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.SQLMetadataConnector.createPendingSegmentsTable(SQLMetadataConnector.java:388) [druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.IndexerSQLMetadataStorageCoordinator.start(IndexerSQLMetadataStorageCoordinator.java:91) [druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_65]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_65]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_65]
        at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_65]
        at com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler.start(Lifecycle.java:350) [java-util-0.27.6.jar:?]
        at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:259) [java-util-0.27.6.jar:?]
        at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:155) [druid-api-0.3.15.jar:0.9.0-SNAPSHOT]
        at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:73) [druid-services-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.cli.ServerRunnable.run(ServerRunnable.java:40) [druid-services-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.cli.Main.main(Main.java:107) [druid-services-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.BatchUpdateException: Specified key was too long; max key length is 1000 bytes [statement:"null", located:"null", rewritten:"null", arguments:null]
        at org.skife.jdbi.v2.Batch.execute(Batch.java:131) ~[jdbi-2.63.1.jar:2.63.1]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:172) ~[druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:162) ~[druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
        ... 17 more
Caused by: java.sql.BatchUpdateException: Specified key was too long; max key length is 1000 bytes
        at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1110) ~[?:?]
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) ~[commons-dbcp2-2.0.1.jar:2.0.1]
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) ~[commons-dbcp2-2.0.1.jar:2.0.1]
        at org.skife.jdbi.v2.Batch.execute(Batch.java:121) ~[jdbi-2.63.1.jar:2.63.1]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:172) ~[druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:162) ~[druid-server-0.9.0-SNAPSHOT.jar:0.9.0-SNAPSHOT]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
        ... 17 more

[sirpkt]

I think this error is from SQL query of createPendingSegmentsTable() in SQLMetadataConnector,
which has unique key, UNIQUE (sequence_name, sequence_prev_id).
As both sequence_name and sequence_prev_id are defined as VARCHAR(255)
and VARCHAR(255) actually takes 255 * 3 = 765 bytes with UTF-8,
key size exceeds 1000 bytes limit of MySQL MyISAM(and MariaDB also has the same key size limit).

[sirpkt]

Reducing the size of sequence_name and sequence_prev_id column (for example, to 127) could be a solution for the issue.
Or, we can recommend to use InnoDB with set global innodb_large_prefix=on.
I'm not sure which one is better or another good way to solve the issue.

[drcrallen]

@sirpkt Thanks for the report. What version of druid are you seeing this error on?

[gianm]

That table is from #1896 so probably a build from master. Given that it's not in a release yet, I think it'd be ok to make a backwards incompatible change like changing it to VARBINARY (assuming that works on derby/mysql/postgresql) or changing the index to be on a new column like sequence_name_prev_id_sha1 (sha1 of both those fields).

Thanks for the catch @sirpkt.

FYI: This CREATE TABLE had worked for me using InnoDB on MySQL 5.6.27. http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html seems to imply that InnoDB can do a multi-column index that is longer than 767 bytes, up to 3072 bytes total (but not more than 767 bytes per column) so I guess that makes sense.

[zhaojiandong]

change mysql conf add default-storage-engine=INNOD to [mysqld], then restart mysql

[ans76]

@gianm still get this error on druid 0.13.0
Variable_name: innodb_version Value: 5.6.35

-- create a druid database, make sure to use utf8mb4 as encoding CREATE DATABASE druid DEFAULT CHARACTER SET utf8mb4;

utf8mb4 take 4-byte, sequence_name_prev_id_sha1 VARCHAR(255) takes 4*255 bytes