slu

Søren's Blog

Random Ramblings

Previous Entry Share Next Entry
Partial migration of data
slu

I'm currently involved in updating an in-house developed issue/bug tracking system. It's a simple web application with a SQL database used for persistence. We've decided not to do a big bang migration of all data, instead we're migration one project/product at a time. This makes the transition easier to manage, as fewer users need to be informed of the change. Also, if the new system has any serious bugs, not that many users will be affected.

The migration of data from the existing production database to the new database is a little more complex, when we just can't migrate everything at once.

I've create an example to demonstrate how I did the migration. The example below is using MySQL, but the technique can be used with all SQL databases.

To try the code below you'll need a database. Often MySQL is installed with a test database, and you can use that. Or create one like this:


create database catalog default character set utf8;


Then switch to using the database:


use catalog


Now create the first set of tables. They are what's in production right now:


create table countries (
  id int not null auto_increment primary key,
  code varchar(20) not null,
  name varchar(200)
) ENGINE = InnoDB;

create table cars (
  id int not null auto_increment primary key,
  name varchar(50),
  country_id int not null,
  foreign key (country_id) references countries(id) on delete cascade
) ENGINE = InnoDB;



Add some data:

insert into countries (code, name) value ('DK', 'Denmark');
insert into countries (code, name) value ('SE', 'Sweden');
insert into cars (name, country_id) value ('Volvo', (select id from countries where code = 'SE'));
insert into cars (name, country_id) value ('Ellert', (select id from countries where code = 'DK'));



Let's see what's in the database:

mysql> select a.name Car,b.name Country from cars a, countries b where a.country_id = b.id;
+--------+---------+
| Car    | Country |
+--------+---------+
| Ellert | Denmark |
| Volvo  | Sweden  |
+--------+---------+
2 rows in set (0.00 sec)


Now create a set of new set of tables. This is the new system, where data from production will be migrated to:

create table countries_copy (
  id int not null auto_increment primary key,
  code varchar(20) not null,
  name varchar(200)
) ENGINE = InnoDB;

create table cars_copy (
  id int not null auto_increment primary key,
  name varchar(50),
  country_id int not null,
  foreign key (country_id) references countries_copy(id) on delete cascade
) ENGINE = InnoDB;



As the two system will run in parallel, we'll add some data to this as well:

insert into countries_copy (code, name) value ('DE', 'Germany');
insert into cars_copy (name, country_id) value ('Audi', (select id from countries_copy where code = 'DE'));



And let's see that:

mysql> select a.name Car,b.name Country from cars_copy a, countries_copy b where a.country_id = b.id;
+------+---------+
| Car  | Country |
+------+---------+
| Audi | Germany |
+------+---------+
1 row in set (0.00 sec)



Now we'll prepare the new (copy) database for migration. Because both databases are in use, we can't migrate the id's. Instead will create an extra column in the coutries_copy table to hold the original id:

alter table countries_copy add column id_orig int;



Now we can copy all the countries:

insert into countries_copy (code,name,id_orig) select code,name,id from countries;



If we look at the data, we can see that the entries has new ids, but we'll also have the original id stored:

mysql> select * from countries_copy;
+----+------+---------+---------+
| id | code | name    | id_orig |
+----+------+---------+---------+
|  1 | DE   | Germany |    NULL |
|  2 | DK   | Denmark |       1 |
|  3 | SE   | Sweden  |       2 |
+----+------+---------+---------+
3 rows in set (0.00 sec)



We can use the link between the original and new id when we copy data to the cars_copy table. We'll only copy the swedish cars:

insert into cars_copy (name, country_id) select a.name, b.id from cars a, countries_copy b
where a.country_id in (select id from countries where code = 'SE')
and a.country_id = b.id_orig;



The copy now contains data from both systems:

mysql> select a.name Car,b.name Country from cars_copy a, countries_copy b where a.country_id = b.id;
+-------+---------+
| Car   | Country |
+-------+---------+
| Audi  | Germany |
| Volvo | Sweden  |
+-------+---------+
2 rows in set (0.00 sec)



And the relations are still correct.

Now we can continue to add Danish cars to the original table, cars. But Swedish cars should be added to the new table, cars_copy. If we need to add a new country, this should also be added to the new table, countries_copy.

The above code does not remove or somehow deactivate the migrated data in the old system. This should be done when doing this in real life.
Tags: ,

?

Log in