MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Trying to Migrate Data

with 16 comments

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.

Written by maclochlainn

September 13th, 2012 at 1:25 am

16 Responses to 'Trying to Migrate Data'

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

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. […] MySQL Workbench 5.2.43 migrates successfully […]

  11. I filed a bug report in Bug #66861.

    Marc

    18 Sep 12 at 9:41 am

  12. 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

  13. MySQL 5.0.89
    mysql> describe mysql.proc;

    Field Type Null Key Default Extra
    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

  14. 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

  15. Thank you very mutch for your help…I really appreciate it !

    Marc

    20 Sep 12 at 5:34 am

  16. 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

Leave a Reply