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

Mike Thornton endoscope at gmail.com
Thu Apr 16 05:29:15 EDT 2009


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.trading-shim.org/pipermail/ts-general/attachments/20090416/591ed4ca/attachment.html 


More information about the ts-general mailing list