MySQL Bugs: #17007: sum crashes MySQL

Bug #17007	sum crashes MySQL
Submitted:	1 Feb 2006 14:56	Modified:	3 Feb 2006 13:58
Reporter:	Juergen Weiss	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18-nt	OS:	Windows (windows2003 web edition)
Assigned to:		CPU Architecture:	Any

Description:
I'm running a webserver with windows 2003, IIS 6, and MySQL. Connection to MySQL is done through Active Server Pages via ODBC Connector. Erverything was fine until I upgraded from 4.0.22 to 5.0.18 a few days ago.

I now found a query which crashes MySQL. The query worked fine on 4.0.22. The only thing I changed was MySQL 4.0.22 -> 5.0.18 and ODBC-Connector 3.51.06 -> 3.51.12

Here is the query:

select projekt, sum(bis-von) as gesamtstunden, art from tp_termine inner join tp_projekte on tp_termine.projektid=tp_projekte.projektid group by projekt,art order by projekt,art

if I change the query to:

select projekt, tp_termine.projektid, sum(bis-von) as gesamtstunden, art from tp_termine inner join tp_projekte on tp_termine.projektid=tp_projekte.projektid group by projekt,tp_termine.projektid,art order by projekt,art

erverything is ok.

I reproduced the crash serveral times making the query by ASP (with ODBC) or by phpMyAdmin (php ist also installed).

I know the programming is not best, it comes from an older website where the database was MS Access, but as I already told it worked fine with 4.x

 

How to repeat:
This is the first table tp_termine (it has about 28000 entries)

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Erstellungszeit: 01. Februar 2006 um 15:46
-- Server Version: 5.0.18
-- PHP-Version: 5.1.2
-- 
-- Datenbank: `badnet`
-- 

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `tp_termine`
-- 

CREATE TABLE `tp_termine` (
  `terminid` int(11) NOT NULL auto_increment,
  `userid` int(11) default '0',
  `kundenid` int(11) default '0',
  `typ` char(1) default NULL,
  `datum` date default NULL,
  `von` tinyint(3) unsigned default '0',
  `bis` tinyint(3) unsigned default '0',
  `beschreibung` varchar(255) default NULL,
  `projektid` int(11) default '0',
  `katid` int(11) default '0',
  `art` char(2) default NULL,
  `eingabe` int(11) default '0',
  `eingabedatum` datetime default NULL,
  PRIMARY KEY  (`terminid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74930 ;

This is the second table tp_projekte (it has about 1500 entries):

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Erstellungszeit: 01. Februar 2006 um 15:52
-- Server Version: 5.0.18
-- PHP-Version: 5.1.2
-- 
-- Datenbank: `badnet`
-- 

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `tp_projekte`
-- 

CREATE TABLE `tp_projekte` (
  `projektid` int(11) NOT NULL auto_increment,
  `kundenid` int(11) default '0',
  `projekt` varchar(20) default NULL,
  `beschreibung` longtext,
  `lastupdate` datetime default NULL,
  PRIMARY KEY  (`projektid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2307 ;

Running the query:

select projekt, sum(bis-von) as gesamtstunden, art from tp_termine inner join tp_projekte on tp_termine.projektid=tp_projekte.projektid group by projekt,art order by projekt,art

and MySQL Server crashes with this entry in application event log:

Fehlgeschlagene Anwendung mysqld-nt.exe, Version 0.0.0.0, fehlgeschlagenes Modul ntdll.dll, Version 5.2.3790.1830, Fehleradresse 0x0002f583.

I was unable to repeat having the tables empty. Could you please provide
a dump file for to insert the data. Thanks in advance.

tp_termine part1

Attachment: tp_termine_1.sql.zip (application/x-zip-compressed, text), 153.86 KiB.

tp_termine part2

Attachment: tp_termine_2.sql.zip (application/x-zip-compressed, text), 167.10 KiB.

tp_termine part3

Attachment: tp_termine_3.sql.zip (application/x-zip-compressed, text), 159.53 KiB.

tp_termine part4

Attachment: tp_termine_4.sql.zip (application/x-zip-compressed, text), 183.57 KiB.

I submitted the requested dumps. Do you need more Information?
Hope you can now repeat the error.

I was unable to repeat the crash on Windows 2003/XP querying the server with
the mysql client tool and with the ODBC test tool. Are you able to repeat doing
the restore of your own dump file on a fresh MySQL server install?

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

D:\Documents and Settings\miguel>c:

C:\>cd\mysql\bin

C:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-nt

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

mysql> select projekt, sum(bis-von) as gesamtstunden, art from tp_termine inner join
    -> tp_projekte on tp_termine.projektid=tp_projekte.projektid group by projekt,art
    -> order by projekt,art;
<cut>
| Zeller - Bad         |           518 | NA   |
| Zerbe, Sch├╢neberg   |            22 | NA   |
| ZZZ DBH - F - Ketten |           248 | NA   |
| ZZZ DBH - H - Ketten |           358 | NA   |
| ZZZ DBH - S - Ketten |            80 | NA   |
| ZZZ Winker Bautr├ñge |           452 | NA   |
| ZZZ Wohnbau TUT      |           588 | NA   |
+----------------------+---------------+------+
1242 rows in set (0.77 sec)
-----------------------------------------------------------------------------------
Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'proj'.
SQLExecDirect:
In:				hstmt = 0x00911938, 
szSqlStr = "select projekt, sum(bis-von) as gesamtstunden, art fro...", cbSqlStr = -3
Return:	SQL_SUCCESS=0

"ZZZ DBH - H - Ketten", 358, "NA"
"ZZZ DBH - S - Ketten", 80, "NA"
"ZZZ Winker BautrÃ¤ge", 452, "NA"
"ZZZ Wohnbau TUT", 588, "NA"
1242 rows fetched from 3 columns.