Uploaded image for project: 'Red Hat Data Grid'
  1. Red Hat Data Grid
  2. JDG-3014

Configuration of cache store for mysql database failing to start

    XMLWordPrintable

    Details

    • Target Release:
    • Steps to Reproduce:
      Hide

      Define the cache name in environment variables as below :

      CACHE_NAMES=mycache
      MYCACHE_JDBC_STORE_TYPE=string
      DEFAULT_CACHE=mycache
      CACHE_CONTAINER_START=EAGER
      MYCACHE_JDBC_STORE_DATASOURCE=java:jboss/datasources/mysql

      When the pod will be started will get following ERROR:

      06:30:37,359 ERROR [org.infinispan.persistence.jdbc.table.management.MySQLTableManager] (MSC service thread 1-1) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE `ispn_entry_mycache` (id VARCHAR NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))': com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))' at line 1
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_212]
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_212]
      	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_212]
      	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [rt.jar:1.8.0_212]
      	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      	at com.mysql.jdbc.Util.getInstance(Util.java:386)
      	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
      	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
      	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
      	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2814)
      	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1813)
      	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1727)
      	at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:842)
      	at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:430)
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.executeUpdateSql(AbstractTableManager.java:176) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.createTable(AbstractTableManager.java:136) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.start(AbstractTableManager.java:63) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.initializeConnectionFactory(JdbcStringBasedStore.java:193) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.start(JdbcStringBasedStore.java:120) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$startWriter$22(PersistenceManagerImpl.java:1035) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.startStore(PersistenceManagerImpl.java:1064) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persiste
      
      ~~~~~nce.manager.PersistenceManagerImpl.startWriter(PersistenceManagerImpl.java:1027) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$start$0(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at java.util.ArrayList.forEach(ArrayList.java:1257) [rt.jar:1.8.0_212]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.start(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_212]
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_212]
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_212]
      	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_212]
      
      Show
      Define the cache name in environment variables as below : CACHE_NAMES=mycache MYCACHE_JDBC_STORE_TYPE=string DEFAULT_CACHE=mycache CACHE_CONTAINER_START=EAGER MYCACHE_JDBC_STORE_DATASOURCE=java:jboss/datasources/mysql When the pod will be started will get following ERROR: 06:30:37,359 ERROR [org.infinispan.persistence.jdbc.table.management.MySQLTableManager] (MSC service thread 1-1) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE `ispn_entry_mycache` (id VARCHAR NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))' : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_212] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_212] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_212] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [rt.jar:1.8.0_212] at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2814) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1813) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1727) at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:842) at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:430) at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.executeUpdateSql(AbstractTableManager.java:176) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.createTable(AbstractTableManager.java:136) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.start(AbstractTableManager.java:63) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.initializeConnectionFactory(JdbcStringBasedStore.java:193) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.start(JdbcStringBasedStore.java:120) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$startWriter$22(PersistenceManagerImpl.java:1035) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.manager.PersistenceManagerImpl.startStore(PersistenceManagerImpl.java:1064) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persiste ~~~~~nce.manager.PersistenceManagerImpl.startWriter(PersistenceManagerImpl.java:1027) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$start$0(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at java.util.ArrayList.forEach(ArrayList.java:1257) [rt.jar:1.8.0_212] at org.infinispan.persistence.manager.PersistenceManagerImpl.start(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_212] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_212] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_212] at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_212]
    • Sprint:
      DataGrid Sprint #32
    • QE Test Coverage:
      -

      Description

      When I tried to deploy the datagrid73-mysql on OCP and tried to configure <MYCACHE>_JDBC_STORE_TYPE=string and MYCACHE>_JDBC_STORE_DATASOURCE=java:jboss/datasources/mysql.

      On start-up I am getting the following error:

      06:30:37,359 ERROR [org.infinispan.persistence.jdbc.table.management.MySQLTableManager] (MSC service thread 1-1) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE `ispn_entry_mycache` (id VARCHAR NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))': com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))' at line 1
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_212]
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_212]
      	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_212]
      	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [rt.jar:1.8.0_212]
      	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      	at com.mysql.jdbc.Util.getInstance(Util.java:386)
      	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
      	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
      	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
      	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2814)
      	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1813)
      	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1727)
      	at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:842)
      	at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:430)
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.executeUpdateSql(AbstractTableManager.java:176) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.createTable(AbstractTableManager.java:136) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.table.management.AbstractTableManager.start(AbstractTableManager.java:63) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.initializeConnectionFactory(JdbcStringBasedStore.java:193) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.start(JdbcStringBasedStore.java:120) [infinispan-cachestore-jdbc-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$startWriter$22(PersistenceManagerImpl.java:1035) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.startStore(PersistenceManagerImpl.java:1064) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.startWriter(PersistenceManagerImpl.java:1027) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$start$0(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at java.util.ArrayList.forEach(ArrayList.java:1257) [rt.jar:1.8.0_212]
      	at org.infinispan.persistence.manager.PersistenceManagerImpl.start(PersistenceManagerImpl.java:166) [infinispan-core-9.4.6.Final-redhat-00002.jar:9.4.6.Final-redhat-00002]
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_212]
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_212]
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_212]
      	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_212]
      

      When I check the query (CREATE TABLE `ispn_entry_mycache` (id VARCHAR (the column size is missing) NOT NULL, datum BINARY NOT NULL, version BIGINT NOT NULL, PRIMARY KEY (id))')and the code which is forming the query.

      I find, when the datatype is VARCHAR it also needs to define the size of the column in query. Which is missing.

        Attachments

          Activity

            People

            Assignee:
            ooliveir@redhat.com Osni Oliveira
            Reporter:
            rhn-support-danaokar Durgesh Anaokar
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: