For the brave: compiling Workbench 6.3 using Visual Studio 2013 on Windows

Compiling MySQL Workbench yourself is quite a common task for Linux users, even though the application is available precompiled for certain platforms ready from our download page and available in our yum and apt repositories. In this blog post we show you how to compile it on Windows.
Continue reading “For the brave: compiling Workbench 6.3 using Visual Studio 2013 on Windows”

How-To: Guide to Database Migration from MS Access using MySQL Workbench

Edit: added sample table output in MySQL

MySQL Workbench 6.2 introduces support for MS Access migration. This tutorial should help you get your Access tables, indexes, relationships and data in MySQL.

Preparation

Because MS Access ODBC drivers are only available for Windows, migrating from it is also only possible from Windows. As for the destination MySQL server, you can have it in the same local machine or elsewhere in your network.

MS Access stores relationship/foreign key information in an internal table called MSysRelationships. That table is protected against read access even to the Admin user, so if you try to migrate without opening up access to it, you will get an error like this:

[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'msysobjects'. (-1907) (SQLExecDirectW)

The steps to grant read access to Admin are explained below. Unfortunately, the Access UI for that seems to change every version, but we’ll cover at least version 2007.

Preparing a Database Under MS Access 2007

  1. Open the database in Access
  2. Under the “Database Tools”, click the “Macro -> Visual Basic” button to open the VB console
    Screenshot 2014-08-19 11.51.40
  3. To confirm that you’re logged in as “Admin”, type the “? CurrentUser” and press Enter, in the “Immediate” panel:

    ? CurrentUser
    Admin

  4. Type the following command to grant access:

    CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin”

    Screenshot 2014-08-19 11.55.53
  5. Quit

 Open MySQL Workbench and start the Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database –> Migrate in the main menu.

migration_start

A new tab showing the Overview page of the Migration Wizard should appear.

migration_1

 

ODBC Drivers

To check if you have the ODBC driver installed, click “Open ODBC Administrator” to open the system ODBC tool and look at the Drivers tab.

odbcad_access

Important: MySQL Workbench has 32bit and 64bit executables. The ODBC drivers you use must be of the same architecture as the Workbench binaries you are using. So if you’re using Workbench 32bits, you must have 32bit ODBC drivers. Same for 64bits. Because Office 2007 and older was 32bit only and even Office 2010 installs as 32bit by default, you may need to install Workbench 32bits to migrate from Access, even if you have a 64bit machine. If during migration you get an ODBC error about “architecture mismatch between the Driver and Application”, you installed the wrong Workbench.

 

In the User DSN tab, click on Add… to create a DSN for for your database file. For the example, we created one for the northwind sample database.

odbcad_dsn

 

Set up the parameters for your source Access database

Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use and the parameters for the connection.

If you open the Database System combo box you’ll find a list of the supported RDBMSes. Select Microsoft Access from the list. Just below it there’s another combo box named Stored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name of your preference.

The next combo box is for the selection of the Connection Method. This time we are going to select ODBC Data Source from the list. This allows you to select pre-existing DSNs that you have configured in your system.

The DSN dropdown will have all DSNs you have defined in your system. Pick the one you created for the DB being migrated from the list.

In the Default Character Set field you can select the character set of your database. If your Access version uses western/latin characters, you can leave the default cp1252. However if you use a localized version of Access, such as Japanese, you must enter the correct characterset used by your edition of Office, otherwise the data will be copied incorrectly.

Access source

Click on the Test Connection button to check whether an ODBC connection can be established. If you put the right parameters you should see a message reporting a successful connection attempt.

Set up the parameters to connect to your target MySQL database

Click on the Next button to move to the Target Selection page. Once there set the parameters to connect to your MySQL Server instance. When you are done click on the Test Connection button and verify that you can successfully connect to it.

Target connection parameters

Select the objects to migrate

Move to the next page using the Next button. You should see the reverse engineering of the selected database in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below.

Reverse Engineer Progress

Wait for it to finish and verify that everything went well. Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:

Source Objects Page

As you can see the Migration Wizard discovered table and view objects in our source database. Note that only the table objects are selected by default to be migrated. You can select the view objects too, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them yet) so let’s leave them off for now. The same applies for stored procedures, functions and triggers.

If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:

Source Objects Page Expanded

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed as you can see in the image above). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the Objects to Migrate list and that the Migrate Table Objects checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click Next.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

Manual Editing Page

As you can see in the image above there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the Northwind row and rename it.

Manual Editing Page Expanded

An interesting option in the View combo box is the Column Mappings one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

Column Mappings

Run the resulting MySQL code to create the database objects

Move to the Target Creation Options page. It will look like this:

Target Creation Options Page

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the Create Schemata page:

Create Schemata Progress Page

Once the creation of the schemata and their objects finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

Create Target Results Page

You can still edit the migration code using the code box to the right and save your changes by clicking on the Apply button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the Recreate Objects button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the Next button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are for the transference of data from the source Access database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

Data Transfer Setup Page

There are two sets of options here. The first one allows you to perform a live transference and/or to dump the data into a batch file that you can run later. The other set of options gives you a way to tune up this process.

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It will take a little while to copy the data. At this point the corresponding progress page will look familiar:

Bulk Data Transfer Page

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

Migration Report Page

And that should be it. Click on the Finish button to close the Migration Wizard.

 

A little verification step

Now that the Northwind database was successfully migrated, let’s see the results. Open an SQL Editor page associated with your MySQL Server instance and query the Northwind database. You can try something like “SELECT * FROM Northwind.customers”. You should get something like this:

Screenshot 2014-08-20 16.57.45

MySQL Workbench: Vertical Query Output

MySQL Workbench have one nice feature which is probably a stranger for some of us. The name of this feature is vertical query output, it help in situations where the standard Workbench output will not be very useful. This functionality is very easy to use and in this post I’ll try to visualize some of it’s benefits.

First we need to know how to use it, so we’ve provided you two options to execute the query with vertical output. One of them is the menu bar where you can find item named Execute vertically, you’ll also find hint about the shortcut for that option it’s CTRL+ALT+RETURN.

After you know how to get the vertical query output, I’ll show you some screen shots to compare it with command line output.

Let’s take the command that suits best to this type of output, it’s SHOW ENGINE INNODB STATUS. Normally to understand the output, you probably copy it to some notepad app, and add line breaks. Well it was a little annoying, especially when you know how does it look in command line client with \G. So let’s take a look for the output of console  and Workbench.

Vertical output console preview Vertical Output Workbench preview

You should find out that it’s the same view as in the console. Below you’ll see how it looks in Standard Output

vertical_output_show_engine_normal

and with Text Output.

vertical_output_show_engine_text

Here is also one more screen shot of the EXPLAIN query:

vertical_output_explain

Please fell free to comment this, and let us know how do you like it.

MySQL Workbench 6.0: Help is on the way…

Do you know this scenario: you are writing down  a stored procedure but you can’t for the life of you remember the exact syntax of that CASE statement? Has it to end with CASE or not? Can I use more than one WHEN part and how should that be written? Usually you end up opening a web page and read through the excellent MySQL online docs. However, this might cost too much time if you quickly need different statements and other detail info. Here’s where MySQL Workbench’s context help jumps in.

Continue reading “MySQL Workbench 6.0: Help is on the way…”

Video Tutorial: Setup a Restricted SQL Server Account for Migrations with MySQL Workbench

Some users have asked us what is the minimum set of privileges that your Microsoft SQL Server user needs to successfully migrate databases from SQL Server using the MySQL Workbench Migration Wizard. Even though we don’t execute any query that alters anything in your source RDBMS servers, it’s never a bad idea to add an extra security barrier around it.

In short, you need the VIEW ANY DEFINITION permission for the server and the CONNECT andSELECT permissions for the database(s) you want to migrate. But to make this easier for you, we have created a video tutorial showing how to create a user with these permissions using the Microsoft SQL Server Management Studio.

Sergio de la Cruz

How-To: Database Migration from Sybase Adaptive Server Enterprise using MySQL Workbench

In this tutorial I’m going to show you how to migrate your Sybase Adaptive Server Enterprise databases to MySQL.

As usual, we’ll start with a couple assumptions:

  • You have MySQL Workbench 6.0 installed.
  • You have a running Sybase Adaptive Server Enterprise database somewhere in your network. I’ll be using the pubs3 sample database that Sybase distributes with Adaptive Server Enterprise 15.
  • You have installed the Sybase Adaptive Server Enterprise ODBC drivers in the same PC where MySQL Workbench is running. The ODBC drivers are distributed with theSybase Adaptive Server Enterprise SDK and included in the Adaptive Server Enterprise Developer Edition.
  • A running MySQL Server instance with proper user access is available and you are able to connect to it from MySQL Workbench. The Migration Wizard supports MySQL versions from 5.1 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.6.12 CE installed in the same PC where MySQL Workbench is running.

Open MySQL Workbench and start the Migration Wizard

You will find the Migration Wizard icon in the sidebar in Workbench’s main screen, under the name “Database Migration”. Click on it to start the Migration Wizard:

MySQL Worbench Main Screen

A new tab showing the Overview page of the Migration Wizard should appear.

Home Screen of the MySQL Workbench Migration Wizard

In this tutorial we’ll be using an ODBC DSN connection to connect to our source Sybase Adaptive Server Enterprise database. Click on the “Open ODBC Administrator” and create a new connection under the User DSN tab in the window that appears. Select the Sybase Adaptive Server Enterprise driver and set the parameters for your source connection. Here’s how it looks in my PC:

Defining an ODBC connection

Set up the parameters to connect to your source database

Back in the Migration Wizard, move to the next page by clicking on the “Start Migration”button. Now select “Sybase ASE” as the Database System and ODBC Data Source as theConnection Method. Then select the ODBC connection that you defined in the previous step from the DSN combo box. Put your user name, password (optional, will be asked later if you don’t set it) and database name (optional, you will be given the option to select it later from a list). You can take a look at this picture to get an idea of what you should have:

The Source Selection Page

When you have entered all the relevant parameters, click on the “Test Connection” button to verify that you can connect to your source database. Once you have successfully tested your connection, move to the next page by clicking on the “Next” button.

Set up the parameters to connect to your target database

Now you have to set the parameters to connect to your target MySQL server. If you have already created a MySQL Workbench connection to it, you can select it from the Stored Connection selector. Remember to test your connection parameters and make sure that you can connect before moving to the next page.

MySQL Workbench Migration Wizard | Target Selection Page

Click on the “Next” button to move to the next page. The Migration Wizard will communicate to your Adaptive Server Enterprise instance to fetch a list of the catalogs and schemata. If you left blank the Database field in the Source Selection page it will retrieve all of the catalogs in the server. Otherwise it will just fetch the schemata corresponding to the catalog you explicitly typed.

MySQL Workbench Migration Wizard | Fetch Schemata List Page

Verify that all tasks finished successfully and click on the “Next” button to move forward. You will be given a list of catalogs and their corresponding schemata to select the ones to migrate. Keep in mind that you can only migrate schemata from one catalog at a time.

Beware that the concept of schema in Adaptive Server Enterprise is more close to the definition of schemata in Microsoft SQL Server. A schema is more of a group of users that owns certain database objects than a logical grouping or classification of objects.

The Schema Selection page will look like this:

MySQL Workbench Migration Wizard | Schemata Selection Page

As you can see, the pubs3 sample database has only one schema named “dbo”. Now take a look at the “Schema Name Mapping Method” group box. MySQL only supports one schema in each database (to be more precise, a MySQL database is a schema) so we have to tell the Migration Wizard how to handle the migration of schemas in our source database. We can either keep all of the schemas as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. The two last options are for specifying how the merge should be done: either remove the schema names (the Migration Wizard will handle the possible name colisions thay may appear along the way) or either adding the schema name to the database object names as a prefix. Let’s select the second option (it’s the default option) since we only have one schema and we are not particularly interested in keeping its meaningless name.

Select the objects to migrate

Move to the next page using the “Next” button. You should see the reverse engineering of the selected schema in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below:

MySQL Workbench Migration Wizard | Reverse Engineer Page

In the next page (the Source Objects page) you will have a list with the objects that were retrieved and are available for migration. If you click on the “Show Selection” button you will be able to select exactly which of the retrieved table objects you want to migrate, as shown here:

MySQL Workbench Migration Wizard | Source Objects Page

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the “Objects to Migrate” list and that the“Migrate Table Objects” checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click “Next”.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You may have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

MySQL Workbench Migration Wizard | Manual Editing Page

As you can see in the image above, there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the “pubs3? row and rename it.

MySQL Workbench Migration Wizard | Manual Editing Page (Expanded)

An interesting option in the View combo box is the “Column Mappings” one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

MySQL Workbench Migration Wizard | Manual Editing Page (Columns View)

Run the resulting MySQL code to create the database objects

Check any warning or error you get there (you should get none) and move to the Target Creation Options page. It will look like this:

MySQL Workbench Migration Wizard | Target Creation Options Page

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the next page: the Create Schemata page.

Once the creation of the pubs3 database finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

MySQL Workbench Migration Wizard | Create Target Results Page

You can still edit the migration code using the code box to the right and save your changes by clicking on the “Apply” button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the “Recreate Objects” button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the “Next” button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are related to the transference of data from the source Adaptive Server Enterprise database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

MySQL Workbench Migration Wizard | Data Transfer Setup Page

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It may take a little while to copy the data. At this point the corresponding progress page will look familiar:

MySQL Workbench Migration Wizard | Bulk Data Transfer Page

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

MySQL Workbench Migration Wizard | Migration Report Page

A little verification step

Now that the “pubs3? database was successfully migrated, let’s see the results. Open your MySQL Server instance and query the newly created “pubs3? database. You can try something like “SELECT * FROM pubs3.authors;”. You should get something like this:

Querying a migrated table

Conclusions

By now you should have a pretty good idea of the capabilities of the Migration Wizard and should be ready to use it for your own migrations. The official documentation is also there for you and you can always ask any question in the comments of this post or in the migration official forum.

Sergio de la Cruz

 

MySQL Workbench 6.0: Model Synchronization Improvements

In MySQL Workbench 6.0, three longstanding issues related to model synchronization were addressed. In this post, we’ll cover how these issues can happen, the underlying cause and how to resolve them.

Model Synchronization

When you synchronize a model to a database, Workbench performs the following steps:

  1. Retrieve and reverse engineer the target schema from the database, to an internal representation that can be worked on by Workbench
  2. Compare the schema from the model with the corresponding schema from the database, object by object
  3. For each difference found, perform the corresponding action needed to change either the source or the target object, so they’re both looking the same. This may mean creating, altering or dropping objects in the database or updating the model to match the database. Because some of these actions can be destructive (such as when a table or column needs to be dropped), care must be taken to review the generated MySQL script for any mistakes before applying it to the actual server.

Reverse Engineering Source and/or Target

The first step is usually straightforward, since the internal representation used in WB maps 1:1 to schemas and database objects from MySQL. Workbench also supports synchronising with schemas reverse engineered from a MySQL DDL script, which is pretty much the same as using a live database.

Synchronizing with Schemas with Other Names

By default, Workbench will search the server for a schema having the same name as the one in the model. But sometimes, the desired target schema has a different name. For example, one may want to synchronize a model of sakila with a schema called sakila_prod in the database. To allow that, select a schema in the list and click the Force Synchronize With… in the Schema Selection Page of the synchronization wizard.

References to the schema name in code objects (views, triggers, SPs and functions) will be automatically refactored to the target name. So, for example, if your sakila schema has a view defined as:

CREATE VIEW sakila.film_list AS select * from sakila.film

and you synchronize it against sakila_prod it will be changed to:

CREATE VIEW sakila_prod.film_list AS select * from sakila_prod.film

Renaming schemas

WARNING: A related case is that of when instead of synchronizing a schema with another of a different name, you actually want to rename it. But because altering the name of an existing schema is not supported by MySQL, a rename  would mean dropping the original schema and creating a new one in its place. Because data is not copied by the model synchronization, your data would be lost. Therefore, keep that in mind when renaming schemas in your model and synchronizing it.

Example

Here we are synchronizing a schema called sakila, but the target MySQL server does not have that schema in it. In this server, the schema was created with the name sakila_test

To force Workbench to sync sakila from your model with sakila_test from the database, select it in the list and then pick the target schema you want in the dropdown list and click [Override Target].

Schema Comparison

Things are easy when you just add, modify or delete an object in your model. We just need to lookup an object with the same name in the database and perform the appropriate action: a new object in the model must be CREATEd in the database and a removed object must be DROPed. Changed objects are handled differently depending on their type. Views, indexes, functions, stored procedures and triggers can just have their old version dropped and recreated from scratch. Tables and columns are updated using the ALTER statement as recreating them would mean data loss.

However, when an object is renamed, there’s no straightforward way to know that, for ex., the column you once created as name in your model and then forward engineered to the database, is now renamed in the model to first_name. To solve that, WB stores the original name of the object in an additional internal field called oldName. Then, when searching for a matching object in the database, it searches by oldName. So it would correctly determine that the column called name corresponds to first_name, which should be renamed for synchronization purposes. Once synchronize renames the object in the database, oldName is updated to the new name, so next time a rename occurs, we can locate the DB object by the new name.

Sometimes, the object is renamed directly in the database. So when you synchronise the model to the database, there’s no way for WB to know that by itself. oldName is of no help here. So the result is that WB thinks the renamed DB object is an object that was removed from the model, so it must be DROPped, while the original object in the model doesn’t exist yet, so it is to be CREATEd. Which could lead to potentially unpleasant consequences.

Sinchronizing against a script or when both source and targets come from a script/database is an even bigger problem, since we don’t have any information at all about renames from either direction.

Above, we’re synchronizing sakila from the model with sakila_test from the database. In the database, the table actor was created with a typo and it’s called atcor. The table was created in the database independently from the model, so Workbench doesn’t know that atcor is supposedly the same column as actor. If you click the rows corresponding for both, you see a CREATE statement for actor

and a DROP for atcor. We need to tell Workbench that actor from the model and atcor from the database are the same columns.

In the country table, there’s a similar problem with columns. In the database, the column called country was created as name, supposedly outside of Workbench, so there’s no knowledge that country was once called name or anything like that. Thus, the synchronization action appropriate for this case is to DROP name and add country. Again, what we actually want is some way to match country and name together.

So starting from MySQL Workbench 6.0, you can manually specify the mapping of tables and columns between source and target schemas. In the “Model and Database Differences” page in the synchronisation wizard, click the Column Mapping… button to bring up the Column Name Mapping dialog. For fixing table mapping issues, click Table Mapping… for an analogous dialog for tables.

When you click the [Table Mapping…] button a dialog pops up, where all the tables for that schema is shown in a list. The list has the name of the table in the source/model, the default table to be matched against (taken from oldName), the actual table that it will be synchronized with (same as the previous column by default, blank if the target table doesn’t exist or the table it will actually be matched against, if it’s being overriden). The last column shows the expected action that would be taken (drop, create, change or rename), always considering a Model to Database synchronization direction. To override the mapping of a table, select it in the list and pick the table from the database that it should be mapped to.

Here is the result of the remapping of tables, as you see in the generated SQL, it will simply rename atcor to actor.

The Column Name Mapping dialog is similar to the one for tables. Click the table you want to fix and select [Column Mapping…] to bring it up.

Conclusion

Ideally, any changes to the database schema would first happen in your model and the database would only be updated using code generated from it. That allows WB to transparently and automatically handle any renames and changes made to your schema objects and graciously update your database to match it. But reality is not always so neat and often changes are made in the database independent of your model or in an order/situation that’s not very easy to track.

As we’ve seen, MySQL Workbench 6.0 includes some functionality to work around problems of that kind, which now allows Model Synchronization to work correctly in almost any scenario.

 

MySQL Workbench 6.0: Table Data Search

scr 1. Location of Search table data on the main toolbar

One of the new features of MySQL Workbench 6.0 is Table Data Search. The main purpose of this was to ease data searching through the whole instance. Previously, we needed to use some tricks to get the query to run over all schemas that we’ve got on the server. Now it’s easy to find the searched term with much less hassle. This functionality is easy to use and provides searching through all columns and even all types. However, we can’t forget that due to the nature of this tool we must take some precautions to not overload your server.

To use this functionality we pick it up from the Database menu called “Search Table Data…” or just click the icon on the main toolbar (scr 1). The third option is to select Search Table Data.. from context menu when you right click on the schema list on some schema.
After that you will see the new screen (scr 2) with a few options which you must provide to get started working with it.

scr 2. Search table data window

As you can see, the interface is very simple, but I’ll still try to explain some of the options. The first thing is the Search for Text input, where you just put the phrase that you’d like to find. The type of the phrase depends on the select box that is located below this input (scr 3). That select box has three different options of search type which are:

Search using =
Search using LIKE
Search using REGEXP

The first one Search using =, is the simplest one, it just matches fields using the = operator.
Second option is little more powerful, it allows you to search using the database LIKE  operator where you can provide wild cards like % (match any character any number of times) or _ (match any char, a single time).
The third option allows you to use regular expressions.

scr 3. Search table data match options

Next to the selection box, you’ll see two inputs described as Max. matches per table, and Max. total matches. The first one is responsible for limiting search occurrence through one table. The second one will limit the whole search, so when there will be over 10000 (initial value) entries, search will stop. Last option is the check box named Search columns of all types. Initially searching is done through only text fields, when this option is enabled, then all columns will be used and will be cast to the char data type. This check box have a great impact over whole server performance use it with caution!

Now that you know how each option relates to the searching, I’ll try to step through a sample search and describe the results. I assume that you’ve got sakila database, cause I’ll make a sample using that database.  

  1. open the Search table data…
  2. enter text mary into the Search for Text field
  3. check if the Search using option is set to the equal sign
  4. check if the Search columns of all types check box is unchecked
  5. select the schema (or column), that you’d like to be searched for the matching phrase in the schema selector
  6. press the Start Search button.
scr 4. Search table data results view

As in the screen shot (scr 4), your result should be the same. You can click on the arrow in the result list to expand details of the row. The columns are as follows:

Schema – name of the database that holds the columns that matched the criteria
Table – name of the table
Key – primary key value assigned to the data that match criteria
Column – column name that holds the matched data
Data – the top row will contain information, how many times the phrase were matched in the details it will contains the matched data

There is also context menu for the result set, it’s available when you right click on the result set. The menu allows you to copy query that where used to find the matching rows. you can also copy query that will match the rows against primary key, and the last option allows to copy the key values.

And that’s all, you’ve done your first search using the new Search Table Data option. Please remember that using this feature have very big impact on general server performance because you’re generally doing full table scans. Stay with us to get more information cool information.

How-To: Database Migration from Sybase SQLAnywhere using MySQL Workbench

In MySQL Workbench 6.0 two new additions have been made to the supported RDBMS sources list in the Migration Wizard: Sybase SQLAnywhere and SQLite. In this tutorial I’m going to show you how to migrate your Sybase SQLAnywhere databases to MySQL.

As usual, we’ll start with a couple assumptions:

  • You have MySQL Workbench 6.0 installed.
  • You have a running Sybase SQLAnywhere database in your local computer (i.e. the computer where you are running MySQL Workbench. I’ll be using the demo database that Sybase distributes with SQLAnywhere 12.
  • A running MySQL Server instance with proper user access is available and you are able to connect to it from MySQL Workbench. The Migration Wizard supports MySQL versions from 5.1 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.6.12 CE installed in a virtual box inside my home network.

One cool new feature we’ve added to the MySQL Migration Wizard in the 6.0 release is its ability to use Python DB API modules to communicate with the source RDBMSes. We are not abandoning ODBC, but we want you to have more options (= more freedom) for connecting to your source RDBMSes. As it turns out, some RDBMSes don’t provide ODBC drivers compatible with iODBC, the ODBC driver manager that we officially support for Linux and Mac OS X. And, saddly, one of these RDBMSes is Sybase SQLAnywhere.

If you are a Windows user, you’ll be better off by downloading the SQL Anywhere ODBC driver from the Sybase website and using it instead of the sqlanydb module.

In this post I’m going to be running MySQL Workbench in my Ubuntu 12.04 desktop, so I’ll be installing a Python DB API driver module to communicate with my local Sybase SQLAnywhere server. The MySQL Migration Wizard has support for sqlanydb, the official Sybase SQLAnywhere Python DB API module. You can download and install it from its website. One you have extracted the tarball, type “sudo python setup.py install” (without the quotes) in a command line terminal and you should be done. To verify that it was properly installed, type this:

python -c "import sqlanydb"

If everything went well, you should not see any error message when you execute this command. If you do see an error message, please refer to the sqlanydb installation page for further instructions on how to properly install it.

Open MySQL Workbench and start the Migration Wizard

You will find the Migration Wizard icon in the sidebar in Workbench’s main screen, under the name “Database Migration”. Click on it to start the Migration Wizard:

MySQL Workbench Home Screen

A new tab showing the Overview page of the Migration Wizard should appear.

Home Screen of the MySQL Workbench Migration Wizard

Set up the parameters to connect to your source database

Move to the next page by clicking on the “Start Migration” button. Now select “Sybase SQLAnywhere” as the Database System and sqlanydb as the Connection Method. You should as well type all the other parameters that allow you to connect to your source SQLAnywhere database. You can take a look at this picture to get an idea of what you should have:

MySQL Workbench Migration Wizard | Source Selection Page

When you have entered all the relevant parameters, click on the “Test Connection” button to verify that you can connect to your SQL Anywhere database. Once you have successfully tested your connection, move to the next page by clicking on the “Next” button.

Set up the parameters to connect to your target database

Now you have to set the parameters to connect to your target MySQL server. If you have already created a MySQL Workbench connection to it, you can select it from the Stored Connection selector. Remember to test your connection parameters and make sure that you can connect before moving to the next page.

MySQL Workbench Migration Wizard | Target Selection Page

Click on the “Next” button to move to the next page. The Migration Wizard will communicate to your SQLAnywhere instance to fetch a list of the catalogs and schemata. If you left blank the Database field in the Source Selection page it will retrieve all of the catalogs in the server. Otherwise it will just fetch the schemata corresponding to the catalog you explicitly typed.

MySQL Workbench Migration Wizard | Fetch Schemata List Page

Verify that all tasks finished successfully and click on the “Next” button to move forward. You will be given a list of catalogs and their corresponding schemata to select the ones to migrate. Keep in mind that you can only migrate schemata from one catalog at a time.

Beware that the concept of schema in SQLAnywhere is more close to the definition of schemata in Sybase Adaptive Server Enterprise. A schema is more of a group of users that owns certain database objects than a logical grouping or classification of objects.

The Schema Selection page will look like this:

MySQL Workbench Migration Wizard | Schemata Selection Page

The real juice in the demo sample database is in the “GROUPO”. Now take a look at the“Schema Name Mapping Method” group box. MySQL only supports one schema in each database (to be more precise, a MySQL database is a schema) so we have to tell the Migration Wizard how to handle the migration of schemas in our source database. We can either keep all of the schemas as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. The two last options are for specifying how the merge should be done: either remove the schema names (the Migration Wizard will handle the possible name colisions thay may appear along the way) or either adding the schema name to the database object names as a prefix. Let’s select the second option (it’s the default option) since we only have one schema and we are not particularly interested in keeping its meaningless name.

Select the objects to migrate

Move to the next page using the “Next” button. You should see the reverse engineering of the selected schema in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below:

MySQL Workbench Migration Wizard | Reverse Engineer Page

In the next page (the Source Objects page) you will have a list with the objects that were retrieved and are available for migration. If you click on the “Show Selection” button you will be able to select exactly which of the retrieved table objects you want to migrate, as shown here:

MySQL Workbench Migration Wizard | Source Objects Page

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the “Objects to Migrate” list and that the“Migrate Table Objects” checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click “Next”.

Review the proposed migration

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You may have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:

MySQL Workbench Migration Wizard | Manual Editing Page

As you can see in the image above, there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the “demo” row and rename it.

MySQL Workbench Migration Wizard | Manual Editing Page (Expanded)

Note how the Migration Wizard had to change some incompatible settings from the source database to make it work with MySQL. In this case, you can see that the “demo” database has two foreign key constraints with duplicated names, so they had to be changed to make them unique. Every time the Migration Wizard does something like that, you will see a warning message next to the affected objects. At the end of the migration process you will be given a summary that will include this warnings too, so don’t forget to look at that summary too.

An interesting option in the View combo box is the “Column Mappings” one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.

MySQL Workbench Migration Wizard | Manual Editing Page (Columns View)

Run the resulting MySQL code to create the database objects

Since the warnings we got are absolutely fine, we can move to the Target Creation Options page. It will look like this:

MySQL Workbench Migration Wizard | Target Creation Options Page

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the next page: the Create Schemata page.

Once the creation of the demo database finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

MySQL Workbench Migration Wizard | Create Target Results Page

You can still edit the migration code using the code box to the right and save your changes by clicking on the “Apply” button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the “Recreate Objects” button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the “Next” button.

Transfer the data to the MySQL database

The next steps in the Migration Wizard are related to the transference of data from the source SQLAnywhere database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

MySQL Workbench Migration Wizard | Data Transfer Setup Page

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It may take a little while to copy the data. At this point the corresponding progress page will look familiar:

MySQL Workbench Migration Wizard | Bulk Data Transfer Page

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

MySQL Workbench Migration Wizard | Migration Report Page

A little verification step

Now that the “demo” database was successfully migrated, let’s see the results. Open your MySQL Server instance and query the newly created “demo” database. You can try something like “SELECT * FROM demo.Contacts;”. You should get something like this:

Querying a migrated table

And why not take it a step further and create an EER diagram from the migrated database? Click on the “>” icon next to the Models section in the main screen of MySQL Workbench and select “Create EER Model From Existing Database” from the menu that will appear. Follow through the wizard steps. Once done go to Arrange –> Autolayout in the main menu to accomodate your EER model and you should get something like this:

EER Diagram of the Migrated Demo Database

Conclusions

By now you should have a pretty good idea of the capabilities of the Migration Wizard and should be ready to use it for your own migrations. The official documentation is also there for you and you can always ask any question in the comments of this post or in the migration official forum.

Sergio de la Cruz