MySQL Bugs: #3066: Crash mysqld-max-nt

Bug #3066	Crash mysqld-max-nt
Submitted:	4 Mar 2004 13:42	Modified:	8 Mar 2004 4:49
Reporter:	Gerry Riley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.1a-alpha-max-debug for Win95/Win98	OS:	Windows (W2K 5.00 2195 SP 4)
Assigned to:		CPU Architecture:	Any

Description:
First identified using my own java code over Jconnector. However, now completely reproducible using mysql command line with empty database.

Note there does not appear to be a stable release that supports transactions on maxDB which I need.

Crashes mysql-max-nt with following message 'The instruction at 0x004f734f referenced memory at 0x00000006. The memory could not be read'

How to repeat:
To Reproduce create database called TBDEVELOP then build tables using following  script:

USE TBDEVELOP;

REVOKE ALL ON TBDEVELOP.* FROM 'tbdevelop'@'localhost';
GRANT DELETE, INSERT, LOCK TABLES, SELECT, UPDATE ON TBDEVELOP.* TO 'tbdevelop'@'localhost'
IDENTIFIED BY 'sibsonpc';
DROP  INDEX IF EXISTS FP_OBJID 
DROP  INDEX IF EXISTS FP_STRING
DROP  INDEX IF EXISTS FP_INT
DROP  INDEX IF EXISTS FP_DOUBLE
DROP  INDEX IF EXISTS  FP_DATE
DROP  INDEX IF EXISTS FP_DBOREF
DROP INDEX IF EXISTS TBO_CLASS;
DROP INDEX IF EXISTS LINK_PID_CID;

DROP TABLE IF EXISTS TBOBJECT;
DROP TABLE IF EXISTS FINDPARAM;
DROP TABLE IF EXISTS LINK;
DROP TABLE IF EXISTS SEQUENCE;

CREATE TABLE TBOBJECT
(
	ID INT NOT NULL,
	CLASS	VARCHAR(70) NOT NULL,
	STATE BLOB,
	VCOUNT INT NOT NULL,
	RCOUNT INT NOT NULL,
	PRIMARY KEY ( ID )
)
TYPE=InnoDB;

CREATE INDEX TBO_CLASS ON TBOBJECT(CLASS);

CREATE TABLE IF NOT EXISTS FINDPARAM
(
	OBJID INT NOT NULL REFERENCES TBOBJECT(ID),
	NAME VARCHAR(100)	NOT NULL,
	STRING VARCHAR(100),
	INTG INT,
	FLT FLOAT(11,4),
	DT DATETIME,
	DBOREF INT REFERENCES TBOBJECT(ID)
)
TYPE=InnoDB;

CREATE INDEX FP_OBJID ON FINDPARAM ( OBJID );
CREATE INDEX FP_STRING ON FINDPARAM ( NAME, STRING );
CREATE INDEX FP_INT ON FINDPARAM ( NAME, INTG );
CREATE INDEX FP_DOUBLE ON FINDPARAM ( NAME, FLT );
CREATE INDEX FP_DATE ON FINDPARAM ( NAME, DT );
CREATE INDEX FP_DBOREF ON FINDPARAM ( NAME, DBOREF );

CREATE TABLE IF NOT EXISTS LINK
(
	PARENTID INT NOT NULL REFERENCES TBOBJECT(ID),
	CHILDID	INT NOT NULL REFERENCES TBOBJECT(ID)
)
TYPE=InnoDB;

CREATE UNIQUE INDEX LINK_PID_CID ON LINK( PARENTID, CHILDID );

CREATE TABLE SEQUENCE
(
	NEXT_ID INT NOT NULL
)
TYPE=InnoDB;

# insert one row to start increment.
INSERT INTO SEQUENCE VALUES (1);

COMMIT;

use mysql -u root -pxxxx < fail_script.sql

Where fail_script.sql is:

# Connection: develop
# Host: localhost
# Saved: 2004-03-04 20:52:19
# 
USE TBDevelop;
SELECT DISTINCT ID FROM LINK,
( SELECT ID FROM TBOBJECT, 
( SELECT OBJID FROM
(SELECT OBJID, COUNT(*) AS CNT FROM FINDPARAM
 WHERE (STRING='armatrading' AND NAME='ID') OR (STRING='joan armatrading' AND NAME='TITLE') 
   GROUP BY OBJID) AS A WHERE CNT >=2) AS B
  WHERE CLASS='Skrowten.TB.Database.Production' AND ID=OBJID) AS C 
 WHERE ID=CHILDID AND PARENTID=11;

If you use the following script it does not crash. Using this on my sql control center will return an empty set.

OK script follows

# Connection: develop
# Host: localhost
# Saved: 2004-03-04 20:56:44
# 
USE TBDEVELOP;
SELECT DISTINCT ID FROM LINK,
( SELECT ID FROM TBOBJECT, 
( SELECT OBJID FROM
(SELECT OBJID, COUNT(*) AS CNT FROM FINDPARAM
 WHERE (STRING='armatrading' AND NAME='ID') OR (STRING='joan armatrading' AND NAME='TITLE') 
    GROUP BY OBJID) AS A WHERE CNT >=2) AS B
  WHERE CLASS='Skrowten.TB.Database.Production' AND ID=OBJID) AS C 
 WHERE ID=CHILDID AND PARENTID=11;

The only difference between these is some extra spaces. deleting other characters on various lines of the script sometimes makes the difference between crash and working. But the two scripts supplied certainly fail or work

Suggested fix:
None

Build tables script

Attachment: setup.sql (text/plain), 1.84 KiB.

config file

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

fail query

Attachment: fail.sql (text/plain), 526 bytes.

fail query

Attachment: OK.sql (text/plain), 454 bytes.

NOte added files that are identified in text as well as my.ini

Need 4.1 for derived tables not transactions they are available in 4.0 - my mistake

I tested your scripts against a 4.1.2 server and the result I got
was an empty set (not crash):

mysql> SELECT DISTINCT ID FROM LINK,
    -> ( SELECT ID FROM TBOBJECT,
    -> ( SELECT OBJID FROM
    -> (SELECT OBJID, COUNT(*) AS CNT FROM FINDPARAM
    -> WHERE (STRING='armatrading' AND NAME='ID')
    -> OR (STRING='joan armatrading' AND NAME='TITLE')
    -> GROUP BY OBJID) AS A WHERE CNT >=2) AS B
    -> WHERE CLASS='Skrowten.TB.Database.Production'
    -> AND ID=OBJID) AS C
    -> WHERE ID=CHILDID AND PARENTID=11\G
Empty set (0.01 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 4.1.2-alpha-max-debug |
+-----------------------+
1 row in set (0.00 sec)

A couple of points:

I was using 4.1.1a-alpha, which seems to all that is available on web site for download you are using 4.1.2.

Did you actually run the two scripts I sent? You have not mention both. One will fail and one will not returning an empty set as you got. As I metioned, I have found that the differences between the two scripts are only extra spaces in the text. I have also had it fail (or not) by altering removing some characters from the data in the = expression. I guess this is something to do with trashing memory, where sometimes the trash is non critical.

I tested your script that crashes 4.1.1a and then my concern was
to test if the today server also crashes and I didn't analyzed the
return value.

Miguel
       You have not said if you used both scripts or not and that they were read in in exact format. The problem is not that the basic query does not work but that I think during parsing there is some form of memory overwrite. Therefore spaces and length of query is critical. I use the basic same query from my code and it was not crashing at all times. You must use both scripts (fail and OK. I found that one consistently failed and one consistently worked (empty set). If necessary play around with the length of query by adding spaces or by altering query data length to make it fail.

I assume you have tested this on win2k with SP4? 

Can you make the 4.1.2 available for me to try?

For to clarify, I did:

- Tested 4.1.1a and I got the crash reported with the first script fail.sql
- Tested 4.1.2 with both scripts returning empty and not crashed.

Miguel
        I am pleased you have been able to recreate the problem. I notice this problem is suspiciously similar to bug report 2978 which is closed as fixed in 4.1.2. Can you let me have this or the latest form this tree to try. The dowload web site still shows the 4.1.1a.

Thanks

This is fixed in 4.1.2.

It will be available in couple of weeks.