MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL Workbench’ Category

Add User Defined Types

with one comment

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:

UserDefinedType

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

MySQLWB_UserDefinedTypes01

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.

MySQLWB_UserDefinedTypes02

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:

CustomGLTable

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

Written by maclochlainn

February 28th, 2014 at 2:03 am

Relationship Notations

without comments

One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.

It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.

ModifyRelationNotations

Hope this helps those working with MySQL Workbench.

Written by maclochlainn

February 27th, 2014 at 1:22 am

MySQL Image Architecture

with 14 comments

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

imagedb ERD

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The system_user table serves as the Access Control List (ACL) for the application.

Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.

Written by maclochlainn

February 23rd, 2014 at 2:02 am

Open a port on Fedora

with one comment

Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the /etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.

You can open a port by adding the following line to the /etc/sysconfig/iptables file (Fedora’s instructions on editing iptables). The file won’t exist initially, but you can create it by running the following command as the root superuser or sudoer:

shell> service iptables save

You you can run the following commands as the root superuser, which saves the line in the iptables file:

shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT
shell> iptables-save

After making the change to the /etc/iptables file you can change the firewall by running the following command as the root superuser:

shell> service iptables restart

Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:

shell> service iptables status
shell> service ip6tables status

MySQL Workbench Configuration

MySQL Workbench Home Page

  1. The first thing you need to do is click on the + symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.

Setup New Connection

  1. The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.

Connect to MySQL Server

  1. The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the student user (or whatever user you’re interested in), and then click the OK button.

Connection Parameter Acknowledgment

  1. When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.

MySQL Workbench Home Page

  1. Click the VMWare Fedora Instance connection to start a new connection.

Connect to MySQL Server

  1. The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the student user (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.

MySQL Workbench

  1. Type the following two lines in the Query1 panel (at least if you have a studentdb database:

    USE studentdb;
    SELECT DATABASE();

Written by maclochlainn

January 18th, 2014 at 11:51 pm

Oracle OpenWorld 2013

without comments

I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.

Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.

A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.

Missing the MySQL Connect 2013 Bus

Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). :-( It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.

Written by maclochlainn

July 13th, 2013 at 11:34 am

MySQL 5.6 Install Steps

with 10 comments

My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.

Installation Steps

The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:

  • Visual Studio Tools for Office 20120 Runtime
  • Microsoft .NET Framework 4 Client Profile
  • Microsoft Excel 2007 or greater
  • Microsoft .NET Framework 4 Client Profile
  • Microsoft Visual C++ 2010 32-bit runtime
  • Microsoft .NET Framework 4 Client Profile

Below are the installation steps after you download the current release .msi file.MySQL56_Install01 The icon should look like the one to the right. For this example, I”m using the mysql-installer-community-5.6.11.0.msi. Double-click the icon on your desktop or from your C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).

  1. The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.

  1. The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.

  1. The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.

  1. The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.

  1. The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.

  1. The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

  1. The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

  1. The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.

  1. The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.

    MySQL Server
    Both the client and server software for the MySQL Server
    MySQL Workbench
    The GUI application to develop for and manage the server.
    MySQL Visual Studio Plugin
    To work with the MySQL Server from VS.
    MySQL Connectors
    Connector/Net, Java, C/C++, OBDC and others.
    Examples and tutorials
    To help you get started with your development.
    Documentation
    Allows you to read the documentation offline.

  1. The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.

  1. The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.

    • MySQL Server 5.6.11
    • MySQL Workbench CE 5.2.47
    • MySQL Notifier 1.0.3
    • MySQL for Excel 1.1.1
    • Connector/ODBC 5.2.4
    • Connector/C++ 1.1.2
    • Connector/C++ 1.1.2
    • Connector/J 5.1.24
    • Connector/NET 6.6.5
    • MySQL Documentation 5.6.11
    • Samples and Examples 5.6.11

  1. The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.

  1. The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.

This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.

Configuration Steps

You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:

  1. The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.

  1. The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.

  1. The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the root password twice, a trivial and unsecure password cangetin is what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.

  1. The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.

  1. The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.

  1. The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.

  1. The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.

  1. The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.

Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System %PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.

I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.

Written by maclochlainn

April 25th, 2013 at 2:00 pm

MySQL Auto Increment

with one comment

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8' at line 2

They concluded that MySQL requires you to assign a default value of 1 as the initial automatic numbering value; then you use the ALTER statement to change the initial sequence value. That assumption is incorrect. The problem was with their assignment of an overriding AUTO_INCREMENT value inside the parenthetical list of columns. That assignment needs to occur after the list of columns and constraints, like

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT
, movie_title  varchar(60))
  ENGINE=InnoDB
  AUTO_INCREMENT=1001
  CHARSET=utf8;

It’s not an unlikely mistake since there’s no clear example on either of the referenced web pages (at the time of writing). It would be nice if they were added but I’m of the opinion some of the reference manual pages are too sparse.

After creating the table, you have the generic fix that seems to appear most often as an answer to setting or re-setting the auto numbering sequence of a MySQL table:

ALTER TABLE elvira AUTO_INCREMENT=1001;

Why would you set the auto numbering sequence to something other than 1? Some designers consider it a best practice to increment from a set point like 101 or 1,001 for your Application Programming Interface (API) and they leave a readily identifiable sequence number set for pre- or post-seeded data in tables. The region of preallocated numbers are typically only used in a few of the tables, in any application, but consistently managing sequences across all tables does sometimes simplifies data diagnostics.

Hope this helps somebody looking for a syntax fix. By the way, you can find it on Page 162 of the Oracle Database 11g & MySQL 5.6 Developer Handbook.

Written by maclochlainn

January 29th, 2013 at 1:10 am

Posted in MySQL,MySQL Workbench,sql

Tagged with ,

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
type enum(‘FUNCTION’,’PROCEDURE’) 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.

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.

Written by maclochlainn

September 16th, 2012 at 6:55 pm