MySQL JDBC – Differences from MySQL Workbench Behavior

MySQLmysql-5.5mysql-workbenchsyntax

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.

Best Answer

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.

Related Question