MySQL Bugs: #7051: mysql-max.exe Crash

Bug #7051	mysql-max.exe Crash
Submitted:	6 Dec 2004 20:03	Modified:	7 Dec 2004 14:05
Reporter:	Mathieu Tremblay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.7 gamma max	OS:	Windows (windows XP)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Each time I do my query mysql-max.exe crash

SELECT SUM(CostPrice*Quantity)/(SELECT TaxesRate FROM Taxes) AS TotalCost,
Supplier.Name AS Supplier
FROM Invoice,InvoiceLine,Supplier
WHERE InvoiceLine.RefInvoice=Invoice.Number
AND InvoiceLine.RefSupplier=Supplier.Number
AND DATE_FORMAT(Invoce.Date,"%Y-%m-%d") BETWEEN '2004-12-01' AND '2004-12-06'
GROUP BY Supplier.Name

What wrong ???

How to repeat:

Create table and do the query .

Could you please provide the table schema ?
You can upload at:

ftp://support.mysql.com/pub/mysql/upload

thanks in advance.

I drop de shema "shema Bug 7051.sql"

The query is 

SELECT SUM(CostPrice*Quantity)/(SELECT RateTPS FROM Taxes) AS TotalCost,
Supplier.Name AS Supplier
FROM Invoice,InvoiceLine,Supplier
WHERE InvoiceLine.RefInvoice=Invoice.Number
AND InvoiceLine.RefSupplier=Supplier.Number
AND DATE_FORMAT(Invoice.Date,"%Y-%m-%d") BETWEEN '2004-11-19' AND '2004-11-19'
GROUP BY Supplier.Name

There is a error in the schema sent to you in the "InvoiceLine" all constraint are double but not in my schema..

Sorry

Hi!

Please post the COMPLETE .err log of the mysqld server. Do not cut anything off.

Regards,

Heikki

Here is the correct schema
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mybd`;
USE `mybd`;
CREATE TABLE "invoice" (
  "Number" int(11) unsigned NOT NULL auto_increment,
  "RefCustumer" varchar(9) default NULL,
  "Date" datetime default NULL,
  "SubTotal" decimal(15,8) NOT NULL default '0.00000000',
  "TPS" decimal(12,8) NOT NULL default '0.00000000',
  "TVQ" decimal(12,8) NOT NULL default '0.00000000',
  "Total" decimal(12,2) default '0.00',
  "BarreCode" varchar(12) NOT NULL default '',
  PRIMARY KEY  ("Number"),
  KEY "IX_CustumerInvoice" ("RefCustumer"),
  KEY "Date" ("Date"),
  CONSTRAINT "Invoice_Custumer" FOREIGN KEY ("RefCustumer") REFERENCES "custumer" ("Number") ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `invoice` (`Number`,`RefCustumer`,`Date`,`SubTotal`,`TPS`,`TVQ`,`Total`,`BarreCode`) VALUES (1,'test','2004-11-19 00:00:00','55.00000000','3.85000000','4.41375000','63.26','1');
CREATE TABLE "invoiceline" (
  "Number" int(11) unsigned NOT NULL auto_increment,
  "RefInvoice" int(11) unsigned NOT NULL default '0',
  "RefProduct" int(11) unsigned NOT NULL default '0',
  "RefSupplier" int(11) unsigned NOT NULL default '5',
  "Quantity" decimal(12,3) NOT NULL default '0.000',
  "CostPrice" decimal(12,3) default '0.000',
  PRIMARY KEY  ("Number"),
  KEY "IX_LigneInvoice_RefInvoice" ("RefInvoice"),
  KEY "IX_LigneInvoice_RefProduct" ("RefProduct"),
  KEY "IX_LigneInvoice_Supplier" ("RefSupplier"),
  CONSTRAINT "InvoiceLine_Invoice" FOREIGN KEY ("RefInvoice") REFERENCES "invoice" ("Number") ON DELETE CASCADE,
  CONSTRAINT "InvoiceLine_Product" FOREIGN KEY ("RefProduct") REFERENCES "product" ("Number") ON UPDATE CASCADE,
  CONSTRAINT "InvoiceLine_Supplier" FOREIGN KEY ("RefSupplier") REFERENCES "supplier" ("Number") ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `invoiceline` (`Number`,`RefInvoice`,`RefProduct`,`RefSupplier`,`Quantity`,`CostPrice`) VALUES (1,1,1,1,'1.000','35.000'),(2,1,2,1,'1.000','20.000');
CREATE TABLE "product" (
  "Number" int(11) unsigned NOT NULL default '0',
  "Name" varchar(30) default NULL,
  "RetailPrice" decimal(12,3) NOT NULL default '0.000',
  PRIMARY KEY  ("Number"),
  KEY "IX_ProduitNumero" ("Number")
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product` (`Number`,`Name`,`RetailPrice`) VALUES (1,'Plywood sheet','35.000'),(2,'2X4 6\'\'','20.000');
CREATE TABLE "supplier" (
  "Number" int(10) unsigned NOT NULL default '0',
  "Code" int(11) NOT NULL default '0',
  "Name" varchar(25) NOT NULL default '',
  PRIMARY KEY  ("Number")
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `supplier` (`Number`,`Code`,`Name`) VALUES (1,444,'ETC');
CREATE TABLE "taxes" (
  "RateTPS" decimal(12,3) NOT NULL default '0.000',
  "RateTVQ" decimal(12,3) NOT NULL default '0.000'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `taxes` (`RateTPS`,`RateTVQ`) VALUES ('0.070','0.075');
CREATE TABLE "custumer" (
  "Number" varchar(30) NOT NULL default '',
  PRIMARY KEY  ("Number")
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `custumer` (`Number`) VALUES ('test');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

041206 15:18:43InnoDB: Assertion failure in thread 2204 in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\row\row0sel.c line 2840
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 2168 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\os\os0sync.c line 309
InnoDB: Thread 3148 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\os\os0sync.c line 487
InnoDB: Thread 3464 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\sync\sync0arr.c line 126
041206 15:19:03 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Normal shutdown

041206 15:19:04 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 3  user: 'root'

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: ready for connections.
Version: '4.1.7-max'  socket: ''  port: 3306  Source distribution
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: ready for connections.
Version: '4.1.7-max'  socket: ''  port: 3306  Source distribution
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: ready for connections.
Version: '4.1.7-max'  socket: ''  port: 3306  Source distribution
InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 5958, not started, OS thread id 4084, thread declared inside InnoDB 500
MySQL thread id 25, query id 651 localhost 127.0.0.1 root Sending data
SELECT SUM(CostPrice*Quantity)/(SELECT RateTPS FROM Taxes) AS TotalCost,
Supplier.Name AS Supplier
FROM Invoice,InvoiceLine,Supplier
WHERE InvoiceLine.RefInvoice=Invoice.Number
AND InvoiceLine.RefSupplier=Supplier.Number
AND DATE_FORMAT(Invoice.Date,"%Y-%m-%d") BETWEEN '2004-11-19' AND '2004-11-19'

041206 18:27:15InnoDB: Assertion failure in thread 4084 in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\row\row0sel.c line 2840
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 1496 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\os\os0sync.c line 309
InnoDB: Thread 2848 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\os\os0sync.c line 487
InnoDB: Thread 2980 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\sync\sync0arr.c line 126
041206 18:27:55 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Normal shutdown

041206 18:27:56 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 25  user: 'root'

041206 18:27:56 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 10  user: 'root'

041206 18:27:56 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 7  user: 'root'

041206 18:27:56 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 6  user: 'root'

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: ready for connections.
Version: '4.1.7-max'  socket: ''  port: 3306  Source distribution
InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 6414, not started, OS thread id 2616, thread declared inside InnoDB 500
MySQL thread id 13, query id 132 localhost 127.0.0.1 root Sending data
SELECT SUM(CostPrice*Quantity)/(SELECT RateTPS FROM Taxes) AS TotalCost,
Supplier.Name AS Supplier
FROM Invoice,InvoiceLine,Supplier
WHERE InvoiceLine.RefInvoice=Invoice.Number
AND InvoiceLine.RefSupplier=Supplier.Number
AND DATE_FORMAT(Invoice.Date,"%Y-%m-%d") BETWEEN '2004-11-19' AND '2004-11-19'

041206 19:34:23InnoDB: Assertion failure in thread 2616 in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\row\row0sel.c line 2840
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 880 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\sync\sync0arr.c line 126
InnoDB: Thread 2504 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\os\os0sync.c line 487
InnoDB: Thread 924 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\read\read0read.c line 202
InnoDB: Thread 1456 stopped in file C:\cygwin\home\mysqldev\build\mysql-4.1.7-build\mysql-4.1.7\innobase\mem\mem0pool.c line 338
041206 19:39:20 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Normal shutdown

041206 19:39:21 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 14  user: 'root'

041206 19:39:21 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 13  user: 'root'

041206 19:39:21 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-max: Forcing close of thread 8  user: 'root'

If you don't use de group by everything work !!

Like this:
SELECT SUM(CostPrice*Quantity)/(SELECT RateTPS FROM Taxes) AS TotalCost
FROM Invoice,InvoiceLine,Supplier
WHERE InvoiceLine.RefInvoice=Invoice.Number
AND InvoiceLine.RefSupplier=Supplier.Number
AND DATE_FORMAT(Invoice.Date,"%Y-%m-%d") BETWEEN '2004-11-19' AND '2004-11-19'

Hi!

This is probably a duplicate of a sub-SELECT bug that Sanja has fixed in upcoming 4.1.8. I am assigning this bug report to Sanja.

Thank you,

Heikki

Thank you for the bug report. I was able to repeat with server
4.1.7.. I will test it with latest BK source and let you know
the current status.

Thank you for the bug report, testing the offended query against a
Windows server build from latest BK source the crash not happens
anymore:

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

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

mysql> SELECT SUM(CostPrice*Quantity)/(SELECT RateTPS FROM Taxes) AS TotalCost,
    -> Supplier.Name AS Supplier
    -> FROM Invoice,InvoiceLine,Supplier
    -> WHERE InvoiceLine.RefInvoice=Invoice.Number
    -> AND InvoiceLine.RefSupplier=Supplier.Number
    -> AND DATE_FORMAT(Invoice.Date,"%Y-%m-%d") BETWEEN '2004-11-19' AND '2004-11-19'
    -> GROUP BY Supplier.Name;
+-----------+----------+
| TotalCost | Supplier |
+-----------+----------+
| 785.71429 | ETC      |
+-----------+----------+
1 row in set (0.06 sec)

mysql>