Description:
Fine MySQL manual, https://dev.mysql.com/doc/refman/8.0/en/temporary-files.html, says:
"For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*."
I wonder for what kind of queries this happen, example would help a lot. I also wonder if prefix mentioned is correct. I always assumed it's more like "#sql" and quick search in current MySQL 8 code shows this:
[openxs@fc23 mysql-server]$ grep -rni 'FILE_PREFIX' * | grep -v test
sql/sql_alter.cc:168: snprintf(tmp_name, sizeof(tmp_name), "%s-%lx_%x", tmp_file_prefix,
sql/sql_show.cc:482: is_prefix(file->name, tmp_file_prefix))
sql/table.h:133:#define tmp_file_prefix "#sql" /**< Prefix for tmp tables */
sql/table.h:134:#define tmp_file_prefix_length 4
sql/table.h:3772: return strncmp(name, tmp_file_prefix, tmp_file_prefix_length);
sql/sql_db.cc:850: is_prefix(table->name().c_str(), tmp_file_prefix);
sql/handler.cc:2442: bool temp_table = (strstr(path, tmp_file_prefix) != NULL);
sql/handler.cc:4966: (strstr(path, tmp_file_prefix) != NULL);
sql/handler.cc:5301: which match tmp_file_prefix and thus can belong to
sql/handler.cc:5458: if (is_prefix(name, tmp_file_prefix)) /* skip temporary tables */
sql/ndb_name_util.cc:30:#include "sql/table.h" // tmp_file_prefix
sql/ndb_name_util.cc:84: return is_prefix(name, tmp_file_prefix) == 1;
sql/table.cc:4041: sprintf(path, "%s%lx_%x_%x", tmp_file_prefix, current_pid, thd->thread_id(),
sql/sql_partition_admin.cc:367: snprintf(temp_name, sizeof(temp_name), "%sx-%lx_%x", tmp_file_prefix,
sql/sql_base.cc:9480: if (strlen(file->name) > tmp_file_prefix_length &&
sql/sql_base.cc:9481: !memcmp(file->name, tmp_file_prefix, tmp_file_prefix_length)) {
sql/handler.h:1446: which match tmp_file_prefix and thus can belong to
sql/dd/impl/cache/dictionary_client.cc:174: if (!table || is_prefix(table->name().c_str(), tmp_file_prefix))
sql/dd/upgrade/table.cc:1767: if (is_prefix(file.c_str(), tmp_file_prefix)) continue;
sql/dd/dd_table.cc:1828: strstr(table_name.c_str(), tmp_file_prefix))) {
sql/sql_table.cc:605: if (strlen(from) >= tmp_file_prefix_length &&
sql/sql_table.cc:606: !memcmp(from, tmp_file_prefix, tmp_file_prefix_length)) {
sql/sql_table.cc:753: snprintf(p, bufflen - (p - buff), "/%s%lx_%x_%x", tmp_file_prefix,
sql/sql_table.cc:9292: bool temp_table = (bool)is_prefix(old_name, tmp_file_prefix);
sql/sql_table.cc:15306: snprintf(backup_name, sizeof(backup_name), "%s2-%lx-%x", tmp_file_prefix,
storage/innobase/dict/dict0mem.cc:718: size = dblen + (sizeof(TEMP_FILE_PREFIX) + 3 + 20 + 1 + 10);
storage/innobase/dict/dict0mem.cc:722: TEMP_FILE_PREFIX_INNODB UINT64PF "-" UINT32PF, id,
storage/innobase/row/row0mysql.cc:3962: strstr(table->name.m_name, TEMP_FILE_PREFIX_INNODB) != NULL);
storage/innobase/row/row0mysql.cc:4100: return (strstr(name, "/" TEMP_FILE_PREFIX) != NULL);
storage/innobase/clone/clone0copy.cc:396: if (strncmp(name_ptr, TEMP_FILE_PREFIX, TEMP_FILE_PREFIX_LENGTH) == 0) {
storage/innobase/include/dict0types.h:151:#define TEMP_FILE_PREFIX "#sql"
storage/innobase/include/dict0types.h:152:#define TEMP_FILE_PREFIX_LENGTH 4
storage/innobase/include/dict0types.h:153:#define TEMP_FILE_PREFIX_INNODB "#sql-ib"
[openxs@fc23 mysql-server]$
So, I see "#sql" prefix everywhere, but not 'SQL_". Where the idea of this prefix came from?
How to repeat:
Try to build a test case showing some inernal implicit temporary table (or file) created by MySQL in tmpdir with the name starting with 'SQL_", as manual suggests.
Suggested fix:
Add more details on when temporary tables are created for SELECT. Make sure correct prefix is documented based on the source code.
