Hello, I'm having an odd problem with my MySQL server that I thought someone might have some ideas on.
Setup: MySQL 4.0.10-max Mac OS X Server 10.2.3 DP G4 1.25 GHz 1G RAM MySQL data and temp files on 400G striped RAID array (off an Acard hardware controller) that is 90% empty Clients accessing through a mix of command line, phpMyAdmin (just upgraded to 2.4.0rc1, this occurred under 2.3.3pl1 and previous versions) and php scripts PHP install on machine is 4.1.2 (the one that ships with OS X server) We have about 10 users running queries on a hundred or so databases that collectively have about 7G of data. The largest is 2.5 million records/600MB. What happens: Occasionally (every thousand queries or so) a user will run a query which they then try to kill (usually b/c they realize it will take forever to finish due to a mistake of their's). The query gets marked as killed, but it never actually dies, show processlist just shows it at killed even if we allow it to sit for days. Here's an early processlist of one (shortened) that stayed this way for 24 hours before I rebooted the server: +------+------+-----+-------+---------+------+--------------+------+ | Id | User | Host| db | Command | Time | State | Info | +------+------+-----+-------+---------+------+--------------+------+ | 4429 | tsou | dcen| constr| Killed | 2084 | Sending data | | The query that did this was: insert into tsou.new_table select cust_name, id, sales, abbreviation from tsou.customers, tsou.state where customers.id = state.id and state.abbreviation <> 'LA' Customers has about 800,000 records in it, and as it turns out none of them had a state.abbreviation of LA, so the output should have been 800,000 records. All of the tables are MyISAM. The user realized after sending the query that he hadn't indexed the two tables, so he decided to kill the query and then index (this is a query he'll be doing regularly). He issued the query from phpMyAdmin, and unfortunately the version he was using (2.3.3pl1) had a bug that prevented the display of the show processes link. So he opened a command line connection to the MySQL server (using the Win 2000 client) and issued a kill command for the query. The query had written about 250,000 rows (we know after the restart), but it wound up in the state shown above. It didn't seem to be consuming any resources as the mysqld process under top showed 0% cpu usage when this was the only query in the process list. Still, it locked us out of the database it was writing which meant it wouldn't allow our cron job to run mysqlhotcopy and backup all the databases. At this point we decided to try killing it with mysqladmin which didn't work, so we tried to kill -9 mysqld from the Mac OS X command line. This froze the machine and required a hard restart. So I was wondering if this sounded familiar to anyone and if there are any known bugs or work arounds for it. I think that every time it has happened the query has been issued from phpMyAdmin and then the kill has been issued from the command line. I'm wondering if this or some bug in php or phpMyAdmin is causing confusion. Failing any way to avoid these stuck queries in the first place, is there a way to successfully kill them so our backup can run? Or failing that is there a way to kill off mysqld without rebooting the machine? Sorry to be so long winded, but if you've read this far hopefully the detail has been somewhat helpful. Thanks for any help you can give, Ware --------------------------------------------------------------------- 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
