Search code examples
javamysqlprepared-statementmysql-5.6

A sql query that executes without any errors on mysql client fails with a syntax error when executed through code

I need to add a partition to an already partitioned table. My code adds a partition p190409 that would hold some data. The query is as follows:

alter table db.table drop partition future;
alter table db.table add partition (partition p190409 values less than (to_days('2019-04-09 11:50:06')));
alter table db.table add partition (partition future values less than (MAXVALUE));

The same while working perfectly fine on a mysql client, yields the following error through code:

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 'alter table db.table add partition (partition p190409 values less' at line 1

Solution

  • Looks like the method you are using to execute the query only admit one at the time, so instead off executing all queries at once do it one by one:

    executeUpdate("alter table db.table drop partition future");
executeUpdate("alter table db.table add partition (partition p190409 values less than (to_days('2019-04-09 11:50:06')))");
executeUpdate("table db.table add partition (partition future values less than (MAXVALUE))");