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 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) ON > DELETE CASCADE ON UPDATE CASCADE, > PRIMARY KEY (softwareID, osName) > ) TYPE=INNODB; > 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.01 sec) > > mysql> > 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 | > +------------+--------------+--------------+---------------+ > | test-1.1 | test | 1.1 | NULL | > +------------+--------------+--------------+---------------+ > 1 row in set (0.01 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.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > mysql> select * from software; > +------------+--------------+--------------+---------------+ > | softwareID | softwareName | softwareVers | installedDate | > +------------+--------------+--------------+---------------+ > | test-1.6 | test | 1.1 | NULL | > +------------+--------------+--------------+---------------+ > 1 row in set (0.00 sec) > > mysql> select * from software_machineOSs; > +------------+--------+------------+ > | softwareID | osName | osRevision | > +------------+--------+------------+ > | test-1.6 | win | 2002 | > +------------+--------+------------+ > 1 row in set (0.00 sec) > > > -- > > > warm regards > Vinita Vigine Murugiah > Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
