[OSM-dev] GPX Tables and InnoDB

[Robert (Jamie) Munro]

-----BEGIN PGP SIGNED MESSAGE-----
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
ignore;

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


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

iD8DBQFG3p2ez+aYVHdncI0RAugNAJwK1g5vzsuS3s3VRDHmeEnGtDF7OwCgxu4b
z+Ado/O9/07zXqG3UJAcCxQ=
=Davd
-----END PGP SIGNATURE-----