[ts-gen] Cross-session order modification and cancellation ...

pippin at owlriver.net pippin at owlriver.net
Tue Nov 25 15:27:34 EST 2008


I'm glad that you're now able to avoid the seg faults:

> turning off the [ -03 ] flag and recompiling
> ... no Seg faults any more ...

As I noted earlier, I'll see if I can reproduce them on our end, and
figure out what the problem is, in the hope that -03 becomes feasible
for 64bit gcc 4.3.2 on Ubuntu.

You ask, after having either recreated the database via setup.sql,
or the tables via create.sql:

> Is data base re-setup necessary with each new release,
> and how can potential data loss best avoided?

It should not be necessary to re-create tables for every release.  The 
shim checks its internal database version symbol against what it reads
from the Version table, as it was defined via sql/req/Version.sql,
warns you if they differ, and if so, stops.  When this happens, yes,
you do need to run either setup.sql or create.sql.

In this case, however, I seem to have forgotten to update the 
Version.sql load file.  For the future, I've modified our release
script to catch this problem if it occurs again; each external
release event now takes a snapshot of the database setup and creation
scripts, and the tarball build script complains if changes haven't
been propagated correctly.  I also typically try to warn readers in
the NEWS file, though since database table attribute changes have
been frequent and are often trivial, I may not always do this.  

So, given a database change, a new shim, and failure to recreate tables
for your site, then in the past typically, and in the future hopefully
always, you would have seen, or would see, a message more or less like
the following:

              The trading shim has connected to the database server

    Your database version differs from that of shim,
    so that one or the other will need to be updated.

    Problem: 513 the shim and database are out of sync

In your case, since the shim got past the version check, and the table
change was to one of the finite domain tables that the shim specifically
checks, we can see that database changes seems to have included the
addition of new bar size symbols, I believe based on a report from Sam:

>           The trading shim has connected to the database server

> Scan: 2 6 0 <s01>
> Term: BarSize type       (nil)
> Grammar rule: Terminal (type)
> Grammar rule: Compound (BarSize)
> Syntax error:
> Cursor state: probe; text is:
> Terminal tag: name:type index:1
> 1|s01|

> Dbms: 0 BarSize 1
> Dbms: 1 BarSize s01

> Problem: 514 a db tuple could not be typechecked
> Exiting

So, to repeat your first question:

> Is data base re-setup necessary with each new release ...

No, it won't be for the general case.  Ideally, table re-creation
should be needed only when you see a problem code 513 message:

    Problem: 513 the shim and database are out of sync

Since, as you've already discovered, it's possible for the version
check to succeed, wrongly, if I've made a mistake as part of building
a release, you are free to also re-create testing if you are trying
to track down a problem with a new release.  In such a case, please
let us know on the list, and keep in mind that I've fixed the
release process to reduce the likelihood of such error on my part.

Now, about the second part of your question:

> ... how can potential data loss best [be] avoided?

In brief, before running either the setup or create scripts, you need
to export journal data, and then reload it afterwards before making any
more orders with the shim.  If you neglect the second step, you've just
forked your journal, and you'll need a fair amount of offline effort
with isql to fuse the two versions, effort that is outside the scope
of this message.

First, some terminology.  Let 'setting up' the database refer to running
the setup.sql script, which deletes old databases, recreates them from
scratch, sets permissions, creates tables, and populates them; and
'recreating tables', to running the create.sql script, which takes the
database and permissions as given, deletes the tables, recreates them,
and populates them.

Note above that setup.sql builds *both* databases, testing and trading,
while create.sql runs in the context of a particular database, and
affects only the tables there.

Then, you should almost never have to run setup.sql, since it's
unlikely that we will need to change permissions for database tables
in the future, though it has happened a couple of times this year.
Ideally the NEWS and release messages to this list should warn you
of such drastic change.  Clearly, though, when you do, you'll need
to export all your data first, since both of the databases, testing
and trading, go away completely.

Apropos of the above, you ask:

> sourcing the setup.sql script ends with ...
> I don't know [whether] it is of importance ...
> [ various permission related error messages]

In the screen scrape you give, it appears that you are running setup.sql
in the wrong permissions context, which is definitely a problem, since
mysql will have refused to execute much if not all of the setup script.

> ERROR 1044 (42000): Access denied for user 'code'@'localhost' to
> database 'mysql'
> ERROR 1146 (42S02): Table 'trading.user' doesn't exist
> ...
> ERROR 1227 (42000): Access denied; you need the RELOAD privilege for
> this operation
> ERROR 1146 (42S02): Table 'trading.tables_priv' doesn't exist
> ERROR 1227 (42000): Access denied; you need the RELOAD privilege for
> this operation
> Query OK, 0 rows affected (0.00 sec)

If you start up mysql as mysql-root, then setup.sql will not be
giving you access denied messages for code at localhost:

> ERROR 1044 (42000): Access denied for user 'code'@'localhost' ...

Once you've taken whatever steps you need to protect your data, about
which more to follow, then, while logged directly onto the db server
machine, and with the tarball sql directory as the current working
directory, please start mysql with the -p option, and source setup.sql.
You should not see the above errors.

Now, about table recreation.  In this case you won't need to explictly
run this step for your site, since you will have already run setup.sql,
which takes care of it, but the create.sql script is a less drastic and
more flexible option to change table definitions than running setup.sql.
You don't need dba-root perms, the database and table permissions are
left alone, and you only affect one database at a time.  You still lose
all your data, however, unless you export it first.

First off, you are of course free not to upgrade, although that would
make bug fixes problematic.  More likely, however, you might want to
experiment with running create.sql in the context of the testing
database, and try out the new shim against that db, while using the
old shim and database with your production work during a transition
period.  Then, once you are happy with what you see with the new
system, you would need to take care of data migration.

So, your question can be refined to that of data migration for the
trading database in advance of recreation/repopulation via create.sql.
I've addressed this question somewhat in an earlier post, in a reply
to a question from Sam.

In brief, you would want to export your history data, in HistoryBar,
and the journal tables, described in the NEWS as follows:

          ... CreateEvent and ChangeOrder records track order
    requests by the shim towards the IB tws, while OrderStatus,
    ActiveOrder, OrderResult, and OrderReport records reflect
    the order status, open order, portfolio value, and execution
    report messages from the IB tws to the shim.

By the way, the sample/prototype export script described in the
the earlier post is sql/bin/dump .

Thanks much for your bug reports, and good luck.  Please feel free
to let us know if the cross-session order manipulation features
work out for you.



More information about the ts-general mailing list