[ts-gen] Database setup by hand (was: Re: shim setup ...)

[Bill Pippin]

Ric,

The bug Mike found has been, until now, a serious headache:

>> It turns out that there is a documented bug in mysql versions 5.1+
>> which causes the gap in the symbol tables -- the gap occurs when
>> trying to place many rows in the table simultaneously.
>>
>> http://bugs.mysql.com/bug.php?id=40384&error=lp

Your report below is welcome news:

> I have just ran into the same bug but there is a solution from
> 5.1.22 onwards.
 
> http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
 
> setting
 
> innodb_autoinc_lock_mode = 2
 
> Fixes it

Thank you very much for bringing this to our attention, and posting it
to the list for the benefit of others.  It should give me much more
freedom in milestone planning, since the urgency of a port to
postgresql would no longer be so high.

I'll note for other readers that I hope but have not confirmed that
the innodb_autoinc_lock_mode configuration parameter solves the insert
auto enum problem.  The following text from the documentation Ric
refers to leaves more than some room for doubt: 

    Gaps in auto-increment values for bulk inserts:

    With innodb_autoinc_lock_mode set to 0 (traditional) or 1
    (consecutive), the auto-increment values generated by any given
    statement will be consecutive, without gaps, because the
    table-level AUTO-INC lock is held until the end of the statement,
    and only one such statement can execute at a time.

    With innodb_autoinc_lock_mode set to 2 (interleaved), there may
    be gaps in the auto-increment values generated by bulk inserts,
    but only if there are concurrently executing INSERT-like
    statements.

The text appears to claim that values of 0 or 1 are best for our
purposes, yet does not promise that rows from distinct inserts
are consecutive.  If read carefully, the text for (2) appears to
claim that it should work as we would hope --- sequential indexing
within and across inserts --- but even there does not rule out gaps.
In other words, the old behavior of no gaps, which we still see with
5.0.45, may not be available with any of the config values of 0, 1,
or 2, and in any case, we'll just have to see.

Ric, again, your report here is *very* useful to us, and I'm sure
I'll be running many tests of the innodb_autoinc_lock_mode in the
future.

For other users, note that once you get the database populated, this
issue should not be a problem, so that once you get everything setup,
and can run the shim even once successfully, you're safe, though with
one particular (hopefully hypothetical) exception:

For the journal, *if* a create order transaction failed --- and this
is the only point where we need to use transactions --- the docs
clearly say there will be a gap.  Rollback shouldn't happen as long as
users don't try to duplicate order keys, but if such a mistake occurs,
you'll need to unload and reload your order journal, in particular the 
CreateEvent and ChangeOrder tables, and probably do some manual fixup
as well.

Also, Ric, I'll be responding to your reports on the MacOS problems in
following posts.

Thanks,

Bill