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

[Richard Pruss]

Bill,

Thanks for the detailed analysis your suspicion of a lib problem set  
me off looking into the libs.

After a few dead ends like trying on a clean machine. I started  
questioning the MYSQL libs and the Mysql installation turned out to be  
the problem.

The mac packages for Mysql from Mysql DB (now Sun) specifically:
http://dev.mysql.com/downloads/mysql/5.1.html#macosx-dmg
Do not work for shim and who knows probably other things.

 From here is an attempt at a better installation report for shim on  
Mac.

Instead if one installs the mysql package from macport project:
http://www.macports.org/

and then
1.
sudo port install mysql5

Here is a how to on that;
http://systems.takizo.com/2008/06/09/install-mysql5-server-on-apple-mac-leopard-with-macports/

2.
Shim needs a change to my.cnf which is strangely is expected to be in / 
opt/local for this distribution
[mysqld]
transaction-isolation = SERIALIZABLE


Then for shim one needs three changes to the Makefile, all simply  
pointing to the location of mysql:
3.
"
CPPFLAGS := -I/opt/local/include/mysql5/mysql

SQL      := $(shell /opt/local/lib/mysql5/bin/mysql_config --libs)
TWO      := $(filter %/mysql,$(SQL))	\
             $(filter %client,$(SQL))
LIB      := $(TWO) -lm -L/opt/local/lib/mysql5/mysql
"
4.
make the shim and checked by running it and got the help output.

5.
Source the setup.sql from the mysql client as per the instructions in  
the doc directory,
start TWS (I used my paper account) and then enable API and local  
connections.

6.
Started the shim with shim --data

I got a allow connection question from TWS twice and then the shim was  
up and running with the paper account. ;-)

- Ric




On 27/01/2009, at 6:40 AM, Bill Pippin wrote:

> 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