[OSM-dev] GPX Tables and InnoDB

Tom Hughes tom at compton.nu
Wed Sep 5 13:35:10 BST 2007


In message <46DE9D9F.1050203 at arjam.net>
        Robert Munro <rjmunro at arjam.net> wrote:

> 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)

Thanks for teaching me how to suck eggs. I would never have thought
of doing that.

Of course you missed out the bit where, after about 8 hours that
select stops with "Lock table too full" or some such and you start
scrabbling around looking for some other way to copy a table in
MySQL, but there you go.

> 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.

How would a row get missed? The insert into ... select from would have
read locked the old table anyway so it wouldn't have had any changes
made to it (especially if the daemon was stopped).

The point is not that I don't know how to convert the table - of
course I do. The point is that I don't want to if I can avoid it.

Tom (official MySQL hater)

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/




More information about the dev mailing list