| Bug #19874 | SHOW COLUMNS and SHOW KEYS handle identifiers containing \ incorrectly | ||
|---|---|---|---|
| Submitted: | 17 May 2006 9:36 | Modified: | 14 Sep 2006 2:52 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.9 | OS: | Windows (WinXP/Linux) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
| Tags: | identifiers, SHOW COLUMNS, SHOW KEYS | ||
[17 May 2006 9:36]
Peter Laursen
[17 May 2006 10:47]
Peter Laursen
Also the backslash character is not unproblematic: mysql> create table `a\b` (id bigint); Query OK, 0 rows affected (0.14 sec) mysql> show full fields from `test`.`a\b`; ERROR 1146 (42S02): Table 'test.ab' doesn't exist mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | a\b | (abbreviated) You can try DELETE RENAME UPTDATE ect yourself and find dozens of inconstencies in 5 minutes.
[17 May 2006 18:56]
Peter Laursen
We have been discussing in another Forums. Not everything said here is 'gold', but quite a lot of examples of strange behaviour ,, http://www.webyog.com/forums/index.php?showtopic=2153&view=getnewpost
[17 May 2006 21:46]
Peter Laursen
Also ... According to the docs any character in the range ASCII(1) to ASCII(32) is valid in identifiers. Serious about that?
[17 May 2006 21:56]
Paul DuBois
Peter, Please explain your last comment. It's too vague to be meaningful. Thanks.
[17 May 2006 22:47]
Peter Laursen
OK :-) I'll try, but I do not think it is vague myself ... I am referring to this passage: "No identifier can contain ASCII 0 (0x00) or a byte with a value of 255." I read that so that any character in ASCII range 1-32 can be used in identifiers, as it is not explicitly excluded. I just foresee a lot of problems with all sorts of clients if somebody tries to use those characters somehow. I think a character used in an identifier must be a printable character? And printable in the actual charset of the tabale AND utf8 (used with the system tables) as well. BTW: Can I use unicode music-writing and similar symbols in MySQL identifiers i table charset is uft8? The docs as of now tell what CANNOT be used. I think it should tell what CAN be used.
[18 May 2006 15:34]
MySQL Verification Team
Thank you for the bug report. Notice below that version 5.0.21 not allows
to create an invalid table name: create table `a\b` (id bigint); while
5.1 allows:
Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1 |
| tab2 |
+----------------+
2 rows in set (0.08 sec)
mysql> create table asassa (id int);
Query OK, 0 rows affected (0.08 sec)
mysql> create table ggg (id int);
Query OK, 0 rows affected (0.05 sec)
mysql> rename table asassa to t`1;
`> show tables;
`> `> Aborted
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.10-beta
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| asassa |
| ggg |
| tab1 |
| tab2 |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from ggg limit 5;
Empty set (0.03 sec)
mysql> create table `a\b` (id bigint);
Query OK, 0 rows affected (0.05 sec)
mysql>
Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.21
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table `a\b` (id bigint);
ERROR 1103 (42000): Incorrect table name 'a\b'
mysql>
[18 May 2006 15:46]
Peter Laursen
@Miguel I think you miss a point. In 5.1.6 and higher 'identifier quote character' (`), "." , "\" and "/" are valid with table and column names! That is what the docs say! I think \ is the minor problem of them. It is basically inconsticencies regarding escaping in the server code. Must an identifier having a \ always be backticked? Does escaping apply inside identifiers? Hovever "." and "´" (and maybe ' " ' itslef in ANSI mode gives serious problems - such as corrupted non-recoverable tables and (what I experienced twice) situations where it is not possible to connect to server at all, and reinstall is necessary.
[18 May 2006 16:03]
MySQL Verification Team
Thank you for the feedback. Yes Indeed however I was able to repeat: mysql> show full fields from `test`.`a\b`; ERROR 1146 (42S02): Table 'test.ab' doesn't exist and I will see if on Linux I get the same behavior.
[18 May 2006 16:06]
MySQL Verification Team
The same behavior on Linux: miguel@hegel:~/dbs/5.1> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.1.11-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table `a\b` (id bigint); Query OK, 0 rows affected (0.01 sec) mysql> show full fields from `test`.`a\b`; ERROR 1146 (42S02): Table 'test.ab' doesn't exist mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | a\b | | testing | +----------------+ 2 rows in set (0.01 sec) mysql>
[18 May 2006 16:20]
Peter Laursen
A correction: "Hovever "." and "´" (and maybe ' " ' itslef in ANSI mode gives serious problems.." should be "Hovever "." and "`" (and maybe ' " ' itslef in ANSI mode) gives serious problems.."
[18 May 2006 18:04]
Peter Laursen
I still insist that the problems with backtics and punctuation in table names are much more serious than the one with backslash. The problems with backtics and punctuation results in corrupted tables >> data loss. And that is the WORST ting. More serious than even a server crash!
[19 May 2006 6:26]
Martijn Tonies
Using ".", "/" and "\" will get you into all sorts of troubles. No need for testing. Just think about it!! Probably this is part due to the "file per table" structure MySQL uses in directories and file extensions. Another good reason would be - as said by someone else - clients that go ape on escaping slashes or other characters. The ".", "/" and "\" are not your every day characters and IMO, should be disallowed in identifiers. With regard to the "csv" engine, why not make the csv file extension implicit? -- Martijn Tonies Upscene Productions
[19 May 2006 11:57]
Peter Laursen
I can only agree with martijn. Now is he a MySQL person or not ? If those characters 'get you into all sorts of trouble' why then allow for them?
[17 Jun 2006 13:55]
Sergei Golubchik
1. '/' and '\' create no troubles whatsoever in the filesystem, because they are encoded (internally) to generate a filename. You can see what files will be created for a table 'a/b'. 2. > "´" ... gives serious problems - such as corrupted non-recoverable tables ... This simply cannot be true. The session with rename table t`1 that you pasted in the bugreport shows that on the first backtick (`) mysql client goes into string mode, and waits for the second backtick - which can be seen from the changed prompt (`>). Then you press ^C, aborting the client. So, no query is sent to the server, not a single byte. It cannot change anything on the server :) 3. > ERROR 1146 (42S02): Table 'test.ab' doesn't exist This is a real bug.
[17 Jun 2006 15:09]
Peter Laursen
1.
I never claimed that / and \ would cause probles with the file system (And I HAVE noted what file names are created). Martijn did! I claimed there was an inconcisteny with escaping in different areas of the server code when using \. So here we seem to agree! I did also NEVER claimed that there was any problem with /.
I also think the documentation is incomplete about
a) must a \ character indisde an identifier be escaped?
b) must an identifier be quoted/backtick'ed when any of the / \ ' " and . characters are used in it?
2.
> "´" ... gives serious problems - such as corrupted non-recoverable tables
...
This simply cannot be true. The session with rename table t`1 that you pasted in
the bugreport shows that on the first backtick (`) mysql client goes into string
mode, and waits for the second backtick - which can be seen from the changed
prompt (`>). Then you press ^C, aborting the client. So, no query is sent to the
server, not a single byte. It cannot change anything on the server :)
Thanks for the explanation of the client behaviour! Can you tell me then how to use the backtick ('identifier quote character') in a meaningful way inside an identifier?? I guess that the C-API and other connectors would behave a similar way. What is the idea of supporting it when it cannot be used?
I do not think you consider the problems (reported here and in the link to the Webyog Forums) with punctuation in identifiers. Using that has resulted in lost data! Need a new/repeated case (I can easily copy/paste!) ?
3.
See 1)
[17 Jun 2006 15:12]
Peter Laursen
BTW: What I wrote in the first place is as copied from command-line client. You say it cannot be true .. ..well IT WAS WHAT HAPPENED !! I DID NOT CHANGE A COMMA !!
[17 Jun 2006 18:55]
Sergei Golubchik
Yes, I did not mean that it did not happen. What I was saying - it was impossible to have data corruption because of the ` in the client, as client did not send anything to the server. Data corruption must have some other explanation. (bad hardware ? some other program corrupting data ? mysqld bug unrelated to ` ? not a data corruption but ~/.mysql_history corruption that confuses the client ? there could be hundreds of reasons) As for how to create a table with a backtick in the name - it is explained in the manual, by writing the backtick twice: mysql> create table `t``1` (a int); Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t`1 | +----------------+ 1 row in set (0.01 sec) mysql> insert `t``1` values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from `t``1`; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) Webyog forum - most of the postings show correct behaviour. The one does not: mysql> rename table t1 to `testtest.test`; ERROR 7 (HY000): Error on rename of '.\test\t1.MYI' to '.\test\testtest@002etes.MYI' (Errcode: 2) but I failed to repeat it.
[17 Jun 2006 19:24]
Peter Laursen
Thank you for further explanation. Was that (``) added to the manual recently? I think that I have created some 'chaos' here myself by reporting more issues in the same thread. So I suggest that we let this stand for the issue with \ character in identifiers alone (that fails with some statements - or at least one!). Also server 5.11 is now out. I'll try to reproduce the issue with "." (punctuation) in identifiers with server 5.11. If I can reproduce I'll create a new report. Agreed?
[17 Jun 2006 20:24]
Peter Laursen
BTW: 1) I also think one or more crazy table names with / \ ' " . and weird UNICODE characters should be added to the test suite! Simply to 'find out' if something gets broken with future builds. 2) And still: If ASCII (1-32) are legal in identifiers I request a Server setting to turn the use of them off! Are they legal? Is documentation correct? An actually ther should be an option to turn off 4-byte UTF-8 characters as well (as they are only symbols and extinct languages)!
[27 Jun 2006 13:23]
Sergei Golubchik
SQL standard allows ASCII 1-32, and 4-byte UTF-8 characters in delimited identifiers. Basically it allows _any_ character to appear in a delimited identifier.
<delimited identifier> ::= <double quote> <delimited identifier body>
<double quote>
<delimited identifier body> ::= <delimited identifier part>...
<delimited identifier part> ::=
<nondoublequote character>
| <doublequote symbol>
<nondoublequote character> ::= !! See the Syntax Rules
<doublequote symbol> ::= "" !! two consecutive double quote characters
...
Syntax Rules
...
6) A <nondoublequote character> is any character of the source language character set other than a <double quote>.
[27 Jun 2006 23:01]
Peter Laursen
@Sergei Thanks! I learn every day! But I have two comments then: 1) I request a configuration option to turn off the use of ASCII 1-32 in identifers. For the simple reason if it should ever happen here it would be a bug with a client or some piece of code! 2) After thinking, I don't think 4-byte utf8 characters are supported by MySQL at all. Maxlength of utf8 in MySQL is 3 bytes. Right/wrong? If right that must then mean that MySQL does not support characters outside the Unicode 'Basic Multilingual Plane' at all. Not in identifiers and not even in string data /CHAR/VARTCHAR/TEXT). Comments?
[28 Jun 2006 7:05]
Sergei Golubchik
1. ok, but please make it a separate bugreport, with severity "S4 - Feature request". This bug#19874 will be closed when a real bug (aknowledged earlier) will be fixed. 2. Right.
[12 Aug 2006 17:19]
Jim Winstead
As Serg has noted, characters like \ are generally handled in identifiers. It is the SHOW COLUMNS (aka SHOW FIELDS) and SHOW KEYS statements that have trouble with escape characters in identifiers, because they are (unnecessarily?) being stripped. This is easy to reproduce: create table `a\b` (a int); show full fields from `a\b`; The problematic code is the two calls to remove_escape() in the SCH_COLUMNS and SCH_STATISTICS case inside of prepare_schema_table(). All of the calls to remove_escape() should probably be double-checked. Indications are that they are there to fix escaping done by ODBC -- perhaps it should be checking CLIENT_ODBC.
[1 Sep 2006 17:26]
Bugs System
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/11280 ChangeSet@1.2299, 2006-09-01 10:39:48-07:00, jimw@rama.(none) +3 -0 Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing \ incorrectly Identifiers with embedded escape characters were not handled correctly by some SHOW statements due to some old code that was doing some extra unescaping.
[6 Sep 2006 3:43]
Timothy Smith
Looks fine. Thanks, Jim.
[6 Sep 2006 22:27]
Jim Winstead
Pushed to mysql-5.1-new-maint tree.
[13 Sep 2006 8:08]
Timothy Smith
Merged into 5.1.12
[14 Sep 2006 2:52]
Paul DuBois
Noted in 5.1.12 changelog.
