MacLochlainns Weblog

It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.

A warning that caught my eye in MySQL 8 (8.0.21) was this one on Unicode with the utf8 character code:

Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

Currently, a character alias for utf8mb3 is an alias for the deprecated utf8mb3 (a 3-byte character set) until it is removed. When the utf8mb3 character set is removed in a subsequent release the utf8 alias will represent the utf8mb4 (a 4-byte character set), which includes BMP and supplemental character support.

It appears to me that its probably a good time to proactively provision disk space for an upgrade to utf8mb4 (a 4-byte character set). It also probably time to use the utf8mb4 character set rather than the utf8 character set alias. The MySQL Server team wrote a blog entry on the when to use which one. Naturally, all this will have a substantial impact on disk space allocated to the database.

At present, you can’t use the MySQL Workbench migration tool to migrate MySQL 5.0 to MySQL 5.5, as documented in Bug 66861. The only documentation reference that I could find that references the mysql.proc table. Since the physical definition of the mysql.proc table changes across the MySQL 5.0, 5.1, and 5.6 releases, I modified my documentation Bug 66886 to suggest providing online documentation (as a feature request) for the mysql, information_schema, and performance_schema tables across all releases.

The actual definition of the mysql.proc table for MySQL 5.0.91 holds 16 columns not 20 columns as presently expected by the MySQL Workbench migration tool, and is summarized below:

I found out about the issue through a comment on my blog from Marc, who was trying to migrate his production instance. I hope this provides a heads-up to anybody else attempting to migrate a MySQL 5.0 database to a MySQL 5.5. The good news is that the MySQL Workbench team appears to be actively working the issue.

As many already know, I’ve been trying to get the MySQL Workbench migration feature working between Microsoft SQL Server 2012 and MySQL 5.5. There are a number of features added to the 5.2.43 point release, and one led me to believe that the Migration tool expects to find the data in a schema of its own, as opposed to the dbo schema. Having made that change in Microsoft SQL Server, it did appear to have a positive impact on the migration and when I corrected a character set mismatch it worked perfectly!

MySQL Workbench successfully migrated the schema and table but failed to migrate the data because of a character set mismatch. I updated Bug 66516 the log file from the character set mismatch before I retyped all 9 test rows to make sure they were in a latin1 character set.

I shortened the original log file because the actual log had over 2,000 blanks line :-(. That’s probably something that should be fixed in the code too.

Starting...
Prepare information for data copy...
Prepare information for data copy done
Create shell script for data copy...
Table copy script written to C:\Users\McLaughlinM\Desktop\copy_migrated_tables.cmd
Create shell script for data copy done
Determine number of rows to copy....
Counting number of rows in tables...
 
wbcopytables.exe --count-only --passwords-from-stdin --odbc-source=DSN=SQL Server ODBC;DATABASE=;UID=sa --table [studentdb] [studentdb].[conquistador]
18:29:13 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
18:29:14 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
 
9 total rows in 1 tables need to be copied:
- [studentdb].[studentdb].[conquistador]: 9
Determine number of rows to copy finished
 
Copy data to target RDBMS....
 
Migrating data...
 
wbcopytables.exe --odbc-source=DSN=SQL Server ODBC;DATABASE=;UID=sa --target=student@mclaughlinsql:3306 --progress --passwords-from-stdin --thread-count=1 --table [studentdb] [studentdb].[conquistador] `studentdb` `conquistador` [conquistador_id], [conquistador], [actual_name], [nationality]
`studentdb`.`conquistador`:Copying 4 columns of 9 rows from table [studentdb].[studentdb].[conquistador]
 
ERROR: `studentdb`.`conquistador`:Inserting Batch: Incorrect string value: '\x9Acak' for column 'actual_name' at row 7
`studentdb`.`conquistador`:Finished copying 0 rows in 0m00s
 
29:15 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
29:16 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
29:16 [INF][      copytable]: Connecting to MySQL server at mclaughlinsql:3306 with user student
29:16 [INF][      copytable]: Connection to MySQL opened
 
Copy helper has finished
 
Data copy results:
- `studentdb`.`conquistador` has FAILED (0 of 9 rows copied)
0 tables of 1 were fully copied
 
Copy data to target RDBMS finished
Tasks finished with warnings and/or errors, view the logs for details
Finished performing tasks.

Originally, I thought the failure was due to the extended ASCII characters in the Microsoft SQL Server table. It still failed when I took all of the extended characters out. However, Alfredo suggested it was a character set issue, which is obvious when I looked more closely at the log – '\x9Acak' is clearly an incorrect string. I retyped the INSERT statement for the nine rows and it worked perfectly. Naturally, I’ve updated open Bug 66516 with the log file.

If you’re curious about the Microsoft SQL Server configuration check this post.

Getting the MySQL Workbench’s Database Migration has been interesting, and at present incomplete. While I can now successfully connect to the SQL Server 2012 source database and capture a schemata list, migrating the data from SQL Server doesn’t work. Actually, the connection doesn’t work without modifying a Python library in the current MySQL Workbench release.

I blogged about the SQL Server 2012 installation and Windows SQL Server DSN setup last night because the development manager requested them to create a repeatable test case to help resolve Bug 66516. The existing blog post on the MySQL Workbench blog provides step-by-step instructions, so I passed on providing them. This post documents how far I’ve been able to get with the database migration and where I’m stuck.

MySQL Workbench 5.2.42, which is the current release at time of writing, doesn’t retrieve a schemata list from SQL Server 2008 or 2012 unless you edit one of the Python libraries. The instructions for the change are in Bug 66030.

You need to edit the db_mssql_grt.py source file and include the fix from the bug. While the bug lists where to find the file on Mac OS X, you find the db_mssql_grt.py source and compiled files are in the following Windows directory (on a 64-bit OS). Then, you delete the db_mssql_grt.pyc file, which is recompiled the next time you launch MySQL Workbench and there’s a call to a function in the module (or library).

C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules

You need to edit line 173, and add the CAST function call before you attempt the migration.

170
171
172
173
174
175
176
177
178

def getServerVersion(connection):
    """Returns a GrtVersion instance containing information about the server version."""
    version = grt.classes.GrtVersion()
    ver_string = execute_query(connection, "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)").fetchone()[0]
    ver_parts = [ int(part) for part in ver_string.split('.') ] + 4*[ 0 ]
    version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[0:4]
    return version
 
@ModuleInfo.export(grt.LIST, grt.classes.db_mgmt_Connection)

After editing the file and saving it, you need to delete the compiled version and start MySQL Workbench to generate the db_mssql_grt.pyc. If MySQL Workbench is running you need to shut it down after deleting the compiled Python file and restart it to generate a new compiled file.

Having fixed the error, you should see verification of your connection to the Microsoft SQL Server. If it fails at this point, you have made an error installing or configuring the Microsoft SQL Server or Windows DSN, or you haven’t made the change to the db_mssql_grt.py file.

All the other steps work except for copying the data from the Microsoft SQL Server to the MySQL Server. The failure occurs in Bulk Data Transfer segment of the Database Migration wizard. The first error occurs on the Determine number of rows to copy step. The error log didn’t help very much, so I generated the manual script file.

Testing the generated script manually it fails to connect to the SQL Server instance. It appears the command syntax in the generated script is incorrect or one or more of the choices made during the installation of SQL Server or definition of the Windows SQL Server DSN is incorrect.

Here’s the generated script file:

REM Workbench Table Data copy script
REM 
REM Execute this to copy table data from a source RDBMS to MySQL.
REM Edit the options below to customize it. You will need to provide passwords, at least.
REM 
REM Source DB: Mssql@SQL Server ODBC (Microsoft SQL Server)
REM Target DB: Mysql@mclaughlinsql:3306
 
 
REM Source and target DB passwords
REM set arg_source_password=
REM set arg_target_password=
REM Uncomment the following options according to your needs
 
REM Whether target tables should be truncated before copy
REM set arg_truncate_target=--truncate-target
REM Enable debugging output
REM set arg_debug_output=--log-level=debug3
 
wbcopytables.exe '--odbc-source=DSN=SQL Server ODBC;DATABASE=studentdb;UID=sa' --target=student@mclaughlinsql:3306  --source-password=%arg_source_password% --target-password=%arg_target_password% %arg_truncate_target% %arg_debug_output% --table '[dbo].[ORGANIZATION]' '`studentdb`' '`ORGANIZATION`'

You need to add the following directory to your %PATH% environment variable to test a call to wbcopytables executable:

SET PATH=%PATH%;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE

The following call to the wbcopytables executable requires you unremark the source and target password statements or set them externally from the script. This call should connect and migrate data from the Microsoft SQL Server database to the MySQL Workbench.

wbcopytables.exe --odbc-source='[DSN=SQL Server ODBC;DATABASE=studentdb;UID=sa]' --target=student@mclaughlinsql:3306  --source-password=cangetin --target-password=student   --table '[dbo].[ORGANIZATION]' '`studentdb`' '`ORGANIZATION`'

However, running it in my environment generates the following error:

wbcopytables.exe: Invalid option '--odbc-source=DRIVER={.\SQLEXPRESS};DSN=SQL

This was resolved by development in MySQL Workbench 5.2.43; and you can reference Bug 66516 for more information. Although, you need to watch out for character set mismatches, as qualified in this later post.