Hello, I am seeing rather frequently our mySQL DB Freezes up. It runs the back end of a web site that gets decent traffic. When I check the process list for the DB, there is a bunch of processes all locking that table sitting there. I normally disable connections from the Cold Fusion server, meaning I do not let the Cold Fusion server leave an open connection to the database. When it is done with it's transaction, it disconnects the connection, so usually when I check the process list, there is no more than 5 processes at a time.
the reason I turned of leaving open connections is because I was seeing a problem where idle connections would be left in a sleep state and never disconnected... so I would end up using up all of the connections. once this would happen, I would have to restart mySQL to purge off the "zombied" threads. I have a hunch the connections should be allowed to stay, and the CF server should be smart enough to continually use the same connection, but since that did not seem to be the case, I disabled it. Anyhow, does anyone know why processes would stack up, all with a state of "locked"? Is there any countermeasure I can take, or something I am doing wrong? Dan Uyemura IT GSociety, Inc. 7060 Hollywood Blvd. #800 Hollywood, CA 90028 (v) (323) 337-0141 GSociety family of companies: www.gaywired.com www.lesbianation.com www.qtmagazine.com www.dance1.net www.ghighway.com -----Original Message----- From: Mark Maunder [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 2:01 PM To: [EMAIL PROTECTED] Subject: MYSQL 4.0 bug with fulltext (case change) updates Hi, I think this is a bug. The script to recreate the problem is included below. This problem appears consistently as long as there's a fulltext index and a regular index on the same field and you do an update to change the case of a single char. It doesn't matter if the fulltext index includes other fields. If the regular index is removed, then after the insert (where the case of one char in a field is changed) the table is briefly marked as crashed, and then seems to automagically fix itself (which is worrying). The bug also occurs when you change the case of a single char in a varchar field and add a word to the sentence e.g.: 'experience with c required' changes to 'experience with C is required' also causes the problem. Thanks as always, ~mark ps: If anyone knows of a workaround please let me know. thanks. __BEGIN__ #This works fine on mysql 3 but crashes the table on 4 alpha CREATE TABLE tester ( id int(11) auto_increment, title varchar(100) default '', PRIMARY KEY (id), #If you comment out the following index, then the table is briefly marked as crashed # but appears to recover. KEY ind5 (title), #The fulltext index appears to be the root of this prob. FULLTEXT KEY FT1 (title) ) TYPE=MyISAM; insert into tester (title) values ('this is a test'); update tester set title='this is A test' where id=1; check table tester; __END__ Gives us: Table Op Msg_type Msg_text freeusall.tester check warning Table is marked as crashed freeusall.tester check error Checksum for key: 2 doesn't match checksum for records freeusall.tester check error Corrupt We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM tables. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
