[OSM-dev] Don't worry, be happy

[Matt Amos]

On Mon, Dec 1, 2008 at 6:09 PM, Joachim Zobel <jz-2008 at heute-morgen.de> wrote:
> Am Mittwoch, den 26.11.2008, 12:54 +0000 schrieb Matt Amos:
>> > Well, as long as the API code is good, and makes this check, then
>> the
>> > transaction should cover it for inserts/updates. I'd assume the
>> > migration script will have to handle existing inconsistencies.
>>
>> that is exactly why i'm worrying about it :-)
>
> MySQL has the
>
> SET foreign_key_checks = 0;
>
> which can be set before loading the old data. The somewhat scary fact
> that a
>
> SET foreign_key_checks = 1;
>
> does _not_ do a validation of the existing data means that handling
> existing inconsistencies can be delayed until the migration is done.

this is, indeed, a very scary feature of mysql. even within a
transaction on an innodb table it will still check foreign key
constraints as the rows are added - mysql has no concept of "deferred
constraints" like most other databases.

while writing the migration script i'm having to run with the foreign
key checks on to ensure that i have covered all the "existing
inconsistencies", which makes it really slow :-)

but don't worry - once the script works we can turn checks off and
everything will work perfectly, of course. :-P

cheers,

matt