The POST operation should fail as this operation will violate the foreign key constraint in database. Can you please check the table QB_AUTHORIZATION in database to see if the field FK_AUTH_GROUP is defined as a foreign key into table QB_GROUP. If not, please export/import data again to see if the foreign key can be created.

Hi Robin,

This is what I see directly on the database. We haven't made any modification, except migrations since the first 2.1.x version we have installed. Also, I thought that when we migrated to 3.1.51, I saw that the migration process actually exports the data, recreates the database and then imports the data; if this is the case, foreign keys should be created there, correct?

{code}
mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where TABLE_NAME='QB_AUTHORIZATION';
+------------------+---------------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------------+---------------------+---------------------+-----------------------+------------------------+
| QB_AUTHORIZATION | QB_ID | PRIMARY | NULL | NULL |
| QB_AUTHORIZATION | QB_CONFIGURATION_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_GROUP_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_ID | PRIMARY | NULL | NULL |
| QB_AUTHORIZATION | QB_CONFIGURATION_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_GROUP_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_ID | PRIMARY | NULL | NULL |
| QB_AUTHORIZATION | QB_CONFIGURATION_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_GROUP_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_ID | PRIMARY | NULL | NULL |
| QB_AUTHORIZATION | QB_CONFIGURATION_ID | QB_CONFIGURATION_ID | NULL | NULL |
| QB_AUTHORIZATION | QB_GROUP_ID | QB_CONFIGURATION_ID | NULL | NULL |
+------------------+---------------------+---------------------+-----------------------+------------------------+
12 rows in set (0.00 sec)

mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where TABLE_NAME='QB_GROUP';
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| QB_GROUP | QB_ID | PRIMARY | NULL | NULL |
| QB_GROUP | QB_NAME | QB_NAME | NULL | NULL |
| QB_GROUP | QB_ID | PRIMARY | NULL | NULL |
| QB_GROUP | QB_NAME | QB_NAME | NULL | NULL |
| QB_GROUP | QB_ID | PRIMARY | NULL | NULL |
| QB_GROUP | QB_NAME | QB_NAME | NULL | NULL |
| QB_GROUP | QB_ID | PRIMARY | NULL | NULL |
| QB_GROUP | QB_NAME | QB_NAME | NULL | NULL |
+------------+-------------+-----------------+-----------------------+------------------------+
8 rows in set (0.01 sec)
{code}

The database seems to be created incorrectly: there is no foreign keys created. Please do the following to see if foreign keys can be created:
1. backup the database from QuickBuild administration page.
2. drop the quickbuild schema from mysql.
3. create a blank schema using the same name as above.
4. run "bin/restore.bat" to restore the database from the backup created in step 1.

Then please examine if the foreign key exists, if not, please let me know your MySQL version, JDBC driver version, database style (innodb, isam, etc.)

I did as instructed:

1. Created a backup
2. drop table quickbuild;
3. create table quickbuild;
4. <QB server>/bin/restore.sh backup.zip

All data is back in. When checking the information schema, I get the exact same results as above.

MySQL version (one is an older server that ran QB for 1 year, the other is a new server we are using since 3 months ago):

mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1

JDBC: mysql-connector-java-5.1.16

Tables are MyISAM

Please use innodb instead as myisam does not support foreign keys. To do so:
1. Backup QB database via QB administration page.
2. Shutdown QB.
3. Edit /etc/my.cnf to change "default-storage-engine" from "MYISAM" to "innodb".
4. Restart MySQL service.
5. Drop quickbuild schema and create a blank one with the same name.
6. Restore backup to database by running "bin/restore".
7. Check if all tables created are of "innodb" type, and if "QB_AUTHORIZATION" has foreign keys pointing to QB_CONFIGURATION and QB_GROUP.

We will also modify the hibernate.properties to emphasize that "innodb" should be used as the default storage engine.

Please also document it in here: http://wiki.pmease.com/display/QB31/Data+Management

We have been using QB for a while now, but I haven't seen instructions to use InnoDB anywhere.

Mentioned in data management section now.