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.

• Change the trigger create format from the following:

  CREATE TRIGGER T_UNKNOWNPKVC1 
  BEFORE UPDATE ON UNKNOWNPKVC1
  FOR EACH ROW
  		WHEN (NEW.VERSION = OLD.VERSION)
  BEGIN
  		:NEW.VERSION := :OLD.VERSION + 1;
  END;
  /

  To the following:

  DELIMITER |
  CREATE TRIGGER T_UNKNOWNPKVC1
  BEFORE UPDATE ON UNKNOWNPKVC1
  FOR EACH ROW
  		WHEN (NEW.VERSION = OLD.VERSION)
  BEGIN
  		:NEW.VERSION := :OLD.VERSION + 1;
  END
  |
  DELIMITER ;

  For more information, see http://dev.mysql.com/doc/mysql/en/create-trigger.html.

• 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.

• When an SQL script has foreign key constraints defined, capture-schema fails to capture the table information correctly. To work around the problem, remove the constraints and then run capture-schema. Here is an example that illustrates the issue.

  CREATE TABLE ADDRESSBOOKBEANTABLE (ADDRESSBOOKNAME VARCHAR(255) 
      NOT NULL PRIMARY KEY, 
  CONNECTEDUSERS              BLOB NULL, 
  OWNER                       VARCHAR(256), 
  FK_FOR_ACCESSPRIVILEGES     VARCHAR(256), 
  CONSTRAINT FK_ACCESSPRIVILEGE FOREIGN KEY (FK_FOR_ACCESSPRIVILEGES) 
      REFERENCES ACCESSPRIVILEGESBEANTABLE (ROOT) 
  ) ENGINE=InnoDB;  

  To resolve this issue, change the table creation script to the following:

  CREATE TABLE ADDRESSBOOKBEANTABLE (ADDRESSBOOKNAME VARCHAR(255) 
      NOT NULL PRIMARY KEY, 
  CONNECTEDUSERS              BLOB NULL, 
  OWNER                       VARCHAR(256), 
  FK_FOR_ACCESSPRIVILEGES     VARCHAR(256) 
  ) ENGINE=InnoDB;