MySQL Bugs: #72420: Unable to purge a record on temptable on startup.

Bug #72420	Unable to purge a record on temptable on startup.
Submitted:	22 Apr 2014 10:47	Modified:	18 Apr 2016 12:56
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datadictionary, innodb

Description:
2014-04-15 16:13:14 7f935caa4700  InnoDB: unable to purge a record
InnoDB: tuple DATA TUPLE: 6 fields;
*removed*

InnoDB: record PHYSICAL RECORD: n_fields 6; compact format; info bits 0
*removed*

space 891175 offset 7573 (85 records, index id 2163180)
InnoDB: Submit a detailed bug report to http://bugs.mysql.com
140415 16:16:03 mysqld_safe Number of processes running now: 0
140415 16:16:03 mysqld_safe mysqld restarted

I couldn't find a tablespace with id 891175 and it seems likely that this was a temptable.

This happened about an hour after starting the instance (buffer pool restore enabled).

How to repeat:
Unknown.

It looks like the mentioned space doesn't exist, but the index does.

space 891175 offset 7374 (238 records, index id 2163179)

mysql> select * from INNODB_SYS_INDEXES where index_id=2163179;
+----------+---------------+----------+------+----------+---------+--------+
| INDEX_ID | NAME          | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE  |
+----------+---------------+----------+------+----------+---------+--------+
|  2163179 | someindex |   892871 |    0 |        1 |       7 | 891175 |
+----------+---------------+----------+------+----------+---------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INNODB_SYS_DATAFILES WHERE SPACE=891175;
Empty set (0.00 sec)

mysql> SELECT * FROM INNODB_SYS_TABLES WHERE SPACE=891175;
+----------+---------------------------+------+--------+--------+-------------+------------+---------------+
| TABLE_ID | NAME                      | FLAG | N_COLS | SPACE  | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+---------------------------+------+--------+--------+-------------+------------+---------------+
|   892871 | somedb/sometable          |    1 |     12 | 891175 | Antelope    | Compact    |             0 |
+----------+---------------------------+------+--------+--------+-------------+------------+---------------+
1 row in set (0.00 sec)

The names of the database, index and table were replaced.

An optimize table (to recreate the tablespace) worked without any issue. But then the old space number is still present in the buffer pool:

mysql> select * from INNODB_SYS_INDEXES where index_id=2163179;
Empty set (0.00 sec)

mysql> select * from  INNODB_SYS_TABLES WHERE SPACE=891175;
Empty set (0.00 sec)

SET GLOBAL innodb_buffer_pool_dump_now=ON;
$ grep '891175,' ib_buffer_pool | wc -l
3938

mysql> SELECT PAGE_TYPE,COUNT(*) FROM INNODB_BUFFER_PAGE WHERE SPACE=891175 GROUP BY PAGE_TYPE;
+-------------------+----------+
| PAGE_TYPE         | COUNT(*) |
+-------------------+----------+
| FILE_SPACE_HEADER |        1 |
| IBUF_BITMAP       |        1 |
| INDEX             |     3933 |
| INODE             |        1 |
+-------------------+----------+
4 rows in set (12.40 sec)

Added tags

zooming in on nailing this.  see http://bugs.mysql.com/bug.php?id=73767

This appears to be a duplicate of BUG #73767.  If you are still experiencing issues after upgrading, please feel free to re-open this bug report.