[OSM-dev] GPX Tables and InnoDB

Robert (Jamie) Munro rjmunro at arjam.net
Wed Sep 5 13:14:23 BST 2007

Hash: SHA1

Martijn van Oosterhout wrote:
> On 9/5/07, Ray Booysen <raybooysen at rjb.za.net> wrote:
>> Hey all
>> Is there any reason we don't immediately convert the gpx tables into
>> InnoDB to alleviate the locking issue?
> I think someone pointed out that in their experience the conversion
> would take days, maybe weeks. And in that time it will be completely
> locked (no reads or writes). I think the most promising idea was to
> create a new table and copy the data over slowly. But there's no code
> for that.

Surely you just:

show create table;
  (copy the output of that command and add _new to the table name, and
change the table type to InnoDB. You may also want to leave off the
indexes, and run that as a command).

insert into [table_name]_new select * from [table_name];
  (this will take hours, maybe days or even weeks - probably best to run
mysql inside a screen session or something)

rename table [table_name] [table_name]_old;
rename table [table_name]_new [table_name];

You then might want to do something like:

insert into [table_name] select * from [table_name]_old on duplicate key

to catch any rows that were missed, although that last step requires a
little bit of thought to prevent duplicate keys coming from live data.

Robert (Jamie) Munro

Version: GnuPG v1.4.6 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


More information about the dev mailing list