auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server

vinita vigine MURUGIAH Mon, 27 Oct 2003 21:37:36 -0800

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_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_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]

auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server

Reply via email to