Bug #16159 Thread stuck in undocumented preparing state
Submitted: 3 Jan 2006 17:20 Modified: 23 Aug 2009 18:34
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:MySQL 5.0.18-nt OS:Microsoft Windows (Windows XP)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[3 Jan 2006 17:20] Olaf van der Spek
Description:
Hi,

At http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html the state preparing is not documented.
And the included query is stuck in that state for ten minutes now, which I think is a bug.
However, I've not yet been able to reproduce it with empty tables.

How to repeat:
create table xwi_logins (pid int, sid int);
create table xwi_players (pid int);
select * from xwi_players where pid in (select pid from xwi_logins where sid = 1139);
drop table xwi_logins;
drop table xwi_players;
[3 Jan 2006 17:25] Valeriy Kravchuk
Thank you for a problem report. As you can't reproduce it with empty tables, can you, please, inform about the number of rows in your real tables? The real results of SHOW PROCESSLIST will be useful too. You may upload them as private file.
[3 Jan 2006 17:32] Olaf van der Spek
select pid from xwi_logins where sid = 1139
757 rows in set (1.41 sec)
select distinct pid from xwi_logins where sid = 1139;
6 rows in set (0.01 sec)
select * from xwi_players where pid in (18146,34475,18154,53204,35060,18164);
6 rows in set (0.19 sec)
show processlist;
142 | root | localhost:3645 | xcc  | Query   |   59 | preparing | select * from xwi_players where pid in (select pid from xwi_logins where sid = 1139);

xwi_logins: 5,799,457 rows
xwi_players: 189,252 rows
[11 Jan 2006 17:45] Valeriy Kravchuk
In fact, "preparing" state is already mentioned in http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html, so, I believe, it will be documented soon anyway.

The only place in code where "preparing" state is set is (in 5.0.19-BK) in sql/sql_select.cc, around line 684:

  select= make_select(*table, const_table_map,
                      const_table_map, conds, 1, &error);
  if (error)
  {                                             /* purecov: inspected */
    error= -1;                                  /* purecov: inspected */
    DBUG_PRINT("error",("Error: make_select() failed"));
    DBUG_RETURN(1);
    DBUG_RETURN(1);
  thd->proc_info= "preparing";
  if (result->initialize_tables(this))
  {
    DBUG_PRINT("error",("Error: initialize_tables() failed"));
    DBUG_RETURN(1);                             // error == -1
  }
...

It can surely be a bug, but with your large tables and no indexes it may be OK to stay in this state for a long also. 

Please, send the results of EXPLAIN for your select * from xwi_players where pid in (select pid from xwi_logins where sid = 1139);
[11 Jan 2006 18:04] Olaf van der Spek
Where is thd->proc_info set again to something else? And could you explain what it does in this state?

> It can surely be a bug, but with your large tables and no indexes it may be OK
to stay in this state for a long also. 

I do have indexes, see the execution times of the simple queries.

mysql> explain select * from xwi_players where pid in (select pid from xwi_logins where sid =
    -> 1139);
+----+--------------------+-------------+----------------+---------------+------+---------+------+--------+--------------------------+
| id | select_type        | table       | type           | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+--------------------+-------------+----------------+---------------+------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | xwi_players | ALL            | NULL          | NULL | NULL    | NULL | 200902 | Using where              |
|  2 | DEPENDENT SUBQUERY | xwi_logins  | index_subquery | sid,pid       | pid  | 4       | func |     35 | Using index; Using where |
+----+--------------------+-------------+----------------+---------------+------+---------+------+--------+--------------------------+
2 rows in set (0.00 sec)

mysql> 

The explain also shows the DEPENDENT SUBQUERY bug (it's not dependent).
[12 Jan 2006 7:46] Valeriy Kravchuk
Please, send the EXACT SHOW CREATE TABLE results for the tables involved.

As for incorrect subquery type, yes, it is a known bug #12106, already verified but still not completely fixed.
[12 Jan 2006 8:48] Olaf van der Spek
CREATE TABLE `xwi_players` (
  `pid` int(11) NOT NULL auto_increment,
  `pass` varchar(32) NOT NULL default '',
  `name` varchar(9) NOT NULL default '',
  `cid` int(11) NOT NULL default '0',
  `sid` int(11) NOT NULL default '0',
  `flags` int(11) NOT NULL default '0',
  `locale` int(11) NOT NULL default '0',
  `buddy_list` text NOT NULL,
  `ignore_list` text NOT NULL,
  `mtime` int(11) NOT NULL,
  `ctime` int(11) NOT NULL,
  PRIMARY KEY  (`pid`),
  UNIQUE KEY `name` (`name`),
  KEY `cid` (`cid`),
  KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `xwi_serials` (
  `sid` int(11) NOT NULL auto_increment,
  `serial` varchar(32) NOT NULL default '',
  `valid` tinyint(4) NOT NULL default '0',
  `gsku` int(11) NOT NULL default '0',
  `ipa` int(11) NOT NULL default '0',
  `motd` text,
  `wtime` int(11) NOT NULL,
  `mtime` int(11) NOT NULL,
  `ctime` int(11) NOT NULL,
  PRIMARY KEY  (`sid`),
  UNIQUE KEY `serial` (`serial`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[12 Jan 2006 13:36] Mää Haissi
Hi,

ODBC driver don't work. 

Use SQLserver instead.
That was my solution. I would prefer MySQL (better
admintools), but it seems it does not work.

I got following marketing-error-message:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [, SQL state 01000 in SQLConnect in C:\Program Files\Apache Group\Apache2\htdocs\TestiSivuKannastaHaku.jv on line 20

Started to use MSDE and just changed the connection string =>
the same code worked immediately. 

Surprising....usually it's another way around....

BR,

Jarkko

By the way...

If You fix this let me know.... :=)
[12 Jan 2006 13:38] Mää Haissi
Another thing still....we here at Metso Automation use MSDE all the time.

But when testing MySQL, all the tools seemed to be much better.

It was a pity that this ODBC thing messed up the whole thing.

BR,

Jarkko
[13 Jan 2006 7:30] Olaf van der Spek
It appears it's not stuck, it just took a very long time: 6 rows in set (2 min 14.55 sec)
During this time there was constant disk IO. This is with the system idle.
When the disk is being used heavily by other apps it can take much longer.

So in the end this may be the same issue as the sub query optimization issue.
[18 Jan 2006 17:53] Valeriy Kravchuk
Can you, please, try to use the following query, with JOIN:

select a.* from xwi_players a, xwi_logins b
where a.pid = b.pid 
and b.sid = 1139;

instead of that original one:

select * from xwi_players where pid in (select pid from xwi_logins where sid =
1139);

Please, check if they return the same results. That new one should work fast.
[19 Jan 2006 9:09] Olaf van der Spek
select a.* from xwi_players a, xwi_logins b where a.pid = b.pid and b.sid = 1139;
757 rows in set (0.26 sec)
select distinct a.* from xwi_players a, xwi_logins b where a.pid = b.pid and b.sid = 1139;
6 rows in set (0.15 sec)
[19 Jan 2006 16:42] Valeriy Kravchuk
So, the only problem is bad optimization of subqueries... It was reported many times, and it will be fixed later.
[8 Feb 2006 16:00] Olaf van der Spek
Could you add a note to the documentation that describes which usages of sub queries will encounter performance issues?
[20 Aug 2009 0:47] Paul DuBois
http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html already describes the "preparing" state: "This state occurs during query optimization."
[23 Aug 2009 18:27] Olaf van der Spek
> http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html
already describes the "preparing" state

It's not about that state. It's about this:

> Could you add a note to the documentation that describes which usages of
sub queries will encounter performance issues?
[23 Aug 2009 18:28] Olaf van der Spek
Oops
[23 Aug 2009 18:34] Paul DuBois
See:

http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html