You have an error in your SQL syntax

Support
#1

Hello,
I’m fighting this issue now for some time. The automatic update mechanism doesn’t work here. On creation of an index I get an error:

Caused by: java.lang.RuntimeException: 
=================================================
ERROR: Data store update failed. See details below.
=================================================
Error executing SQL script 201210-2-updateRule01.sql
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 index IDX_INVOISARY_RULE_ON_PROVIDED_RULE on INVOISARY_RULE (PROVIDED_R
UL' at line 2
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.executeSqlScript(DbUpdaterEngine.java:389) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.executeScript(DbUpdaterEngine.java:447) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.doUpdate(DbUpdaterEngine.java:252) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.ServerDbUpdater.doUpdate(ServerDbUpdater.java:82) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.updateDatabase(DbUpdaterEngine.java:103) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdateManager.updateDatabase(DbUpdateManager.java:76) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdateManager.applicationInitialized(DbUpdateManager.java:61) ~[na:na]
...

This is the particular SQL line:

create index IDX_INVOISARY_RULE_ON_PROVIDED_RULE on INVOISARY_RULE (PROVIDED_RULE_ID);

If I execute this line in the mysql console on the same server, it works… So I’m thinking it must be the MySQL JDBC driver.

Any ideas?

#2

Hi,
The SQL line from the error message differs from the SQL you are providing below.

One:

create index IDX_INVOISARY_RULE_ON_PROVIDED_RULE on INVOISARY_RULE (PROVIDED_R
UL

Second:

create index IDX_INVOISARY_RULE_ON_PROVIDED_RULE on INVOISARY_RULE (PROVIDED_RULE_ID);

So the most probable explanation is that the application deployed on the server has another version of the 201210-2-updateRule01.sql file with broken SQL.

#3

Hi @AlexBudarov, thanks for your answer.

I thought that the SQL line in the error message is truncated, but if not that’s an interesting point. Although I didn’t change anything manually and the file in the repository is correct.

I fixed this particular error manually, but this problem pops up every now and then, so I will consider your comment the next time I have to investigate and will report back here.

#4

Happened again, this time the whole SQL line is printed in the error log:

=================================================
ERROR: Data store update failed. See details below.
=================================================
Error executing SQL script 201215-2-updateRule01.sql
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 'update INVOISARY_RULE set PROVIDED = true where PROVIDED_RULE_ID is not null' at line 2
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.executeSqlScript(DbUpdaterEngine.java:389) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.executeScript(DbUpdaterEngine.java:447) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.doUpdate(DbUpdaterEngine.java:252) ~[na:na]
        at com.haulmont.cuba.core.sys.dbupdate.ServerDbUpdater.doUpdate(ServerDbUpdater.java:82) ~[na:na]

Contents of 201215-2-updateRule01.sql:

update INVOISARY_RULE set PROVIDED = false where PROVIDED_RULE_ID is null;
update INVOISARY_RULE set PROVIDED = true where PROVIDED_RULE_ID is not null;

… and again, I have no problem running this statement in the mysql console manually.

Interestingly the first line, which is almost the same, throws no exception.

#5

OK, which MySQL version do you use?
Also, can you please show your build.gradle file cleared from password (more specifically, we need dependency string on mysql JDBC driver library).

#6

build.gradle excerpts:

buildscript {
    ext.cubaVersion = '7.2.10'
...
def mysql = 'mysql:mysql-connector-java:8.0.22'
...
configure(coreModule) {

    configurations {
        jdbc
        dbscripts
    }

    dependencies {
        compile(globalModule)
        compileOnly(servletApi)
        jdbc(mysql)
        testRuntime(mysql)

...
task buildUberJar(type: CubaUberJarBuilding) {
    singleJar = true
    appProperties = ['cuba.automaticDatabaseUpdate'    : true,
                     'cuba.dataSource.jdbcUrl'         : 'jdbc:mysql://localhost/dbname?useSSL=false&allowMultiQueries=true&serverTimezone=UTC',
                     'cuba.dataSource.username'        : '...',
                     'cuba.dataSource.password'        : '...',
                     'cuba.dataSource.dbName'          : '...',
                     'cuba.dataSource.host'            : '...',
                     'cuba.dataSource.port'            : '',
                     'cuba.dataSource.connectionParams': '?useSSL=false&allowMultiQueries=true&serverTimezone=UTC',
                     'cuba.dataSource.driverClassName' : 'com.mysql.cj.jdbc.Driver',
                     'cuba.dataSourceProvider'         : 'application']

}

Server version: 5.7.12 MySQL Community Server (GPL)

#9

Changing the delimiter from ; to ^ will resolve the error, but I still don’t know why. I know that ^ will execute the statement in its own transaction, but that shouldn’t be the problem.

Also, the example I gave is a custom made SQL file, but CUBA also auto generates SQL files which won’t work.

Maybe I can make a test case for this.

#10

Ok, I couldn’t reproduce the issue with a new test project, that I run locally. Maybe it has something to do with the Aurora DB engine.

1 Like
#12

A little update: It looks like the error only appears with the newer MySQL 5.7 Aurora database engine.
I didn’t encounter this problem on a project that uses the MySQL 5.6 Aurora database engine.