Hi, You caught that error, because that the MySQL query incomparable with the h2 syntax. (I suppose) look at http://www.h2database.com/html/grammar.html#alter_table_add. This case contains two problems: 1. You should remove the keyword "COLUMN" from your query. 2. Second, add multiple columns at the same time is available with another syntax - ALTER TABLE `..` ADD (,) For example: ALTER TABLE `TEST` ADD (`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, user_name VARCHAR (384) NOT NULL DEFAULT 'john.doe');
пятница, 29 сентября 2017 г., 17:08:25 UTC+3 пользователь gb co написал: > > Hi, > I'm trying to run this query on a H2 DB MYSQL mode instance: > ALTER TABLE `data` > ADD COLUMN `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, > ADD COLUMN `last_modified_date` TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, > ADD COLUMN user_name varchar(384) NOT NULL DEFAULT 'john.doe'; > > The query works fine in MySQL but fails in H2 > > Any ideas? > > The error i get it : > java.lang.IllegalStateException: Failed to load ApplicationContext > at > org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124) > at > org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:83) > at > org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:117) > at > org.springframework.test.context.support.DependencyInjectionTestExecutionListener.prepareTestInstance(DependencyInjectionTestExecutionListener.java:83) > at > org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:230) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:228) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:287) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:289) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:247) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94) > at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) > at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) > at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) > at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) > at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) > at > org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) > at > org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) > at org.junit.runners.ParentRunner.run(ParentRunner.java:363) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191) > at > org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) > at > org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) > Caused by: org.springframework.beans.factory.BeanCreationException: Error > creating bean with name > 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': > Invocation of init method failed; nested exception is > org.springframework.jdbc.datasource.init.ScriptStatementFailedException: > Failed to execute SQL script statement #966 of class path resource > [db/migration.sql]: ALTER TABLE `data` ADD COLUMN `creation_date` TIMESTAMP > NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN `last_modified_date` > TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name > varchar(384) NOT NULL DEFAULT 'john.doe'; nested exception is > org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE > ""INTENTS"" ADD COLUMN ""CREATION_DATE"" TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP,[*] ADD COLUMN ""LAST_MODIFIED_DATE"" TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT NULL > DEFAULT 'john.doe' "; SQL statement: > ALTER TABLE `data` ADD COLUMN `creation_date` TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP, ADD COLUMN `last_modified_date` TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT NULL > DEFAULT 'john.doe' [42000-192] > at > org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628) > at > org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) > at > org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) > at > org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) > at > org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) > at > org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) > at > org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) > at > org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) > at > org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866) > at > org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) > at > org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:128) > at > org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:60) > at > org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.delegateLoading(AbstractDelegatingSmartContextLoader.java:108) > at > org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.loadContext(AbstractDelegatingSmartContextLoader.java:251) > at > org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:98) > at > org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:116) > ... 25 more > Caused by: > org.springframework.jdbc.datasource.init.ScriptStatementFailedException: > Failed to execute SQL script statement #966 of class path resource > [db/migration.sql]: ALTER TABLE `data` ADD COLUMN `creation_date` TIMESTAMP > NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN `last_modified_date` > TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name > varchar(384) NOT NULL DEFAULT 'john.doe'; nested exception is > org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE > ""INTENTS"" ADD COLUMN ""CREATION_DATE"" TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP,[*] ADD COLUMN ""LAST_MODIFIED_DATE"" TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT NULL > DEFAULT 'john.doe' "; SQL statement: > ALTER TABLE `data` ADD COLUMN `creation_date` TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP, ADD COLUMN `last_modified_date` TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT NULL > DEFAULT 'john.doe' [42000-192] > at > org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:491) > at > org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:238) > at > org.springframework.jdbc.datasource.init.CompositeDatabasePopulator.populate(CompositeDatabasePopulator.java:87) > at > org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48) > at > org.springframework.jdbc.datasource.init.DataSourceInitializer.execute(DataSourceInitializer.java:108) > at > org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:93) > at > org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) > at > org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) > ... 40 more > Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement > "ALTER TABLE ""INTENTS"" ADD COLUMN ""CREATION_DATE"" TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP,[*] ADD COLUMN ""LAST_MODIFIED_DATE"" TIMESTAMP > NOT NULL DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT > NULL DEFAULT 'john.doe' "; SQL statement: > ALTER TABLE `data` ADD COLUMN `creation_date` TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP, ADD COLUMN `last_modified_date` TIMESTAMP NOT NULL > DEFAULT CURRENT_TIMESTAMP , ADD COLUMN user_name varchar(384) NOT NULL > DEFAULT 'john.doe' [42000-192] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) > at org.h2.message.DbException.get(DbException.java:179) > at org.h2.message.DbException.get(DbException.java:155) > at org.h2.message.DbException.getSyntaxError(DbException.java:191) > at org.h2.command.Parser.getSyntaxError(Parser.java:530) > at org.h2.command.Parser.prepareCommand(Parser.java:257) > at org.h2.engine.Session.prepareLocal(Session.java:560) > at org.h2.engine.Session.prepareCommand(Session.java:501) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1202) > at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170) > at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) > at > org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:470) > ... 47 more > > > > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
