Vinita, it is most probably this bug fixed in 4.1.14:
" Fixed a bug: if in a FOREIGN KEY with an UPDATE CASCADE clause the parent column was of a different internal storage length than the child column, then a cascaded update would make the column length wrong in the child table and corrupt the child table. Because of MySQL's 'silent column specification changes' a fixed-length CHAR column can change internally to a VARCHAR and cause this error. " Below a test run with the latest 4.0 tree. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables [EMAIL PROTECTED]:~/mysql-4.0/client> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.17-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE software ( -> softwareID CHAR(20) NOT NULL, -> softwareName CHAR(100), -> softwareVers CHAR(20), -> installedDate DATE, -> softwareSource BLOB, -> softwareNote BLOB, -> localMods BLOB, -> PRIMARY KEY (softwareID) -> ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql> mysql> CREATE TABLE software_machineOSs ( -> softwareID CHAR(20) NOT NULL, -> id INT(2) unsigned zerofill NOT NULL auto_increment, -> osName CHAR(20), -> osRevision CHAR(20), -> INDEX (softwareID), -> FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON -> DELETE CASCADE ON UPDATE CASCADE, -> KEY(id), -> PRIMARY KEY (softwareID, id) -> ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql> select * from software; Empty set (0.03 sec) mysql> insert into software -> (softwareID,softwareName,softwareVers,installedDate,softwareSource) -> values ("vim-1.1", "vim", "1.1", NOW(), -> "www.test.com.au"); Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into software_machineOSs (softwareID,osName,osRevision) values -> ("vim-1.1", "sun", "8"); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from software; +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ | vim-1.1 | vim | 1.1 | 2003-10-28 | www.test.com.au | N ULL | NULL | +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ 1 row in set (0.00 sec) mysql> select * from software_machineOSs; +------------+----+--------+------------+ | softwareID | id | osName | osRevision | +------------+----+--------+------------+ | vim-1.1 | 01 | sun | 8 | +------------+----+--------+------------+ 1 row in set (0.00 sec) mysql> update software set softwareID="vi-3" where -> softwareID="vim-1.1"; Query OK, 1 row affected (1 min 14.30 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from software; +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ | vi-3 | vim | 1.1 | 2003-10-28 | www.test.com.au | N ULL | NULL | +------------+--------------+--------------+---------------+---------------- -+-- ------------+-----------+ 1 row in set (0.00 sec) mysql> select * from software_machineOSs; +------------+----+--------+------------+ | softwareID | id | osName | osRevision | +------------+----+--------+------------+ | vi-3 | 01 | sun | 8 | +------------+----+--------+------------+ 1 row in set (0.00 sec) mysql> check table software; +---------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+----------+ | test.software | check | status | OK | +---------------+-------+----------+----------+ 1 row in set (0.01 sec) mysql> check table software_machineOSs; +--------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------+-------+----------+----------+ | test.software_machineOSs | check | status | OK | +--------------------------+-------+----------+----------+ 1 row in set (0.01 sec) mysql> show create table software; +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---+ | Table | Create Table | +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---+ | software | CREATE TABLE `software` ( `softwareID` varchar(20) NOT NULL default '', `softwareName` varchar(100) default NULL, `softwareVers` varchar(20) default NULL, `installedDate` date default NULL, `softwareSource` blob, `softwareNote` blob, `localMods` blob, PRIMARY KEY (`softwareID`) ) TYPE=InnoDB | +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---+ 1 row in set (0.00 sec) mysql> show create table software_machineOSs; +---------------------+----------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- -----------------------------------------------------------+ | Table | Create Table | +---------------------+----------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- -----------------------------------------------------------+ | software_machineOSs | CREATE TABLE `software_machineOSs` ( `softwareID` char(20) NOT NULL default '', `id` int(2) unsigned zerofill NOT NULL auto_increment, `osName` char(20) default NULL, `osRevision` char(20) default NULL, PRIMARY KEY (`softwareID`,`id`), KEY `softwareID` (`softwareID`), KEY `id` (`id`), CONSTRAINT `0_15` FOREIGN KEY (`softwareID`) REFERENCES `software` (`softwareI D`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB | +---------------------+----------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- -----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ----- Original Message ----- From: "vinita vigine MURUGIAH" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, October 28, 2003 6:32 AM Subject: auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server > Hello, > I'm using ver 4.0.12, checked for bugs in ver > 4.0.12(http://bugs.mysql.com/search.php) but couldn't find this one. > > CREATE TABLE software ( > softwareID VARCHAR(20) NOT NULL, > softwareName VARCHAR(100), > softwareVers VARCHAR(20), > installedDate DATE, > softwareSource BLOB, > softwareNote BLOB, > localMods BLOB, > PRIMARY KEY (softwareID) > ) TYPE=INNODB; > > CREATE TABLE software_machineOSs ( > softwareID VARCHAR(20) NOT NULL, > osName VARCHAR(20) NOT NULL, > osRevision VARCHAR(20), > INDEX (softwareID), > FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON > DELETE CASCADE ON UPDATE CASCADE, > PRIMARY KEY (softwareID, osName) > ) TYPE=INNODB; > > insert into software > (softwareID,softwareName,softwareVers,installedDate,softwareSource) > values ("vim-1.1", "vim", "1.1", NOW(), "www.test.com.au"); > > insert into software_machineOSs (softwareID,osName,osRevision) values > ("vim-1.1", "sun", "8"); > mysql> select * from software; > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | softwareID | softwareName | softwareVers | installedDate | > softwareSource | softwareNote | localMods | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | vim-1.1 | vim | 1.1 | 2003-10-28 | > www.test.com.au | NULL | NULL | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > 1 row in set (0.00 sec) > > mysql> select * from software_machineOSs; > +------------+--------+------------+ > | softwareID | osName | osRevision | > +------------+--------+------------+ > | vim-1.1 | sun | 8 | > +------------+--------+------------+ > 1 row in set (0.00 sec) > > mysql> update software set softwareID="vi-3" where softwareID="vim-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 | > softwareSource | softwareNote | localMods | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | vi-3 | vim | 1.1 | 2003-10-28 | > www.test.com.au | NULL | NULL | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > 1 row in set (0.00 sec) > > mysql> select * from software_machineOSs; > +------------+--------+------------+ > | softwareID | osName | osRevision | > +------------+--------+------------+ > | vi-3 | sun | 8 | > +------------+--------+------------+ > 1 row in set (0.01 sec) > > *********************** WORKS OK SO FAR BUT ******************************** > > CREATE TABLE software ( > softwareID CHAR(20) NOT NULL, > softwareName CHAR(100), > softwareVers CHAR(20), > installedDate DATE, > softwareSource BLOB, > softwareNote BLOB, > localMods BLOB, > PRIMARY KEY (softwareID) > ) TYPE=INNODB; > > CREATE TABLE software_machineOSs ( > softwareID CHAR(20) NOT NULL, > id INT(2) unsigned zerofill NOT NULL auto_increment, > osName CHAR(20), > osRevision CHAR(20), > INDEX (softwareID), > FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON > DELETE CASCADE ON UPDATE CASCADE, > KEY(id), > PRIMARY KEY (softwareID, id) > ) TYPE=INNODB; > > insert into software > (softwareID,softwareName,softwareVers,installedDate,softwareSource) > values ("vim-1.1", "vim", "1.1", NOW(), "www.test.com.au"); > > insert into software_machineOSs (softwareID,osName,osRevision) values > ("vim-1.1", "sun", "8"); > > mysql> select * from software; > > ysql> select * from software; > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | softwareID | softwareName | softwareVers | installedDate | > softwareSource | softwareNote | localMods | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | vim-1.1 | vim | 1.1 | 2003-10-28 | > www.test.com.au | NULL | NULL | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > 1 row in set (0.00 sec) > > mysql> select * from software_machineOSs; > +------------+----+--------+------------+ > | softwareID | id | osName | osRevision | > +------------+----+--------+------------+ > | vim-1.1 | 01 | sun | 8 | > +------------+----+--------+------------+ > 1 row in set (0.00 sec) > > mysql> update software set softwareID="vi-3" where softwareID="vim-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 | > softwareSource | softwareNote | localMods | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > | vi-3 | vim | 1.1 | 2003-10-28 | > www.test.com.au | NULL | NULL | > +------------+--------------+--------------+---------------+---------------- -+--------------+-----------+ > 1 row in set (0.00 sec) > > mysql> select * from software_machineOSs; > ERROR 2013: Lost connection to MySQL server during query > > mysql> select * from software_machineOSs; > ERROR 2006: MySQL server has gone away > No connection. Trying to reconnect... > Connection id: 1 > Current database: dept_db > > ERROR 2013: Lost connection to MySQL server during query > > **************************************************************************** ************* > Only different is auto_increment id, The reason for to have key for the > id is otherwise it failes. > > mysql> create table test ( > -> name CHAR(20) NOT NULL, > -> id INT(2) unsigned zerofill NOT NULL auto_increment, > -> PRIMARY KEY(name, id) > -> ) TYPE=INNODB; > ERROR 1075: Incorrect table definition; There can only be one auto > column and it must be defined as a key > mysql> > > mysql> create table test ( > -> name CHAR(20) NOT NULL, > -> id INT(2) unsigned zerofill NOT NULL auto_increment, > -> KEY(id), > -> PRIMARY KEY(name, id) > -> ) TYPE=INNODB; > Query OK, 0 rows affected (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]
