Importing mysqldump into MS SQL Server

To import data from MySQL into a MS SQL database, we need to first analyze the output of mysqldump, the backup utility of MySQL. I have covered the most obvious differences to T-SQL in my previous post.

Fortunately, the output of mysqldump is line-oriented, i.e. every CREATE TABLE statement, table column or index, and INSERT statement is in a separate line.

In a brute-force approach, we can therefore parse each line using regular expressions, and do not require full parsing involving tokenizer and grammar.

I found a couple of interesting points that need to be considered when generating T-SQL statements:

  • As T-SQL did not support “DROP IF EXISTS” before SQL Server 2016, we need to provide the classical variant “IF OBJECT_ID() IS NOT NULL”
  • NVARCHAR(MAX) columns cannot be indexed
  • BINARY data needs to be 0x-encoded
  • UNIQUE INDEXes should not include NULLable columns
  • Multi-row INSERT INTO statements cannot contain more than 1000 rows
  • ‘0000-00-00’ is a valid date in MySQL, but not in MSSQL. The value is being replaced by ‘1800-01-01’ to avoid the error message

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Some indexes may require manually editing column collations, e.g. for unique case-sensitive MySQL indexes.

When executing T-SQL statements in SSMS, a number of warnings or errors can occur:

  • A warning that indexed data is supported to a maximum length of 960 bytes
  • Inserting data may result in the error “String or binary data would be truncated.”
  • Some files are too big to execute in SSMS, and need to be executed using sqlcmd
  • A batch with too many too large INSERT INTO statements may raise the error

    There is insufficient system memory in resource pool ‘default’ to run this query

    so we break up the batch using “GO”

  • Multiple cascade paths

MS SQL Server does not support ON DELETE CASCADE clauses such that the grandchild record of a parent-child-grandchild relation cannot be uniquely identified. Creating such a FOREIGN KEY raises the error message

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint ‘fk_xxx’ on table ‘xxx’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In such cases, either drop the ON DELETE CASCADE if possible, and replace the deletion cascade with an INSTEAD OF DELETE trigger.

I developed mysqldump2mssql, a prototype implementing these rules, which is available on my GitHub. The program has been developed and tested against MySQL dumps of MediaWiki, Joomla, Bugzilla and Nextcloud.

The tool generates up to 4 files for each table defined in the source SQL file:

  • create.sql: the CREATE TABLE statement, optional CREATE UNIQUE INDEX statements
  • data.sql: the INSERT INTO statements
  • fk.sql: the FOREIGN KEY relations to other tables
  • fulltext.sql: the CREATE FULLTEXT statement

The filenames of the generated files consist of the sequence number of execution, the table name, and one of the endings mentioned in above table.

Parsing mysqldump files

MySQL and MS SQLServer both can be accessed using SQL, but if you look closely at both SQL dialects, you’ll find a lot of differences.

Backups of MySQL databases are created using the utility mysqldump, which simply dumps all the SQL statements required to recreate the database into a SQL file, as opposed to SQLServer backup, which creates a binary file representing the database contents.

As we are interesting in parsing the SQL and converting it into valid T-SQL statements, we have to look at the differences between the SQL dialects. Fortunately, since the SQL files are output by a tool, the generated statements are fairly regular.

Here are some special features of the MySQL dialect, which need to be handled for conversion into T-SQL:

  • Identifiers are quoted with a backtick `, rather than square brackets
  • IDENTITY columns are marked by the auto_increment keyword
  • Binary columns may store text values
  • Text values may contain character codes with leading backslash \, as known from C# or C
  • Values for DATETIME, DATE and TIME columns need not be quoted as strings
  • ‘0000-00-00’ is a valid DATE
  • A CREATE TABLE statement may include the definition of indexes, unique indexes and fulltext indexes
  • Both SQL engines provide support for collations, but they are completely different

I have previously written about converting MySQL SQL into T-SQL manually, but having a tool perform the repetitive actions would be very nice 😉

Installing Bitnami Redmine Stack with Separate Data Directory

I downloaded the latest version of the Bitnami Redmine Stack (bitnami-redmine-3.3.0-1-windows-installer.exe) and installed it, which worked without any problems.

However I noticed that the installation does not distinguish between programs/executables and stored data, so I tried to figure out what data is being stored and where.

I found that I only needed to move the MySQL databases to a new data directory. and leave everything else in the installation directory.

First, I created a new location D:\Bitnami-Redmine-data to store Redmine’s data.

MySQL

Next, I started the Bitnami Redmine Stack Manager Tool and stopped the MySQL Database service.

Bitnami Redmine Stack Manager Tool

Bitnami Redmine Stack Manager Tool

I created a new location D:\Bitnami-Redmine-data to store Redmine’s data, and copied the MySQL data directory C:\Bitnami\redmine-3.3.0-1\mysql\data to D:\Bitnami-Redmine-data\mysql\data.

To let MySQL know about the new location of its databases, I edited C:\Bitnami\redmine-3.3.0-1\mysql\my.ini and changed the [mysqld] section to point to the new directory:

[mysqld]
#datadir="C:/Bitnami/redmine-3.3.0-1/mysql/data"
datadir="D:\Bitnami-Redmine-data\mysql\data"

#log-error="C:/Bitnami/redmine-3.3.0-1/mysql/data/mysqld.log"
log-error="D:\Bitnami-Redmine-data\mysql\data\mysqld.log"

The I started up the MySQL Database service again, which did not raise any errors. Be sure to check the Server Events tab and the Windows Event Log for errors or warnings.

SVN

Although the Bitnami Redmine Stack ships with and installs the SVN server svnserve, the installer does not automatically create an SVN repository.

To create an SVN repository, follow the Subversion configuration instructions in the Bitnami wiki:

  • Stop the Subversion Server in the Bitnami Redmine Stack Manager Tool
  • Start the “Use Bitnami Redmine Stack” command prompt
  • Create a repository with the command
svnadmin create "D:\Bitnami-Redmine-data\subversion\repository"
  • Check your settings in “D:\Bitnami-Redmine-data\subversion\repository\conf\svnserve.conf”
  • Add users to “D:\Bitnami-Redmine-data\subversion\repository\conf\passwd” if you do not use SASL
  • Edit the file C:\Bitnami\redmine-3.3.0-1\subversion\scripts\serviceinstall.bat to add the repository root path using “–r param”:
"C:\Bitnami\redmine-3.3.0-1/subversion\scripts\winserv.exe" 
    install redmineSubversion 
    -displayname "redmineSubversion" 
    -start auto "C:\Bitnami\redmine-3.3.0-1/subversion\bin\svnserve.exe" 
    -d 
    --listen-port=3690 
    -r "D:\Bitnami-Redmine-data\subversion\repository"

(note that this is a single command line)

  • Then re-install the SVN service from the Bitnami command prompt
Run serviceinstall.bat
Run serviceinstall.bat INSTALL
  • In the Bitnami Tool, start “Subversion Server”
  • Verify you are able to connect to the SVN repository using your favorite client.