« Return to Thread: Mysql migration problems
Re: Mysql migration problems
by Tauren Mills
::
Rate 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
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
« Return to Thread: Mysql migration problems
| Free embeddable forum powered by Nabble | Forum Help |
