Migration to MySQL with SQLyog ver 4.1. - Page 4

Methods supported and applied by the Migration Tool.

Though the ODBC-standard is about 15 years old, the ODBC-implementation with many database systems has some non-standard issues (for instance, non-standard data-types used for internal purposes, non-standard formats of temporary tables). All of these issues have been sorted out. The ODBC-import tool will import what it should import and no more than that. "Workarounds" for known bugs have been implemented with some commonly used ODBC-drivers.

The new Migration Tool uses ODBC-connections only. JDBC and other (proprietary) connection methods are not supported and there are no plans of supporting any other connection method.

The Migration Tool has been extensively tested with many different database systems: personal/office-suite type databases like Microsoft Access and Dbase as well as common Database Server systems like MS SQL Server, Oracle, Sybase etc.

What's New

We will now delve more deeply into what was briefly introduced with the Northwind-example above. If you had no difficulty in understanding what you have been reading up to now, I propose that you read on.  If not, then try out some rather simple examples for yourself before continuing from here.

An overview

Let's have a more detailed look at what is new with the SQLyog Migration Tool.

• ODBC-import with SQLyog is now implemented in the code of the SJA (SQLyog Job Agent) (sja.exe and not sqlyog.exe in terms of filenames).
• It will now import not only DATA but also METADATA.
• It offers a wide range of general options for the import.
• It offers various options to transform data during import and options for filtering of which should be imported.
• It implements a "trigger" or "write-back" functionality.
• A notifications feature, similar to the SQLyog/SJA "Notifications Service," has been implemented with the new ODBC-import tool.

Let's have it in some more detail.

1) ODBC-import with SQLoyg is now implemented in the code of the SJA.

What is the SJA ?

As you might know, SJA is a standalone command-line MySQL client available for WINDOWS and LINUX (the Windows version, however, can be started from inside the SQLyog GUI as well as we have just seen above). The SJA will run on Windows and Linux, but will connect to any MySQL server running on any OS. That is the meaning of the term "MySQL client."

The ODBC-import tool connects to a MySQL server using the native MySQL C-api (compiled into SJA executable). That MySQL is the "target" of the import. The "source" of the import is any ODBC-compliant database. It uses the Windows standard ODBC-management, so any DSN types such as "user-DSN," "system-DSN" and "file-DSN" are all possible. If Windows can access and accepts the ODBC-driver, SJA can and does too.

The ODBC-import tool has the same advanced connectivity features as the SQLyog GUI itself and the other tools implemented with the Windows' version of the SJA: different tunnelling options and the ability to work behind proxies. The different tunnelling options allow you to connect to your MySQL server even if your ISP/Admin disallows remote connections.

The OBDC-import is only available is the Windows version of the SJA as of now. I cannot tell if it will ever be coded into the Linux-version. I don't think it is "just around the corner." I do not know much about ODBC-implementation on LINUX but I believe that to connect to an ODBC-source from Linux would probably take a completely rewritten version of the code of the Migration Tool. That is opposite to the situation when SQLyog (and the SJA) connects to MySQL, because both MySQL-server and –client implementations on different platforms are very similar.

With the release of SQLyog 4.1, SJA now executes 4 different types of <jobtypes>. Besides the new one with SQLyog 4.1 <importjob> there are the three "old" ones as well: <backupjob>, <notifyjob> and <syncjob>. That I use this XML-like terminology to describe the job types ( or <jobtypes> to stay in XML) is no coincidence, since SJA reads its parameters from an XML-file.

Executing the SJA-based ODBC-import tool.

That the ODBC-import is now implemented in the code of the SJA means that you will not have to start the SQLyog program to do an ODBC-import because the SJA can be run from command-line and more important:

• The SJA itself can be scheduled using the Windows Scheduler (or any other (third-party) scheduler running as a service with the OS).
• The SJA can be run in batch-mode from a Windows .bat-file. That .bat-file of course can contain any legal OS/System command and any other executable that can run in batch mode, and passing of a parameter to the executables follows the standard of the OS. For instance if you want to import from an Access database to MySQL using the SQLyog/SJA trigger functionality (that we will describe later) you might want to make a copy of the database(.mdb)-file. The windows .bat file will then be something like

  copy myaccs.mdb myaccs.bak –y
  sja importjob.xml

  (where the –y parameter answers "yes" to the question whether a previous myaccs.bak should be overwritten)

• There can also be several instances of the SJA in one .bat file. This way you can import from many various sources into a MySQL-database from one .bat file containing more SJA-instances. That the .bat-file itself can be scheduled. Moreover, it may contain other SJA-jobtypes as well – of which each has its own jobfile. For instance, importing (using Migration Tool) from an Oracle Database into MySQL ver 4.1, importing from a MySQL 3.23 server (also using Migration Tool), and importing from another MySQL 4.1 server (where I would generally recommend use of the Synchronization Tool and not the Migration Tool – simply because the checksum's algorithm of the Synchronization Tool is more efficient than using ODBC) could be done as one batch job. Therefore, if you happen to own a factory producing cars and another producing apple juice and even one more – and even if each of those uses different databases – you will be able to transfer all relevant financial data from all of those different places to one central MySQL database when you want to!
• The SJA can be launched form an external program capable of launching an external program, no matter if it is a scripting language such as PHP or a compiled application written in C or Delphi. That gives you even more advanced options than the batch-mode ability – depending on what you are able to do from that other programming language.

The SJA reads its parameters from an XML-file, readable and editable by humans as well as other programs. The "wizards" built into SQLyog let you create a job-file for all job types. You can save it or execute it immediately, as you like. You only need to have a little bit of experience with mark-up languages to be able to understand or edit an SJA job file.

You can also open any existing job file from the appropriate GUI-"wizard", change the parameters as needed and save or execute it. Personally, I prefer to use the GUI for "small" tasks and the command line for "longer-running" tasks

2) Import of DATA and METADATA.

The import of METADATA and not only DATA is a major achievement of the new ODBC-import tool. The term "METADATA" means "DATA about DATA." It is the data stored in the database itself about the organisation of data--table definitions, information about all sorts of indexes and that sort of thing. How these METADATA are organized and stored differ with various databases, but any database engine will need to store METADATA some way. The new ODBC-import tool reads them all.

The METADATA it reads and imports are

• (ordinary) indexes, including – of course – unique indexes
• primary keys
• foreign keys

That is what is normally associated with the term "METADATA."

Note that when importing to MySQL version 5, it does not attempt to import

• Stored Procedures
• Triggers
• Views

It does not matter whether these database features are supported or not by the source ODBC-database.

As far as Stored Procedures and Triggers are concerned, that is OK with me! Except for the most trivial examples, I do not think I would ever trust an automatic import/conversion tool anyway, unless it had proven very reliable! However, as far as Views are concerned I think that could be implemented safely whenever the ODBC-driver allows for it. So that is one of my wishes for the future.

3) General options for the import

If you are familiar with the SQLyog/SJA MySQL-to-MySQL Synchronization tool, then you also know what to expect to find here. Basically, the same options are implemented with both tools. There are options to choose whether to append imported data to an existing database or table or whether to delete old data before the start of the import. There are also options for what to do in case of errors – cancel the job or simply go on with the next row of data. In addition, in case you have chosen to append data to an existing database, there are options for what then to do in case a Primary Key or Unique Index conflict occurs during import: keep the old data or replace the data with the one from the ODBC-source.

There are options too whether foreign keys and indexes should be imported. Simply uncheck both if you want to import DATA only and NO METADATA.

4) Transformation and Filtering options.

Transformation:

The transformation option lets you transform the names of tables and columns and the column-definition data type for each column of data during import. This is very practical for many reasons – the most obvious is the simple one that field-length definitions with similar data types on various databases differ. This is most obvious with the VARCHAR-type but applies to other types as well. The ODBC-driver used for the import will "propose" a conversion of the data-type, but it might not be the one you want! If the ODBC-driver "proposes" a char(40) as the data type to create with MySQL simply change it to a varchar(60) if you like.

You can do a little experiment yourself to understand the usefulness of this facility. If you have a MySQL and a MS Access database available, try to EXPORT from Access to MySQL (using the MyODBC-driver from MySQL) and compare the result with the one you get when IMPORTing into MySQL (using the Microsoft ODBC driver for Access and SQLyog, but without making use of the transform option). If you never did that comparison before you will probably be surprised how much ODBC-drivers disagree on how a data type from one database is best created with another database!

With the transformation option of the new SQLyog import tool it is YOU that is in control – not a piece of software that you cannot configure. There is no need to edit any strangely formatted file to do any transform of data types or any renaming of tables or columns. It is all available from the GUI-wizard and from an easily understandable XML-file as well, if you prefer.

Be aware that data may be truncated by MySQL when importing, if you specify a too short data type. In case there is a conflict with the transform option you have chosen and the data, the MySQL Server is in control. Data might be truncated or converted (if MySQL considers it safe) or an error may arise – the rules of MySQL decide!  In some cases, what happens could depend on the MySQL Server version.

You should know your data so much yourself, that you know what you are doing when using the transform option!

Filtering:

The filtering options available with the new ODBC-import Tool allows for "horizontal" as well as "vertical filtering." Together, they constitute what I would call a "grid filtering."

What I call "horizontal filtering" is simply an ability to select/deselect tables and columns for the import. That is done from a checkbox in the GUI-wizard.

What I call "vertical filtering" is an ability to select/deselect rows for the import. The selection of rows to import is done by specifying a WHERE-clause to execute against each row on the source. It is identical to the implementation of the SQL_WHERE with the SQLyog/SJA Synchronization Tool – except for one difference. This difference is that the WHERE-clause of the Migration Tool is executed with the ODBC-source. Therefore, the syntax must follow the syntactic rules of the SQL-"dialect" of the source-database (that most likely is not MySQL) and the ODBC-driver used for import. With the Synchronization Tool, both source and target are identical – or "almost identical" - MySQL-versions, so here you will not have to take the differences of various SQL-"dialects into account.  We have seen one example already: that Microsoft Access allowed us to use the '*' –character as a wildcard in the WHERE-statement.

5) Trigger options

The "trigger" functionality is the ability to perform changes to the ODBC-database from where data is imported. The changes take place with each row of the source after data has been saved in the MySQL-database. The option is available and configurable for each individual column.

The trigger functionality simply sends an SQL-statement to the source database. The statement will be executed on the row that has just been imported to MySQL. The two SQL types of statements available as of now are DELETE and UPDATE.

The DELETE trigger option simply deletes that row of data from the source after it has been saved to the MySQL database. Not so much more to write about this one, I believe!

The UPDATE trigger option lets you perform UPDATES to the source database on the row in operation. You simple enter the "last half part" of a normal SQL UPDATE-statement.

Note that what you enter as an UPDATE-trigger sent to the source database must follow the syntactic rules of the SQL-"dialect" of the source database, as was the case with WHERE-clause. Thus, not only constants but also functions and expressions are possible - actually, any expression that is legal with the syntax of the SQL-dialect of the ODBC-source and the ODBC-driver used for the import will work. With MS-Access you can enter "date()" and the current date will be written to the source – provided that the date()-function is valid with the column-definition. In addition, you might increment a "mycolumn"-column by entering "mycolumn+1" as the UPDATE-trigger to execute against the "mycolumn"-column.

Note that this is a trigger-functionality of the SJA itself. It has nothing to do with any (native) trigger functionality that might exist with the database imported from – or with the trigger functionality of MySQL version 5.  It is something of its own. And very unique actually!

6) Notifications

A notifications' feature, similar to the SQLyog/SJA "Notifications Service," has been implemented with the new Migration Tool. It lets you send an email notification informing you of the result of the ODBC-import. The message is configurable--you can choose to either always send a message or only to send a message in case of an error. This notifications' feature is particularly useful when used with scheduled imports. In addition, it can be very useful with imports that are started manually. For instance, you can set up a "rule" with the mail-client receiving the message, telling that mail-client that these messages should all be moved to a certain folder in the mailbox folder system. The messages gathered there provide you with complete logging of all ODBC-imports (and other SJA jobs as well, if you want).