Problems migrating data from SQL Server to MySQL

Trying to Migrate Data

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.

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.

Written by maclochlainn

September 13th, 2012 at 1:25 am

Posted in MySQL Workbench,SQL Server 2008,SQL Server 2012

Tagged with mysql, MySQL Migration, MySQL Workbench, SQL Server Migration

16 Responses to 'Trying to Migrate Data'

Subscribe to comments with RSS or TrackBack to 'Trying to Migrate Data'.

How do we delete the compiled version and start MySQL Workbench to generate the db_mssql_grt.pyc ?

Marc

13 Sep 12 at 2:15 pm
Great catch! You delete the .pyc file from the directory, preferably when the software isn’t running.

maclochlainn

13 Sep 12 at 2:29 pm
Today, MySQL Workbench 5.2.43 was released with the fix noted above. The preferred solution is to upgrade to MySQL Workbench 5.2.43+.

maclochlainn

13 Sep 12 at 2:42 pm
I have delete db_mssql_grt.pyc in Program Files(x86)/MySQL/MySQL Workbench CE 5.2.42/modules … but when I restart the program, this file is not regenerate.

What do I miss ?
Do I have to delete all .pyc files ?

Marc

13 Sep 12 at 2:58 pm
Python only compiles a source when calling a function in the module (or file) that lacks a compiled equivalent. That means source code files compile when you run the tool.

By coincidence, they released MySQL Workbench 5.2.43 today. Why don’t you upgrade to the new release?

maclochlainn

13 Sep 12 at 5:04 pm

I upgrate MySQL Workbench to 5.2.43 and try the migration process again but the task Reverse engineer selected schemata has failed executing:

Logs:

Starting...
Connect to source DBMS...
- Connecting...
Connecting to Mysql@192.168.0.183:3306...
Connected
Connect to source DBMS done
Reverse engineer selected schemata....
Reverse engineering utilisateurs from def
- Preparing...
Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\db_mysql_re_grt.py", line 214, in reverseEngineer procedure_names = getProcedureNames(connection, catalog_name, schema_name)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\db_mysql_re_grt.py", line 152, in getProcedureNames
  result = execute_query(connection, "SHOW PROCEDURE STATUS WHERE Db='%s'" % escape_sql_string(schema_name))
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\db_mysql_re_grt.py", line 43, in execute_query
    return get_connection(connection_object).executeQuery(query)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\workbench\db_utils.py", line 214, in executeQuery
    raise QueryError("Error executing '%s'\n%s"%(query, error), code)
workbench.db_utils.QueryError: Error executing 'SHOW PROCEDURE STATUS WHERE Db='utilisateurs''
Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.
SQL Error: 1547
 
Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\workbench\wizard_progress_page_widget.py", line 191, in thread_work self.func()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\migration_schema_selection.py", line 160, in task_reveng
    self.main.plan.migrationSource.reverseEngineer()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\migration.py", line 329, in reverseEngineer
    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: QueryError("Error executing 'SHOW PROCEDURE STATUS WHERE Db='utilisateurs''
Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.
SQL Error: 1547"): error calling Python module function DbMySQLRE.reverseEngineer
ERROR: Reverse engineer selected schemata: QueryError("Error executing 'SHOW PROCEDURE STATUS WHERE Db='utilisateurs''
Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.
SQL Error: 1547"): error calling Python module function DbMySQLRE.reverseEngineer

Failed

I am trying to migrate data from MySQL 5.0.89 to MySQL 5.6.6 and tables are apparently not compatibles between MySQL 5.0 and MySQL 5.1.

Thank you very much for your help so far…
Any idea on how to fix this ?

Marc

17 Sep 12 at 12:40 pm

As you observed, it appears that the errors occur while reading the MySQL 5.0 instance with an expectation of finding MySQL 5.1+ data catalog definitions. Unfortunately, the MySQL Workbench documentation page for Migratability doesn’t provide a list.

I understand it migrates SQL Server 2000, 2005, 2008, and 2012; and MySQL 5.1 to MySQL 5.5.

maclochlainn

17 Sep 12 at 4:36 pm
Hi Marc, could you file a bug report in http://bugs.mysql.com?
That is specific MySQL 5.0, we didn’t specifically test with that version and it seems there’s an incompatibility.

Alfredo

17 Sep 12 at 9:14 pm
For reference, MySQL Workbench 5.2.43 worked to fix my problem migrating from Microsoft SQL Server 2012; and you can read the most recent blog about my last battle with character sets. I’ve also updated the blog post to reflect the fix.

maclochlainn

17 Sep 12 at 9:25 pm
[...] MySQL Workbench 5.2.43 migrates successfully [...]

Bulk Migration Works

17 Sep 12 at 9:31 pm
I filed a bug report in Bug #66861.

Marc

18 Sep 12 at 9:41 am
Marc, Can you post the describe results for mysql.proc?

According to the documentation, it should have 20 columns. I wonder if there was a patch (to fix one of the many Persistent Stored Module (PSM) problems) that should have changed the data catalog for the mysql.proc table. Any way, what do you have in your mysql catalog?

maclochlainn

18 Sep 12 at 12:19 pm

MySQL 5.0.89
mysql> describe mysql.proc;

Field	Type	Null	Key
db	char(64)	NO	PRI
name	char(64)	NO	PRI
type	enum(‘FUNCTION’,'PROCEDURE’)	NO	PRI
specific_name	char(64)	NO
language	enum(‘SQL’)	NO	SQL
sql_data_access	enum(‘CONTAINS_SQL’,'NO_SQL’,'READS_SQL_DATA’,'MODIFIES_SQL_DATA’)	NO	CONTAINS_SQL
is_deterministic	enum(‘YES’,'NO’)	NO	NO
security_type	enum(‘INVOKER’,'DEFINER’)	NO	DEFINER
param_list	blob	NO
returns	char(64)	NO
body	longblob	NO
definer	char(77)	NO
created	timestamp	NO	CURRENT_TIMESTAMP
modified	timestamp	NO	0000-00-00 00:00:00
sql_mode	set(‘REAL_AS_FLOAT’,'PIPES_AS_CONCAT’,'ANSI_QUOTES’,'IGNORE_SPACE’,'NOT_USED’,'ONLY_FULL_GROUP_BY’,'NO_UNSIGNED_SUBTRACTION’,'NO_DIR_IN_CREATE’,'POSTGRESQL’,'ORACLE’,'MSSQL’,'DB2′,’MAXDB’,'NO_KEY_OPTIONS’,'NO_TABLE_OPTIONS’,'NO_FIELD_OPTIONS’,'MYSQL323′,’MYSQL40′,’ANSI’,'NO_AUTO_VALUE_ON_ZERO’,'NO_BACKSLASH_ESCAPES’,'STRICT_TRANS_TABLES’,'STRICT_ALL_TABLES’,'NO_ZERO_IN_DATE’,'NO_ZERO_DATE’,'INVALID_DATES’,'ERROR_FOR_DIVISION_BY_ZERO’,'TRADITIONAL’,'NO_AUTO_CREATE_USER’,'HIGH_NOT_PRECEDENCE’)	NO
comment	char(64)	NO

Marc

19 Sep 12 at 9:03 am

Marc, I formatted the output in an HTML table, and I downloaded and installed MySQL 5.0.91. It appears the posted documentation on the information_schema.routines table is the closest you can get to information on the mysql.proc table. It also appears your Bug #66861 is verified. It appears that the MySQL Workbench team is working the issue and you may not be stuck on MySQL 5.0 very long.

I also logged a feature request for web pages on all tables in the mysql, information_schema, and performance_schema databases.

maclochlainn

19 Sep 12 at 11:50 pm
Thank you very mutch for your help…I really appreciate it !

Marc

20 Sep 12 at 5:34 am
I followed the Workbench migration tool and created a .cmd file to create the table structure later. How do I execute the .cmd file to copy the table structure or do I need to use the sql file to do the migration?

Best,
Steve

Stephen Chovan

12 Feb 13 at 8:01 am

MacLochlainns Weblog

Trying to Migrate Data

16 Responses to 'Trying to Migrate Data'

Leave a Reply

Recent Posts

Things Written About

Pages

Blogroll

Archives