MySQL Bugs: #21387: Undocumented feature: Prefix #mysql50# suppresses tablename encoding

Bug #21387	Undocumented feature: Prefix #mysql50# suppresses tablename encoding
Submitted:	1 Aug 2006 14:04	Modified:	27 Sep 2006 17:55
Reporter:	Ingo Strüwing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.12	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Since 5.1.6 we have a mapping of identifiers to filenames (http://dev.mysql.com/doc/refman/5.1/en/identifier-mapping.html).

The documentation does not mention that one can suppress the mapping by prefixing the identifier with #mysql50#. This can be used to DROP or open tables created before 5.1.6 if they contain characters that prevent a normal DROP or open.

I can add here Sergeis opinion that this prefix should be removed. Opening an old table works even without it because MySQL tries the encoded name first and then, if it fails, the unencoded name.

However, there is Bug#21373 (Old tables with '@' in their name cannot be opened) which needs to be fixed first. Also, as my tests to Bug#21373 suggest, a DROP of an old table works with the prefix only. The same may perhaps be true for RENAME?

So someone please decide if #mysql50# shall be documented or removed after the above problems are fixed.

How to repeat:
Search for "#mysql50#" or "mysql50" in the manual.
See the tests for Bug#21373.

Removing the prefix would break the foreign keys of InnoDB tables and could make InnoDB tables with the old encoding inaccessible. Please document the prefix instead, or at most rename it (also in InnoDB).

Searching for the encoded name and falling back to the old name can create many problems. I guess that the purpose of the encoding is to avoid problems with systems that treat file names as something else than binary. The fallback mechanism sort of defeats this purpose. However, when I tested, in its current form it seems to work correctly with InnoDB.

ingo> I can add here Sergeis opinion that this prefix should be removed. Opening an
ingo> old table works even without it because MySQL tries the encoded name first and
ingo> then, if it fails, the unencoded name.

Prefix is needed for 'RENAME TABLE' and 'RENAME DATABASE', which
is used in mysql_upgrade script.

ingo> However, there is Bug#21373 (Old tables with '@' in their name cannot be opened)
ingo> which needs to be fixed first.

This is intentional behaviour. See my comments for bug#21373

ingo> So someone please decide if #mysql50# shall be documented or removed after
ingo> the above problems are fixed.

I vote to document this feature. Also, maybe it's worthy to
add a warnign when creating a table with this prefix.
Something like "Table or database name having prefix '#mysql50#' is
for internal purposes only and should normally be uses".

Or maybe add some internal secret variable, which allows to use this prefix
(off by default).

This must be documented. The reason is - it's not a hidden feature, this prefix is added automatically to all old incompatible names in the output of SHOW DATABASES and SHOW TABLES. So it's very user visible. And even if the decision will be to remove it, the prefix still must be documented for versions where it existed.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.