mq
  1. mq
  2. MQ-320

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)

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 4.4, 4.4u1, 4.4u2, 4.5, 4.5.1, 4.5.2, 5.0
    • Fix Version/s: 5.1 (RI-Bug-Fix)
    • Component/s: broker-core
    • Labels:
      None
    • Environment:

      Java 1.7.0_25, Mysql Percona Cluster 5.5

      Description

      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
      

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            amyk
            Reporter:
            manuel_b
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: