search

josefmezera / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

H2 mode MySQL- SET foreign_key_checks = 0; fails with an error "Syntax error in SQL statement" #487

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.Start h2 with MODE=MySQL 
2.Execute the query : SET foreign_key_checks = 0;
3.The following error will be returned: Caused by: 
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SET 
FOREIGN_KEY_CHECKS[*] = 0 "; expected "@, AUTOCOMMIT, MVCC, EXCLUSIVE, 
IGNORECASE, PASSWORD, SALT, MODE, COMPRESS_LOB, DATABASE, COLLATION, 
BINARY_COLLATION, CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, 
DEFAULT_TABLE_TYPE, CREATE, HSQLDB.DEFAULT_TABLE_TYPE, PAGE_STORE, CACHE_TYPE, 
FILE_LOCK, DB_CLOSE_ON_EXIT, AUTO_SERVER, AUTO_SERVER_PORT, AUTO_RECONNECT, 
ASSERT, ACCESS_MODE_DATA, OPEN_NEW, JMX, PAGE_SIZE, RECOVER, NAMES, SCHEMA, 
DATESTYLE, SEARCH_PATH, SCHEMA_SEARCH_PATH, LOGSIZE"; SQL statement:
SET foreign_key_checks = 0 [42001-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.getSyntaxError(DbException.java:194)
    at org.h2.command.Parser.getSyntaxError(Parser.java:491)
    at org.h2.command.Parser.parseSet(Parser.java:4676)
    at org.h2.command.Parser.parsePrepared(Parser.java:407)
    at org.h2.command.Parser.parse(Parser.java:279)
    at org.h2.command.Parser.parse(Parser.java:255)
    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:425)
    at org.h2.engine.Session.prepareCommand(Session.java:374)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:167)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:186)
    ... 51 more

What is the expected output? What do you see instead?

FOREIGN_KEY_CHECKS should be set to 0.

What version of the product are you using? On what operating system, file
system, and virtual machine?

- H2 v1.3.172
- Windows 7 64bit
- java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

Do you know a workaround?
No

What is your use case, meaning why do you need this feature?

How important/urgent is the problem for you?

Please provide any additional information below.

Original issue reported on code.google.com by queeq...@gmail.com on 12 Jul 2013 at 5:02

GoogleCodeExporter commented 9 years ago 
Sorry, we don't support disabling foreign key constraints.
And it's not something we are likely to add.
If you don't want foreign key constraints, rather just don't use them.

Original comment by noelgrandin on 15 Jul 2013 at 8:59

GoogleCodeExporter commented 9 years ago 
Turns out I was wrong again, we do support this mode.
Fixed in SVN, by mapping this command to "SET REFERENTIAL_INTEGRITY FALSE"

Original comment by noelgrandin on 16 Jul 2013 at 8:28