Description:
When I happen to run SELECT on a column without an index, the query takes long, cannot be aborted or KILLed. It seems the query never gets back to a state where it would check the kill flag.
Workaround: Restart mysqld process.
At least for SELECTs, it doesn't really matter, as they don't seem to be blocking anything else, yet it would be proper if the query got killed at some point.
How to repeat:
mysql> show create table TESTTBL\G
*************************** 1. row ***************************
Table: TESTTBL
Create Table: CREATE TABLE `TESTTBL` (
`MYPK` int(11) NOT NULL,
`MYFK` int(11) NOT NULL,
`ID` smallint(6) NOT NULL,
`col2` varchar(40) NOT NULL,
`VVALID` bigint(20) NOT NULL,
`col3` smallint(6) DEFAULT NULL,
`col4` varchar(1000) DEFAULT NULL,
`VINVALID` bigint(20) DEFAULT NULL,
`col5` tinyint(4) DEFAULT NULL,
`col6` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`MYPK`,`MYFK`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (MYFK) */
1 row in set (0.00 sec)
mysql> select count(*) from TESTTBL;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.02 sec)
mysql> explain select * from TESTTBL where col2='4774465977';
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | TESTTBL | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> select * from TESTTBL where col2='4774465977';
^CQuery aborted by Ctrl+C
-- Ctrl-C does not abort it...
^C
-- Second Ctrl-C kills mysql client
root@host1 # bin/mysql -h host1-ge3
mysql> use TESTDB;
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> show processlist;
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 2 | root | | TESTDB | Query | 134 | Sending data | select * from TESTTBL where col2='4774465977' |
| 21 | root | | TESTDB | Query | 0 | NULL | show processlist |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> kill query 2;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 2 | root | | TESTDB | Query | 229 | Sending data | select * from TESTTBL where col2='4774465977' |
| 21 | root | | TESTDB | Query | 0 | NULL | show processlist |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 2 | root | | TESTDB | Query | 237 | Sending data | select * from TESTTBL where col2='4774465977' |
| 21 | root | | TESTDB | Query | 0 | NULL | show processlist |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
3 rows in set (0.00 sec)
-- No matter how long you wait, it is not killed
mysql> select * from TESTTBL WHERE MYPK=1;
+----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+
| MYPK | MYFK | ID | col2 | VVALID | col3 | col4 | VINVALID | col5 | col6 |
+----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+
| 1 | 1 | 10 | 4773840001 | 0 | 1 | fooxxxxx 1 | 999999999999 | 1 | 3 |
+----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+
1 row in set (0.03 sec)
-- OTOH, other selects can run just fine, so it is not critical.
mysql> show processlist;
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 2 | root | | TESTDB | Query | 297 | Sending data | select * from TESTTBL where col2='4774465977' |
| 21 | root | | TESTDB | Query | 0 | NULL | show processlist |
+----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
Suggested fix:
At some point, a running NDB query should check whether it should abort itself.
Description: When I happen to run SELECT on a column without an index, the query takes long, cannot be aborted or KILLed. It seems the query never gets back to a state where it would check the kill flag. Workaround: Restart mysqld process. At least for SELECTs, it doesn't really matter, as they don't seem to be blocking anything else, yet it would be proper if the query got killed at some point. How to repeat: mysql> show create table TESTTBL\G *************************** 1. row *************************** Table: TESTTBL Create Table: CREATE TABLE `TESTTBL` ( `MYPK` int(11) NOT NULL, `MYFK` int(11) NOT NULL, `ID` smallint(6) NOT NULL, `col2` varchar(40) NOT NULL, `VVALID` bigint(20) NOT NULL, `col3` smallint(6) DEFAULT NULL, `col4` varchar(1000) DEFAULT NULL, `VINVALID` bigint(20) DEFAULT NULL, `col5` tinyint(4) DEFAULT NULL, `col6` tinyint(4) DEFAULT NULL, PRIMARY KEY (`MYPK`,`MYFK`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (MYFK) */ 1 row in set (0.00 sec) mysql> select count(*) from TESTTBL; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.02 sec) mysql> explain select * from TESTTBL where col2='4774465977'; +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | TESTTBL | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> select * from TESTTBL where col2='4774465977'; ^CQuery aborted by Ctrl+C -- Ctrl-C does not abort it... ^C -- Second Ctrl-C kills mysql client root@host1 # bin/mysql -h host1-ge3 mysql> use TESTDB; 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> show processlist; +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL | | 2 | root | | TESTDB | Query | 134 | Sending data | select * from TESTTBL where col2='4774465977' | | 21 | root | | TESTDB | Query | 0 | NULL | show processlist | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> kill query 2; Query OK, 0 rows affected (0.00 sec) mysql> show processlist; +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL | | 2 | root | | TESTDB | Query | 229 | Sending data | select * from TESTTBL where col2='4774465977' | | 21 | root | | TESTDB | Query | 0 | NULL | show processlist | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> show processlist; +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL | | 2 | root | | TESTDB | Query | 237 | Sending data | select * from TESTTBL where col2='4774465977' | | 21 | root | | TESTDB | Query | 0 | NULL | show processlist | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ 3 rows in set (0.00 sec) -- No matter how long you wait, it is not killed mysql> select * from TESTTBL WHERE MYPK=1; +----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+ | MYPK | MYFK | ID | col2 | VVALID | col3 | col4 | VINVALID | col5 | col6 | +----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+ | 1 | 1 | 10 | 4773840001 | 0 | 1 | fooxxxxx 1 | 999999999999 | 1 | 3 | +----------+----------+----+------------+-------------+----------------+-------------------------+---------------+--------------+----------+ 1 row in set (0.03 sec) -- OTOH, other selects can run just fine, so it is not critical. mysql> show processlist; +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ | 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL | | 2 | root | | TESTDB | Query | 297 | Sending data | select * from TESTTBL where col2='4774465977' | | 21 | root | | TESTDB | Query | 0 | NULL | show processlist | +----+-------------+------+------+---------+------+-----------------------------------+--------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> Suggested fix: At some point, a running NDB query should check whether it should abort itself.