MySQL Database Guide

Use the MyISAM database engine instead of InnoDB

The InnoDB engine does not support nested transactions and will cause the scheduled billing process to fail. The problem is that data created in the transaction in the beginning of the process is unavailable to until the billing process completes and the transaction ends. This means that statistics, billing process records, and other data is missing when the billing process goes to look for it. The only solution is to use MyISAM which has no transaction support whatsoever - or to use a database that fully supports nested transactions.

Set the engine to MyISAM for your mysql client session before loading jbilling-schema-mysql.sql:

mysql> SET storage_engine=MyISAM

Warning: When using MyISAM the database will be left in an inconsistent state if the billing process fails. Any transaction that fails will not be rolled back.

Return NULL for null date/time values instead of 0

zeroDateTimeBehavior=convertToNull

Add the above parameter to your JDBC connection URL. MySQL accepts parameters from the JDBC driver by parsing them out of the connection URL in the format:

jdbc:mysql://[host:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Use DATETIME instead of TIMESTAMP

According to the MySQL documentation, if a schema is created while in the “MaxDB” mode all TIMESTAPS will be considered DATETIME types.

To enable MaxDB mode, set the MySQL mode at startup using the "--sql-mode=MAXDB" command line option, or by setting the global variable “sql_mode” to MAXDB at runtime:

mysql> SET GLOBAL sql_mode=MAXDB;

Edit the jbilling-schema-mysql.sql schema script and change all instances of TIMESTAMP to DATETIME.