« Return to Thread: Mysql migration problems
Re: Mysql migration problems
by Stephen Eaton
::
Rate 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)
> > atcom.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
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
>
)
> 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
> > 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
« Return to Thread: Mysql migration problems
| Free embeddable forum powered by Nabble | Forum Help |
