Migrating data to MySQL

Unlike with simple data imports, with migration or transfer of old data to a MySQL database a major problem arises when it is not possible to retain ID field values (Primary Keys) and the relations between tables have to be reconstructed. With this example we want to demonstrate the possibilities that FlowHeater offers to help with migration. Several example cases are listed, ranging from simple to fairly complex.

Please refer to information about the examples used that is given at the end of the article.

Migration of old Auto ID (auto increment) values

MySQL data migration - import auto increment fieldsThis is certainly the simplest way. To migrate the data without restructuring into a new MySQL database, you simply need to create an import Definition for each table to be migrated and import each table once into the new MySQL database.

The only problem comes from Auto ID values. MySQL has a built-in possibility to assign values to fields with the attribute “auto increment” to keep them unique. The MySQL database automatically records the highest key value and takes this (+1) as the next automatic value. In this way it ensures there are no duplicate keys for normally inserted records.

To retain the original value with FlowHeater you must mark the auto-incremented field in the MySQL Adapter on the WRITE side under “Fields / Data types” and uncheck the “Auto increment” option. This way FlowHeater treats the field like any other and imports the value into the MySQL database.

Migration of master/detail (one-to-N) relations - simple

MySQL data migration - add second adapterTo transfer master/detail (one-to-N) relations and the ID of the master table (Primary Key) in the new database is of type “auto increment” then FlowHeater offers a powerful function to implement the relation relatively simply.

For this purpose create a new Definition and select the Adapter/data source for the READ side you wish to migrate from. In the case of a database Adapter you can use an SQL join on the READ side e.g. to export invoices with their related invoice lines.

select * from INVOICES as V inner join INVLINES as L on V.InvNo = L.InvNo order by L.InvNo

On the WRITE side select the MySQL Adapter. When creating the new FlowHeater Definition you first choose an Adapter as master to write the invoices. Then using the Designer (see screenshot) add a further MySQL Adapter as detail to write the invoice lines. Now we have to configure the Adapters.

MySQL master table

First we configure the MySQL Adapter for the master table (INVOICES). Open the Configurator and enter the connection details for the MySQL database. It is also important under the General tab to check “Insert data” and “Ignore existing records”. This ensures that only a new record is imported for each invoice rather than for each Invoice line, of which there can be several for each invoice.

Under the “Fields / Data types” tab, choose the master table (INVOICES) fields and load the schema information. Close the Configurator window and connect all necessary fields apart from the Primary Key (Auto Increment) field with the first MySQL Adapter.

MySQL detail table

Now we must configure the MySQL Adapter for the detail table (Invoice lines). Open the Configurator for the detail table and also enter the connection details for the MySQL database. Under the General tab for the Adapter of the detail table only the “Insert data” option should be checked. Under the “Fields / Data types” tab choose the detail table (INVLINES) fields and load the schema information.

Copying the new auto-incremented values from the master table

obtain auto increment value from master tableTo ensure that after migration the data remains related we must somehow obtain the new auto-incremented value (Primary Key) for each invoice written and enter this as a foreign key field in the invoice lines. The problem is that this value is determined by the MySQL database and so we can assign no connection in the Designer for this value.

To obtain this value FlowHeater offers the possibility of using the FlowHeater DefaultValue feature for the auto-increment field to transfer the value from the master table. To do this mark the foreign key field and select from the DefaultValue dropdown box the INVOICE ID field (see screenshot). When you have closed the Configurator you will see that the field is deactivated (grayed out) and no value can be assigned to the field in the Designer. FlowHeater takes care of this during data import.

Note: As the auto-increment value is only obtainable when running live, in Test Mode only the Adapter name followed by the field name appears.

Warning: To allow this procedure to work, it is necessary that the detail data records on the READ side are delivered in a sequence sorted by invoice.

Migration of master/detail (one-to-N) relations – complex

The following method is useful for cases where no auto-increment value can be obtained, or when we must assign the new primary key / foreign key values separately.
Let us assume in our example that in addition the foreign key of a customer table ID must also be reassigned.

Implementation of foreign keys using CSV lookup

A simple CSV text file is temporarily stored where the old “Primary Key” and the new “Primary Key” are listed side by side to assist the migration. Such a lookup file is saved during the export of the MySQL customer table in a CSV text file. The structure of the CSV lookup file is “oldID;newID”.

Migration of the MySQL “Customer” table

Create a new FlowHeater Definition and again select on the READ side the Adapter/data source you need for the migration. Select the MySQL Adapter on the WRITE side and configure the Adapter to import the CUSTOMER table. Next add a further TextFile Adapter to the WRITE side of the Definition so that during the migration of the table a CSV lookup file is also exported. As the file name for the CSV export file enter “customer-id.csv ” and add two fields “oldID” and “newID”. Close the Configurator and connect the old Primary Key field with the CSV field “oldID”. The new ID we collect from the database using the SQL Heater. In our case we simply obtain the maximum ID from the CUSTOMER table and add one (select max(id) + 1 ...) so predicting the increment that will be written.

Now as we import the data into the new CUSTOMER table in MySQL, we export at the same time a CSV file “customer-ids.csv” with the old and new IDs.

Carry out the CSV lookup

We now use this CSV file in order to translate the customer ID number in the migration of invoices/invoice lines. To do this we use a String Replace Heater. In the Extended settings of the Heater it can perform a CSV lookup. Enter the path to the generated CSV file under “Replace from list file” and for “Find” select column 1 and for “Replace” select column 2. When the String Replace Heater received as input parameter the “old” customer ID it will find the first occurrence of this and replace it with the value in the second column, i.e. the “new” customer ID. In this way data migration of the Primary Key and its related foreign keys can relatively elegantly completed.

Note: As an alternative method to using a CSV text file the lookup data of old and new IDs can be saved in a temporary MySQL table. ID translation could then be determined for each record using the Lookup Heater or the SQL Heater.

Information about the example used

In order not to make the example too complicated it was based on three tables (customers, invoices and invoice lines). The example is intended only to give an overview of the general procedure of data migration. In practice it is unusual for customer and invoice ID numbers to need reassignment as part of a migration. In addition, solely a CSV file is used as the data source on the READ side. You can download the Definition files used in the example as a ZIP archive here: MySQL data migration examples