ON DELETE CASCADE ON UPDATE CASCADE
HI I'm not sure whether it's a bug or my configuration problem?? I have upgraded mysql from 3.23.53 to 4.0.12 so that the on update cascade will work! then the I got mysql crash when I did the update. Any idea?? mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY (softwareID, osName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql insert into software (softwareID,softwareName,softwareVers) values (test-1.1, test, 1.1); Query OK, 1 row affected (0.00 sec) mysql insert into software_machineOSs (softwareID,osName,osRevision) values (test-1.1, win, 2002); Query OK, 1 row affected (0.00 sec) mysql select * from software; ++--+--+---++ | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---++ | test-1.1 | test | 1.1 | NULL | NULL | ++--+--+---++ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.1 | win| 2002 | ++++ 1 row in set (0.00 sec) mysql update software set softwareID=test-1.6 where softwareID=test-1.1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---++ | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---++ | test-1.6 | test | 1.1 | NULL | NULL | ++--+--+---++ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql ** mungah.cs.mu.OZ.AU.err mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 sort_buffer_size=524280 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 030611 13:59:39 mysqld restarted 030611 13:59:39 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 137838052 InnoDB: Doing recovery: scanned up to log sequence number 0 137838052 InnoDB: Last MySQL binlog file position 0 2391, file name ./mungah-bin.043 030611 13:59:39 InnoDB: Flushing modified pages from the buffer pool... 030611 13:59:39 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.12-log' socket: '/var/mysql/mysql.sock' port: 3306 *** mysqlbinlog mungah-bin.043 update software set softwareID=test-1.6 where softwareID=test-1.1; # at 2391 #030611 13:59:39 server id 1 log_pos 2391 Stop ### When I don't have the column softwareSource ( BLOB) it works fine!!! --- mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), INDEX (softwareID), FOREIGN KEY (softwareID) REFERENCES software (softwareID
Re: ON DELETE CASCADE ON UPDATE CASCADE
Vinita, thank you for the bug report. The BLOB silently changes the CHAR(20) field to a VARCHAR(20) field in te parent table. But the field in the child stays CHAR(20). There is a bug in ON UPDATE CASCADE which forgets to pad the field in the child table with spaces. The child table becomes corrupt because a fixed length field is shorter than it should be! The fix will be in 4.0.14. Thank you, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL - Original Message - From: vinita Vigine Murugiah [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 9:03 AM Subject: ON DELETE CASCADE ON UPDATE CASCADE HI I'm not sure whether it's a bug or my configuration problem?? I have upgraded mysql from 3.23.53 to 4.0.12 so that the on update cascade will work! then the I got mysql crash when I did the update. Any idea?? mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY (softwareID, osName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql insert into software (softwareID,softwareName,softwareVers) values (test-1.1, test, 1.1); Query OK, 1 row affected (0.00 sec) mysql insert into software_machineOSs (softwareID,osName,osRevision) values (test-1.1, win, 2002); Query OK, 1 row affected (0.00 sec) mysql select * from software; ++--+--+---+ + | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---+ + | test-1.1 | test | 1.1 | NULL | NULL | ++--+--+---+ + 1 row in set (0.00 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.1 | win| 2002 | ++++ 1 row in set (0.00 sec) mysql update software set softwareID=test-1.6 where softwareID=test-1.1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+ + | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---+ + | test-1.6 | test | 1.1 | NULL | NULL | ++--+--+---+ + 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql ** mungah.cs.mu.OZ.AU.err mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 sort_buffer_size=524280 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 030611 13:59:39 mysqld restarted 030611 13:59:39 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 137838052 InnoDB: Doing recovery: scanned up to log sequence number 0 137838052 InnoDB: Last MySQL binlog file position 0 2391, file name ./mungah-bin.043 030611 13:59:39 InnoDB: Flushing modified pages from the buffer pool... 030611 13:59:39 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.12-log' socket: '/var/mysql/mysql.sock' port: 3306 *** mysqlbinlog mungah-bin.043 update software set softwareID=test-1.6 where softwareID=test-1.1; # at 2391 #030611 13:59:39
