MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<sinisa Date:September 1 1999 12:49pm
Subject:Re: mysqld hangs in infinite loop on particular sequence of updates
View as plain text  
martin.gisch@stripped writes:
 > >Description:
 > 	For one particular table I have, running a series of inserts,
 > 	followed by one update which updates one of the inserted lines,
 > 	the mysqld process hangs and starts chewing up all available cpu
 > 	cycles.
 > 
 > >How-To-Repeat:
 > 	I apologize for the length of this query sequence, but if I trim it
 > 	down to only the 'ncr.com' affecting entries, the error does not occur.
 > 
 > 	Table in question is this:
 > 
 > # MySQL dump 5.13
 > #
 > # Host: localhost    Database: logstats
 > #--------------------------------------------------------
 > # Server version    3.22.19
 > 
 > #
 > # Table structure for table 'smtp_out'
 > #
 > CREATE TABLE smtp_out (
 >   date int(11) DEFAULT '0' NOT NULL,
 >   src tinyint(1) DEFAULT '0' NOT NULL,
 >   domain varchar(50) DEFAULT '' NOT NULL,
 >   messages int(10) unsigned DEFAULT '0' NOT NULL,
 >   bytes bigint(20) unsigned DEFAULT '0' NOT NULL,
 >   mailhost varchar(20) DEFAULT '' NOT NULL,
 >   KEY datemsg (date,messages)
 > );
 > 
 > Running these queries on the newly created table produces the error
 > on my system: (Actually only the last one, the update query kills it,
 > but only if all the inserted data is there.)
 > 
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,0,'austin-hayne.com',4,4025,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,0,'ncr.com',1,1520,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,0,'skytel.com',1,462,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,1,'corp',4,4025,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,1,'eng',1,1520,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990717,1,'east',1,462,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ncr.com',2425,5370846,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'aol.com',969,12110665,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'hotmail.com',864,6559713,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'yahoo.com',483,9312342,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'docomonet.or.jp',439,310587,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'java',310,842615,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'earthlink.net',270,1845030,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'netcom.com',264,8817386,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'skytel.com',257,388733,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'home.com',248,1911983,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'airtouch.com',187,4442178,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'msn.com',183,1093962,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'pacbell.net',176,4805480,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'oracle.com',169,798869,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'att.net',151,844622,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ctc-g.co.jp',144,2466797,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ibm.com',143,2664344,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'intel.com',133,2226844,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'hp.com',132,1800271,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'nifty.ne.jp',122,345336,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'cisco.com',117,1353614,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'juno.com',113,821389,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'usa.net',106,1511254,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'mbn.or.jp',102,348030,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'stanford.edu',99,428852,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'nec.co.jp',94,782060,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ntt.co.jp',92,163609,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'tid.co.jp',92,1504649,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ibm.net',87,304978,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'compuserve.com',87,880386,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'so-net.ne.jp',82,199583,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'berkeley.edu',79,556288,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'clsnet.com.au',76,516594,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'slip.net',76,296823,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'solnet.co.nz',75,1106161,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'sirius.com',73,318385,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'mindspring.com',71,1238056,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'jps.net',69,29223004,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'netscape.net',66,531397,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'compaq.com',63,331626,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'modusmedia.com',62,296668,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'vsnl.net.in',62,318817,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'home.net',61,157500,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'iname.com',59,334770,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'ucdavis.edu',54,207880,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'toshiba.co.jp',53,3033298,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'concentric.net',52,1239677,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'pacific.net.sg',51,236358,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'sgi.com',51,396481,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,0,'sony.co.jp',50,161970,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'eng',9742,56479837,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'japan',3456,22142452,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'aus',1627,111517728,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'corp',1559,14377859,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'east',1276,17487425,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'netcom.com',1035,6627106,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'sun.com',1010,15097484,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'singapore',771,12079394,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'uk',653,28787526,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'ebay',586,24454794,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'newzealand',502,6096797,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'west',336,40855431,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'jps.net',302,1138643,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'hotmail.com',298,908317,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'mozcom.com',290,1389154,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'holland',277,4363651,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'taiwan',257,16304353,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'sun.co.jp',244,2317975,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'germany',227,8407112,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'india',201,832298,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'france',199,4697098,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'asia',194,7450312,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'israel',190,3719299,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'vsnl.net.in',174,1229484,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'prc',162,10103881,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'keio.ac.jp',128,201920,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'korea',125,9918735,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'davis.com',116,399272,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'dubai',115,10500648,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'swiss',99,5055280,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'yahoo.com',98,1073252,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'ukuu.org.uk',86,120357,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'mindspring.com',84,445088,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'malaysia',83,473147,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'thailand',81,1764004,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'usco.com',77,8129331,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'juno.com',77,156223,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'armstrongs.org',71,572828,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'sweden',70,876958,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'bungi.com',70,117705,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'living-net.com',66,168410,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'seikei.ac.jp',64,92672,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'kobe-u.ac.jp',64,112576,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'primenet.com',60,238152,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'canada',56,1473122,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'pacbell.net',48,337905,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'roche.com',45,4737536,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'aol.com',43,79338,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'ox.ac.uk',43,91719,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990718,1,'accuk.co.uk',43,87720,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,0,'austin-hayne.com',4,4029,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,0,'mbn.or.jp',2,4476,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,0,'ncr.com',1,1519,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,0,'aol.com',1,1935,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,1,'corp',4,4029,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,1,'wako.ac.jp',2,4476,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,1,'eng',1,1519,'mercury.ebay');
 > insert into smtp_out (date,src,domain,messages,bytes,mailhost)
values(19990716,1,'east',1,1935,'mercury.ebay');
 > update smtp_out set messages=messages+2055,bytes=bytes+3177999 where date=19990717
and src=0 and domain='ncr.com' and mailhost='mercury.ebay';
 > 
 > 
 > >Fix:
 > 	Dropping the index over date&messages seems to fix the problem.
 > 
 > >Category:	mysql
 > >Class:		sw-bug
 > >Release:	mysql-3.22.25 (TCX binary)
 > 
 > >Environment:
 > 	
 > System: SunOS raptor 5.6 Generic_105181-11 sun4m sparc SUNW,SPARCstation-5
 > Architecture: sun4
 > Memory: 128MB, 463MB Swap
 > 
 > Some paths:  /bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/local/bin/cc
 > GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.8.1/specs
 > gcc version 2.8.1
 > Compilation info: CC='gcc'  CFLAGS='-O3 -fomit-frame-pointer'  CXX='gcc' 
CXXFLAGS='-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' 
LDFLAGS=''
 > Configure command: ./configure  --prefix=/usr/local/mysql '--with-comment=TCX
binary' --with-low-memory --disable-shared
 > Perl: This is perl, version 5.005_02 built for sun4-solaris-thread
 > 
 > mysqld started with this script:
 > #!/bin/csh
 > cd /usr/local/mysql
 > ./bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O
record_buffer=1M -O tmp_table_size=8M &
 > 
 > 
 > Truss output of hung process:
 > 
 > llseek(126, 50, SEEK_SET)                       = 50
 > read(126, "01 &\0 &01 1\bBD07 n c", 11)         = 11
 > pread64(126, " &01 1\bBD07 n c r . c o".., 38, 53) = 38
 > llseek(126, 50, SEEK_SET)                       = 50
 > read(126, "01 &\0 &01 1\bBD07 n c", 11)         = 11
 > pwrite64(126, "01 &\0 &01 1\bBD07 n c r".., 41, 50) = 41
 > ....continues in this repeat sequence forever.
 > 
 > If you need any other details, feel free to ask: Martin.Gisch@stripped.
 > 
 > 

Hi!

There is a small discrepancy. There are two server versions. One is
3.22.25 and a second one is 3.22.19.

Second. This to me looks like a disk full situation !! Please could
you advise us on your df output for a device on which datadir resides.

Third, check whether you have a right binary for your Solaris, which
seems to be 2.5 ???

And you could check with Sun (!) do you have a fully patched OS ! This 
one should not be that difficult for you ;o)

Last, but not least, MySQL supports multi-row inserts ...

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+
Thread
mysqld hangs in infinite loop on particular sequence of updatesmartin.gisch1 Sep
  • Re: mysqld hangs in infinite loop on particular sequence of updatessinisa1 Sep
  • mysqld hangs in infinite loop on particular sequence of updatesMichael Widenius1 Sep