Porting to MySQL
When we first started using the migration toolkit, everything seemed simple. Start the toolkit, tell it what your source DBMS is and where your destination MySQL DBMS is located. The toolkit can migrate data from Oracle, Microsoft Access, Microsoft SQL Server and virtually any other database that can be accessed via a Java Database Connectivity (JDBC) driver, as source. In our case, we chose SQL Server, and the toolkit started the reverse-engineering process, which performs an inventory of the source database, collecting information on all the tables, views and stored procedures it contains. At this point, we started to see a few problems and received an error code 10008 with no real information about what it meant. We were guessing that the problem might be caused by the number of tables in the database – as mentioned above, Demographix is used to conducting surveys and so there’s one table for each survey conducted, containing the answers to that survey. There were literally thousands of tables, so we decided to remove a large number of them and the problem went away.
It may be worth noting that the Demographix application we were migrating didn’t make use of either views or stored procedures, so we didn’t test that aspect of the migration toolkit. If we’d wanted to do that, we’d have had to use the more recent version MySQL 5 that supports those features.
Having got that far, the next stage was to decide which tables we wanted to migrate. In our case, that was quite simple – we were going to migrate all of them – but you do have to choose a method for the migration. The toolkit uses various heuristics that can be applied to the process and you can tweak various parameters. At this point, the toolkit goes off and produces an SQL script to create the tables on the destination server and then applies this script to the destination server.
This is where we discovered that the job was going to be rather more difficult than we’d first thought, for three main reasons. First, the migration toolkit itself created some SQL, which didn’t load properly into MySQL. For example, we had a couple of tables where the default value for a column got set to ‘(-1)’ rather than ‘-1’. These glitches were easy enough to cure, if slightly irritating. The second and biggest problem was the incompatibilities between SQL Server tables and MySQL tables, which were maintained in the SQL scripts created by the toolkit. The two main examples of this we saw were again related to default values: in MySQL, default values can only be constants, while SQL Server allows the use of system functions; and MySQL doesn’t allow default values for large string fields such as blobs. Both of these problems are potentially fiddly to fix and would require changes to the application code. The third problem was that SQL Server’s identity columns weren’t mapped into MySQL’s auto-increment columns. There’s a bug report already raised for this problem, but it still meant altering a number of table definitions.
Despite these problems, all proved quite easy to fix in the toolkit itself, as you can simply change the definitions and then rerun the creation scripts. At this point, the final stage of the operation was to transfer the data from the SQL Server tables to the new MySQL tables, which the migration toolkit did efficiently and without complaint.
Porting the code
To make the Demographix application work under MySQL, we had to complete two main tasks. The simplest of these was to change the connection string from connecting to SQL Server to connect to our MySQL server. The new string was created reasonably easily and at this point we carried out our usual highly scientific test – we connected to our copy of the website to see what happened. Not surprisingly, we received some ASP errors that we then set about fixing. As with the problems with the migration toolkit, we came across different types of problem. The first set of problems related to differences in the way SQL Server and MySQL do things: for example, if you want the first row of a results set in SQL Server, you say something like ‘SELECT TOP 1 … FROM’, while in MySQL you say something like ‘SELECT … FROM … LIMIT 1’. Also, SQL Server supports stored procedures, which are used to find out the properties of a table. In the Demographix application, there are various functions that look for the existence of certain columns in tables, and under SQL Server these had been implemented using the sp_columns stored procedure. To port this over to MySQL server, we used the SHOW COLUMNS FROM table command.