MySQL Database Restrictions

The following restrictions apply when you use a MySQL database with the Communications 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.

  )  Engine=InnoDB;

  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.