QueueMetrics bug that has something to do with a Connector/J auto-created union

[AlexForster]

QueueMetrics: version 1.6.3
Connector/J: version 5.1.14
MySQL: version 5.5.8
Servlet Container: Apache Tomcat 6.0.29
Java Runtime: 1.6.0_23-b05
Operating System: Windows Server 2008 R2

Immediately when visiting http://localhost/queuemetrics, after accepting the TOS and completing the database upgrade, I receive the following error-

Error (tech) in class 'it.loway.app.queuemetrics.autenticazione.caricaDatiIniziali'.
--- Inner Exception ---
Exception: it.loway.tpf.common.exceptions.TechException - Stack trace: - Problema DB: - com.mysql.jdbc.exceptions.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 'TYPE=HEAP SELECT id_location, loc_name, read_key from locations WHERE id_locatio' at line 1 - at it.loway.tpf.SQL.execSql(SQL.java:101) - at it.loway.app.queuemetrics.FA.makeSqlUnionLt(FA.java:1103) - at it.loway.app.queuemetrics.autenticazione.caricaDatiIniziali.doRun(caricaDatiIniziali.java:186) - at it.loway.tpf.transaction.servlets.LowayTransactionController.runVerb(LowayTransactionController.java:262) - at it.loway.tpf.transaction.servlets.LowayTransactionController.serveRequest(LowayTransactionController.java:552) - at it.loway.tpf.transaction.servlets.LowayTransactionController.serveRequestWrapper(LowayTransactionController.java:373) - at it.loway.tpf.transaction.servlets.LowayTransactionController.doGet(LowayTransactionController.java:217) - at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) - at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) - at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) - at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) - at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) - at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) - at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) - at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) - at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) - at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298) - at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861) - at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579) - at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584) - at java.lang.Thread.run(Thread.java:662)
--- End Inner Exception ---

Running the MySQL query logger, this is what's happening on the backend-

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.8-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
101228 11:36:14       1 Connect   queuemetrics@localhost on queuemetrics
          1 Query   /* mysql-connector-java-5.1.14 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
          1 Query   /* mysql-connector-java-5.1.14 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
          1 Query   SHOW COLLATION
          1 Query   SET NAMES utf8mb4
          1 Query   SET character_set_results = NULL
          1 Query   SET autocommit=1
          1 Query   SELECT version_id FROM dbversion LIMIT 1
          1 Query   SELECT lic_approved FROM dbversion LIMIT 1
          1 Quit   
          2 Connect   queuemetrics@localhost on queuemetrics
          2 Query   /* mysql-connector-java-5.1.14 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
          2 Query   /* mysql-connector-java-5.1.14 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
          2 Query   SHOW COLLATION
          2 Query   SET NAMES utf8mb4
          2 Query   SET character_set_results = NULL
          2 Query   SET autocommit=1
          2 Query   SET net_write_timeout=600
          2 Query   SELECT nome_coda, LCASE(composizione_coda) as composizione_coda, id_coda, visibility_key, q_direction   FROM code_possibili  WHERE q_frontpage = 1  ORDER BY nome_coda
          2 Query   SET net_write_timeout=60
          2 Query   SET net_write_timeout=600
          2 Query   SELECT LCASE(A.nome_agente) as nome_agente, A.descr_agente, L.loc_name, L.loc_description, A.current_terminal, A.vnc_url, A.xmpp_address, A.chiave_agente, G.group_name, G.group_icon, G.id_group   FROM agenti_noti as A   LEFT JOIN locations as L     ON A.location = L.id_location   LEFT JOIN agent_groups as G    ON A.group_by = G.id_group   ORDER BY A.descr_agente
          2 Query   SET net_write_timeout=60
          2 Query   CREATE TEMPORARY TABLE temp_union_211644 TYPE=HEAP SELECT id_location, loc_name, read_key from locations WHERE id_location = -1
          2 Quit   

[AlexForster]

Also it's probably worth noting that I have tried both the previous version of Connector/J (5.0.8 ) and the previous version of QueueMetrics (1.6.2), and that I've tried this on two different machines with all fresh installs of the same software (including the operating system).

[marcos]

Hi Alex.

I'm trying to replicate here the problem installing MySQL 5.5.8 on a Windows debug machine.
Just one question to better understand your environment: did you installed QueueMetrics 1.6.3 from scratch or it was an upgrade of a previous QM version?

Thank you and regards,

Marco.

[marcos]

Hi Alex.

Thanks for opening this thread and to the information you provided. I was able to replicate the problem with a debug machine and, effectively, there are some queries that are not working with the latest MySQL server (5.5..
I've opened a bug in our bug tracker and it will be fixed as soon as possible.

In the meantime I suggest you, if possible, to use an old SQL server engine. We tested with 5.1.41 and all seems working fine.

Marco.

[AlexForster]

Sorry about the late reply. MySql 5.1.41 will be fine as a workaround. Thank you for the suggestion.

[marcos]

Hi Alex.

Thank you for the valuable information provided.
I can confirm that we fixed the problem with QueueMetrics and that will be present since next QueueMetrics 1.7 version.
I've tested the next coming QueueMetrics 1.7. with MySQL 5.5.8 and seems working fine.

Thank you again.
Marco.