[OSM-dev] GPX Tables and InnoDB
Robert (Jamie) Munro
rjmunro at arjam.net
Wed Sep 5 13:14:23 BST 2007
-----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-----
More information about the dev
mailing list