MySQL Bugs: #28112: delete from table using results from another query fails

Bug #28112	delete from table using results from another query fails
Submitted:	26 Apr 2007 5:57	Modified:	3 Jun 2007 12:52
Reporter:	Rotem Fogel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.16-beta	OS:	Solaris
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
while issuing delete from table using results from another table I get the error:
InnoDB: ERROR: Submit the output to http://bugs.mysql.com
InnoDB: ibuf cursor restoration fails!
InnoDB: ibuf record inserted to page 49409
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;; 3: len 13; hex 000c0f00ff00300c0f00ff8030; asc       0     0;; 4: SQL NULL, size 0 ; 5: len 15; hex 323332353737383734323837343838; asc 232577874287488;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;; 3: len 13; hex 000c0f00ff00300c0f00ff8030; asc       0     0;; 4: SQL NULL, size 0 ; 5: len 15; hex 323332353737383734323837343838; asc 232577874287488;;
DATA TUPLE: 3 fields;
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c101; asc     ;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b97; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000b480; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323336333331393139373638303431; asc 236331919768041;;
InnoDB: Validating insert buffer tree:
InnoDB: ibuf tree ok
InnoDB: ERROR: Submit the output to http://bugs.mysql.com
InnoDB: ibuf cursor restoration fails!
InnoDB: ibuf record inserted to page 49408
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
DATA TUPLE: 3 fields;
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;; 3: len 13; hex 000c0f00ff00300c0f00ff8030; asc       0     0;; 4: SQL NULL, size 0 ; 5: len 15; hex 323332353737313138393830373234; asc 232577118980724;;
InnoDB: Validating insert buffer tree:
InnoDB: ibuf tree ok
InnoDB: ERROR: Submit the output to http://bugs.mysql.com
InnoDB: ibuf cursor restoration fails!
InnoDB: ibuf record inserted to page 49408
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
DATA TUPLE: 3 fields;
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;; 3: len 13; hex 000c0f00ff00300c0f00ff8030; asc       0     0;; 4: SQL NULL, size 0 ; 5: len 15; hex 323332353737383734323837343838; asc 232577874287488;;
InnoDB: Validating insert buffer tree:
InnoDB: ibuf tree ok
InnoDB: ERROR: Submit the output to http://bugs.mysql.com
InnoDB: ibuf cursor restoration fails!
InnoDB: ibuf record inserted to page 49408
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c0f8; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323337373334343739333533303235; asc 237734479353025;;
DATA TUPLE: 3 fields;
 0: len 4; hex 00011b96; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000c100; asc     ;;
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
 0: len 4; hex 00011b97; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 0000b480; asc     ;; 3: len 13; hex 0083100001003f0c0f00ff8030; asc       ?     0;; 4: len 1; hex 01; asc  ;; 5: len 15; hex 323336333331393139373638303431; asc 236331919768041;;
InnoDB: Validating insert buffer tree:
InnoDB: ibuf tree ok

How to repeat:
delete from MyTab where id in (select id from MyTabDel);

Thank you for the report.

But there is no 5.15 MySQL server. Please indicate accurate version of the server you use.

Also please provide output of SHOW CREATE TABLE  and SHOW TABLE STATUS for tables MyTab and MyTabDel

CREATE TABLE `mytab` (
  `MYID` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `MYSEG` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `MYSUB` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`MYID`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `mytabdel` (
  `MYSUB` varchar(255) COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Maybe the same bug as http://bugs.mysql.com/bug.php?id=27276

Rotem,

Please paste the exact query that causes a crash, and the full output of SHOW CREATE TABLE <tablename> for each involved table.  You can make it a private comment if needed.

Please answer last Shane's question. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Bug #27276 could be a bogus warning.

Did the delete fail? Did CHECK TABLE fail? If no, then this is probably just a duplicate of Bug #27276.

I fail to see how the DELETE...SELECT could have prompted InnoDB to contract the insert buffer B-tree, invoking ibuf_get_merge_page_nos() on a dropped *.ibd tablespace, as discussed in Bug #27276. It should be just a coincidence, caused by the master thread or by another thread that was buffering some inserts while the statement was running.