MySQL Bugs: #66253: [Warning] Invalid (old?) table or database name '#sqlXXXX_XXX

Bug #66253	[Warning] Invalid (old?) table or database name '#sqlXXXX_XXX_XXXX'
Submitted:	8 Aug 2012 3:45	Modified:	9 Sep 2012 16:52
Reporter:	henry king	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.20	OS:	Linux (Centos 6.2)
Assigned to:		CPU Architecture:	Any

Description:
The mysql error log always reports a warning like:
120410  2:15:21 [Warning] Invalid (old?) table or database name '#sql5790_2e86_186e'
120410  2:15:21 [Warning] Invalid (old?) table or database name '#sql5790_2e86_186e'
120410  2:15:21 [Warning] Invalid (old?) table or database name '#sql5790_2e86_186f' 

Through analysis of the binlog,there are three statements in this period. the statements like these:
1.   " DROP TABLE IF EXISTS [TEMP_TABLE_NAME];"
2. “create temporary table [TEMP_TABLE_NAME] select col1,col2,... from [TABLE_NAME];" 
3. “alter table [TEMP_TABLE_NAME] add unique idx_col1(col1);"

I debug the mysql source attempt to trace the problem where happened,
but has not been able to repeat it. However, I get some ideas on the
problem through trace the mysql source (based on 5.5.20 source). The
detail as follows:
The warning information appears only in the function of
explain_filename (THD* thd, const char *from, char *to , uint
to_length , enum_explain_filename_mode explain_mode ) in line 279 of
sql/sql_table.cc.
I continue to trace the source and find that only the function
innobase_convert_identifier() in line 1946 of ha_innodb.cc call
explain_filename, the function implement as follows:
 
/*****************************************************************//**
Convert an SQL identifier to the MySQL system_charset_info (UTF-8)
and quote it if needed.
@return       pointer to the end of buf */
static char* innobase_convert_identifier (
/*========================*/
           char*           buf,     /*!< out: buffer for converted
identifier */
           ulint             buflen,          /*!< in: length of buf,
in bytes */
           const char *  id,       /*!< in: identifier to convert */
           ulint             idlen,   /*!< in: length of id, in bytes
*/
           void*           thd,     /*!< in: MySQL connection thread,
or NULL */
           ibool            file_id) /*!< in: TRUE=id is a table or
database name;
                                      FALSE=id is an UTF-8 string */
 
 
Next, I trace the source where call the function of
innobase_convert_identifier(), I found that there are two clues.
First, in line 2034 of ha_innodb.cc, the function
innobase_convert_name() call the innobase_convert_identifier() to
convert a table or index name to the MySQL system_charset_info(UTF-8)
and quote it if needed. So I examine the database of production
environment, and find that the charset of all databases and tables are
utf8. So I think the problem can not be caused by charset. The
function implement as follows:
/*****************************************************************//**
Convert a table or index name to the MySQL system_charset_info (UTF-8)
and quote it if needed.
@return       pointer to the end of buf */
extern "C" UNIV_INTERN char* innobase_convert_name (
/*==================*/
           char*           buf,     /*!< out: buffer for converted
identifier */
           ulint             buflen,          /*!< in: length of buf,
in bytes */
           const char *  id,       /*!< in: identifier to convert */
           ulint             idlen,   /*!< in: length of id, in bytes
*/
           void*           thd,     /*!< in: MySQL connection thread,
or NULL */
           ibool            table_id) /*!< in: TRUE=id is a table or
database name;
                                      FALSE=id is an index name */
 
 
Second, in line 6269 of ha_innodb.cc, the function create_table_def()
call the innobase_convert_identifier() only when the error state is
DB_DUPLICATE_KEY, and after calling the function of
row_create_table_for_mysql()(row0mysql.c:1820). But I throughout have
not found the detail where change the error state. The function
implement as follows:
/*****************************************************************//**
Creates a table definition to an InnoDB database. */
static create_table_def (
/*=============*/
           trx_t*          trx,               /*!< in: InnoDB
transaction handle */
           TABLE*                form,           /*!< in: information
on table
                                                columns and indexes */
           const char *  table_name,  /*!< in: table name */
           const char *  path_of_temp_table, /*!< in: if this is a
table explicitly
                                                created by the user
with the
                                                TEMPORARY keyword,
then this
                                                parameter is the dir
path where the
                                                table should be placed
if we create
                                                an .ibd file for it
(no .ibd extension
                                                in the path, though);
otherwise this
                                                is NULL */
           ulint             flags)            /*!< in: table flags */
 
 
In summary, I am sure the problem caused by the second clue, and both
two statements call the create_table_def() function. But I still have
not found the specific reason, so I want you to give me some
information and suggestion to trace the problem.

How to repeat:
I can't repeat the problem.

Please, send your my.cnf file content. I wonder if these warnings appear after not-clean server restart or crash, so content of the error log may be also useful.

Thank you for you help, the warning just happened in few applications, but the configure file my.cnf is the same. specially, when I use mysqldump to backup the databases of these applications, the warning will be reported frequently.
    So I think the problem caused by the function row_create_table_for_mysql()(row0mysql.c:1820). I think maybe the lock tables triggered the warning, because when I backup the databases, the tables will be locked.However, I lock tables to execute the three statements, still can't repeat the problem.
    Otherwise, the server never restart or crash in these period.

The mysql server from 2012/08/05 to now, and all of the [DATABASE] and [TABLE] are the same.

Attachment: mysql-error.log (application/octet-stream, text), 20.47 KiB.

i once saw this message alot when i ran 'show engine innodb status' frequently. or if there were many deadlocks or foreign key errors happening at the time...

Thank you for the report.

This looks like duplicate of bug #62100 fixed in version 5.5.22. Please upgrade to current version 5.5.27 and inform us if problem still exists in your environment.

Thank you for your help. But the problem is different from bug #62100. The warning information in the error log and the cause of the problem are different .Please double check the problem, thank you very much.

Thank you for the feedback.

But, please, still try with version 5.5.27: we don't backport bug fixes anyway.

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".