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]
