Recently, I encountered a problem, the details is: The mysql error log
always reports a warning like “[Warning] Invalid (old?) table or
database name”. Through analysis of the binlog, there are two
statements in this period. The statements like these “create temporary
table [TEMP_TABLE_NAME] select col1,col2,... from [TABLE_NAME];” ,
“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.
> Recently, I encountered a problem, the details is: The mysql error log
> always reports a warning like “[Warning] Invalid (old?) table or
> database name”. Through analysis of the binlog, there are two
> statements in this period. The statements like these “create temporary
> table [TEMP_TABLE_NAME] select col1,col2,... from [TABLE_NAME];” ,
> “alter table [TEMP_TABLE_NAME] add unique idx_col1(col1);”.
Thank you for you help, but the description of bug #51180 is not very detailed. And the repeat situation seems not to meet our problem. Our mysql server has never been stoped or restarted, and the method of "copy the .frm file and try DROP TABLE " does not according to our application. In our application, we need a temporary table to store a large of data summarized from another tables. In order to promote the performance to query the result from temporary table, we add a unique index. The SQL statements like this:
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);"
Please give me more detailed information in order to confirm the problem is the bug #51180.
Thank you very much and looking forward to hearing from you.
From: Laurynas Biveinis
Date: 2012-08-07 11:55
To: percona-discussion
Subject: Re: Problem of " Invalid (old?) table or database name"
Heng -
> Recently, I encountered a problem, the details is: The mysql error log
> always reports a warning like 揫Warning] Invalid (old?) table or
> database name? Through analysis of the binlog, there are two
> statements in this period. The statements like these 揷reate temporary
> table [TEMP_TABLE_NAME] select col1,col2,... from [TABLE_NAME];?,
> 揳lter table [TEMP_TABLE_NAME] add unique idx_col1(col1);?
-- You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-discussion@googlegroups.com.
To unsubscribe from this group, send email to percona-discussion+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
> Thank you for you help, but the description of bug #51180 is not very
> detailed. And the repeat situation seems not to meet our problem.
> 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);"
> Please give me more detailed information in order to confirm the problem is
> the bug #51180.
The 51180 "How to repeat" does not list all the ways to repeat the
issue. It is enough to have an open temporary table (can be also an
internal temporary table, such as for ALTER TABLE) and then do any
operation that invokes explain_filename() at one point or another, for
example, issue SHOW ENGINE InnoDB STATUS in other thread. The issue
here is that explain_filename() is not fully compatible with the
"#sql..." names that are generated for those temp tables. Bug
http://bugs.mysql.com/bug.php?id=32430 might provide more background
on this.
Hi :
The previous emails ignore another warning followed the warning of " Invalid (old?) table or database name ". Now, provide the mysql server error log from 2012/08/05 to now attached in the email, maybe it is useful for confirming the problem.
Thank you very much and looking forward to hearing from you!
From: Laurynas Biveinis
Date: 2012-08-08 10:43
To: percona-discussion
Subject: Re: Re: Problem of " Invalid (old?) table or database name"
Heng -
> Thank you for you help, but the description of bug #51180 is not very
> detailed. And the repeat situation seems not to meet our problem.
> 1. " DROP TABLE IF EXISTS [TEMP_TABLE_NAME];"
> 2. 揷reate temporary table [TEMP_TABLE_NAME] select col1,col2,... from
> [TABLE_NAME];"
> 3. 揳lter table [TEMP_TABLE_NAME] add unique idx_col1(col1);"
> Please give me more detailed information in order to confirm the problem is
> the bug #51180.
The 51180 "How to repeat" does not list all the ways to repeat the
issue. It is enough to have an open temporary table (can be also an
internal temporary table, such as for ALTER TABLE) and then do any
operation that invokes explain_filename() at one point or another, for
example, issue SHOW ENGINE InnoDB STATUS in other thread. The issue
here is that explain_filename() is not fully compatible with the
"#sql..." names that are generated for those temp tables. Bug
http://bugs.mysql.com/bug.php?id=32430 might provide more background
on this.
-- You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-discussion@googlegroups.com.
To unsubscribe from this group, send email to percona-discussion+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
Dear :
Thanks Laurynas Biveinis , but I think the bug#51180 is not the same problem. The error log always reports the warning like these: 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);"
And the difference from bug#51180 is the error log never reports the information like these:
<result 2 when explaining filename '#sql2-4947-72390'> does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
So I think there are some other points triggering the problem.
Thank you very much and looking forward to hearing from you.
Heng Wang
Vaya Building, Suzhou Street, Haidian District, Bei Jing, China. 100080
https://github.com/HengWang/ From: Laurynas Biveinis
Date: 2012-08-08 10:43
To: percona-discussion
Subject: Re: Re: Problem of " Invalid (old?) table or database name"
Heng -
> Thank you for you help, but the description of bug #51180 is not very
> detailed. And the repeat situation seems not to meet our problem.
> 1. " DROP TABLE IF EXISTS [TEMP_TABLE_NAME];"
> 2. 揷reate temporary table [TEMP_TABLE_NAME] select col1,col2,... from
> [TABLE_NAME];"
> 3. 揳lter table [TEMP_TABLE_NAME] add unique idx_col1(col1);"
> Please give me more detailed information in order to confirm the problem is
> the bug #51180.
The 51180 "How to repeat" does not list all the ways to repeat the
issue. It is enough to have an open temporary table (can be also an
internal temporary table, such as for ALTER TABLE) and then do any
operation that invokes explain_filename() at one point or another, for
example, issue SHOW ENGINE InnoDB STATUS in other thread. The issue
here is that explain_filename() is not fully compatible with the
"#sql..." names that are generated for those temp tables. Bug
http://bugs.mysql.com/bug.php?id=32430 might provide more background
on this.
-- You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-discussion@googlegroups.com.
To unsubscribe from this group, send email to percona-discussion+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
> Hi :
> The previous emails ignore another warning followed the warning of
> " Invalid (old?) table or database name ". Now, provide the mysql server error
> log from 2012/08/05 to now attached in the email, maybe it is useful
> for confirming the problem.
> Thank you very much and looking forward to hearing from you!
If anything I'd like to say that I recognize this behavior but have never been
able to pinpoint a specific statement in the binlog as the culprit.
Happens about weekly for us, on replication slaves (both 5.1 and 5.5).
> Thanks Laurynas Biveinis , but I think the bug#51180 is not the same
> problem. The error log always reports the warning like these:
> 120410 2:15:21 [Warning] Invalid (old?) table or database name
> '#sql5790_2e86_186e'
[...]
> And the difference from bug#51180 is the error log never reports the
> information like these:
> <result 2 when explaining filename '#sql2-4947-72390'> does not exist in the
> InnoDB internal
> InnoDB: data dictionary though MySQL is trying to drop it.
Perhaps I keep missing some you point you make, but I still think that
this is #51180. The second error message in the bug report, which you
don't have in your logs, is secondary. The bug report is about the
first message, which you do experience, and which is caused by
incorrect temp table name handling in explain_filename().
The error message is annoying but entirely benign. If you want to fix
it, you have to fix explain_filename() only and nothing else.
Dear Laurynas-
Thanks Laurynas help, The previous emails ignore another warning followed by " Invalid (old?) table or database name ". Now, provide the parts of mysql server error log and attach the full error log from 2012/08/05 to now in the email, maybe it is useful for confirming the problem.
The detail:
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8ff'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
120807 2:50:32 [Warning] Invalid (old?) table or database name '#sql4925_56c424_8fe'
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
InnoDB: Warning: hash index ref_count (4) is not zero after fil_discard_tablespace().
index: "PRIMARY" table: "[DATABASE]/[TABLE]"
Thank you very much and look forward to hearing from you.
From: Laurynas Biveinis
Date: 2012-08-09 14:13
To: percona-discussion
Subject: Re: Re: Problem of " Invalid (old?) table or database name"
Dear Heng -
> Thanks Laurynas Biveinis , but I think the bug#51180 is not the same
> problem. The error log always reports the warning like these:
> 120410 2:15:21 [Warning] Invalid (old?) table or database name
> '#sql5790_2e86_186e'
[...]
> And the difference from bug#51180 is the error log never reports the
> information like these:
> <result 2 when explaining filename '#sql2-4947-72390'> does not exist in the
> InnoDB internal
> InnoDB: data dictionary though MySQL is trying to drop it.
Perhaps I keep missing some you point you make, but I still think that
this is #51180. The second error message in the bug report, which you
don't have in your logs, is secondary. The bug report is about the
first message, which you do experience, and which is caused by
incorrect temp table name handling in explain_filename().
The error message is annoying but entirely benign. If you want to fix
it, you have to fix explain_filename() only and nothing else.
-- You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-discussion@googlegroups.com.
To unsubscribe from this group, send email to percona-discussion+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
> Thanks Laurynas help, The previous emails ignore another warning followed by
> " Invalid (old?) table or database name ". Now, provide the parts of mysql
> 120807 2:50:32 [Warning] Invalid (old?) table or database name
> '#sql4925_56c424_8fe'
> InnoDB: Warning: hash index ref_count (4) is not zero after
> fil_discard_tablespace().
Yes, I ignored the second error message before. It happens on
tablespace discard (DROP TABLE with file per table enabled) and it
means that not all of the adaptive hash index entries concerning the
tablespace being removed were dropped yet. This should be benign as
well (in the worst case, if those entries are actually never dropped,
this amounts to AHI performance degradation). If you have a repeatable
isolated testcase, feel free to submit a bug report to us. Also try
changing the innodb_lazy_drop_table setting, and testing with at least
Percona Server 5.5.23.
