MySQL Bugs: #28935: MySQL Server crashes on SELECT COUNT(DISTINCT) query in Windows Vista

Bug #28935	MySQL Server crashes on SELECT COUNT(DISTINCT) query in Windows Vista
Submitted:	6 Jun 2007 21:03	Modified:	12 Jun 2007 19:28
Reporter:	Dmytro Shteflyuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.41	OS:	Windows (Vista Ultimate)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	vista

Description:
MySQL crashes when I'm trying to execute SELECT COUNT(DISTINCT id) query in Windows Vista.

Additional information from Event Viewer:

Log Name:      Application
Source:        Application Error
Date:          06.06.2007 23:53:34
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      kikimora
Description:
Faulting application mysqld-nt.exe, version 0.0.0.0, time stamp 0x463ae808, faulting module mysqld-nt.exe, version 0.0.0.0, time stamp 0x463ae808, exception code 0xc0000005, fault offset 0x00090783, process id 0xd80, application start time 0x01c7a87c9dba5da2.

How to repeat:
CREATE TABLE test(id INTEGER AUTO_INCREMENT PRIMARY KEY);
SELECT COUNT(DISTINCT id) FROM test;

Thank you for the bug report. I was not able to repeat the crash on Windows
Vista Ultimate with both 5.0.41 server versions Win32 and Win64, could please
provide more information of your environment (my.ini file) and if your
installation is a fresh install or an upgrade over an older version.
Thanks in advance.

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE test(id INTEGER AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.44 sec)

mysql> SELECT COUNT(DISTINCT id) FROM test;
+--------------------+
| COUNT(DISTINCT id) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.06 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.41-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| protocol_version        | 10                            |
| version                 | 5.0.41-community-nt           |
| version_comment         | MySQL Community Edition (GPL) |
| version_compile_machine | unknown                       |
| version_compile_os      | Win64                         |
+-------------------------+-------------------------------+
5 rows in set (0.00 sec)

mysql>

Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.

c:\>cd\released\mysql-5.0.41-win32\bin

c:\released\mysql-5.0.41-win32\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%version%";
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| protocol_version        | 10                            |
| version                 | 5.0.41-community-nt           |
| version_comment         | MySQL Community Edition (GPL) |
| version_compile_machine | ia32                          |
| version_compile_os      | Win32                         |
+-------------------------+-------------------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE test(id INTEGER AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT COUNT(DISTINCT id) FROM test;
+--------------------+
| COUNT(DISTINCT id) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.02 sec)

mysql>

my.ini

Attachment: my.ini (application/octet-stream, text), 8.99 KiB.

Just tested this query with MyISAM and it's working fine:

mysql> ALTER TABLE test ENGINE = MyISAM;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT id) FROM test;
+--------------------+
| COUNT(DISTINCT id) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

And now InnoDB:

mysql> ALTER TABLE test ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT id) FROM test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Is this problem only in Vista?

--Heikki

Does mysqld print something to the .err file? Please post the entire .err file.
--Heikki

mysql.err

Attachment: kikimora.err (application/octet-stream, text), 7.42 KiB.

mysql.log

Attachment: kikimora.log (application/octet-stream, text), 1.07 KiB.

It does working on Windows XP without problems.

Thank you for the feedback. I am not able to repeat on Windows Vista
Ultimate, however notice in your err file the below:

070607  8:38:26070607  8:38:26 [ERROR] Cannot find table test/test from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
how you can resolve the problem.

070607  8:38:32  InnoDB: Error: table `test/test` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
070607  8:38:55 [Note] E:\work\_tools\mysql\bin\mysqld-nt: Normal shutdown

070607  8:38:57 [Warning] E:\work\_tools\mysql\bin\mysqld-nt: Forcing close of thread 1  user: 'root'

070607  8:38:57  InnoDB: Starting shutdown...
070607  8:39:00  InnoDB: Shutdown completed; log sequence number 0 43655
070607  8:39:00 [Note] E:\work\_tools\mysql\bin\mysqld-nt: Shutdown complete

InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
070607  8:39:17  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
070607  8:39:17  InnoDB: Log file .\ib_logfile0 did not exist: new to be created

with above InnoDB looked the test.frm file but not in its data dictionary
and you have created again the InnoDB files. Can you please explain what
steps you did for the above?. Thanks in advance.

Lines that you found are my error. I have removed InnoDB files (ib_logfile0, ib_logfile1, ibdata1) by hands, and forgot to remove folder with database.

Anyway, error still exists after fixing my mistake...

Currently I have to switch my database to MyISAM, looks like this is only way I can get things working.

Thank you for the feedback. Could you please provide the output
of the below commands:

Within the mysql client:

mysql> show variables like "tmpdir";
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tmpdir        | C:\Windows\TEMP\ |
+---------------+------------------+
1 row in set (0.15 sec)

From a command prompt:

c:\released\mysql-5.0.41-win32>set tmp
TMP=C:\Users\miguel\AppData\Local\Temp

c:\released\mysql-5.0.41-win32>set temp
TEMP=C:\Users\miguel\AppData\Local\Temp

Thanks in advance.

Thanks for your help, Miguel. Here is commands output on my machine:

mysql> show variables like "tmpdir";
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tmpdir        | C:\Windows\TEMP\ |
+---------------+------------------+
1 row in set (0.04 sec)

C:\Users\kpumuk>set tmp
TMP=C:\Users\kpumuk\AppData\Local\Temp

C:\Users\kpumuk>set temp
TEMP=C:\Users\kpumuk\AppData\Local\Temp

BTW, I have tested Mysql 4.1.22 and it works perfect.

Thank you for the feedback. I guess you have on your environment something
I can't reproduce on my environment, do you have the mysqld-debug.exe server? otherwise could you please download the mysql-noinstall-5.0.41-win32 package and then test with that server using the debug start option and provide its contents if your are able to repeat the issue?. Thanks in advance.

Debug version works fine. I have re-installed MySQL to another folder and it seems working.

Strange things happened. Sorry for the false alarm.

Debug Trace for MyISAM

Attachment: select-myisam.txt (text/plain), 31.87 KiB.

Debug trace for InnoDB

Attachment: select-innodb.txt (text/plain), 40.72 KiB.

Thank you for the feedback. The only doubt I have is if
the installation which presented that crash is a Windows
Vista security issue i.e: creating temporary files on
determined directory, I attached the debug trace files
for MyISAM and InnoDB and you can notice that MyISAM
doesn't create temporary files and InnoDB does when
performing the reported query. If you are able to
remember some installation detail about and it can
be reproduced please let me know. Thanks in advance.