The following restrictions apply when you use a MySQL database with the Application Server for persistence.
MySQL treats int1 and int2 as reserved words. If you want to define int1 and int2 as fields in your table, use `int1` and `int2` field names in your SQL file.
When VARCHAR fields get truncated, a warning is displayed instead of an error. To get an error message, start the MySQL database in strict SQL mode.
The order of fields in a foreign key index must match the order in the explicitly created index on the primary table.
The CREATE TABLE syntax in the SQL file must end with the following line.
InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine having commit, rollback, and crash recovery capabilities.
For a FLOAT type field, the correct precision must be defined. By default, MySQL uses four bytes to store a FLOAT type that does not have an explicit precision definition. For example, this causes a number such as 12345.67890123 to be rounded off to 12345.7 during an INSERT. To prevent this, specify FLOAT(10,2) in the DDL file, which forces the database to use an eight-byte double-precision column. For more information, see http://dev.mysql.com/doc/mysql/en/numeric-types.html.
To use || as the string concatenation symbol, start the MySQL server with the --sql-mode="PIPES_AS_CONCAT" option. For more information, see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html and http://dev.mysql.com/doc/mysql/en/ansi-mode.html.
MySQL always starts a new connection when autoCommit==true is set. This ensures that each SQL statement forms a single transaction on its own. If you try to rollback or commit an SQL statement, you get an error message.
javax.transaction.SystemException: java.sql.SQLException: Can't call rollback when autocommit=true
javax.transaction.SystemException: java.sql.SQLException: Error open transaction is not closed
To resolve this issue, add relaxAutoCommit=true to the JDBC URL. For more information, see http://forums.mysql.com/read.php?39,31326,31404.
MySQL does not allow a DELETE on a row that contains a reference to itself. Here is an example that illustrates the issue.
create table EMPLOYEE ( empId int NOT NULL, salary float(25,2) NULL, mgrId int NULL, PRIMARY KEY (empId), FOREIGN KEY (mgrId) REFERENCES EMPLOYEE (empId) ) ENGINE=InnoDB; insert into Employee values (1, 1234.34, 1); delete from Employee where empId = 1;
This example fails with the following error message.
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
To resolve this issue, change the table creation script to the following:
create table EMPLOYEE ( empId int NOT NULL, salary float(25,2) NULL, mgrId int NULL, PRIMARY KEY (empId), FOREIGN KEY (mgrId) REFERENCES EMPLOYEE (empId) ON DELETE SET NULL ) ENGINE=InnoDB; insert into Employee values (1, 1234.34, 1); delete from Employee where empId = 1;
This can be done only if the foreign key field is allowed to be null. For more information, see http://bugs.mysql.com/bug.php?id=12449 and http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html.