[ts-gen] setting the ANSI sql isolation level, and populating tables

[Bill Pippin]

Billy Pilgrim notes problems with database setup:

> Thanks shim now builds fine however I am having a problem with the mysql
> database creation scripts.  A few of generate syntax errors and not all
> the db tables are created.

> Does anyone have any thoughts on this!

Paul C goes right to the source of the problem [thanks, Paul!]:

> Could be associated with the "sql-mode=ANSI" in your mysql.cnf file ...
> enabling sql-mode=ANSI in my /etc/mysql/my.cnf [i'm debian, not fedora]
> reproduces some of your errors ...

Yes.  Although setting sql-mode=ANSI will indeed provide the required
isolation level, of SERIALIZABLE, it will also disable mysql extensions
to the syntax of ANSI sql, some of which our database setup scripts depend
on.

>From the manual, section 3.3.1, "Setting the Dbms Isolation Level",
also at, currently, http://www.trading-shim.com/doc/node36.html :

> Edit the /etc/my.cnf file to insert the line
> transaction-isolation = SERIALIZABLE in the mysql stanza.

As suggested by the manual, and in addition the patch sql/mysql.iso.patch,
the recommended approach to achieving the ANSI-standard isolation level
is to add the lines below to the conf file.

    [mysqld]
    transaction-isolation = SERIALIZABLE

Although you could also set sql-mode to achieve the same result, it would
cost you the ability to run the setup file scripts.

Thanks to Billy Pilgrim for exposing the need for more detail in our
documentation to explain the drawbacks of setting ANSI mode.  Although
the text of the previous version of the manual gave weak reasons why it
was not a good idea to use sql-mode=ANSI on the command line, it did not
consider its use in the configuration file.  I've now added the following
footnote to the manual:

> If you choose to set the sql-mode via sql-mode=ANSI, whether from
> the command line or in the configuration file, be aware that this
> approach is neither recommended nor supported.  It disables a
> number of mysql extensions to the syntax of ANSI sql, including
> the freedom to use double quotes in place of single quotes for
> strings that have embedded single quotes, as with some of the
> database symbol load scripts.  Perhaps more critically, there
> are also reports of additional restrictions on the use of sql
> reserved words as identifiers.

The manual continues to recommend an alternative, that of setting the
isolation level explicitly in the configuration file.  That remains the
recommended approach; it uses the least change to the server to achieve
the needed result.

Thanks,

Bill