Creation of tables fails when using MySQL and UTF-8 as default encoding (MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes)

[glassfishrobot]

When using a mq cluster with a mysql database and the default database with UTF-8 as the default character set. The creation of the database tables fails:

com.sun.messaging.jmq.jmsserver.util.BrokerException: [B3073]: Failed to create database table(s) - jdbc:mysql://10.39.51.118/jpa2?UseCompression=true&autoReconnect=true 	at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.checkStore(JDBCStore.java:3889)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.<init>(JDBCStore.java:144)
	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:526)
	at java.lang.Class.newInstance(Class.java:374)
	at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:169)
	at com.sun.messaging.jmq.jmsserver.Globals.getStore(Globals.java:995)
	at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:955)
	at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:456)
	at com.sun.messaging.jmq.jmsserver.BrokerProcess.start(BrokerProcess.java:164)
	at com.sun.messaging.jmq.jmsserver.DirectBrokerProcess.start(DirectBrokerProcess.java:92)
	at com.sun.messaging.jmq.jmsclient.runtime.impl.BrokerInstanceImpl.start(BrokerInstanceImpl.java:206)
	at com.sun.messaging.jms.blc.EmbeddedBrokerRunner.start(EmbeddedBrokerRunner.java:331)
	at com.sun.messaging.jms.blc.LifecycleManagedBroker.start(LifecycleManagedBroker.java:454)
	at com.sun.messaging.jms.ra.ResourceAdapter.start(ResourceAdapter.java:383)
	at com.sun.enterprise.connectors.jms.system.ActiveJmsResourceAdapter$1.run(ActiveJmsResourceAdapter.java:364)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sun.enterprise.connectors.jms.system.ActiveJmsResourceAdapter.startResourceAdapter(ActiveJmsResourceAdapter.java:357)
	at com.sun.enterprise.connectors.ActiveOutboundResourceAdapter.init(ActiveOutboundResourceAdapter.java:129)
	at com.sun.enterprise.connectors.inbound.ActiveInboundResourceAdapterImpl.init(ActiveInboundResourceAdapterImpl.java:90)
	at com.sun.enterprise.connectors.ActiveRAFactory.instantiateActiveResourceAdapter(ActiveRAFactory.java:135)
	at com.sun.enterprise.connectors.ActiveRAFactory.createActiveResourceAdapter(ActiveRAFactory.java:106)
	at com.sun.enterprise.connectors.service.ResourceAdapterAdminServiceImpl.createActiveResourceAdapter(ResourceAdapterAdminServiceImpl.java:212)
	at com.sun.enterprise.connectors.service.ResourceAdapterAdminServiceImpl.createActiveResourceAdapter(ResourceAdapterAdminServiceImpl.java:348)
	at com.sun.enterprise.connectors.ConnectorRuntime.createActiveResourceAdapter(ConnectorRuntime.java:357)
	at com.sun.enterprise.connectors.jms.system.ActiveJmsResourceAdapter.initializeService(ActiveJmsResourceAdapter.java:2196)
	at com.sun.enterprise.v3.services.impl.ServiceInitializerHandler.onAcceptInterest(ServiceInitializerHandler.java:105)
	at com.sun.grizzly.SelectorHandlerRunner.handleSelectedKey(SelectorHandlerRunner.java:301)
	at com.sun.grizzly.SelectorHandlerRunner.handleSelectedKeys(SelectorHandlerRunner.java:263)
	at com.sun.grizzly.SelectorHandlerRunner.doSelect(SelectorHandlerRunner.java:200)
	at com.sun.grizzly.SelectorHandlerRunner.run(SelectorHandlerRunner.java:132)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:724)
Caused by: com.sun.messaging.jmq.jmsserver.util.BrokerException: [B4236]: Failed to create database table MQTMLRJMSBG41SSemRecSys1005apaxode
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommBaseDAOImpl.createTable(CommBaseDAOImpl.java:254)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBTool.createTables(DBTool.java:313)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBTool.createTables(DBTool.java:275)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBTool.createTables(DBTool.java:270)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.checkStore(JDBCStore.java:3880)
	... 35 more
Caused by: com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.MQSQLException: [CREATE TABLE MQTMLRJMSBG41SSemRecSys1005apaxode (XID VARCHAR(256) NOT NULL,LOG_RECORD MEDIUMBLOB NOT NULL,NAME VARCHAR(100) NOT NULL,BROKER_ID VARCHAR(100) NOT NULL,CREATED_TS BIGINT NOT NULL,UPDATED_TS BIGINT NOT NULL,PRIMARY KEY(XID)) ENGINE=InnoDB]: Specified key was too long; max key length is 767 bytes[42000, 1071]
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.wrapSQLException(CommDBManager.java:1101)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommBaseDAOImpl.createTable(CommBaseDAOImpl.java:249)
	... 39 more
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:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.Util.getInstance(Util.java:384)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1648)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1567)
	at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:842)
	at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommBaseDAOImpl.createTable(CommBaseDAOImpl.java:176)
	... 39 more

The problem is that UTF-8 causes the XID column to take 3 times the bytes as normal and because it is 256 byte (should be 255) the table can't be created because there is a limit on key size for MySQL.

A workaround is to create the 2 tables which weren't create manually:

CREATE TABLE MQTMLRJMSBG41SSemRecSys1005apaxode (
    XID VARCHAR(255) NOT NULL,
    LOG_RECORD MEDIUMBLOB NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    BROKER_ID VARCHAR(100) NOT NULL,
    CREATED_TS BIGINT NOT NULL,
    UPDATED_TS BIGINT NOT NULL,
    PRIMARY KEY(XID)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `MQJMSBG41SSemRecSys1005apaxode` (
  `NAME` varchar(100) NOT NULL,
  `BROKER_ID` varchar(100) NOT NULL,
  `CREATED_TS` bigint(20) NOT NULL,
  `UPDATED_TS` bigint(20) NOT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This will also prevent the system to try to recreate the tables and fail with a table already exists error.

It might help to use the following config but I didn't tested this:

imq.persist.jdbc.vendorName.tableoption=CHARSET=latin1

Environment

Java 1.7.0_25, Mysql Percona Cluster 5.5

Affected Versions

[4.4, 4.4u1, 4.4u2, 4.5, 4.5.1, 4.5.2, 5.0]

[glassfishrobot]

@glassfishrobot Commented
Reported by manuel_b

[glassfishrobot]

@glassfishrobot Commented
This issue was imported from java.net JIRA MQ-320

[glassfishrobot]

• Issue Imported From: https://github.com/javaee/openmq/issues/320
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @amykang2020