SET GLOBAL FOREIGN KEY CHECKS Syntax Error Migrating from 1.3.172 to 1.3.176

[Paul Erdos]

Migrating from 1.3.172 to 1.3.176 I get the following error

giu 16, 2016 2:51:21 PM Errmsg$DefaultErrorHandler errmsg
GRAVE: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SET GLOBAL[*] 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, JAVA_OBJECT_SERIALIZER, LOGSIZE, FOREIGN_KEY_CHECKS"; SQL statement:
SET GLOBAL FOREIGN_KEY_CHECKS = 0; [42001-176]


seems what I previously used ("SET GLOBAL FOREIGN_KEY_CHECKS = 0;") to temporarily disable foreign key check now is not supported anymore, the above code was needed to update older versions of the schema to current one, changing it or removing would spell disaster for old databases schema of my application which need to be upgraded.

Any way around?

[Noel Grandin]

Probably the only thing you could do would be to 

(a) catch that exception

(b) spawn a subprocess which invokes a custom program which uses the last version of H2 to support that check to upgrade your DB to current schema, then close the DB

(c) then the main program can re-open the DB and proceed with further upgrading

​

[Noel Grandin]

or you could use

  SET REFERENTIAL_INTEGRITY FALSE

  http://h2database.com/html/grammar.html#set_referential_integrity

which it looks like we created to supercede that prior command.

[Thomas Mueller Graf]

Hi,

I don't think "SET GLOBAL FOREIGN_KEY_CHECKS" was ever supported by H2. This seems to be a MySQL feature.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.