Porting to MySQL

Evan Gower October 7, 2009

We should have known better than to emit phrases such as ‘That sounds like an interesting problem’ or ‘I can’t see why you couldn’t do that’ or, the real killer, ‘That should be quite easy’. We can’t quite remember which of these three it was precisely that got us into trouble, but it was almost certainly that last one, when we were discussing a problem with a client. The problem – the one that was going to be ‘easy’ – was porting a web-based ASP application based on Microsoft’s SQL Server 2000, about which we knew almost nothing, over to MySQL. Obviously, we made the attempt, and in the end the exercise went fairly well or we wouldn’t be writing about it here, but there were some fun and games along the way.

It’s often the case that one person’s real-world problem is someone else’s totally obscure problem that could never crop up. To many eyes, porting an application from one database management system (DBMS) to another might seem like an act of extreme futility, but there was some method to our madness. Many people have applications that are based on a particular DBMS: that application works well and the DBMS they’re using does everything they want it to. Then, at some point, the machines running the application need to be updated, and so a new operating system requires a new DBMS version.

Now, the best-known commercial DBMSes are by and large expensive products – an SQL Server installation will often cost more than the hardware you run it on. So at this update point, porting to a different database may well become a worthwhile activity. We ought to stress that, for the purposes of this article, the idea of porting an application to a different DBMS was more about bragging rights and less about a rational business decision.

The application we’d talked ourselves into porting was the Demographix online survey system. There were various reasons we got involved with Demographix: first of all, our company hosts the Demographix servers at one of our facilities, so we had ready access to the application. Second, the application was developed by Derek Cohen, a former editorial director at PC Pro’s publisher Dennis Publishing – hence he was both our boss and client.

So the challenge had been laid down – could we port the Demographix application from SQL Server to MySQL. It was going to be interesting, because we hadn’t used SQL Server ourselves for a couple of years, and also due to the fact that the application was written in Visual Basic using ASP, which isn’t something we encounter too often. The most obvious approach to the problem was to port the data from one DBMS to the other, and then to take a look at the code of the application.

Porting the data

We did have one ace up our sleeves when we said this was going to be easy, which was that we knew of a product called the MySQL Migration Toolkit. This application is specifically designed to allow a user to port their data from another DBMS into MySQL, and we knew the toolkit could solve a number of problems for us, so that was our starting point. Like all MySQL-related software, it’s downloaded from the website and simply installed. In our case, we decided to install the migration software on a Windows machine, but to use a Linux machine to run the new DBMS. Our source was SQL Server 2000 running on Windows 2000, and our destination database was MySQL 4.1.

Before we started the process, we took a few simple precautions. We took a copy of the existing Demographix data from a backup and restored it to a completely separate server. We also wrote a simple script which went through all the data and removed any personal information such as people’s names. Although all the correct legal permissions were in place for us to process any personal information we found, it is always safer to take this step when you are working on a development. Beyond that everything else was the same – the number of tables and their definitions.
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.
The second set of problems we encountered related to differences between the ODBC implementations for the two DBMSes. In particular, we did have some problems with record sets, but these were resolved by changing some options on their creation. In all these cases, the problems we had were solved after a quick Google search using some keywords.

And the final result?

By the time we’d finished, we had most of the Demographix application working on MySQL – we could create surveys, edit surveys and publish them, meaning they could have been used commercially. At that point, we’d run out of time for this exercise. There were reporting facilities and other parts of the application that we didn’t get to look at, but we’d expect those to be reasonably easy to update. All in all, it would probably take a working week or two to port the whole application, test that it worked and, more importantly, to test that it worked the same way as it had done under SQL Server.

At this point, we need to make several observations. The application we tried porting didn’t have any of its own stored procedures, and if there had been any it’s unlikely we’d have been able to port the database from Windows to Linux. As we mentioned above, MySQL 5 does support stored procedures, so in principle it could be done. The application did make use of some stored system procedures, but those could easily be mapped onto MySQL commands.

The most important thing about the MySQL Migration Toolkit is its very existence, as without it we probably wouldn’t have even considered trying to port an application from one DBMS to another. While it may not be perfect, the MySQL Migration Toolkit really does bootstrap the whole process and this is its real strength. Many people who use SQL Server don’t use very much of its substantial and powerful feature set: instead, they’ve created some application in Microsoft Access and reached the limits of its abilities, at which point the obvious way forward is to continue developing in SQL Server using only a subset of its features.

A dbms is not just about data

One of SQL Server’s real strengths is that it comes bundled with a couple of great utilities: the Enterprise Manager and the Query Analyzer. For those who haven’t used SQL Server, the Enterprise Manager is the administrator’s front-end onto the database, and the Query Analyzer is the user’s place for building SQL queries.

The Enterprise Manager is really very good indeed – it lets the administrator carry out many of the operations that need to be performed on any database with minimum fuss. Everyone who’s used it will tell you that there are things it can’t do, but the things it does do it does well. The Query Analyzer is a much simpler utility, but nevertheless is a good editor for creating SQL queries. For a DBMS to be successful in competing with SQL Server then, it needs to have some equivalent to these two excellent tools.

MySQL does now have two applications that are aimed at completing similar tasks: the MySQL Administrator and the MySQL Query Browser. To start with the Administrator, this program provides a reasonably good interface into the operation of a MySQL Server. It shows you what’s going on and lets you change many of the parameters that affect the operation of the server. It makes it possible to set up and schedule backups, but only a full dump of the database. MySQL itself does support incremental dumps of databases by backing up the transaction log, but this can’t be performed via the Administrator interface. You can use it to set up new users and control their access to the DBMS and its objects, and it’s also possible to get some nice graphs showing how your installation is behaving.
All in all, this isn’t a bad utility, but it doesn’t feel quite as good as Enterprise Manager. The reason for this slight lack of enthusiasm is rather hard to pin down, but ultimately it probably just arises from the fact that MySQL is a simpler DBMS than SQL Server, and therefore many of the options that appear in Enterprise Manager just aren’t required in the MySQL Administrator.

Moving on to the Query Browser, this is similar to Microsoft’s equivalent, which lets the user type in and set up queries. These queries can then be run and the results viewed, and both query and results can be saved for future use. There are similar functions to the object browser in Query Analyzer, which allow the user to browse a schema for tables and their columns. You can also look at the complete set of built-in functions, and double-clicking on any syntactic structure, function or database entity will cause it to be added to the editable syntax window.

Once written, the query is submitted to the connected server and the results returned to the window. There’s a nice online help system for all the command syntax, and an equally nice history system that allows you to call up previous edits of a particular query. The one thing that’s missing from the Query Browser is any access to query analysis.

MySQL has a reasonably strong query analysis command that predicts how a query would run and what indices it would use, which is useful when diagnosing performance problems, but this is not employed by the Query Browser. Overall, though, these are both good add-ons that help make MySQL more usable.