[ts-gen] Mysql and rerunning setup.sql [Was: MySql 5.1.37 config ... ]

[Bill Pippin]

Billy,

With respect to the foreign key constraint error message:

> Running the setup.sql script generates this error ...

> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint fails (`testing`.`Template`, CONSTRAINT `Template_ibfk_4`
> FOREIGN KEY (`par`) REFERENCES `Template` (`uid`))

Foreign key constraints are checked when tables are created, since
catalog entries are needed; when tables are dropped, ditto, and about
which more to follow; and when data is loaded into an application
domain table, since the user table may well have foreign key checks,
and in fact the shim typically does.

Given the appropriate permissions, and a fresh mysql install that does
not have an existing trading-shim database, the setup.sql script runs
like clockwork.  So, problems such as yours are almost always related
to either of both of the following:

    1. database permissions, or
    2. an existing database

You should be able to diagnose and fix problems stemming from either
of the above factors via sql commands such as:

    show databases;
    use DATABASE;       -- substitute identifiers as needed
    show tables;
    show warnings;
    drop TABLE;         -- substitute identifiers as needed

Of course you can also do tremendous damage with the last of the above,
so: do all your damage to testing, that's what it's there for, and keep
at it until you know exactly what you're doing; *always* make sure you
know what database provides your current context, and by the way, make
sure it's testing --- yes, I'm repeating myself --- and, once you've
started trading, unload your journal first, before upgrading, since you
may want those journal records later.

Recall from the above that foreign key constraints may be checked when
tables are dropped, since some table attribute may occur as a foreign 
key in some other table, in which case there are dependencies from
the table key, to its index, to the table where the foreign key lives.

So, you'll find that you can't drop fundamental tables until the
related tables with foreign key attributes to those fundamental tables
are first dropped themselves.  It's entirely possible that, if you've
waited a long time between upgrades, some unwanted table is hanging
around, and you'l have to drop it manually.  This issue is one of the
reasons we go to so much trouble to track the trading-shim database
tables version, including a Version table, startup checks, and
frequent notes in the NEWS file.

The error message you report, for the order Template table, suggests
that the problem is occurring during table load, that is after the
table has been successfully created, and while it is being populated.
However, you should keep in mind that the problem may also be when
the table is dropped and then created, much earlier in the database
build process.

So, you'll need to be able and willing to step through the steps of
database creation to be sure of tracking down your problem.  You
probably would have already figured out the issue otherwise.

Steps to consider:

    Start work in the sql subdirectory of the directory point where
    you untarred the trading-shim tarball; sql doesn't know about
    directory paths, so the install scripts are all meant to be
    run from this point.

    Verify that the testing and trading database exist, by using
    the sql command "show databases" :

        mysql> show databases;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | test               |
        | testing            |
        | trading            |
        +--------------------+
        4 rows in set (0.00 sec)

    The list you see may differ in details from the above, but
    the catalog, testing, and trading databases must exist.
    Once given that the databases exist, reduce the scope of your
    problem, by working with the create.sql script;

    reduce it furthur, by commenting out each and every step of that
    script other than the first active line, which sources drop.sql;

    for the database "testing", run your modified create.sql from
    within the mysql interpreter to drop all tables, and then run
    the show tables; command:

        mysql> show tables;
        +-------------------+
        | Tables_in_testing |
        +-------------------+
        | Bool              |
        +-------------------+
        1 row in set (0.00 sec)

    If the list you see is longer than the above, you'll most
    likely need to do some manual drops, as explained above.

    Once you've actually cleared away the rubble from old versions,
    uncomment-out those lines near the bottom of create.sql, that
    you commented out earlier, to recover the following text.:

        -- source five.sql;	-- transitional, from ver <= 1.68 ...
           source drop.sql;	-- create
           source enum.sql;
           source base.sql;
           source syms.sql;
           source subs.sql;
           source xact.sql;
           source proc.sql;

           source load.sql;	-- populate: source
           source more.sql;	-- populate: insert select
           source rest.sql;
           source risk.sql;

    Rerun create.sql from within the mysql interpreter:

         source create.sql

If you still have problems, continue debugging each step, by
commenting out following steps in create.sql, running that script,
and using "show warnings;" to see what if anything went wrong.
Eventually you want to be able to run create.sql with all the
source commands listed above, from drop.sql to risk.sql, active
and successful.

Since you are seeing problems with the order Template table,
the results of the last step, "risk.sql", which loads that table
with test entries, may be of interest.  Keep in mind, though, that
your problem is most likely earlier, in drop.sql; database version
changes may be leaving some table behind.

As long as you make *sure* that you're working in the database
testing, and you use "create.sql" rather than setup.sql, you
can't do any serious harm; the worst that can happen is that
you loose existing test journal records, which, if you care,
is why you should dump your tables before upgrading.

Once you've fixed your problems, and built testing successfully,
then you can either use create.sql to rebuild the trading database,
or use setup.sql to recreate both at once, starting from scratch.
Keep in mind that you must have dba permissions to run setup.sql,
while create.sql can take advantage of the user accounts setup
earlier, by setup.sql; and that setup.sql drops and recreates
the databases, while create.sql just drops, recreates, and
repopulates tables for a given database.

By the way, it will probably be useful to read the comments in
the create.sql script.  Also, don't hesitate to search the mysql
site for help on the database commands, if you need such.

The above should be enough for you to track down the problem.

Thanks,

Bill