Bug #93400 MySQL manual states "SQL_" prefix is used for some temporary table names
Submitted: 29 Nov 2018 10:26 Modified: 1 Feb 2019 14:47
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: #sql, missing manual, temporary table

[29 Nov 2018 10:26] Valeriy Kravchuk
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.
[30 Nov 2018 11:01] MySQL Verification Team
Hello Valeriy,

Thank you for the report!

[30 Jan 2019 19:12] Paul DuBois
Posted by developer:
Looks like it changed between 3.23.5 and 3.23.6.


-#define tmp_file_prefix "SQL"          /* Prefix for tmp tables */
-#define tmp_file_prefix_length 3
+#define tmp_file_prefix "#sql"         /* Prefix for tmp tables */
+#define tmp_file_prefix_length 4
[1 Feb 2019 14:47] Paul DuBois
Posted by developer:
Changed prefix from "SQL_" to "#sql".
Looks like the page already indicates when such tables are created. Amplified on it a little.