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

Bill Pippin pippin at owlriver.net
Wed Apr 15 15:11:40 EDT 2009


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:


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

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.



More information about the ts-general mailing list