MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

Open World 2012

with 2 comments

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …

Written by maclochlainn

October 4th, 2012 at 12:30 pm

MySQL Workbench Book

with 2 comments

Finally, I finished writing the MySQL Workbench book. It’ll be available next spring. Now it’s time to leave for the plane, fly to San Francisco, and see everyone at MySQL Connect.

I look forward to meeting folks, I’ll be presenting after MySQL Connect for those staying for Oracle Open World. My presentation is at Oracle Develop on Monday, 10/1/12 from 16:45 – 17:45, in the Marriott Marquis – Foothill F. As I mentioned in an earlier post, you can probably catch me in Moscone West at the bookstore. The publisher requests we attend book signings. ;-)

Unfortunately as a speaker I need to convert my Keynote to Powerpoint, and had to purchase, install, and update Microsoft Office 2011 on my Mac. Open Office and Keynote weren’t on the approved list, alas …

Update: The book published 4/9/2013 (a bit of a delay from completing the write, eh?). It’s available on Safari as of yesterday.

Written by maclochlainn

September 28th, 2012 at 12:00 pm

Posted in MySQL,MySQL Workbench,Oracle

Tagged with ,

MySQL 5.0 migration bug

without comments

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:

Field Type Null Key
db char(64) NO PRI
name char(64) NO PRI
specific_name char(64) NO  
language enum(‘SQL’) NO SQL
sql_data_access enum(‘CONTAINS_SQL’,…) 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’,…) NO  
comment char(64) NO  

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.

Written by maclochlainn

September 20th, 2012 at 12:47 am

Bulk Transfer Works

with 9 comments

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.

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.

Written by maclochlainn

September 16th, 2012 at 6:55 pm

UTOUG Fall Symposium

without comments

Heading out to speak on Comparative SQL between Oracle and MySQL at the Utah Oracle User’s Group Fall Symposium. I’ll look forward to meeting old and new friends. Also, I’ve got a couple promotional copies of my Oracle Database 11g and MySQL 5.6 Developer Handbook to give out at the end of the session.

I’m also presenting Monday evening at Oracle Develop during Oracle Open World 2012. I’ll also have copies of the Oracle Database 11g PL/SQL Programming and Oracle Database 11g PL/SQL Programming Workbook books to give away at the end of the session. Naturally, McGraw-Hill wants me to stop by the bookstore for a signing. If you have time and are in Moscone West, stop by and visit.

Naturally, I’m also attending MySQL Connect to see the brilliant sessions being presented. Hope to see you in San Francisco! :-)

Written by maclochlainn

September 5th, 2012 at 1:56 pm

Posted in MySQL,Oracle

Hostname Change Error

without comments

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

Written by maclochlainn

September 2nd, 2012 at 10:23 pm

MySQL Workbench Limit

with 6 comments

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


\. c:\DATA\some_script.SQL

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

Written by maclochlainn

September 1st, 2012 at 11:59 pm

Posted in MySQL,MySQL Workbench

Tagged with ,

MySQL Workbench Scripts

with 2 comments

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:


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

# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# 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])
    if not line:

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):

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

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.

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

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.

Written by maclochlainn

August 18th, 2012 at 4:57 pm

Oracle and Java Tutorial

without comments

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

  • A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the source code with the javac utility, as shown below:

After you compile it, you should run it as follows:

java WriteReadCLOB
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The hostname of your machine.
  • Port: The port that the Oracle Listener is running on (the default value is 1521).
  • Database: The Oracle TNS Alias, which is orcl for the full database sample database or xe for the Oracle Database 10g Express Edition.
  • UserID: The user (schema) name where you’ve created an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

August 1st, 2012 at 12:25 pm

Delay or synchronize it?

with one comment

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

package vulcantech.vth.server.commands;
import vulcantech.vth.server.combeans.MessageBean;
public class MessageHandler implements Handler {
  public void handleIt(Object... args) {
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
    DatabaseConnection dbconnection = new DatabaseConnection();
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
    synchronized (this) {
    try {
    } catch (IOException e) {

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).

Written by maclochlainn

July 22nd, 2012 at 3:53 pm