Five Stars

[resolved] Admin Migration from MySql to H2 on another box

Hi, I'm trying to migrate from an older version of the Admin on a box with mysql to a new box using H2 and Taland Admin 5.4.1.
It fails with the error below. In the migrationInfo I also see severe errors where it is trying to create a Backup mysql DB on the NEW box using the old box's mysql login info. There is no mysql db on the NEW box so of course there are a lot of failure messages due to that, before the exception we see below.
The version I'm migrating from is quite old (3.2.3). Any ideas? I'm not sure where the problem is coming from. I'm thinking 3.2.3 might be too old, or perhaps it's something to do with migrating the DB to another box and the script is assuming I'm migrating to the same box?
Is there a way to open the migration in studio to see what it is doing? I don't have a lot of flexibility to try things (ie I can't install the new version on the old box, nor can I install mysql on the new box and move the old version's mysql DB there.) I'm kinda stuck with old stuff on the old box and new stuff on the new box.
Thanks
Pete

Migration will use folder /usr/share/apache-tomcat-7.0.50/Talend-5.4.1/cmdline/studio/migration-workspace/migration_140508_1807_30
Connection from jdbc:mysql://xx.xx.xx.xxx:3306/talend_admin_bk to jdbc:h2:/usr/share/tomcat7/webapps/org.talend.administrator/WEB-INF/database/talend_administrator;AUTO_SERVER=TRUE;MVCC=TRUE
Start Database migration
database schema migration failed.
org.talend.commons.exception.PersistenceException: Exception when saving resource DataStore
at org.talend.repository.databaseprovider.DatabaseRepositoryFactory.initResource(DatabaseRepositoryFactory.java:594)
at org.talend.migration.DatabaseSchemaMigrationDatabaseRepositoryFactory.initialize(DatabaseSchemaMigrationDatabaseRepositoryFactory.java:36)
at org.talend.core.repository.model.ProxyRepositoryFactory.initialize(ProxyRepositoryFactory.java:1462)
at org.talend.migration.DatabaseSchemaMigrationHandler.createDatastoreAndRepositoryFactory(DatabaseSchemaMigrationHandler.java:150)
at org.talend.migration.TalendMigrationApplication.start(TalendMigrationApplication.java:199)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:619)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:574)
at org.eclipse.equinox.launcher.Main.run(Main.java:1407)
at org.eclipse.equinox.launcher.Main.main(Main.java:1383)
Caused by: org.eclipse.emf.teneo.hibernate.HbMapperException: Exception when saving resource DataStore
at org.eclipse.emf.teneo.hibernate.resource.HibernateResource.loadResource(HibernateResource.java:278)
at org.eclipse.emf.teneo.resource.StoreResource.load(StoreResource.java:277)
at org.talend.repository.databaseprovider.DatabaseRepositoryFactory.initResource(DatabaseRepositoryFactory.java:592)
... 17 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.eclipse.emf.teneo.hibernate.resource.HibernateResource.loadUsingDefinedQueries(HibernateResource.java:355)
at org.eclipse.emf.teneo.hibernate.resource.HibernateResource.loadFromStore(HibernateResource.java:320)
at org.eclipse.emf.teneo.hibernate.resource.HibernateResource.loadResource(HibernateResource.java:272)
... 19 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'project0_.type' in 'field list'
at sun.reflect.GeneratedConstructorAccessor7.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 29 more
Main infos are in: /usr/share/apache-tomcat-7.0.50/Talend-5.4.1/cmdline/studio/migration-workspace/migration_140508_1807_30/migrationLog
Detailed technical infos are in: /usr/share/apache-tomcat-7.0.50/Talend-5.4.1/cmdline/studio/migration-workspace/migration_140508_1807_30/migrationTechnicalInfo
1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: [resolved] Admin Migration from MySql to H2 on another box

I did figure this out. It was a permissions thing for the root user in mysql. It was only allowed to make DB changes when logging with "localhost" as the connect string. When I changed the in the login string to "localhost" instead of the actual machine name I was able to proceed.
Thanks
Pete
6 REPLIES
Moderator

Re: [resolved] Admin Migration from MySql to H2 on another box

Hi,
Could you please connect to the Talend Support Portal and log your request, so that our colleagues from support team will schedule a webex with you to give a remote assistance through the support cycle.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: [resolved] Admin Migration from MySql to H2 on another box

I actually started there, and they sent me here.
Moderator

Re: [resolved] Admin Migration from MySql to H2 on another box

Hi,
Have you already checked document TalendHelpCenter:Upgrading the administration database?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: [resolved] Admin Migration from MySql to H2 on another box

Yes, those are the directions I was following.
Here is the command that I ran:
./Talend-Studio-linux-gtk-x86_64 -nosplash -application org.talend.repository.TalendMigration -consoleLog -data migration-workspace -sourceUrl jdbc:mysql://vthabcpg01:3306/talend_admin_bk -sourceUser root -sourcePasswd xxxx -targetUrl "jdbc:h2:/usr/share/tomcat7/webapps/org.talend.administrator/WEB-INF/database/talend_administrator;AUTO_SERVER=TRUE;MVCC=TRUE" -targetUser tisadmin -targetPasswd xxxx -vmargs -Xss5m -Xms64m -Xmx1024m
Below is the exception from the technicalInfo log. You can see in the 2nd SEVERE error the CREATE command is failing to create a table in vthabcpg01-new. There is no mysql DB on vthabcpg01-new, mysql is not even installed. vthabcpg01-new is the target box where I am running the migration, which only will have H2. The source box is vthabcpg01 (no -new) and does have a mysql DB. I would expect the backup tables there if it's using mysql.
Is there something wrong that you can see with my migration commandline?
Thanks
Pete
technical info log, first error.
SEVERE: Unsuccessful: create table `artifactnotification` (e_id bigint not null auto_increment, `dtype` varchar(255) not null, e_version integer not null, `name` varchar(255) not null, primary key (e_id)) ENGINE=InnoDB
May 08, 2014 6:07:47 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
--------------------------------------------Look here, why is it trying to create a table on vthabcpg01-new as root using mysql InnoDB engine? Backup should be on the old box, vthabcpg01 (no -new) ------------------
SEVERE: CREATE command denied to user 'root'@'vthabcpg01-new.abacus-us.com' for table 'artifactnotification'
May 08, 2014 6:07:47 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table `backup` (`id` integer not null auto_increment, `dtype` varchar(255) not null, e_version integer not null, `active` bit, `label` varchar(255), `targettype` varchar(255), `description` varchar(255), `username` varchar(255), `password` varchar(255), `location` varchar(255), `dumppath` varchar(255), `idquartzjob` integer, `idquartztrigger` varchar(255), `starttime` datetime, `endtime` datetime, `previousfiretime` datetime, `finalfiretime` datetime, `resumepauseupdated` datetime, `previouslypaused` bit, `listdaysofweek` varchar(255), `listdaysofmonth` varchar(255), `listmonths` varchar(255), `listyears` varchar(255), `listhours` varchar(255), `listminutes` varchar(255), `cronexpression` mediumtext, `backuptype` varchar(255), `status` varchar(255), `command` varchar(255), `errormsg` varchar(255), primary key (`id`)) ENGINE=InnoDB
Five Stars

Re: [resolved] Admin Migration from MySql to H2 on another box

I did figure this out. It was a permissions thing for the root user in mysql. It was only allowed to make DB changes when logging with "localhost" as the connect string. When I changed the in the login string to "localhost" instead of the actual machine name I was able to proceed.
Thanks
Pete
Moderator

Re: [resolved] Admin Migration from MySql to H2 on another box

Hi Pete,
Thanks for your feedback on this issue. It's helpful for us to address similar migration DB issue. Could you please mark this topic as "resolved" so that other users will be informed that this thread has been resolved.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.