house9

random code and what not

mysql migration toolkit

I am using the mysql migration toolkit to transfer a database from MSSQL 2005 over to MySQL database. Ran into an issue with the port, by default the tool wants to use 1433 (MSSQL 2000 default), but it looks like 2005 uses a default port of 1434 (well it is more complicated then that http://technet.microsoft.com/en-us/library/ms165724.aspx) - you can use the stored procedure xp_readerrorlog to verify the port your database is listening on - one of the rows in the text column of the result should be something like ’Server is listening on [ 127.0.0.1 1434].

anyhow after changing the port number everything else was smooth sailing, yeah right…
once I got to the reverse engineer stage then this java stack came up, looks like it still doesn’t like the connection string, even though I have already authenticated with it?

Initializing JDBC driver …
Driver class MS SQL JDBC Driver
Opening connection …
Connection jdbc:jtds:sqlserver://127.0.0.1:1434/databasename;user=sa;password=;charset=utf-8;
Initializing JDBC driver …
Driver class MS SQL JDBC Driver
Opening connection …
Connection jdbc:jtds:sqlserver://127.0.0.1:1434/databasename;user=sa;password=;charset=utf-8;
The schema could not be reverse engineered (error: 0).
ReverseEngineeringMssql.reverseEngineer :I/O Error: Connection reset
Details:
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2277)
net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:599)
net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:331)net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)
net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:141)com.mysql.grt.modules.ReverseEngineeringGeneric.getVersion(ReverseEngineeringGeneric.java:161)com.mysql.grt.modules.ReverseEngineeringMssql.reverseEngineer(ReverseEngineeringMssql.java:164)sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

ok, so close the migration tool reopen and go through the same process, exact same behaviour, clicking the back button brings me to the original screen where the MSSQL connection information was entered, everything is as it was, try to connect again and this time another java stack trace, the important part was this line - ’I/O Error: DB server closed connection.’ - Weird

a google search led me here - http://bugs.mysql.com/bug.php?id=20674
Using the ‘SQL Server Configuration Manager’ I had to set the ‘SQL Server 2005 Network Configuration’ -> ‘Protocols for MSSQLSERVER’ -> TCP/IP to Enabled

doing this set my default port back to 1433 (actually that is the one I used, xp_readerrorlog shows the database listening on both 1433 and 1434), so re-connecting with the migration tool and using the 1433 default port worked and I was on my way…

so every table fails to migrate except 2? well I guess the migration tool could be better, lucky for me I only had 12 tables going here, I had to update the migration code for all of these tables

`is_active` TINYINT NOT NULL DEFAULT (1),
had to be changed to
`is_active` TINYINT NOT NULL DEFAULT 1,


yes removing the () from the default value - now that sure is a pain. It was only occuring on types of tinyint - these were coming from MSSQL bit datatypes, could be an issue only in that one case?