I hope someone can spot why shutting mysql down and restarting should result in ERROR 1452 (23000): Cannot add or update a child row.... (see below).

The tables were created by Spring running Hibernate with entity annotations. I shut this down before running the command line tests below. All the mysql variables have default values (I don't have a my.cnf file). innodb_fastshutdown is set to 1, but I also tried setting it to 0 without effect.

I am using Spring 2.5.2 with hibernate-core-3.3.2.GA, hibernate-entitymanager-3.4.0.GA, hibernate-annotations-3.4.0.GA, hibernate-commons-annotations-3.1.0.GA, hibernate-validator-4.0.0.GA, hibernate-validator-annotation-processor-4.1.0.Final etc Let me know if you want to see the full pom file.

I have two tables:

mysql> show create table staff;
+-------+-------------------------+
| Table | Create Table |
+-------+-------------------------+
| staff | CREATE TABLE `staff` (
`identifier` varchar(255) NOT NULL,
PRIMARY KEY (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table badge;
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| badge | CREATE TABLE `badge` (
`rfid` varchar(255) NOT NULL,
`shortId` varchar(255) DEFAULT NULL,
`owner_identifier` varchar(255) DEFAULT NULL,
PRIMARY KEY (`rfid`),
KEY `FK3CFAB83C30D38F7` (`owner_identifier`),
CONSTRAINT `FK3CFAB83C30D38F7` FOREIGN KEY (`owner_identifier`) REFERENCES `Staff` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from staff;
+------------+
| identifier |
+------------+
| S0 |
| S1 |
| S10 |
....

mysql> select * from badge;
+------+---------+------------------+
| rfid | shortId | owner_identifier |
+------+---------+------------------+
| B0 | NULL | S0 |
| B1 | NULL | S1 |
| B10 | NULL | S10 |
...

mysql> update badge set owner_identifier=NULL where rfid='B0';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from badge;
+------+---------+------------------+
| rfid | shortId | owner_identifier |
+------+---------+------------------+
| B0 | NULL | NULL |
| B1 | NULL | S1 |
| B10 | NULL | S10 |
...

mysql> update badge set owner_identifier='S1' where rfid='B0';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This works fine as would be expected since S1 exists in Staff.

At this point MySQL is shutdown with: mysqladmin shutdown -u root -p

110513 16:25:54 [Note] Event Scheduler: Purging the queue. 0 events
110513 16:25:54 InnoDB: Starting shutdown...
110513 16:25:56 InnoDB: Shutdown completed; log sequence number 58384723
110513 16:25:56 [Note] mysqld: Shutdown complete

and then started again:

bash-3.2$ mysqld
110513 16:26:48 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.5.9-osx10.6-x86_64/data/ is case insensitive
110513 16:26:48 [Note] Plugin 'FEDERATED' is disabled.
110513 16:26:48 InnoDB: The InnoDB memory heap is disabled
110513 16:26:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110513 16:26:48 InnoDB: Compressed tables use zlib 1.2.3
110513 16:26:48 InnoDB: Initializing buffer pool, size = 128.0M
110513 16:26:48 InnoDB: Completed initialization of buffer pool
110513 16:26:48 InnoDB: highest supported file format is Barracuda.
110513 16:26:48 InnoDB: Waiting for the background threads to start
110513 16:26:49 InnoDB: 1.1.5 started; log sequence number 58384723
110513 16:26:49 [Note] Event Scheduler: Loaded 0 events
110513 16:26:49 [Note] mysqld: ready for connections.
Version: '5.5.9' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)


Then we run two commands again:
mysql> update badge set owner_identifier=NULL where rfid='B0';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update badge set owner_identifier='S1' where rfid='B0';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`astute`.`badge`, CONSTRAINT `FK3CFAB83C30D38F7` FOREIGN KEY (`owner_identifier`) REFERENCES `Staff` (`identifier`))

I don't understand why shutting mysql down and restarting should then change the behaviour of the
second command. I was able to run this update
prior to the sutdown, but not after.

mysql> select * from staff;
+------------+
| identifier |
+------------+
| S0 |
| S1 |
| S10 |
| S11 |
....

mysql> select * from badge;
+------+---------+------------------+
| rfid | shortId | owner_identifier |
+------+---------+------------------+
| B0 | NULL | NULL |
| B1 | NULL | S1 |
| B10 | NULL | S10 |
...

What have I done wrong?

Chris