MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes

[jmiranda]

2021-05-12 21:35:44,586 [localhost-startStop-1] ERROR context.ContextLoader  - Context initialization failed
org.springframework.beans.factory.access.BootstrapException: Error executing bootstraps; nested exception is org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: 
Migration failed for change set 0.8.x/changelog-2019-03-17-0104-consolidate-inventory-snapshot-tables.xml::1552802756393-7::jmiranda (generated):
     Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number
`, `bin_location_name`):
          Caused By: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number`, `bin_location_name`):
          Caused By: Specified key was too long; max key length is 3072 bytes
	at org.codehaus.groovy.grails.web.context.GrailsContextLoader.createWebApplicationContext(GrailsContextLoader.java:87)
	at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
	at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5128)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5653)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:1007)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:983)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:639)
	at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1128)
	at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:2020)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:473)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set 0.8.x/changelog-2019-03-17-0104-consolidate-inventory-snapshot-tab
les.xml::1552802756393-7::jmiranda (generated):
     Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number
`, `bin_location_name`):
          Caused By: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number`, `bin_location_name`):
          Caused By: Specified key was too long; max key length is 3072 bytes
	at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
	at grails.util.Environment.executeForEnvironment(Environment.java:244)
	at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)
	... 5 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set 0.8.x/changelog-2019-03-17-0104-consolidate-inventory-snapshot-tables.xml::1552802756393-7::jmiranda (generated):
     Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number
`, `bin_location_name`):
          Caused By: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number`, `bin_location_name`):
          Caused By: Specified key was too long; max key length is 3072 bytes
	at liquibase.ChangeSet.execute(ChangeSet.java:238)
	at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
	at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
	at liquibase.Liquibase.update(Liquibase.java:112)
	at liquibase.Liquibase$update.call(Unknown Source)
	at BootStrap$_closure1.doCall(BootStrap.groovy:447)
	... 8 more
Caused by: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `openboxes`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`, `lot_number`,
 `bin_location_name`)
	at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
	at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
	at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
	at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
	at liquibase.ChangeSet.execute(ChangeSet.java:214)
	... 13 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes
	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:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
	at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
	at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
	... 17 more

[jmiranda]

This might be caused by the fact that I created the database like this

create database openboxes;

instead of like this

create database openboxes default charset utf8;

[jmiranda]

mysql> alter database openboxes default charset utf8;
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[jmiranda]

Recreated database to see if that fixes the issue.

[jmiranda]

Recreating the database fixed this issue. Need to investigate how to make this work with utf8mb4;

[jmiranda]

Long term we should consider using prefix indices.
https://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes

[jmiranda]

I just ran into this again while attempting to deploy OpenBoxes to a Digital Ocean managed database using MySQL 8. The ALTER DATABASE command had no effect

alter database openboxes default charset utf8;

so I just used a index prefix length for multiple columns.

ALTER TABLE `tireco`.`inventory_snapshot` ADD CONSTRAINT `inventory_snapshot_key` UNIQUE (`date`, `location_id`, `product_code`(25), `lot_number`(150), `bin_location_name`(100));

The lengths above were derived by querying live data to find the appropriate size.

Max length of product.product_code

mysql> select max(length(product_code)) from product;
+---------------------------+
| max(length(product_code)) |
+---------------------------+
|                        11 |
+---------------------------+
1 row in set (0.01 sec)

Max length of inventory_item.lot_number

mysql> select max(length(lot_number)) from inventory_item;
+-------------------------+
| max(length(lot_number)) |
+-------------------------+
|                     106 |
+-------------------------+
1 row in set (0.03 sec)

Max length of location.name

mysql> select max(length(location.name)) from location join location_type on location.location_type_id = location_type.id where location_type_code ='BIN_LOCATION';
+----------------------------+
| max(length(location.name)) |
+----------------------------+
|                         78 |
+----------------------------+
1 row in set (0.05 sec)

[EWaterman]

When this is resolved, please double check https://github.com/openboxes/openboxes/blob/develop/src/test/resources/testcontainers/my.cnf as there might be changes required there as well.