|

Mysql migration problems

View: New views

9 Messages — Rating Filter: Alert me

	Mysql migration problems by Stephen Eaton :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message I'm trying to migrate my existing JTRAC installation, the current stable release of jtrac using HQDB on a win2003 server, to mysql running on a seperate linux server. I have made the changes the jtrac.properties connector and restarted jtrac, jtrac successfully connects to mysql and creates the missing tables, however the migration script bombs out with the following errors: D:\jtrac\data\db>java -cp bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) value s (?, ?, ?, ?, ?) Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocat ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : stmt2 .ex ecute ( ) Target exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab le 'jtrac.ATTACHMENTS' doesn't exist com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jtrac.ATTACHME NTS' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou rce) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) What looks like the problem are the table names that jtrac created within mysql are all in lowercase, not uppercase that the script seems to show and so does not see them. Stephen... ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by ptrthomas :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Some parts of this message have been removed. Learn more about Nabble's security policy. Hi, I ran into this exact same problem today (coincidence!) when migrating another Java app from windows to linux at work. We found this link which saved the day: http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLException++Table+%27confluence.BANDANA%27+doesn%27t+exist I think the simplest solution is to switch your MySQL database to case-insensitive, here is the link the above knowledge-base article points to: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html Thanks, Peter. On 4/2/08, Stephen Eaton <seaton@...> wrote: I'm trying to migrate my existing JTRAC installation, the current stable release of jtrac using HQDB on a win2003 server, to mysql running on a seperate linux server. I have made the changes the jtrac.properties connector and restarted jtrac, jtrac successfully connects to mysql and creates the missing tables, however the migration script bombs out with the following errors: D:\jtrac\data\db>java -cp bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) value s (?, ?, ?, ?, ?) Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocat ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : stmt2 .ex ecute ( ) Target exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab le 'jtrac.ATTACHMENTS' doesn't exist com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jtrac.ATTACHME NTS' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou rce) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) What looks like the problem are the table names that jtrac created within mysql are all in lowercase, not uppercase that the script seems to show and so does not see them. Stephen... ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by Stephen Eaton :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Some parts of this message have been removed. Learn more about Nabble's security policy. lol good timing on my part, that definately fixed the problem thatnks foir that Peter, but has raised another during the migration process D:\jtrac\data\db>java -cp bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) values (?, ?, ?, ?, ?) insert into CONFIG (PARAM, VALUE) values (?, ?) insert into HISTORY (ID, ITEM_ID, VERSION, TYPE, ACTUAL_EFFORT, ATTACHMENT_ID, COMMENT, TIME_STAMP, LOGGED_BY, ASSIGNED_TO, SUMMARY, DETAIL, STATUS, SEVERITY, PRIORITY, CUS_DBL_01, CUS_DBL_02, CUS_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) insert into ITEMS (ID, VERSION, TYPE, PARENT_ID, SPACE_ID, SEQUENCE_NUM, TIME_STAMP, PLANNED_EFFORT, LOGGED_BY, ASSIGNED_TO, SUMMARY, DETAIL, STATUS, SEVERITY,PRIORITY, CUS_DBL_01, CUS_DBL_02, US_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) insert into ITEM_ITEMS (ID, ITEM_ID, RELATED_ITEM_ID, TYPE) values (?, ?, ?, ?) insert into ITEM_TAGS (ID, TAG_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) insert into ITEM_USERS (ID, USER_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) insert into METADATA (ID, VERSION, TYPE, NAME, DESCRIPTION, PARENT_ID, XML_STRING) values (?, ?, ?, ?, ?, ?, ?) insert into SPACES (ID, VERSION, TYPE, PREFIX_CODE, NAME, DESCRIPTION, METADATA_ID, GUEST_ALLOWED) values (?, ?, ?, ?, ?, ?, ?, ?) Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocation stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : stmt2 .execute ( ) Target exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'guest_allowed' at row 1 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'guest_allowed' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995) at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at bsh.Reflect.invokeMethod(Unknown Source) at bsh.Reflect.invokeObjectMethod(Unknown Source) at bsh.Name.invokeMethod(Unknown Source) at bsh.BSHMethodInvocation.eval(Unknown Source) at bsh.BSHPrimaryExpression.eval(Unknown Source) at bsh.BSHPrimaryExpression.eval(Unknown Source) at bsh.BSHBlock.evalBlock(Unknown Source) at bsh.BSHBlock.eval(Unknown Source) at bsh.BSHBlock.eval(Unknown Source) at bsh.BSHWhileStatement.eval(Unknown Source) at bsh.BSHBlock.evalBlock(Unknown Source) at bsh.BSHBlock.eval(Unknown Source) at bsh.BSHBlock.eval(Unknown Source) at bsh.BSHEnhancedForStatement.eval(Unknown Source) at bsh.Interpreter.eval(Unknown Source) at bsh.Interpreter.source(Unknown Source) at bsh.Interpreter.main(Unknown Source) From: j-trac-users-bounces@... [mailto:j-trac-users-bounces@...] On Behalf Of Peter Thomas Sent: Wednesday, 2 April 2008 11:33 PM To: JTrac users mailing-list Subject: Re: [jtrac-users] Mysql migration problems Hi, I ran into this exact same problem today (coincidence!) when migrating another Java app from windows to linux at work. We found this link which saved the day: http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLException++Table+%27confluence.BANDANA%27+doesn%27t+exist I think the simplest solution is to switch your MySQL database to case-insensitive, here is the link the above knowledge-base article points to: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html Thanks, Peter. On 4/2/08, Stephen Eaton <seaton@...> wrote: I'm trying to migrate my existing JTRAC installation, the current stable release of jtrac using HQDB on a win2003 server, to mysql running on a seperate linux server. I have made the changes the jtrac.properties connector and restarted jtrac, jtrac successfully connects to mysql and creates the missing tables, however the migration script bombs out with the following errors: D:\jtrac\data\db>java -cp bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) value s (?, ?, ?, ?, ?) Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocat ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : stmt2 .ex ecute ( ) Target exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab le 'jtrac.ATTACHMENTS' doesn't exist com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jtrac.ATTACHME NTS' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou rce) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) What looks like the problem are the table names that jtrac created within mysql are all in lowercase, not uppercase that the script seems to show and so does not see them. Stephen... ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by Tauren Mills :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message I ran into these exact same problems, but I didn't want to set my database to be case insensitive. So I hacked the jtrac-hsqldb-to-mysql.bsh file. See below for the full file. To get past the first problem (the case sensitive table names), I added this line: tableName = tableName.toLowerCase(); The data truncation error happens for MySQL fields that are of type BIT(1), which is used for storing boolean values. To get past this error, which is a problem for both spaces.guest_allowed and users.locked, I added these lines: } else if (md.getColumnType(i) == Types.INTEGER) { String name = md.getColumnName(i).toLowerCase(); if (name.equals("guest_allowed") \|\| name.equals("locked")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else { stmt2.setString(i, rs.getString(i)); } This is because in hsqldb, the column type is integer, but the bsh script was attempting to convert it to a string. And the string wouldn't fit into a bit(1). I hope this helps! Below is the full script. Tauren ---------- import java.sql.; Class.forName("org.hsqldb.jdbcDriver"); Class.forName("com.mysql.jdbc.Driver"); conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); conn2 = DriverManager.getConnection("jdbc:mysql://localhost/jtrac", "root", ""); md1 = conn1.getMetaData(); rs = md1.getTables(null, null, null, new String[] { "TABLE" }); tableNames = new ArrayList(); while (rs.next()) { tableNames.add(rs.getString("TABLE_NAME")); } stmt1 = conn1.createStatement(); stmt2 = conn2.createStatement(); stmt2.executeUpdate("delete from user_space_roles"); stmt2.executeUpdate("delete from users"); for (tableName : tableNames) { tableName = tableName.toLowerCase(); rs = stmt1.executeQuery("select from " + tableName); md = rs.getMetaData(); cols = ""; vals = ""; for (int i = 1; i <= md.getColumnCount(); i++) { cols = cols + md.getColumnName(i); vals = vals + "?"; if (i != md.getColumnCount()) { cols = cols + ", "; vals = vals + ", "; } } ins = "insert into " + tableName + " (" + cols + ") values (" + vals + ")"; print(ins); stmt2 = conn2.prepareStatement(ins); while (rs.next()) { for (int i = 1; i <= md.getColumnCount(); i++) { if (md.getColumnType(i) == Types.TIMESTAMP) { stmt2.setTimestamp(i, rs.getTimestamp(i)); } else if (md.getColumnType(i) == Types.INTEGER) { String name = md.getColumnName(i).toLowerCase(); if (name.equals("guest_allowed") \|\| name.equals("locked")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else { stmt2.setString(i, rs.getString(i)); } } else { stmt2.setString(i, rs.getString(i)); } } stmt2.execute(); } } conn1.close(); conn2.close(); On Wed, Apr 2, 2008 at 8:35 PM, Stephen Eaton <seaton@...> wrote: > > > lol good timing on my part, that definately fixed the problem thatnks foir > that Peter, but has raised another during the migration process > > D:\jtrac\data\db>java -cp > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh > insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) > values (?, ?, ?, ?, ?) > insert into CONFIG (PARAM, VALUE) values (?, ?) > insert into HISTORY (ID, ITEM_ID, VERSION, TYPE, ACTUAL_EFFORT, > ATTACHMENT_ID, COMMENT, TIME_STAMP, LOGGED_BY, ASSIGNED_TO, SUMMARY, DETAIL, > STATUS, SEVERITY, PRIORITY, CUS_DBL_01, CUS_DBL_02, CUS_DBL_03, CUS_INT_01, > CUS_INT_02, CUS_INT_03, CUS_INT_04, CUS_INT_05, CUS_INT_06, CUS_INT_07, > CUS_INT_08, CUS_INT_09, CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, > CUS_STR_04, CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?) > insert into ITEMS (ID, VERSION, TYPE, PARENT_ID, SPACE_ID, SEQUENCE_NUM, > TIME_STAMP, PLANNED_EFFORT, LOGGED_BY, ASSIGNED_TO, SUMMARY, DETAIL, STATUS, > SEVERITY,PRIORITY, CUS_DBL_01, CUS_DBL_02, US_DBL_03, CUS_INT_01, > CUS_INT_02, CUS_INT_03, CUS_INT_04, CUS_INT_05, CUS_INT_06, CUS_INT_07, > CUS_INT_08, CUS_INT_09, CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, > CUS_STR_04, CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?) > insert into ITEM_ITEMS (ID, ITEM_ID, RELATED_ITEM_ID, TYPE) values (?, ?, ?, > ?) > insert into ITEM_TAGS (ID, TAG_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) > insert into ITEM_USERS (ID, USER_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) > insert into METADATA (ID, VERSION, TYPE, NAME, DESCRIPTION, PARENT_ID, > XML_STRING) values (?, ?, ?, ?, ?, ?, ?) > insert into SPACES (ID, VERSION, TYPE, PREFIX_CODE, NAME, DESCRIPTION, > METADATA_ID, GUEST_ALLOWED) values (?, ?, ?, ?, ?, ?, ?, ?) > Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method > Invocation stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh > : stmt2 .execute ( ) > > Target exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data > too long for column 'guest_allowed' at row 1 > > com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for > column 'guest_allowed' at row 1 > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) > at > com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) > at > com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995) > at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > at java.lang.reflect.Method.invoke(Unknown Source) > at bsh.Reflect.invokeMethod(Unknown Source) > at bsh.Reflect.invokeObjectMethod(Unknown Source) > at bsh.Name.invokeMethod(Unknown Source) > at bsh.BSHMethodInvocation.eval(Unknown Source) > at bsh.BSHPrimaryExpression.eval(Unknown Source) > at bsh.BSHPrimaryExpression.eval(Unknown Source) > at bsh.BSHBlock.evalBlock(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHWhileStatement.eval(Unknown Source) > at bsh.BSHBlock.evalBlock(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHEnhancedForStatement.eval(Unknown Source) > at bsh.Interpreter.eval(Unknown Source) > at bsh.Interpreter.source(Unknown Source) > at bsh.Interpreter.main(Unknown Source) > > ________________________________ > From: j-trac-users-bounces@... > [mailto:j-trac-users-bounces@...] On Behalf Of Peter > Thomas > Sent: Wednesday, 2 April 2008 11:33 PM > To: JTrac users mailing-list > Subject: Re: [jtrac-users] Mysql migration problems > > > > Hi, > > I ran into this exact same problem today (coincidence!) when migrating > another Java app from windows to linux at work. We found this link which > saved the day: > > http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLException++Table+%27confluence.BANDANA%27+doesn%27t+exist > > I think the simplest solution is to switch your MySQL database to > case-insensitive, here is the link the above knowledge-base article points > to: > > http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html > > Thanks, > > Peter. > > > On 4/2/08, Stephen Eaton <seaton@...> wrote: > > > > > > I'm trying to migrate my existing JTRAC installation, the current stable > release of jtrac using HQDB on a win2003 server, to mysql running on a > seperate linux server. > > > > I have made the changes the jtrac.properties connector and restarted > jtrac, jtrac successfully connects to mysql and creates the missing tables, > however the migration script bombs out with the following errors: > > > > D:\jtrac\data\db>java -cp > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh > > insert into ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) > value > > s (?, ?, ?, ?, ?) > > Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : Method > Invocat > > ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : > stmt2 .ex > > ecute ( ) > > > > Target exception: > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab > > le 'jtrac.ATTACHMENTS' doesn't exist > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table > 'jtrac.ATTACHME > > NTS' doesn't exist > > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > > > > at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown > Source) > > > > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou > > rce) > > at java.lang.reflect.Constructor.newInstance(Unknown Source) > > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) > > at com.mysql.jdbc.Util.getInstance(Util.java:381) > > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) > > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) > > > > > > What looks like the problem are the table names that jtrac created within > mysql are all in lowercase, not uppercase that the script seems to show and > so does not see them. > > > > > > Stephen... > > ------------------------------------------------------------------------- > > Check out the new SourceForge.net Marketplace. > > It's the best place to buy or sell services for > > just about anything Open Source. > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > > _______________________________________________ > > j-trac-users mailing list > > j-trac-users@... > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for > just about anything Open Source. > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by Stephen Eaton :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message thanks for that, while it worked on the spaces, it bombed out on the users where you said so I modified your script and this worked for me! Stephen... ---------------- import java.sql.; Class.forName("org.hsqldb.jdbcDriver"); Class.forName("com.mysql.jdbc.Driver"); conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); conn2 = DriverManager.getConnection("jdbc:mysql://mysql/jtrac", "jtrac", "jtrac"); md1 = conn1.getMetaData(); rs = md1.getTables(null, null, null, new String[] { "TABLE" }); tableNames = new ArrayList(); while (rs.next()) { tableNames.add(rs.getString("TABLE_NAME")); } stmt1 = conn1.createStatement(); stmt2 = conn2.createStatement(); stmt2.executeUpdate("delete from user_space_roles"); stmt2.executeUpdate("delete from users"); for (tableName : tableNames) { tableName = tableName.toLowerCase(); rs = stmt1.executeQuery("select from " + tableName); md = rs.getMetaData(); cols = ""; vals = ""; for (int i = 1; i <= md.getColumnCount(); i++) { cols = cols + md.getColumnName(i); vals = vals + "?"; if (i != md.getColumnCount()) { cols = cols + ", "; vals = vals + ", "; } } ins = "insert into " + tableName + " (" + cols + ") values (" + vals + ")"; print(ins); stmt2 = conn2.prepareStatement(ins); while (rs.next()) { for (int i = 1; i <= md.getColumnCount(); i++) { if (md.getColumnType(i) == Types.TIMESTAMP) { stmt2.setTimestamp(i, rs.getTimestamp(i)); } else { String name = md.getColumnName(i).toLowerCase(); if (name.equals("locked")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else if (name.equals("guest_allowed")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else { stmt2.setString(i, rs.getString(i)); } } } stmt2.execute(); } } conn1.close(); conn2.close(); -----Original Message----- From: j-trac-users-bounces@... [mailto:j-trac-users-bounces@...] On Behalf Of Tauren Mills Sent: Thursday, 3 April 2008 1:34 PM To: JTrac users mailing-list Subject: Re: [jtrac-users] Mysql migration problems I ran into these exact same problems, but I didn't want to set my database to be case insensitive. So I hacked the jtrac-hsqldb-to-mysql.bsh file. See below for the full file. To get past the first problem (the case sensitive table names), I added this line: tableName = tableName.toLowerCase(); The data truncation error happens for MySQL fields that are of type BIT(1), which is used for storing boolean values. To get past this error, which is a problem for both spaces.guest_allowed and users.locked, I added these lines: } else if (md.getColumnType(i) == Types.INTEGER) { String name = md.getColumnName(i).toLowerCase(); if (name.equals("guest_allowed") \|\| name.equals("locked")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else { stmt2.setString(i, rs.getString(i)); } This is because in hsqldb, the column type is integer, but the bsh script was attempting to convert it to a string. And the string wouldn't fit into a bit(1). I hope this helps! Below is the full script. Tauren ---------- import java.sql.; Class.forName("org.hsqldb.jdbcDriver"); Class.forName("com.mysql.jdbc.Driver"); conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); conn2 = DriverManager.getConnection("jdbc:mysql://localhost/jtrac", "root", ""); md1 = conn1.getMetaData(); rs = md1.getTables(null, null, null, new String[] { "TABLE" }); tableNames = new ArrayList(); while (rs.next()) { tableNames.add(rs.getString("TABLE_NAME")); } stmt1 = conn1.createStatement(); stmt2 = conn2.createStatement(); stmt2.executeUpdate("delete from user_space_roles"); stmt2.executeUpdate("delete from users"); for (tableName : tableNames) { tableName = tableName.toLowerCase(); rs = stmt1.executeQuery("select from " + tableName); md = rs.getMetaData(); cols = ""; vals = ""; for (int i = 1; i <= md.getColumnCount(); i++) { cols = cols + md.getColumnName(i); vals = vals + "?"; if (i != md.getColumnCount()) { cols = cols + ", "; vals = vals + ", "; } } ins = "insert into " + tableName + " (" + cols + ") values (" + vals + ")"; print(ins); stmt2 = conn2.prepareStatement(ins); while (rs.next()) { for (int i = 1; i <= md.getColumnCount(); i++) { if (md.getColumnType(i) == Types.TIMESTAMP) { stmt2.setTimestamp(i, rs.getTimestamp(i)); } else if (md.getColumnType(i) == Types.INTEGER) { String name = md.getColumnName(i).toLowerCase(); if (name.equals("guest_allowed") \|\| name.equals("locked")) { stmt2.setBoolean(i, rs.getInt(i) == 1); } else { stmt2.setString(i, rs.getString(i)); } } else { stmt2.setString(i, rs.getString(i)); } } stmt2.execute(); } } conn1.close(); conn2.close(); On Wed, Apr 2, 2008 at 8:35 PM, Stephen Eaton <seaton@...> wrote: > > > lol good timing on my part, that definately fixed the problem thatnks > foir that Peter, but has raised another during the migration process > > D:\jtrac\data\db>java -cp > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) values > (?, ?, ?, ?, ?) insert into CONFIG (PARAM, VALUE) values (?, ?) insert > into HISTORY (ID, ITEM_ID, VERSION, TYPE, ACTUAL_EFFORT, > ATTACHMENT_ID, COMMENT, TIME_STAMP, LOGGED_BY, ASSIGNED_TO, SUMMARY, > DETAIL, STATUS, SEVERITY, PRIORITY, CUS_DBL_01, CUS_DBL_02, > CUS_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?) insert into ITEMS (ID, VERSION, TYPE, PARENT_ID, > SPACE_ID, SEQUENCE_NUM, TIME_STAMP, PLANNED_EFFORT, LOGGED_BY, > ASSIGNED_TO, SUMMARY, DETAIL, STATUS, SEVERITY,PRIORITY, CUS_DBL_01, > CUS_DBL_02, US_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?) insert into ITEM_ITEMS (ID, ITEM_ID, > RELATED_ITEM_ID, TYPE) values (?, ?, ?, > ?) > insert into ITEM_TAGS (ID, TAG_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) > insert into ITEM_USERS (ID, USER_ID, TYPE, ITEM_ID) values (?, ?, ?, > ?) insert into METADATA (ID, VERSION, TYPE, NAME, DESCRIPTION, > PARENT_ID, > XML_STRING) values (?, ?, ?, ?, ?, ?, ?) insert into SPACES (ID, > VERSION, TYPE, PREFIX_CODE, NAME, DESCRIPTION, METADATA_ID, > GUEST_ALLOWED) values (?, ?, ?, ?, ?, ?, ?, ?) Script threw exception: > Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocation > stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh > : stmt2 .execute ( ) > > Target exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: > Data too long for column 'guest_allowed' at row 1 > > com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for > column 'guest_allowed' at row 1 > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) > at > com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734 ) > at > com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995) > at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > at java.lang.reflect.Method.invoke(Unknown Source) > at bsh.Reflect.invokeMethod(Unknown Source) > at bsh.Reflect.invokeObjectMethod(Unknown Source) > at bsh.Name.invokeMethod(Unknown Source) > at bsh.BSHMethodInvocation.eval(Unknown Source) > at bsh.BSHPrimaryExpression.eval(Unknown Source) > at bsh.BSHPrimaryExpression.eval(Unknown Source) > at bsh.BSHBlock.evalBlock(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHWhileStatement.eval(Unknown Source) > at bsh.BSHBlock.evalBlock(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHBlock.eval(Unknown Source) > at bsh.BSHEnhancedForStatement.eval(Unknown Source) > at bsh.Interpreter.eval(Unknown Source) > at bsh.Interpreter.source(Unknown Source) > at bsh.Interpreter.main(Unknown Source) > > ________________________________ > From: j-trac-users-bounces@... > [mailto:j-trac-users-bounces@...] On Behalf Of Peter > Thomas > Sent: Wednesday, 2 April 2008 11:33 PM > To: JTrac users mailing-list > Subject: Re: [jtrac-users] Mysql migration problems > > > > Hi, > > I ran into this exact same problem today (coincidence!) when migrating > another Java app from windows to linux at work. We found this link > which saved the day: > > http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLExc > eption++Table+%27confluence.BANDANA%27+doesn%27t+exist > > I think the simplest solution is to switch your MySQL database to > case-insensitive, here is the link the above knowledge-base article > points > to: > > http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.htm > l > > Thanks, > > Peter. > > > On 4/2/08, Stephen Eaton <seaton@...> wrote: > > > > > > I'm trying to migrate my existing JTRAC installation, the current > > stable > release of jtrac using HQDB on a win2003 server, to mysql running on a > seperate linux server. > > > > I have made the changes the jtrac.properties connector and restarted > jtrac, jtrac successfully connects to mysql and creates the missing > tables, however the migration script bombs out with the following errors: > > > > D:\jtrac\data\db>java -cp > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) > value > > s (?, ?, ?, ?, ?) > > Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : > > Method > Invocat > > ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : > stmt2 .ex > > ecute ( ) > > > > Target exception: > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab > > le 'jtrac.ATTACHMENTS' doesn't exist > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table > 'jtrac.ATTACHME > > NTS' doesn't exist > > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > > > > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown > Source) > > > > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou > > rce) > > at java.lang.reflect.Constructor.newInstance(Unknown Source) > > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) > > at com.mysql.jdbc.Util.getInstance(Util.java:381) > > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) > > at > > com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) > > > > > > What looks like the problem are the table names that jtrac created > > within > mysql are all in lowercase, not uppercase that the script seems to > show and so does not see them. > > > > > > Stephen... > > -------------------------------------------------------------------- > > ----- Check out the new SourceForge.net Marketplace. > > It's the best place to buy or sell services for just about anything > > Open Source. > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > tplace > > _______________________________________________ > > j-trac-users mailing list > > j-trac-users@... > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > > ---------------------------------------------------------------------- > --- Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for just about anything > Open Source. > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > tplace _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users __________ NOD32 2996 (20080403) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by tauren :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message That's weird. Your script does essentially the same thing, doesn't it? You just split the test into two if/else clauses instead of using an or operator. Hmm. Well, at least you got it going! Tauren On Wed, Apr 2, 2008 at 11:41 PM, Stephen Eaton <seaton@...> wrote: > thanks for that, while it worked on the spaces, it bombed out on the users > where you said so I modified your script and this worked for me! > > > Stephen... > > ---------------- > > import java.sql.; > > Class.forName("org.hsqldb.jdbcDriver"); > Class.forName("com.mysql.jdbc.Driver"); > > conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); > conn2 = DriverManager.getConnection("jdbc:mysql://mysql/jtrac", "jtrac", > "jtrac"); > > > > md1 = conn1.getMetaData(); > rs = md1.getTables(null, null, null, new String[] { "TABLE" }); > > tableNames = new ArrayList(); > > while (rs.next()) { > tableNames.add(rs.getString("TABLE_NAME")); > } > > stmt1 = conn1.createStatement(); > > stmt2 = conn2.createStatement(); > stmt2.executeUpdate("delete from user_space_roles"); > stmt2.executeUpdate("delete from users"); > > for (tableName : tableNames) { > tableName = tableName.toLowerCase(); > rs = stmt1.executeQuery("select from " + tableName); > md = rs.getMetaData(); > cols = ""; > vals = ""; > for (int i = 1; i <= md.getColumnCount(); i++) { > cols = cols + md.getColumnName(i); > vals = vals + "?"; > if (i != md.getColumnCount()) { > cols = cols + ", "; > vals = vals + ", "; > } > } > ins = "insert into " + tableName + " (" + cols + ") values (" + vals + > ")"; > print(ins); > stmt2 = conn2.prepareStatement(ins); > while (rs.next()) { > for (int i = 1; i <= md.getColumnCount(); i++) { > if (md.getColumnType(i) == Types.TIMESTAMP) { > stmt2.setTimestamp(i, rs.getTimestamp(i)); > } else { > > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("locked")) { > > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else if (name.equals("guest_allowed")) { > > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > } > } > > stmt2.execute(); > } > } > > conn1.close(); > conn2.close(); > > > > -----Original Message----- > From: j-trac-users-bounces@... > > > [mailto:j-trac-users-bounces@...] On Behalf Of Tauren > Mills > Sent: Thursday, 3 April 2008 1:34 PM > To: JTrac users mailing-list > Subject: Re: [jtrac-users] Mysql migration problems > > I ran into these exact same problems, but I didn't want to set my database > to be case insensitive. So I hacked the jtrac-hsqldb-to-mysql.bsh file. > See below for the full file. > > To get past the first problem (the case sensitive table names), I added this > line: > > tableName = tableName.toLowerCase(); > > The data truncation error happens for MySQL fields that are of type BIT(1), > which is used for storing boolean values. To get past this error, which is > a problem for both spaces.guest_allowed and users.locked, I added these > lines: > > } else if (md.getColumnType(i) == Types.INTEGER) { > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("guest_allowed") \|\| name.equals("locked")) { > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > > This is because in hsqldb, the column type is integer, but the bsh script > was attempting to convert it to a string. And the string wouldn't fit into > a bit(1). > > I hope this helps! Below is the full script. > > Tauren > > > ---------- > > import java.sql.; > > Class.forName("org.hsqldb.jdbcDriver"); > Class.forName("com.mysql.jdbc.Driver"); > > conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); > conn2 = DriverManager.getConnection("jdbc:mysql://localhost/jtrac", "root", > ""); > > md1 = conn1.getMetaData(); > rs = md1.getTables(null, null, null, new String[] { "TABLE" }); > > tableNames = new ArrayList(); > > while (rs.next()) { > tableNames.add(rs.getString("TABLE_NAME")); > } > > stmt1 = conn1.createStatement(); > > stmt2 = conn2.createStatement(); > stmt2.executeUpdate("delete from user_space_roles"); > stmt2.executeUpdate("delete from users"); > > for (tableName : tableNames) { > tableName = tableName.toLowerCase(); > rs = stmt1.executeQuery("select from " + tableName); > md = rs.getMetaData(); > cols = ""; > vals = ""; > for (int i = 1; i <= md.getColumnCount(); i++) { > cols = cols + md.getColumnName(i); > vals = vals + "?"; > if (i != md.getColumnCount()) { > cols = cols + ", "; > vals = vals + ", "; > } > } > ins = "insert into " + tableName + " (" + cols + ") values (" + vals + > ")"; > print(ins); > stmt2 = conn2.prepareStatement(ins); > while (rs.next()) { > for (int i = 1; i <= md.getColumnCount(); i++) { > if (md.getColumnType(i) == Types.TIMESTAMP) { > stmt2.setTimestamp(i, rs.getTimestamp(i)); > } else if (md.getColumnType(i) == Types.INTEGER) { > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("guest_allowed") \|\| name.equals("locked")) { > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > } else { > stmt2.setString(i, rs.getString(i)); > } > } > stmt2.execute(); > } > } > > conn1.close(); > conn2.close(); > > > On Wed, Apr 2, 2008 at 8:35 PM, Stephen Eaton <seaton@...> wrote: > > > > > > lol good timing on my part, that definately fixed the problem thatnks > > foir that Peter, but has raised another during the migration process > > > > D:\jtrac\data\db>java -cp > > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) values > > (?, ?, ?, ?, ?) insert into CONFIG (PARAM, VALUE) values (?, ?) insert > > into HISTORY (ID, ITEM_ID, VERSION, TYPE, ACTUAL_EFFORT, > > ATTACHMENT_ID, COMMENT, TIME_STAMP, LOGGED_BY, ASSIGNED_TO, SUMMARY, > > DETAIL, STATUS, SEVERITY, PRIORITY, CUS_DBL_01, CUS_DBL_02, > > CUS_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?) insert into ITEMS (ID, VERSION, TYPE, PARENT_ID, > > SPACE_ID, SEQUENCE_NUM, TIME_STAMP, PLANNED_EFFORT, LOGGED_BY, > > ASSIGNED_TO, SUMMARY, DETAIL, STATUS, SEVERITY,PRIORITY, CUS_DBL_01, > > CUS_DBL_02, US_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?) insert into ITEM_ITEMS (ID, ITEM_ID, > > RELATED_ITEM_ID, TYPE) values (?, ?, ?, > > ?) > > insert into ITEM_TAGS (ID, TAG_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) > > insert into ITEM_USERS (ID, USER_ID, TYPE, ITEM_ID) values (?, ?, ?, > > ?) insert into METADATA (ID, VERSION, TYPE, NAME, DESCRIPTION, > > PARENT_ID, > > XML_STRING) values (?, ?, ?, ?, ?, ?, ?) insert into SPACES (ID, > > VERSION, TYPE, PREFIX_CODE, NAME, DESCRIPTION, METADATA_ID, > > GUEST_ALLOWED) values (?, ?, ?, ?, ?, ?, ?, ?) Script threw exception: > > Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocation > > stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh > > : stmt2 .execute ( ) > > > > Target exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: > > Data too long for column 'guest_allowed' at row 1 > > > > com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for > > column 'guest_allowed' at row 1 > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) > > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) > > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) > > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) > > at > > > com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734 > ) > > at > > com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995) > > at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) > > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > > at java.lang.reflect.Method.invoke(Unknown Source) > > at bsh.Reflect.invokeMethod(Unknown Source) > > at bsh.Reflect.invokeObjectMethod(Unknown Source) > > at bsh.Name.invokeMethod(Unknown Source) > > at bsh.BSHMethodInvocation.eval(Unknown Source) > > at bsh.BSHPrimaryExpression.eval(Unknown Source) > > at bsh.BSHPrimaryExpression.eval(Unknown Source) > > at bsh.BSHBlock.evalBlock(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHWhileStatement.eval(Unknown Source) > > at bsh.BSHBlock.evalBlock(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHEnhancedForStatement.eval(Unknown Source) > > at bsh.Interpreter.eval(Unknown Source) > > at bsh.Interpreter.source(Unknown Source) > > at bsh.Interpreter.main(Unknown Source) > > > > ________________________________ > > From: j-trac-users-bounces@... > > [mailto:j-trac-users-bounces@...] On Behalf Of Peter > > Thomas > > Sent: Wednesday, 2 April 2008 11:33 PM > > To: JTrac users mailing-list > > Subject: Re: [jtrac-users] Mysql migration problems > > > > > > > > Hi, > > > > I ran into this exact same problem today (coincidence!) when migrating > > another Java app from windows to linux at work. We found this link > > which saved the day: > > > > http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLExc > > eption++Table+%27confluence.BANDANA%27+doesn%27t+exist > > > > I think the simplest solution is to switch your MySQL database to > > case-insensitive, here is the link the above knowledge-base article > > points > > to: > > > > http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.htm > > l > > > > Thanks, > > > > Peter. > > > > > > On 4/2/08, Stephen Eaton <seaton@...> wrote: > > > > > > > > > I'm trying to migrate my existing JTRAC installation, the current > > > stable > > release of jtrac using HQDB on a win2003 server, to mysql running on a > > seperate linux server. > > > > > > I have made the changes the jtrac.properties connector and restarted > > jtrac, jtrac successfully connects to mysql and creates the missing > > tables, however the migration script bombs out with the following errors: > > > > > > D:\jtrac\data\db>java -cp > > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > > > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) > > value > > > s (?, ?, ?, ?, ?) > > > Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : > > > Method > > Invocat > > > ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : > > stmt2 .ex > > > ecute ( ) > > > > > > Target exception: > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab > > > le 'jtrac.ATTACHMENTS' doesn't exist > > > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table > > 'jtrac.ATTACHME > > > NTS' doesn't exist > > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > > Method) > > > > > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown > > Source) > > > > > > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou > > > rce) > > > at java.lang.reflect.Constructor.newInstance(Unknown Source) > > > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) > > > at com.mysql.jdbc.Util.getInstance(Util.java:381) > > > at > com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) > > > at > > > com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) > > > > > > > > > What looks like the problem are the table names that jtrac created > > > within > > mysql are all in lowercase, not uppercase that the script seems to > > show and so does not see them. > > > > > > > > > Stephen... > > > -------------------------------------------------------------------- > > > ----- Check out the new SourceForge.net Marketplace. > > > It's the best place to buy or sell services for just about anything > > > Open Source. > > > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > > tplace > > > _______________________________________________ > > > j-trac-users mailing list > > > j-trac-users@... > > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > > > > > > > ---------------------------------------------------------------------- > > --- Check out the new SourceForge.net Marketplace. > > It's the best place to buy or sell services for just about anything > > Open Source. > > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > > tplace _______________________________________________ > > j-trac-users mailing list > > j-trac-users@... > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for just about anything Open > Source. > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > __________ NOD32 2996 (20080403) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for > just about anything Open Source. > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by Tauren Mills :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message That's weird. Your script does essentially the same thing, doesn't it? You just split the test into two if/else clauses instead of using an or operator. Hmm. Well, at least you got it going! On Wed, Apr 2, 2008 at 11:41 PM, Stephen Eaton <seaton@...> wrote: > thanks for that, while it worked on the spaces, it bombed out on the users > where you said so I modified your script and this worked for me! > > > Stephen... > > ---------------- > > import java.sql.; > > Class.forName("org.hsqldb.jdbcDriver"); > Class.forName("com.mysql.jdbc.Driver"); > > conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); > conn2 = DriverManager.getConnection("jdbc:mysql://mysql/jtrac", "jtrac", > "jtrac"); > > > > md1 = conn1.getMetaData(); > rs = md1.getTables(null, null, null, new String[] { "TABLE" }); > > tableNames = new ArrayList(); > > while (rs.next()) { > tableNames.add(rs.getString("TABLE_NAME")); > } > > stmt1 = conn1.createStatement(); > > stmt2 = conn2.createStatement(); > stmt2.executeUpdate("delete from user_space_roles"); > stmt2.executeUpdate("delete from users"); > > for (tableName : tableNames) { > tableName = tableName.toLowerCase(); > rs = stmt1.executeQuery("select from " + tableName); > md = rs.getMetaData(); > cols = ""; > vals = ""; > for (int i = 1; i <= md.getColumnCount(); i++) { > cols = cols + md.getColumnName(i); > vals = vals + "?"; > if (i != md.getColumnCount()) { > cols = cols + ", "; > vals = vals + ", "; > } > } > ins = "insert into " + tableName + " (" + cols + ") values (" + vals + > ")"; > print(ins); > stmt2 = conn2.prepareStatement(ins); > while (rs.next()) { > for (int i = 1; i <= md.getColumnCount(); i++) { > if (md.getColumnType(i) == Types.TIMESTAMP) { > stmt2.setTimestamp(i, rs.getTimestamp(i)); > } else { > > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("locked")) { > > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else if (name.equals("guest_allowed")) { > > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > } > } > > stmt2.execute(); > } > } > > conn1.close(); > conn2.close(); > > > > -----Original Message----- > From: j-trac-users-bounces@... > > > [mailto:j-trac-users-bounces@...] On Behalf Of Tauren > Mills > Sent: Thursday, 3 April 2008 1:34 PM > To: JTrac users mailing-list > Subject: Re: [jtrac-users] Mysql migration problems > > I ran into these exact same problems, but I didn't want to set my database > to be case insensitive. So I hacked the jtrac-hsqldb-to-mysql.bsh file. > See below for the full file. > > To get past the first problem (the case sensitive table names), I added this > line: > > tableName = tableName.toLowerCase(); > > The data truncation error happens for MySQL fields that are of type BIT(1), > which is used for storing boolean values. To get past this error, which is > a problem for both spaces.guest_allowed and users.locked, I added these > lines: > > } else if (md.getColumnType(i) == Types.INTEGER) { > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("guest_allowed") \|\| name.equals("locked")) { > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > > This is because in hsqldb, the column type is integer, but the bsh script > was attempting to convert it to a string. And the string wouldn't fit into > a bit(1). > > I hope this helps! Below is the full script. > > Tauren > > > ---------- > > import java.sql.; > > Class.forName("org.hsqldb.jdbcDriver"); > Class.forName("com.mysql.jdbc.Driver"); > > conn1 = DriverManager.getConnection("jdbc:hsqldb:file:jtrac", "sa", ""); > conn2 = DriverManager.getConnection("jdbc:mysql://localhost/jtrac", "root", > ""); > > md1 = conn1.getMetaData(); > rs = md1.getTables(null, null, null, new String[] { "TABLE" }); > > tableNames = new ArrayList(); > > while (rs.next()) { > tableNames.add(rs.getString("TABLE_NAME")); > } > > stmt1 = conn1.createStatement(); > > stmt2 = conn2.createStatement(); > stmt2.executeUpdate("delete from user_space_roles"); > stmt2.executeUpdate("delete from users"); > > for (tableName : tableNames) { > tableName = tableName.toLowerCase(); > rs = stmt1.executeQuery("select from " + tableName); > md = rs.getMetaData(); > cols = ""; > vals = ""; > for (int i = 1; i <= md.getColumnCount(); i++) { > cols = cols + md.getColumnName(i); > vals = vals + "?"; > if (i != md.getColumnCount()) { > cols = cols + ", "; > vals = vals + ", "; > } > } > ins = "insert into " + tableName + " (" + cols + ") values (" + vals + > ")"; > print(ins); > stmt2 = conn2.prepareStatement(ins); > while (rs.next()) { > for (int i = 1; i <= md.getColumnCount(); i++) { > if (md.getColumnType(i) == Types.TIMESTAMP) { > stmt2.setTimestamp(i, rs.getTimestamp(i)); > } else if (md.getColumnType(i) == Types.INTEGER) { > String name = md.getColumnName(i).toLowerCase(); > if (name.equals("guest_allowed") \|\| name.equals("locked")) { > stmt2.setBoolean(i, rs.getInt(i) == 1); > } else { > stmt2.setString(i, rs.getString(i)); > } > } else { > stmt2.setString(i, rs.getString(i)); > } > } > stmt2.execute(); > } > } > > conn1.close(); > conn2.close(); > > > On Wed, Apr 2, 2008 at 8:35 PM, Stephen Eaton <seaton@...> wrote: > > > > > > lol good timing on my part, that definately fixed the problem thatnks > > foir that Peter, but has raised another during the migration process > > > > D:\jtrac\data\db>java -cp > > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) values > > (?, ?, ?, ?, ?) insert into CONFIG (PARAM, VALUE) values (?, ?) insert > > into HISTORY (ID, ITEM_ID, VERSION, TYPE, ACTUAL_EFFORT, > > ATTACHMENT_ID, COMMENT, TIME_STAMP, LOGGED_BY, ASSIGNED_TO, SUMMARY, > > DETAIL, STATUS, SEVERITY, PRIORITY, CUS_DBL_01, CUS_DBL_02, > > CUS_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?) insert into ITEMS (ID, VERSION, TYPE, PARENT_ID, > > SPACE_ID, SEQUENCE_NUM, TIME_STAMP, PLANNED_EFFORT, LOGGED_BY, > > ASSIGNED_TO, SUMMARY, DETAIL, STATUS, SEVERITY,PRIORITY, CUS_DBL_01, > > CUS_DBL_02, US_DBL_03, CUS_INT_01, CUS_INT_02, CUS_INT_03, CUS_INT_04, > > CUS_INT_05, CUS_INT_06, CUS_INT_07, CUS_INT_08, CUS_INT_09, > > CUS_INT_10, CUS_STR_01, CUS_STR_02, CUS_STR_03, CUS_STR_04, > > CUS_STR_05, CUS_TIM_01, CUS_TIM_02, CUS_TIM_03) values (?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?) insert into ITEM_ITEMS (ID, ITEM_ID, > > RELATED_ITEM_ID, TYPE) values (?, ?, ?, > > ?) > > insert into ITEM_TAGS (ID, TAG_ID, TYPE, ITEM_ID) values (?, ?, ?, ?) > > insert into ITEM_USERS (ID, USER_ID, TYPE, ITEM_ID) values (?, ?, ?, > > ?) insert into METADATA (ID, VERSION, TYPE, NAME, DESCRIPTION, > > PARENT_ID, > > XML_STRING) values (?, ?, ?, ?, ?, ?, ?) insert into SPACES (ID, > > VERSION, TYPE, PREFIX_CODE, NAME, DESCRIPTION, METADATA_ID, > > GUEST_ALLOWED) values (?, ?, ?, ?, ?, ?, ?, ?) Script threw exception: > > Sourced file: jtrac-hsqldb-to-mysql.bsh : Method Invocation > > stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh > > : stmt2 .execute ( ) > > > > Target exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: > > Data too long for column 'guest_allowed' at row 1 > > > > com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for > > column 'guest_allowed' at row 1 > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) > > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) > > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) > > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) > > at > > > com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734 > ) > > at > > com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995) > > at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) > > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > > at java.lang.reflect.Method.invoke(Unknown Source) > > at bsh.Reflect.invokeMethod(Unknown Source) > > at bsh.Reflect.invokeObjectMethod(Unknown Source) > > at bsh.Name.invokeMethod(Unknown Source) > > at bsh.BSHMethodInvocation.eval(Unknown Source) > > at bsh.BSHPrimaryExpression.eval(Unknown Source) > > at bsh.BSHPrimaryExpression.eval(Unknown Source) > > at bsh.BSHBlock.evalBlock(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHWhileStatement.eval(Unknown Source) > > at bsh.BSHBlock.evalBlock(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHBlock.eval(Unknown Source) > > at bsh.BSHEnhancedForStatement.eval(Unknown Source) > > at bsh.Interpreter.eval(Unknown Source) > > at bsh.Interpreter.source(Unknown Source) > > at bsh.Interpreter.main(Unknown Source) > > > > ________________________________ > > From: j-trac-users-bounces@... > > [mailto:j-trac-users-bounces@...] On Behalf Of Peter > > Thomas > > Sent: Wednesday, 2 April 2008 11:33 PM > > To: JTrac users mailing-list > > Subject: Re: [jtrac-users] Mysql migration problems > > > > > > > > Hi, > > > > I ran into this exact same problem today (coincidence!) when migrating > > another Java app from windows to linux at work. We found this link > > which saved the day: > > > > http://confluence.atlassian.com/display/KB/CSP-13667+-+java.sql.SQLExc > > eption++Table+%27confluence.BANDANA%27+doesn%27t+exist > > > > I think the simplest solution is to switch your MySQL database to > > case-insensitive, here is the link the above knowledge-base article > > points > > to: > > > > http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.htm > > l > > > > Thanks, > > > > Peter. > > > > > > On 4/2/08, Stephen Eaton <seaton@...> wrote: > > > > > > > > > I'm trying to migrate my existing JTRAC installation, the current > > > stable > > release of jtrac using HQDB on a win2003 server, to mysql running on a > > seperate linux server. > > > > > > I have made the changes the jtrac.properties connector and restarted > > jtrac, jtrac successfully connects to mysql and creates the missing > > tables, however the migration script bombs out with the following errors: > > > > > > D:\jtrac\data\db>java -cp > > bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java- > > > 5.1.6-bin.jar bsh.Interpreter jtrac-hsqldb-to-mysql.bsh insert into > > > ATTACHMENTS (ID, PREVIOUS_ID, FILE_NAME, FILE_PREFIX, ITEM_ID) > > value > > > s (?, ?, ?, ?, ?) > > > Script threw exception: Sourced file: jtrac-hsqldb-to-mysql.bsh : > > > Method > > Invocat > > > ion stmt2.execute : at Line: 48 : in file: jtrac-hsqldb-to-mysql.bsh : > > stmt2 .ex > > > ecute ( ) > > > > > > Target exception: > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Tab > > > le 'jtrac.ATTACHMENTS' doesn't exist > > > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table > > 'jtrac.ATTACHME > > > NTS' doesn't exist > > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > > Method) > > > > > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown > > Source) > > > > > > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou > > > rce) > > > at java.lang.reflect.Constructor.newInstance(Unknown Source) > > > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) > > > at com.mysql.jdbc.Util.getInstance(Util.java:381) > > > at > com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) > > > at > > > com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) > > > > > > > > > What looks like the problem are the table names that jtrac created > > > within > > mysql are all in lowercase, not uppercase that the script seems to > > show and so does not see them. > > > > > > > > > Stephen... > > > -------------------------------------------------------------------- > > > ----- Check out the new SourceForge.net Marketplace. > > > It's the best place to buy or sell services for just about anything > > > Open Source. > > > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > > tplace > > > _______________________________________________ > > > j-trac-users mailing list > > > j-trac-users@... > > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > > > > > > > ---------------------------------------------------------------------- > > --- Check out the new SourceForge.net Marketplace. > > It's the best place to buy or sell services for just about anything > > Open Source. > > > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marke > > tplace _______________________________________________ > > j-trac-users mailing list > > j-trac-users@... > > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > > > > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for just about anything Open > Source. > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > > __________ NOD32 2996 (20080403) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > > > ------------------------------------------------------------------------- > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services for > just about anything Open Source. > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > _______________________________________________ > j-trac-users mailing list > j-trac-users@... > https://lists.sourceforge.net/lists/listinfo/j-trac-users > ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by TimP-3 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message On Wednesday 02 April 2008 07:59, Stephen Eaton wrote: > I'm trying to migrate my existing JTRAC installation, the current stable > release of jtrac using HQDB on a win2003 server, to mysql running on a > seperate linux server. Did you consider using hsqldb on Linux with save to disk set? Thats how I am running, with no problems. TimP ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users
	Re: Mysql migration problems by Stephen Eaton :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Why I needed to mysql was for JDBC access for some reporting tools, I guess I could run HQDB in server mode, but I'm familiar and happy with mysql. Stephen... -----Original Message----- From: j-trac-users-bounces@... [mailto:j-trac-users-bounces@...] On Behalf Of TimP Sent: Friday, 4 April 2008 4:17 AM To: JTrac users mailing-list Subject: Re: [jtrac-users] Mysql migration problems On Wednesday 02 April 2008 07:59, Stephen Eaton wrote: > I'm trying to migrate my existing JTRAC installation, the current > stable release of jtrac using HQDB on a win2003 server, to mysql > running on a seperate linux server. Did you consider using hsqldb on Linux with save to disk set? Thats how I am running, with no problems. TimP ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users __________ NOD32 2996 (20080403) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ j-trac-users mailing list j-trac-users@... https://lists.sourceforge.net/lists/listinfo/j-trac-users