[ts-gen] foreign key constraint fails during setup.sql

[Bill Pippin]

Richard,

You make three points, first about compilation on your Mac, second about
the documentation, and third about the database setup.  I'll take them
in turn.

First, your mac: we're always delighted to get installation reports
from the less frequently reported platforms (most use I know of has been
on debian or centos) and you point to a particularly irritating gotcha,
the location of the mysql libraries and header files:

> Built the shim on MacOs.  No problems in build except the change
> in mysql location to Lib in the Makefile.

Please feel free to post your fix to the list, or even just the
eventual value you came up with for the LIB variable.  As you've
found, there seems to be some variation on how mysql gets installed
on macs, depending not least on the version that is installed.

A full solution to this issue waits on our addition of autotools
make config support, which should be soon, but is not currently our
first priority.

For now, the Makefile includes the following lines

>   SQL      := $(shell mysql_config --libs)
>   TWO      := $(filter %/mysql,$(SQL))    \
>               $(filter %client,$(SQL))
>   LIB      := $(TWO) -lm

Was make able to evaluate the assignment to SQL, above, by running
mysql_config?  If so, did our somewhat brute-force grepping on the
results find the libraries needed?  What was the eventual result
for the LIB variable, and what did you need to set it to?  I'd
very much appreciate your posting the two values of lib --- what
the Makefile computed, and what you used instead --- to the list.
A scrape-and-paste of the Makefile output before and after your
fix would be fine, and feel free to trim it as your common sense
dictates.

Second, a warning about the documentation: it's out of date,
since the shim is under active development, and we view the
implementation of new features needed to reach our
"feature-complete" 1.0 release as properly coming prior to
the production of a completed manual.

> The source included README should point to the online doc
> as that is clearly more up to date.

I'm not sure what you mean here.  You may be referring to the
compilation date that shows on the cover, or root html page,
of the manual.  This varies depending on when that compilation
occurred, but the underlying textual sources are, I believe,
the same.

In other words, as a developer, I recompile (latex and latex2html)
the manual whenever I add new text, and on other occasions to
exercise the Makefile, while the automated test process which
produces the online manual recompiles every time I push a new tarball
out to the site, even though for the common case the only changes in
the overall tarball are to the source code for the shim itself, and
not to the manual.

It follows that sometimes the online manual has a newer
compilation date than the ps and pdf binaries I include in the
tarball.  Unfortunately, the manual, via whatever route you come
to it, is almost certainly derived from the same --- seriously
dated --- sources.

I've checked the most recent release, of 090120, and in this case
the tarball pdf and web site html have the same creation dates,
of 090112.

Third, and most significant, about setting up your database.  As
you surmise, problems here can prevent --- and, in your case have
prevented --- successful startup of the shim.

I've just checked that the most recent public release, the tarball
dated 090120, sets up the database for me just fine, so perhaps you
have some mac-specific problem.  We're talking about sql scripts
here, so I'm not sure what that problem might be, but clearly from
the message you've posted there is *something*.

> I have a error during ... setup.sql.
>
> 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`))
> Query OK, 0 rows affected, 1 warning (0.00 sec)

First off, you should know that your database setup for the first of
two databases, testing (the other is trading) almost reached the end,
where it creates sample order template records that would allow you
to run the orders' regression tests exs/risk.rb and exs/kill.rb .

So, if you commented out references to risk.sql, for setup.sql, in
the file split.sql; and for create.sql, at the end of that file;
and then reran create.sql for the testing database, then you would
expect to be able to run the shim for data collection purposes,
that is in --data mode.  (But read on, since the problem loading
the Template relation seems to be only a symptom of deeper
problems.)

As for orders, and --risk mode, you would of course need some order
template records.  They would be created via the risk.sql script if
you could see how to get it to work, or could be instantiated by
some other means under your control, and the shim won't know or care
which.

> Possibly as a result ... [the] shim does not start:

> shim-090120$ ./shim --data
> 
>           The trading shim has connected to the database server
> 
> The record with uid 65536 from table Symbol is out of sequence.
> Fill: 1 65536 45223 Symbol
> Dbms: uid(65536) next(45223)
> 
> Problem: 515 db record uid -- vector offset mismatch
> Exiting

The record index where input failed for a Symbol tuple is very suspicious;
it's exactly 2^16.  I have to wonder if this is a symptom of some library
problem.  This suggests, by the way, that although the setup script failed
near the end, something else may well have been wrong before this; the
security and derivative symbols in the Symbol relation are loaded much
earlier.

Are the records in the Symbol relation numbered sequentially, or are
there gaps?  That is, given appropriate modifications to ../bin/mysql
to provide the proper connection parameters, what do you find when
you run the following:

> sql$ echo 'select * from Symbol;' | ../bin/mysql > flat.syms
> sql$ head -1 flat.syms
> uid     tid     cid     rid     curr    region  name    ibc
> sql$ tail -1 flat.syms
> 45909   5       127     197     SEK     SE      VOLV.B  NULL
> sql$ wc flat.syms
>   45910  367916 1651376 flat.syms

In the trace above, the query answer line count is one greater
than the greatest record uid due to the header line, and this
is normal.  It's possible that the symbol values you get may
differ from the example above, but it's critical that the record
indexing be sequential starting from one.  If not, the database
setup scripts have failed badly; otherwise, the shim is having
problems reading query answers via the mysql client libraries.

If you patched the Makefile to fix the LIB variable, did you first run
"make clean" to eliminate all old object files?  Are the libraries and
include files from the right locations?  More precisely, given a link
list such as the following:

> make[1]: Entering directory ...
> distcc g++ -Wall -O3 -g -I/usr/include/mysql  -c -o     ...
> (cd obj; g++ -g -o ../shim main.o once.o bind.o mode.o  ...
> type.o tabs.o syms.o init.o link.o open.o late.o fsms.o ...
> unit.o perm.o shim.o fill.o wait.o time.o read.o ctor.o ...
> wire.o post.o past.o echo.o form.o tick.o task.o name.o ...
> envs.o else.o scan.o hash.o text.o wrap.o inet.o pool.o ...
> -L/usr/lib/mysql -lmysqlclient -lm)
> make[1]: Leaving directory ...

Is the include file directory (-I/usr/include/mysql) consistent with the
library (-L/usr/lib/mysql) location?  By consistent I mean, in particular,
whether the header files and library binaries from mysql are suited to
the processor word width, whether 32 or 64 bit.  By way of example, some
people have dual 32-64 bit mysql package setups, and must take care to
get the right sized libraries at app build time.

> I am probably missing some obvious step??

I don't think so.  Unless you find some simple fix to the LIB
variable --- and feel free to pursue this approach if you wish,
since, if successful, it will have given you the fastest
solution --- I'd like to ask that, in addition to checking the
Symbol relation for sequentiality, you run the sql/bin/get_id.sql
script by piping it into the mysql interpreter, and pipe the
results to a file, say syms.new.

> sql$ ../bin/mysql < bin/get_id.sql > syms.new
> sql$ wc syms.txt
>   45267  362775 1984786 syms.txt
> sql$ diff -q syms.txt syms.new
> Files syms.txt and syms.new differ

In the first command above, you'll need to modify the connection
parameters in the bin/mysql script as appropriate to your site;
the second expects the existence of syms.txt, a symbols dump
distributed with the sources; and, with the third, if the files
are the same, diff will say nothing in place of the message above;
such silence is normal.

If the distributed syms.txt data and the file syms.new resulting
from your query are significantly different, and the reason why
is not obvious, please consider gzipping and emailing your syms.new
file to me.  And yes, I realize it's almost half a meg compressed.
Not a problem on this end, though possibly one for you, and let
me know if so.

Thanks,

Bill