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.
ROOT
.all_tables
(" while the error says "CREATE TABLE IF NOT EXISTSROOT
.`all_table'": there's a missing backtick in the second version, plus the table names are different. Chances are you have a typo somewhere.START TRANSACTION; CREATE; [COMMIT]; CREATE; [COMMIT]; COMMIT;
Drop theSTART TRANSACTION
and the finalcommit
.