MySQL Bugs: #23253: Blocked SELECT queries using INNODB tables

Bug #23253	Blocked SELECT queries using INNODB tables
Submitted:	13 Oct 2006 10:08	Modified:	13 Nov 2006 11:47
Reporter:	Patrice Damezin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24a-log	OS:	Other (OpenBSD)
Assigned to:		CPU Architecture:	Any

Description:
I tried to kill the queries but it had no effect at all.
The only way to unlock this situation is to kill the mysql server. mysqladmin shutdown wont work. (S1 Severity ?)
The query seem to lock near a same moment. (6 queries 62490 seconds in the past, 3 at 57040 seconds, as you can see on the show processlist above).

Show processlist :
mysql> show processlist;
+---------+------------+-----------------+------------+---------+--------+----------------+------------------------------------------------------------------------------------------------------+
| Id      | User       | Host            | db         | Command | Time   | State          | Info                                                                                                 |
+---------+------------+-----------------+------------+---------+--------+----------------+------------------------------------------------------------------------------------------------------+
| 1433256 | tropmalin2 | localhost:39717 | tropmalin2 | Killed  | 140998 | Sending data   | SELECT R.rubrique_id, COUNT(*) AS annonces FROM tm_rubrique AS R, tm_rubrique AS R2, tm_annonce AS A |
| 1754942 | web        | localhost:37642 | ladiz      | Killed  |  62490 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1754945 | web        | localhost:8575  | ladiz      | Killed  |  62490 | Sorting result | SELECT * FROM commentaire_desc as a LEFT JOIN commentaire_data as b ON (a.idComment = b.idComment)   |
| 1754948 | web        | localhost:31334 | ladiz      | Killed  |  62490 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1754951 | web        | localhost:45616 | ladiz      | Killed  |  62490 | Sorting result | SELECT * FROM commentaire_desc as a LEFT JOIN commentaire_data as b ON (a.idComment = b.idComment)   |
| 1754955 | web        | localhost:9230  | ladiz      | Killed  |  62489 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1754957 | web        | localhost:16866 | ladiz      | Killed  |  62489 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1819605 | web        | localhost:12200 | ladiz      | Killed  |  57041 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1819616 | web        | localhost:33619 | ladiz      | Killed  |  57040 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1819618 | web        | localhost:2082  | ladiz      | Killed  |  57040 | Sending data   | SELECT count(*) FROM fiche_historique                                                                |
| 1999588 | root       | localhost       | ladiz      | Query   |      0 | NULL           | show processlist                                                                                     |
+---------+------------+-----------------+------------+---------+--------+----------------+------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

But i can execute the queries that appears to block the server :
mysql> use ladiz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT count(*) FROM fiche_historique;
+----------+
| count(*) |
+----------+
|    77430 |
+----------+
1 row in set (0.00 sec)

More info :
bash-3.1# uname -a
OpenBSD ufo.orbus.fr 4.0 GENERIC.MP#1 i386

How to repeat:
No idea how to repeat, but it happend on our server since weeks now.

Thank you for a problem report. Please, send the exact ./configure command line you had used while building MySQL.

MySQL has been build from the OpenBSD ports.

It's seem from logs it used :

./configure --enable-shared --enable-thread-safe-client --localstatedir=/var/mysql --with-big-tables --with-comment=OpenBSD port: mysql-server-5.0.24a --with-libwrap --with-low-memory --with-mysqld-user=_mysql --with-openssl --with-unix-socket-path=/var/run/mysql/mysql.sock --with-vio --without-bench --without-debug --without-docs --without-extra-tools --without-readline --with-client-ldflags=-lsupc++ --with-mysqld-ldflags=-lsupc++ --enable-assembler --prefix=/usr/local --sysconfdir=/etc --mandir=/usr/local/man --infodir=/usr/local/info

I think threading library may be a problem. Anyway, please, try to provide set of steps to reproduce this problem each and every time. Try to check newer version, 5.0.26, also. Not sure it is in ports already.

Patrice,

please post the .err log.

--Heikki

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Similar Problem here:
OS: OpenSuse 10.1
Mysqld 5.1.29-rc

Sometimes a query hangs and is not killable. Server won't shutdown...

143  	web2_u1  	localhost  	web2_db1  	Query  	18815  	Sending data  	SELECT *
FROM Buecher
WHERE (
Bestellnr = 'BRH41-0025'
OR Bestellnr = 'BKD00-0051'
)
AND Haendler = '85'

...

|   143 | web2_u1  | localhost                     | web2_db1 | Killed  | 19027 | Sending data | select * from Buecher where ( Bestellnr='BRH41-0025'  or  Bestellnr='BKD00-0051' ) and Haendler='85' |
...

|   143 | web2_u1  | localhost                     | web2_db1 | Killed  | 19045 | Sending data | select * from Buecher where ( Bestellnr='BRH41-0025'  or  Bestellnr='BKD00-0051' ) and Haendler='85' |
...

|   143 | web2_u1  | localhost                     | web2_db1 | Killed  | 19553 | Sending data | select * from Buecher where ( Bestellnr='BRH41-0025'  or  Bestellnr='BKD00-0051' ) and Haendler='85' |
|

/etc/init.d # ./mysql stop
Shutting down MySQL.......................................(forever)

then i'm doing kill -9 <pid> (of mysqld-safe and mysqld)

Table Structure is not relevant... it also happens with other (smaller) tables.
In this case, Buecher has about 2.000.000 entries. repeating the same query after restarting the server results in immediate answer (about few usec) as well as each other query.

Is there a known problem with suse linux or big tables on dualcore x86_64 systems?