| Bug #18714 | damage table and server crash/restart after update table | ||
|---|---|---|---|
| Submitted: | 2 Apr 2006 0:13 | Modified: | 8 May 2006 15:12 |
| Reporter: | Radovan Jablonovsky | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.19 | OS: | Linux (SLES 9/ Win2003 server) |
| Assigned to: | CPU Architecture: | Any | |
[2 Apr 2006 8:05]
Valeriy Kravchuk
Thank you for a detailed bug report. It looks like a duiplicate of a know bug #16504, though. Please, check.
[5 Apr 2006 18:38]
Radovan Jablonovsky
Hi, The first problem, slow query response is solved in bug #16504. (select join use OR clausule, which this bug solved.) Hovewer second part of my bug report, damaged table and server crash-restart, it probably did not solved. Why. SQL UPDATE command below, which worked on MySQl 4.1.12 OK, damaged updated table (it was neccesary repair table), crashed and then restarted MySQL 5.0.19 server. This violent behaviour was not desribed in bug #16504 and it is not performance problem, but qualitative problem. (damaged table and server restart) /* Result of this update is crashed table vwarload and mysqld server crash-restart. */ UPDATE vwarload l1, vwarload l2, updatevwArLoad u SET l1.Origin = l2.Destination WHERE l1.load_id = u.load_id AND l2.load_id = u.load_idOrigin AND u.ID <> 1 AND l1.DivisionName = l2.DivisionName AND l1.TrailerA = l2.TrailerA AND (l1.TrailerB = l2.TrailerB OR (l1.TrailerB is NULL AND l2.TrailerB is NULL) ) AND DATE_SUB(l1.LoadDate, interval 7 day ) < l2.UnloadDate;
[6 Apr 2006 6:54]
Valeriy Kravchuk
OK. Let's concentrate on server crash. Please, send the appropriate part of your error log and you my.cnf/my.ini files content.
[8 Apr 2006 21:25]
Radovan Jablonovsky
Hi, Do you need full dataset to populate table vwArLoad for testing? If yes, where should I upload it? It is about 2MB in zip file.
[10 Apr 2006 10:17]
Valeriy Kravchuk
Please, describe your upgrade procedure. Did you use mysqldump in 4.1.x and then restored data in 5.0.19 (as it is recommended, by the way) or just installed new binaries?
[10 Apr 2006 18:47]
Radovan Jablonovsky
I do not think this question is relevant as you can see from error logs, but here is response. On production server SLES9 SP2 I created backup - mysqldump and then installed binaries. I did not use mysqldump for recreating database. When I had problem with table vwArLoad, I drop this table and recreated it from mysqldump, rest was still from version 4.1.12. I tried update and had problem(s) again. On testing server on Windows 2003 server R2, I installed fresh MySQL 5.0.19 server. Then I created database from mysqldump from production server. I had problem(s) with update.
[8 May 2006 14:46]
Radovan Jablonovsky
I check version 5.0.21a and it seem to me that this particular bug is solved. I did not experienced more table coruptions or server restart(s) with.
[8 May 2006 15:12]
Valeriy Kravchuk
According to the reporter, the problem seems to be solved in 5.0.21.
Description: Slow select performance 100 times and probably infinite update after running the same batch sctript against 4.1.12 and 5.0.19 Update problems finished in reboot server and this error message: ERROR 2013 (HY000) at line 67: Lost connection to MySQL server during query and table was marked crashed and repair is necessary. How to repeat: CREATE TABLE `vwarload` ( `LoadVehID` int(11) NOT NULL default '0', `LoadTruck` varchar(50) default NULL, `LoadDriver` varchar(95) default NULL, `UnloadDuration` int(11) default NULL, `UnloadTruck` varchar(50) default NULL, `UnloadDriver` varchar(95) default NULL, `Waybill` varchar(20) NOT NULL default '', `Ticket` varchar(20) NOT NULL default '', `RouteName` varchar(20) NOT NULL default '', `Product` varchar(20) NOT NULL default '', `GrossWeight` int(11) NOT NULL default '0', `TareWeight` int(11) NOT NULL default '0', `NetWeight` int(11) default NULL, `TrailerA` varchar(7) NOT NULL default '', `TrailerB` varchar(7) default NULL, `AxleConfig` varchar(25) default NULL, `LoadDate` datetime NOT NULL default '1900-01-01 00:00:00', `LoadPoint` varchar(20) NOT NULL default '', `UnloadDate` datetime NOT NULL default '1900-01-01 00:00:00', `Destination` varchar(20) NOT NULL default '', `LoadTaskID` int(11) NOT NULL default '0', `DivisionName` varchar(50) NOT NULL default '', `ServerName` varchar(16) NOT NULL default '[Unknown]', `Load_ID` int(11) NOT NULL default '0', `LoadDuration` int(11) default NULL, `LoadWait` int(11) default NULL, `Origin` varchar(35) NOT NULL default '[Unknown]', `EID` smallint(6) unsigned NOT NULL default '7', `UnloadWait` int(11) default NULL, KEY `IX_vwArLoad_Key1` (`Waybill`,`Ticket`,`Product`,`TrailerA`,`LoadPoint`,`Destination`,`UnloadDate`,`AxleConfig`,`LoadDriver`,`UnloadDriver`,`LoadTruck`,`UnloadTruck`,`Origin`,`RouteName`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `updatevwarload` ( `ID` bigint(20) NOT NULL auto_increment, `load_id` int(11) NOT NULL default '0', `load_idOrigin` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), UNIQUE KEY `IX_updatevwArLoad_Key1` (`load_id`,`load_idOrigin`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; --------------------------------------------------------------- I have 23000rows of data in table vwarload data example are in cvs file. ----------------------------------------------------- -- TESTING SCRIPT ---------------------------------------------------- /* update vwArLoad table in db */ --BEGIN OK part truncate table updatevwArLoad; INSERT INTO updatevwArLoad(load_id, load_idorigin) SELECT load_id, 0 FROM vwarload WHERE UnloadDate > DATE_SUB(now(), interval 90 day ) order by DivisionName, trailera, trailerb, loaddate; --move Origin to Destination UPDATE updatevwArLoad u1, updatevwArLoad u2 SET u1.load_idorigin = u2.load_id WHERE u1.ID = u2.ID+1; --END this was OK /* This select is 100 slower than on the same pc with MySQL 4.1.12 */ SELECT l1.load_id, l1.DivisionName, l1.TrailerA, l1.TrailerB, l1.LoadTruck, l1.UnloadTruck, l1.LoadDate, l1.UnloadDate, l1.LoadPoint, l1.Destination, l2.Destination as Origin FROM vwarload l1, vwarload l2, updatevwArLoad u WHERE l1.load_id = u.load_id AND l2.load_id = u.load_idOrigin AND u.ID <> 1 AND l1.DivisionName = l2.DivisionName AND l1.TrailerA = l2.TrailerA AND (l1.TrailerB = l2.TrailerB OR (l1.TrailerB is NULL AND l2.TrailerB is NULL) ) AND DATE_SUB(l1.LoadDate, interval 7 day ) < l2.UnloadDate; /* This updated on MySQL 4.1.12 takes about 5sec on the same pc. Posible infinity loop (taked more then 15min before i choose kill it). Rresult of this update is crashed table vwarload and mysqld server restart. */ UPDATE vwarload l1, vwarload l2, updatevwArLoad u SET l1.Origin = l2.Destination WHERE l1.load_id = u.load_id AND l2.load_id = u.load_idOrigin AND u.ID <> 1 AND l1.DivisionName = l2.DivisionName AND l1.TrailerA = l2.TrailerA AND (l1.TrailerB = l2.TrailerB OR (l1.TrailerB is NULL AND l2.TrailerB is NULL) ) AND DATE_SUB(l1.LoadDate, interval 7 day ) < l2.UnloadDate; Suggested fix: Probably, check table join and update algorithm.