MySQL JDBC does not behaves as the MySQL Workbench

Question

I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc.

START TRANSACTION;
CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` (
 `_id` BIGINT NOT NULL AUTO_INCREMENT,
 `class_type` VARCHAR(100) NOT NULL,
 `schema` VARCHAR(30) NOT NULL,
 `table_name` VARCHAR(30) NOT NULL,
 `server_id` VARCHAR(30) NULL,
 `fields` VARCHAR(0) NOT NULL, PRIMARY KEY (`_id`),
UNIQUE INDEX `_id_UNIQUE` (`_id` ASC));
COMMIT;

It is a copy of query right before the execution which I compose in order to run via the jdbc which fails time after time with the following error:

com.mysql.jdbc.exceptions.jdbc4.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 'CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_table' at line 2

So technically even if only execute the create table this fails with the same error, only line 1.

BUT, when I run the same query via the MySQL Workbench, it works wonderfully... time and time again!

Any idea what I'm missing?

Thanks.

• Vérace: Can you show us the actual Java string that is sent to the database server? How do you construct it? Can we see that code?
• TacB0sS: I construct it using java string builder. With a whole lot of 'append'. Why does it matter how I create the string?
• watery: What Vérace said. If you pay close attention, your query line 2 is "CREATE TABLE IF NOT EXISTS ROOT.all_tables (" while the error says "CREATE TABLE IF NOT EXISTS ROOT.`all_table'": there's a missing backtick in the second version, plus the table names are different. Chances are you have a typo somewhere.
• jynus: Another extra comment: DDLs are atomic in MySQL, but they force a commit after its execution. Surrounding the two statements in a transaction is not going to work- you have to control the execution on the application manually. You are now doing: START TRANSACTION; CREATE; [COMMIT]; CREATE; [COMMIT]; COMMIT; Drop the START TRANSACTION and the final commit.
• TacB0sS: @watery I copied the query right before the execution, so this is the actual query. when I copy paste it to the workbench it works.

Answer 1

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send.

See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.

• TacB0sS: Good call... when I break it to two calls it works fine. Thanks.