MySQL Bugs: #77519: Reported location of Innodb Merge Temp File is wrong

Bug #77519	Reported location of Innodb Merge Temp File is wrong
Submitted:	28 Jun 2015 16:01	Modified:	28 Aug 2018 17:26
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.24, 5.6.27, 5.7.8, 5.6.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The "Innodb Merge Temp File" as reported in a wait which is part of an InnoDB Online Alter Table is reported as "/home/dveeden/sandboxes/msb_5_6_24/data/Innodb Merge Temp File" while an "lsof +L1" during an online alter table shows that two temp files are created in @@tmpdir instead of in the @@datadir

From https://dev.mysql.com/doc/refman/5.6/en/temporary-files.html
"In most cases, ALTER TABLE creates a temporary copy of the original table in the same directory as the original table. However, if ALTER TABLE uses the in-place technique (online DDL), InnoDB creates temporary files in the temporary file directory. If this directory is not large enough to hold such files, you may need to set the tmpdir system variable to a different directory."

How to repeat:
mysql [localhost] {msandbox} (performance_schema) > select * from events_waits_history_long where event_name='wait/io/file/innodb/innodb_temp_file' limit 1\G
*************************** 1. row ***************************
            THREAD_ID: 20
             EVENT_ID: 11175
         END_EVENT_ID: 11175
           EVENT_NAME: wait/io/file/innodb/innodb_temp_file
               SOURCE: row0merge.cc:3061
          TIMER_START: 353832932176384
            TIMER_END: 353833102594624
           TIMER_WAIT: 170418240
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: /home/dveeden/sandboxes/msb_5_6_24/data/Innodb Merge Temp File
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 139689729982400
     NESTING_EVENT_ID: 11120
   NESTING_EVENT_TYPE: STAGE
            OPERATION: open
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
1 row in set (0.00 sec)

Suggested fix:
1. Ensure the OBJECT_NAME is in the @@tmpdir instead of the @@datadir;
2. If possible report the actual name of the file instead of 'Innodb Merge Temp File'

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.27                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.27-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

..
. for schema etc refer https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-examples.html#online-ddl-ex-se...

mysql> create index i_dtyp_big on big_table (data_type) algorithm=inplace;
Query OK, 0 rows affected (6.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from performance_schema.events_waits_history_long where event_name='wait/io/file/innodb/innodb_temp_file' limit 1\G
*************************** 1. row ***************************
            THREAD_ID: 20
             EVENT_ID: 14208957
         END_EVENT_ID: 14208957
           EVENT_NAME: wait/io/file/innodb/innodb_temp_file
               SOURCE: row0merge.cc:3112
          TIMER_START: 404909171159300
            TIMER_END: 404909266345356
           TIMER_WAIT: 95186056
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: /export/umesh/server/binaries/mysql-5.6.27/bug/Innodb Merge Temp File
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 140347761666240
     NESTING_EVENT_ID: 12459153
   NESTING_EVENT_TYPE: STAGE
            OPERATION: close
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
1 row in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

// 5.7.8

mysql> select * from performance_schema.events_waits_history_long where event_name='wait/io/file/innodb/innodb_temp_file' limit 1\G
*************************** 1. row ***************************
            THREAD_ID: 24
             EVENT_ID: 3313672
         END_EVENT_ID: 3313672
           EVENT_NAME: wait/io/file/innodb/innodb_temp_file
               SOURCE: row0merge.cc:1049
          TIMER_START: 840005960260428
            TIMER_END: 840006230494396
           TIMER_WAIT: 270233968
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: /export/umesh/server/binaries/mysql-5.7.8/bug/Innodb Merge Temp File
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 139872424624704
     NESTING_EVENT_ID: 3210146
   NESTING_EVENT_TYPE: STAGE
            OPERATION: read
      NUMBER_OF_BYTES: 1048576
                FLAGS: NULL
1 row in set (0.00 sec)

Posted by developer:
 
Fixed as of the upcoming 5.6.42, 5.7.24, 8.0.13 release, and here's the changelog entry:

The location of the Innodb Merge Temp File that reported by the
wait/io/file/innodb/innodb_temp_file Performance Schema instrument was
incorrect.