-1

I am struggling with HikariCP + with Direct SQL query execution. I am getting below error for creating the table. If I copy SQL query and execute in MYSQL COMMAND it works but not from java.

Error: java.sql.SQLSyntaxErrorException: 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

Versions:

  • HikariCP- compile group: 'com.zaxxer', name: 'HikariCP', version: '3.3.1'

  • MYSQL Driver- compile group: 'mysql', name:'mysql-connector-java', version: '8.0.15'

  • MYSQL SERVER - docker run --name mysql -p 3306:3306 -e ALLOW_EMPTY_PASSWORD=yes -e MYSQL_DATABASE=tp --rm bitnami/mysql

I don't know what's wrong in the query it is mentioning. I have tried all the possibilities I can.

   root
     CID-{} 2019-07-25 11:30:24,235 [DEBUG] - [Driver class com.mysql.cj.jdbc.Driver found in Thread context class loader sun.misc.Launcher$AppClassLoader@6d06d69c] - HikariConfig:900
     CID-{} 2019-07-25 11:30:24,518 [DEBUG] - [HikariPool-1 - configuration:] - HikariConfig:1020
     CID-{} 2019-07-25 11:30:24,536 [DEBUG] - [allowPoolSuspension.............false] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,536 [DEBUG] - [autoCommit......................true] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,542 [DEBUG] - [catalog.........................none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,543 [DEBUG] - [connectionInitSql...............none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,543 [DEBUG] - [connectionTestQuery.............none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,544 [DEBUG] - [connectionTimeout...............30000] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,544 [DEBUG] - [dataSource......................none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,545 [DEBUG] - [dataSourceClassName.............none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,546 [DEBUG] - [dataSourceJNDI..................none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,548 [DEBUG] - [dataSourceProperties............{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, prepStmtCacheSize=250}] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,549 [DEBUG] - [driverClassName................."com.mysql.cj.jdbc.Driver"] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,550 [DEBUG] - [healthCheckProperties...........{}] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,550 [DEBUG] - [healthCheckRegistry.............none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,551 [DEBUG] - [idleTimeout.....................600000] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,554 [DEBUG] - [initializationFailTimeout.......1] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,554 [DEBUG] - [isolateInternalQueries..........false] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,556 [DEBUG] - [jdbcUrl.........................jdbc:mysql://localhost:3306] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,556 [DEBUG] - [leakDetectionThreshold..........0] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,557 [DEBUG] - [maxLifetime.....................1800000] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,557 [DEBUG] - [maximumPoolSize.................10] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,557 [DEBUG] - [metricRegistry..................none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,558 [DEBUG] - [metricsTrackerFactory...........none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,558 [DEBUG] - [minimumIdle.....................10] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,559 [DEBUG] - [password........................<masked>] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,559 [DEBUG] - [poolName........................"HikariPool-1"] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,560 [DEBUG] - [readOnly........................false] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,561 [DEBUG] - [registerMbeans..................false] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,561 [DEBUG] - [scheduledExecutor...............none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,562 [DEBUG] - [schema..........................none] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,562 [DEBUG] - [threadFactory...................internal] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,563 [DEBUG] - [transactionIsolation............default] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,563 [DEBUG] - [username........................"root"] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,564 [DEBUG] - [validationTimeout...............5000] - HikariConfig:1052
     CID-{} 2019-07-25 11:30:24,567 [INFO ] - [HikariPool-1 - Starting...] - HikariDataSource:80
     CID-{} 2019-07-25 11:30:27,299 [DEBUG] - [HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@42d8062c] - HikariPool:566
     CID-{} 2019-07-25 11:30:27,303 [INFO ] - [HikariPool-1 - Start completed.] - HikariDataSource:82
     CID-{} 2019-07-25 11:30:27,306 [DEBUG] - [ SQl QUERY : [CREATE SCHEMA IF NOT EXISTS ${schemaName} ;]] - Sql2:76
     CID-{} 2019-07-25 11:30:27,306 [DEBUG] - [ SQl QUERY : [CREATE SCHEMA IF NOT EXISTS ${schemaName} ;]] - Sql2:76
     CID-{} 2019-07-25 11:30:27,337 [DEBUG] - [ SQl QUERY : [CREATE SCHEMA IF NOT EXISTS REFS ;]] - Sql2:79
     CID-{} 2019-07-25 11:30:27,337 [DEBUG] - [ SQl QUERY : [CREATE SCHEMA IF NOT EXISTS REFS ;]] - Sql2:79
     CID-{} 2019-07-25 11:30:27,407 [DEBUG] - [HikariPool-1 - Pool stats (total=1, active=1, idle=0, waiting=0)] - HikariPool:417
     CID-{} 2019-07-25 11:30:27,439 [DEBUG] - [ SQl QUERY : [USE ${schemaName}; CREATE TABLE IF NOT EXISTS ${tableName} (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);]] - Sql2:76
     CID-{} 2019-07-25 11:30:27,439 [DEBUG] - [ SQl QUERY : [USE ${schemaName}; CREATE TABLE IF NOT EXISTS ${tableName} (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);]] - Sql2:76
     CID-{} 2019-07-25 11:30:27,441 [DEBUG] - [ SQl QUERY : [USE REFS; CREATE TABLE IF NOT EXISTS KUMARREFS (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);]] - Sql2:79
     CID-{} 2019-07-25 11:30:27,441 [DEBUG] - [ SQl QUERY : [USE REFS; CREATE TABLE IF NOT EXISTS KUMARREFS (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);]] - Sql2:79
     CID-{} 2019-07-25 11:30:27,508 [INFO ] - [HikariPool-1 - Shutdown initiated...] - HikariDataSource:350
     CID-{} 2019-07-25 11:30:27,509 [DEBUG] - [HikariPool-1 - Before shutdown stats (total=1, active=0, idle=1, waiting=0)] - HikariPool:417
     CID-{} 2019-07-25 11:30:27,550 [DEBUG] - [HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@42d8062c: (connection evicted)] - PoolBase:129
     CID-{} 2019-07-25 11:30:27,571 [DEBUG] - [HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@3ceaa46] - HikariPool:730
     CID-{} 2019-07-25 11:30:27,580 [DEBUG] - [HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@3ceaa46: (connection evicted)] - PoolBase:129
     CID-{} 2019-07-25 11:30:27,581 [DEBUG] - [HikariPool-1 - After shutdown stats (total=0, active=0, idle=0, waiting=0)] - HikariPool:417
     CID-{} 2019-07-25 11:30:27,582 [INFO ] - [HikariPool-1 - Shutdown completed.] - HikariDataSource:352
     java.sql.SQLSyntaxErrorException: 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 'CREATE TABLE IF NOT EXISTS KUMARREFS (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MO' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
        at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
        at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
        at com.test.ref.sql2.Sql2.executeStatement(Sql2.java:86)
Slok
  • 576
  • 1
  • 12
  • 27
  • Accodring to the full error message you are probably missing a `;` before `CREATE TABLE IF NOT EXISTS KUMARREFS` – juergen d Jul 25 '19 at 03:48
  • You can actually reduce your question to the error message and the query. The rest is not relevant. – juergen d Jul 25 '19 at 03:49
  • it has semicolon before `CREATE TABLE IF NOT EXISTS KUMARREFS `. I thought it will help others to see my config. – Slok Jul 25 '19 at 04:11

2 Answers2

0

One troubleshooting tip is to print your query to your console. This will show you if your query is really the same as the SQL query that you used directly in MYSQL COMMAND.

I recently have been doing a lot of work with MSQL and this has troubleshooting tip has fixed a lot of my similar errors.

John Peabody
  • 39
  • 4
0

I got my issue fixed. Issue is we cannot use two queries in execution with jdbc. After removing USE REFS; All good.

Error:

USE REFS; CREATE TABLE IF NOT EXISTS KUMARREFS (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);

Working:

CREATE TABLE IF NOT EXISTS ${tableName} (ID VARCHAR(50) NULL, CONTENT TEXT NULL, MODIFIED_DATE timestamp DEFAULT CURRENT_TIMESTAMP);

Slok
  • 576
  • 1
  • 12
  • 27