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

Question

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

I've to write an API that would be scheduled to run everyday, to add a partition corresponding to that day — hisdudeness
– hisdudeness, Commented Apr 9, 2019 at 6:41
Many MySQL API's cannot execute more than one query in a call. Try splitting it into three queries. — Nick
– Nick, Commented Apr 9, 2019 at 6:42
@cricketeer it seems I should have posted it as an answer! :-) — Nick
– Nick, Commented Apr 9, 2019 at 8:56

José Carlos Guevara Turruelles · Accepted Answer · 2019-04-09 07:41:59Z

1

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))");

edited Apr 9, 2019 at 7:41

answered Apr 9, 2019 at 6:50

José Carlos Guevara Turruelles

4819 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

José Carlos Guevara Turruelles Over a year ago

@JoopEggen thanks for your observations, I have correct them :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related