MySQL Bugs: #37554: Use of character set and collate as 'filename' shows unusual behaviour.

Bug #37554	Use of character set and collate as 'filename' shows unusual behaviour.
Submitted:	20 Jun 2008 18:34	Modified:	6 Feb 2009 20:13
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
1) I create database with character set 'filename' and collate 'filename'
2) Then I perform show create database. I see unusual, behavior by using this character set.
Also the SHOW VARIABLES LIKE 'ch%' command shows empty set.
Junk is detected if we use show variables command.

How to repeat:
mysql> create database x character set filename collate filename;
Query OK, 1 row affected (0.02 sec)

mysql> set names filename;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'ch%';
Empty set (0.00 sec)

mysql> show create database x;
+----------+----------------------------------------------------------------------------------------------------------------------------+
| Database | Create@0020Database                                                                                                        |
+----------+----------------------------------------------------------------------------------------------------------------------------+
| x        | CREATE@0020DATABASE@0020@0060x@0060@0020@002f@002a@002140100@0020DEFAULT@0020CHARACTER@0020SET@0020filename@0020@002a@002f |
+----------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables;

| version                             | 6@002e0@002e6@002dalpha@002ddebug@002dlo                                             g                                                                        |
| version_comment                     | Source@0020distribution                                                                                                                                       |
| version_compile_machine             | x86_64                                                                                                                                                        |
| version_compile_os                  | unknown@002dlinux@002dgnu                                                                                                                                     |

Verified the strange effects "SET NAMES filename" has on SHOW VARIABLES

I'm not sure whether this isn't expected behavior after all though:

Non-alphanumerical characters being escaped as @#### (with #### being the escaped characters numeric unicode id in hex, e.g. @0020) is probably expected behavior for this charset encoding.

And so replacing % with @0025 in SHOW VARIABLES works, too (see end of this comment).

What i wonder about though is that "filename" does not show as valid character set in "SHOW CHARSET" or "SELECT ... FORM I_S.CHARACTER_SETS" ... and i could not find any information about it in the manual either ...

So this is probably actually a documentation bug?

> show variables like 'c@0025';
+--------------------------+-------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                     |
+--------------------------+-------------------------------------------------------------------------------------------+
| character_set_client     | filename                                                                                  | 
| character_set_connection | filename                                                                                  | 
| character_set_database   | utf8                                                                                      | 
| character_set_filesystem | binary                                                                                    | 
| character_set_results    | filename                                                                                  | 
| character_set_server     | latin1                                                                                    | 
| character_set_system     | utf8                                                                                      | 
| character_sets_dir       | @002fusr@002flocal@002fmysql@002d6@002e0@002dbackup@002fshare@002fmysql@002fcharsets@002f | 
| collation_connection     | filename                                                                                  | 
| collation_database       | utf8_general_ci                                                                           | 
| collation_server         | latin1_swedish_ci                                                                         | 
| completion_type          | 0                                                                                         | 
| concurrent_insert        | 1                                                                                         | 
| connect_timeout          | 10                                                                                        | 
+--------------------------+-------------------------------------------------------------------------------------------+

Committed a patch fixing COLLATION_CHARACTER_SET_APPLICABILITY problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48952

2672 Alexander Barkov	2008-07-03
      Bug#37554 Use of character set and collate as 'filename' shows unusual behaviour.
      Problem: internal collation "filename" was shown in
      I_S.COLLATION_CHARACTER_SET_APPLICABILITY table.
      Fix: don't display internal collations.
      
      mysql-test/r/information_schema.result
      mysql-test/t/information_schema.test
        Adding tests
      
      sql/sql_show.cc
         Don't display special purpose collations (e.g. "filename")
         in COLLATION_CHARACTER_SET_APPLICABILITY

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48999

2673 Alexander Barkov	2008-07-04
      Bug#37554 Use of character set and collate as 'filename' shows unusual behaviour.
      Problem: internal collation "filename" was shown in
      I_S.COLLATION_CHARACTER_SET_APPLICABILITY table.
      Fix: don't display internal collations.
            
      mysql-test/r/information_schema.result
      mysql-test/t/information_schema.test
        Adding tests
            
      sql/sql_show.cc
         Don't display special purpose collations (e.g. "filename")
         in COLLATION_CHARACTER_SET_APPLICABILITY

Pushed into 6.0.6-rpl.

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.10 changelog.

The internal-use-only filename character set was visible in the
output of some SHOW statements and in the contents of the
CHARACTER_SET_COLLATION_APPLICABILITY table of INFORMATION_SCHEMA.