0

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.

7
  • 1
    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?
    – Vérace
    Aug 28, 2014 at 0:02
  • I construct it using java string builder. With a whole lot of 'append'. Why does it matter how I create the string?
    – TacB0sS
    Aug 28, 2014 at 4:49
  • 1
    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.
    – watery
    Aug 28, 2014 at 6:17
  • 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.
    – jynus
    Aug 28, 2014 at 7:25
  • @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.
    – TacB0sS
    Aug 28, 2014 at 9:10

1 Answer 1

3

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.

1
  • Good call... when I break it to two calls it works fine. Thanks.
    – TacB0sS
    Aug 28, 2014 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.