[ts-gen] Database setup by hand (was: Re: shim setup ...)
Richard Pruss
boadie at gmail.com
Fri May 15 01:00:48 EDT 2009
Hi Mike,
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,
Ric
On 16/04/2009, at 7:29 PM, Mike Thornton wrote:
> Hi Bill,
>
> 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
>
> Using version "14.12 Distrib 5.0.77" solves the problem.
>
>
> Thanks.
> Mike
>
>
>
> On Wed, Apr 15, 2009 at 12:11 PM, Bill Pippin <pippin at owlriver.net>
> wrote:
> Mike,
>
> Thanks much for your report. You've already run into teething
> problems due to my preliminary work on options support, and now
> you may be having knock-on problems due in part to some cruft in the
> setup scripts that I overlooked, that most people don't see, but
> that may have been uncovered by the particular sequence in which
> you've been doing the setup. Lastly, you will in addition see a need
> for some more entries for FUT:ECBOT:YM symbols before the regression
> tests will work properly, and for these you'll need to either apply
> the
> patch I include below, or else download the newest data-090415 tarball
> in order to add the symbols. I very much appreciate your help in
> identifying these problems. Sorry about the headaches.
>
> > Running "mysql_install_db" again + other minor file edits ... then
> > sourcing "setup.sql" cleaned up most of those errors. My
> > last error (I hope) is a gap in the Symbol table. ...
>
> I've already pushed the new symbols tarball out, and it should show up
> within the hour on our site.
>
> I've tried to duplicate what you're doing, and found a problem.
> That being said, I would expect the setup.sql script to run for you,
> and suspect that you may also be having some permissions issues.
>
> The setup script issues I've found will be fixed in the next release
> of
> the shim sources, that is a tarball shim-0904??.tgz, which I hope to
> put
> out either today or tomorrow. Rather than ask you to wait, however,
> I'll
> tell you how to diagnose the problem if you wish, and in any case,
> where
> the script problem I see is, and you can fix that yourself.
>
> What follows is a detailed recipe for fixing all the various issues
> I've
> noticed that you might be running across. Please feel free to ask
> questions as needed.
>
> ________________________________________________________________________
>
> A. Adding symbols
>
> Carrying out this step first will allow you to avoid repeating the
> next
> steps over again. The only dependency between this step and the other
> two is that you need a few additional symbols to allow the shim's
> regression tests to work properly; those symbols must be included
> in the database; and the following two steps do just that. So, either
> you want to get the new symbols before you create the databases; or
> else you'll probably want to repopulate those databases later.
>
> You may either apply the patch that follows, or else look it over and
> add the relevant entries by hand, or else download the data-090415
> symbols tarball. In the latter case, you'll need to either copy the
> file data-090415/A0Fut01.sql into data-090320, or else update the
> top symlink in the shim source directory, the one that points up and
> over, to point to the new symbols directory.
>
> diff -rc data-090320/A0Fut01.sql data-090415/A0Fut01.sql
> *** data-090320/A0Fut01.sql Wed Jan 21 12:25:34 2009
> --- data-090415/A0Fut01.sql Wed Apr 15 14:07:26 2009
> ***************
> *** 2340,2345 ****
> --- 2340,2349 ----
> (49652399, 'FUT', 'DD' , 'USD', 'US' , '20090319', 'DD MAR
> 09' , 'BIG SIZED DOW JONES INDUSTRIAL AVERAGE
> $25' , '' , 'ECBOT' ),
> (49652401, 'FUT', 'DJUSRE' , 'USD', 'US' , '20090319', 'RX MAR
> 09' , 'DOW JONES US REAL ESTATE
> INDEX' , '' , 'ECBOT' ),
> (49652404, 'FUT', 'YM' , 'USD', 'US' , '20090320', 'YM MAR
> 09' , 'MINI SIZED DOW JONES INDUSTRIAL AVERAGE
> $5' , '' , 'ECBOT' ),
> + (51865515, 'FUT', 'YM' , 'USD', 'US' , '20090609', 'YM JUN
> 09' , 'MINI SIZED DOW JONES INDUSTRIAL AVERAGE
> $5' , '' , 'ECBOT' ),
> + (53893055, 'FUT', 'YM' , 'USD', 'US' , '20090918', 'YM SEP
> 09' , 'MINI SIZED DOW JONES INDUSTRIAL AVERAGE
> $5' , '' , 'ECBOT' ),
> + (56578477, 'FUT', 'YM' , 'USD', 'US' , '20091218', 'YM DEC
> 09' , 'MINI SIZED DOW JONES INDUSTRIAL AVERAGE
> $5' , '' , 'ECBOT' ),
> + (58700627, 'FUT', 'YM' , 'USD', 'US' , '20100319', 'YM MAR
> 10' , 'MINI SIZED DOW JONES INDUSTRIAL AVERAGE
> $5' , '' , 'ECBOT' ),
> (49666526, 'FUT', 'PAN' , 'NOK', 'NO' , '20080918',
> 'MHG8U' , 'Marine
> Harvest' , '' ,
> 'OMLX' ),
> (49666530, 'FUT', 'NSG' , 'NOK', 'NO' , '20080918',
> 'NSG8U' , 'Norske Skogindustrier
> ASA' , '' , 'OMLX' ),
> (49666535, 'FUT', 'OBX' , 'NOK', 'SE' , '20080619',
> 'OBX8F' , 'OBX
> INDEX' ,
> '' , 'OMLX' ),
>
> I realize that the patch above may not make it through your email
> system, in which case, feel free to add the new entries for YM by
> hand,
> or get the new symbols tarball.
> ________________________________________________________________________
>
> B. Diagnosis
>
> You may diagnose the setup problem you've been seeing to confirm that
> my explanation actually applies. Or, if you wish you can just go
> straight ahead to (C), the fix and its execution.
>
> While in the directory shim-090327/sql , check for the existence of a
> script "secs.sql". It's not supposed to exist, but almost certainly
> is there. Check for a reference to it in the script table.sql, ditto.
> You may also check for the existence of the tables ComboLeg and
> ComboSet, neither of which should exist.
>
> E.g.: echo 'show tables;' | mysql -u code -h xps400 testing | more
> adapted as needed to your site will probably produce something
> initially like the following:
>
> Tables_in_testing
> AccountCode
> AccountEdge
> ActiveOrder
> AtomTag
> BarSize
> Bool
> BoxOptions
> ChangeOrder
> ComboLeg
> ComboSet
> CreateEvent
> Currency
> ...
>
> The problem here is that the tables ComboSet and ComboLeg have foreign
> key dependencies on other tables, that they are not currently used,
> are not initially dropped by the create.sql script, prevent other
> tables
> from being dropped, and so that script is not able to recreate the
> tables
> in a database properly. If you've been using setup.sql only, I'm not
> sure how this could be your problem, but the two tables ComboSet and
> ComboLeg are currently cruft, and so should in any case be eliminated
> for now.
> ________________________________________________________________________
>
> C. Solution
>
> Remove the reference to secs.sql in table.sql, by deleting that line;
> go ahead and delete the file secs.sql, if you wish, since it's just
> a bug magnet; and use the setup.sql script (not create.sql, you need
> to start from scratch here) to recreate both the testing and trading
> databases.
>
> E.g., adapting the following command to your site's mysql server
> hostname, and working within the directory sql, one down from the
> directory where you unpacked the shim's sources:
>
> sql$ time mysql -p -u root -h xps400 --show-warnings <
> setup.sql
>
> Enter password:
> Note (Code 1305): PROCEDURE OrderUpdate does not exist
> Note (Code 1051): Unknown table 'Bool'
> Note (Code 1305): PROCEDURE OrderUpdate does not exist
> Note (Code 1051): Unknown table 'Bool'
> Note (Code 1051): Unknown table 'TsNames'
> Note (Code 1051): Unknown table 'TmpTabs'
> Note (Code 1051): Unknown table 'TmpUser'
> Note (Code 1051): Unknown table 'TmpHost'
> Note (Code 1051): Unknown table 'TmpShim'
>
> real 1m1.894s
> user 0m0.324s
> sys 0m0.032s
>
> As noted in an earlier post, the unknown table and nonexistent
> procedure
> warnings are superfluous, and should be ignored.
>
> To verify that you've reached a clean fix point, and that ComboSet and
> ComboLeg are gone, now try to recreate the tables in just one of the
> databases, and this time not starting from scratch:
>
> sql$ time mysql -u code -h xps400 testing --show-warnings <
> create.sql
>
> real 0m28.655s
> user 0m0.172s
> sys 0m0.036s
>
> The absence of warnings shows that you are able to freely recreate the
> tables, without going to the trouble of recreating the databases.
>
> At this point the shim should be able to connect to the database and
> read in its tables. You may also want to update your symbols listing,
> the file syms.txt, as follows (you may do this whenever you wish; the
> resulting file is FYI).
>
> sql$ time mysql -u code -h xps400 testing < bin/get_id.sql >
> syms.txt
>
> Please let us know if this helps.
>
> Thanks,
>
> Bill
>
>
> _______________________________________________
> ts-general mailing list
> ts-general at trading-shim.org
> http://www.trading-shim.org/mailman/listinfo/ts-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.trading-shim.org/pipermail/ts-general/attachments/20090515/00947ab2/attachment.html
More information about the ts-general
mailing list