MacLochlainns Weblog

Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:

You enter user defined types by entering a name value and choosing valid MySQL type value before clicking the Add button. When you’ve added your last user defined type, click the OK button instead of the Add button. The next screen shot shows how you can create Oracle Database 11g native data types, specifically the NUMBER and VARCHAR2 data types.

Hopefully, this has shown that you can create User Defined Types let you use MySQL Workbench to create Oracle ERD models. Here’s an example of a table with Oracle’s NUMBER and VARCHAR2 data types:

Yes, MySQL Workbench is a marvelous tool with wide potential for use to solve problems with MySQL and other databases.

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.

While staging to rebuild the Oracle DB Console (Oracle Enterprise Manager – OEM), I needed to check something in my MySQL instance and ran into the following error after changing the machine’s hostname for that OEM test. The message basically says that MySQL Workbench can’t resolve the connection.

The dialog error provides an excellent note, which lists the actual error as the first thing to check. The dialog follows:

This lists the text of the error dialog:

Your connection attempt failed for user '<user_name>' from your host to server at <server_name>:3306:
  Unknown MySQL server host '<server_name>' (0)
 
Please:
1 Check that mysql is running on server <server_name>
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the student has rights to connect to <server_name> from your address (mysql rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for <server_name> connecting from the host address you're connecting from

Navigate to Database -> Manage Connections… in the menu. This opens a dialog where you can manage the details of a connection.

It opens the following dialog page:

The Test button lets you retest whether the connection works. The error message raised by the test, noted below, is not nearly as detailed as the previous error message.

Inspecting the Parameters tab below, you see the hostname value McLaughlinMySQL. The actual hostname is mclaughlinsql. You need to replace the displayed value with the correct value.

After making the change, click the Test Connection button. A correct change prompts you for the user’s password and when successfully entered yields the following dialog.

Hope this helps a few folks resolve the problem when the machine hostname changes.

Working with MySQL Workbench, I was always curious why you couldn’t run a script with a sourcing command, like source or \. command. It raises a 1064 error code, like the one shown in the illustration.

It turned out that there’s a pending feature request to add the ability to run a sourcing command like the following:

SOURCE c:\DATA\some_script.sql

or,

\. c:\DATA\some_script.sql

I added my business reason to the bug. Let’s hope the product managers add it quickly.

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py and .lua file extensions, which would preclude opening a .sql file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.

As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.

After you dismiss (by clicking the x) the non-editable .sql file, you need to click on the Open Script file icon shown below.

This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.

The ReadFile.py below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.

Here’s the ReadFile.py file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print() function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.

Python 2.7 (compatible with MySQL Workbench 5.2):

1
2
3
4
5
6
7
8
9

import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

Python 3.0 (not-compatible with MySQL Workbench 5.2)

You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('') statement.

1
2
3
4
5
6
7
8
9

import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.

If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.

If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …

Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.