SQL SET SQL_MODE= NO_AUTO_VALUE_ON_ZERO

[GoogleCodeExporter]

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?
1.Start h2 with MODE=MySQL 
2. Execute SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO':
3. The following exception will be thrown 
liquibase.exception.DatabaseException: Error executing SQL SET 
SQL_MODE='NO_AUTO_VALUE_ON_ZERO': Syntax error in SQL statement "SET 
SQL_MODE[*]='NO_AUTO_VALUE_ON_ZERO'"; 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, FOREIGN_KEY_CHECKS"; SQL 
statement:
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' [42001-173]
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:56)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:98)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1341)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1325)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:343)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:28)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
    at liquibase.Liquibase.update(Liquibase.java:135)
    at com.groupon.affnet.test.DbTest.initializeDatabase(DbTest.java:27)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
    at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:564)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:213)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:138)
    at org.testng.internal.TestMethodWorker.invokeBeforeClassMethods(TestMethodWorker.java:175)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:107)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1224)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1149)
    at org.testng.TestNG.run(TestNG.java:1057)
    at org.apache.maven.surefire.testng.TestNGExecutor.run(TestNGExecutor.java:70)
    at org.apache.maven.surefire.testng.TestNGDirectoryTestSuite.execute(TestNGDirectoryTestSuite.java:108)
    at org.apache.maven.surefire.testng.TestNGProvider.invoke(TestNGProvider.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
    at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:172)
    at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:104)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:70)

What is the expected output? What do you see instead?
I would have expected the sql to execute normally without exception

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

Do you know a workaround?
nope

What is your use case, meaning why do you need this feature?
testing, I'm spinning a H2 db in MySQL mode in order to test init scripts

How important/urgent is the problem for you?
medium

Please provide any additional information below.
looks very similar to: https://code.google.com/p/h2database/issues/detail?id=487

Original issue reported on code.google.com by gargiulo...@gmail.com on 20 Aug 2013 at 4:09

[GoogleCodeExporter]

Hi,

Thanks! I will not work on this issue right now, but if you want to have it 
fixed: patches are welcome! (with test case, see 
http://h2database.com/html/build.html#providing_patches ).

Original comment by thomas.t...@gmail.com on 21 Nov 2013 at 1:49

• Changed state: PatchesWelcome

[GoogleCodeExporter]

Hi,
any news related to the above issue? Is there a possible-already-planned 
solution or is there any plan for such a solution in the near future?

Thanks.

Original comment by peteanus...@gmail.com on 6 Apr 2015 at 11:26