Global Collections created or renamed using problematic SQL characters can lead to Report Manager event processing failure.

Document ID : KB000051764
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

If a Global Collection is created or renamed to include problematic SQL characters (such as single or double-quotes), Report Manager will stop processing events and a BadSqlGrammarException gets printed to OneClick's tomcat stdout.log/catalina.out.

Solution:

An example appears at the bottom of this solution of a BadSqlGrammarException printed to OneClick's tomcat stdout.log/catalina.out.

This solution represents a workaround to resume normal Report Manager processing. Be aware that a fix will be delivered in 9.2 H01 to allow Global Collections to be named/renamed in OneClick using previously problematic characters without stopping Report Manager processing. Furthermore, these previously problematic Global Collections will be successfully managed in Report Manager, and will be available for reporting purposes.

In order to resume Report Manager processing, apply the following workaround

  1. Stop OneClick/tomcat.

  • Log on to mysql using "root" credentials such as the following:
    oscmdline>mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting

    Note: If Report Manager has been installed on Windows, the "--defaults-file=../my-spectrum.cnf" option should not be included when logging on.

  • At the MySQL command line, locate the problematic event with event code = 0x110a0a:
    mysql>select * from event where type=0x110a0a order by time desc limit 1\G

    Note: Write down the "event_key" value for this event since this value will be needed in a subsequent step.

  • At the MySQL command line, identify all "modelcreatedestroy" bucket tables in the database using the following command:
    mysql>show tables like '%modelcreatedestroybucket%';

    Note: Write down the first "bucket" table at the top of the output list; it should be named something like "modelcreatedestroybucket4292870144_1273083798969." The numeric suffix in your environment will most likely differ from our sample value.

  • Delete the problematic event from this bucket table.
    In order to do this, you need the event_key and bucket table name captured in previous steps:
    mysql>delete from <insert your bucket table name here> where event_key = <insert your problematic event_key value here> .

    Note: Do not actually type the "<" and ">" characters above. After issuing the delete statement, you should receive a confirmation that a record was deleted such as the following:
    Query OK, 1 row affected (0.00 sec)

  • Start OneClick/tomcat and Report Manager should resume normal event processing.
Example of a BadSqlGrammarException printed to OneClick's tomcat stdout.log/catalina.out
May 5, 2010 2:23:24 PM (SRM/ModelCreateDestroyHandler/bucketReader) (SRM_Model_Create_Destroy_Events) - (ERROR) - Unknown exception encountered while processing name events:  processing halted for all servers
 
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [UPDATE globalcollection SET gc_name = 'Isn't a problem, right?' WHERE gc_unique_key = '4be1b778-
0162-1000-0711-8a2af9470000']; nested exception is 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 't a problem, right?' WHERE gc_unique_key = '4be1b778-0162-1000-0711-8a2af9470000' at line 1 at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:78) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:522) at com.aprisma.spectrum.app.repmgr.dc.db.dao.jdbc.GlobalCollectionDAO.updateCollectionName(GlobalCollectionDAO.java:332) at com.aprisma.spectrum.app.repmgr.dc.event.handler.ModelCreateDestroyHandler.handleGCNameChangeEvent(ModelCreateDestroyHandler.java:915) at com.aprisma.spectrum.app.repmgr.dc.event.handler.ModelCreateDestroyHandler.parseVardata(ModelCreateDestroyHandler.java:390) at com.aprisma.spectrum.app.repmgr.dc.event.handler.ModelCreateDestroyHandler.access$000(ModelCreateDestroyHandler.java:84) at com.aprisma.spectrum.app.repmgr.dc.event.handler.ModelCreateDestroyHandler$HistoricalUpdate.processBucketRecords(ModelCreateDestroyHandler.java:1597) at com.aprisma.spectrum.app.repmgr.dc.event.handler.AbstractBucketReader.readBucket(AbstractBucketReader.java:156) at com.aprisma.spectrum.app.repmgr.dc.event.handler.BucketReaderScheduler$ScheduledReader.run(BucketReaderScheduler.java:140) at com.aprisma.util.thread.JobQueue.runJobThread(JobQueue.java:237) at com.aprisma.util.thread.JobQueue.access$000(JobQueue.java:38) at com.aprisma.util.thread.JobQueue$JobRunnable.run(JobQueue.java:47) at java.lang.Thread.run(Thread.java:619) Caused by: 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 't a problem, right?' WHERE gc_unique_key = '4be1b778-0162-1000-0711-8a2af9470000' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1605) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1524) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225) at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:512) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 12 more