Problematic Name-change Of Table

[peterlaursen]

Summary:

With YOG + MySQL 5.1.9 + InnoDB you can create (rename) a tablename with a '.' ' like 'tablename.ext'. You cannot with other clients and/or Server versions.

My experience more in detail:

With MySQL 5.1.9 (running on Win XP) and SQLyog I can rename an InnoDB table from 'tablename' to 'tablename.ext'. Actually it shows up with 'show tables' and I can insert data etc. Other server versions reject that. Also with command-line client and MySQL Administrator it it not possible with 5.1 either.

Now I have experienced two times that if I rename to 'tablename.csv' and disconnect after doing so, I AM NOT ABLE TO CONNECT anymore with ANY client! But if I replace Server installation folder with the binaries and all other files (except the /datadir, that is located on another drive!) and can connect – and after that it shows that the table name was NOT changed on disk! )

Now MySQL 5.1 introduces the CSV storage engine. Any connection here?? Or just a coincidence that it happened when renaming table to .csv and not something else ?

This is pretty dangerous, I think.

I don't know what is the issue. A combination of the use of 5.0 API and a flush-bug with InnoDB for MySQL 5.1? I think it is obvious some temporary tables had their names changed – tables on disk do not. Should SQLyog flush immediately after such operation (on InnoD:cool: ?

@Ritesh

I write it here in the first place. But even if SQLyog's API is not 'fully fit' for 5.1, this should not happen. I think you should research into it and maybe file a bug-report or discuss with MySQL AB. I don't think I (yet) have precise information to file anything.

But this should be simple to verify:

“With MySQL 5.1.9 and SQLyog I can rename an InnoDB table from 'tablename' to 'tablename.ext'. Actually it shows up with 'show tables' and I can insert data etc. Other server versions reject that. Also with command-line client and MySQL Administrator it it not possible with 5.1 either.”

[Ritesh]

Will look into it now. Looks like a client/server issue because we just execute queries which the MySQL server allows. Maybe its a bug with MySQL itself 🙂

[Manoj]

@Peter

I never faced any problem with 5.1.9…till now….

here I am pasting my one session…just look to it once….

---

G:Installmysql519bin>mysql -h localhost -u root -P 3310

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 9 to server version: 5.1.9-beta

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> select version();

+

---

+

| version() |

+

---

+

| 5.1.9-beta |

+

---

+

1 row in set (0.00 sec)

mysql> use test;

Database changed

mysql> show tables from test;

+

---

+

| Tables_in_test |

+

---

+

| n.s |

| stu.new |

+

---

+

2 rows in set (0.00 sec)

mysql> show create table `stu.new`;

+

---

+

---

---

+

| Table | Create Table

|

+

---

+

---

---

+

| stu.new | CREATE TABLE `stu.new` (

`ID` int(11) DEFAULT NULL,

`Name` char(30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+

---

+

---

---

+

1 row in set (0.00 sec)

mysql> rename table `stu.new` to `stu.cls`;

Query OK, 0 rows affected (0.01 sec)

mysql> quit

Bye

G:Installmysql519bin>mysql -h localhost -u root -P 3310

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 10 to server version: 5.1.9-beta

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test;

Database changed

mysql> show tables from test;

+

---

+

| Tables_in_test |

+

---

+

| n.s |

| stu.cls |

+

---

+

2 rows in set (0.00 sec)

mysql> rename table `n.s` to minw;

Query OK, 0 rows affected (0.00 sec)

mysql> rename table mine to `mine.csv`;

ERROR 1017 (HY000): Can't find file: '.testmine.frm' (errno: 2)

mysql> rename table minw to `mine.csv`;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

G:Installmysql519bin>mysql -h localhost -u root -P 3310

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 11 to server version: 5.1.9-beta

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

[peterlaursen]

Well .. I came, I saw …

Now what do you conclude from that?

[Ritesh]

Looks like an issue with MySQL rather then SQLyog. We will need to do more research to get the actual problem.

From the command line, did you execute:

rename `table` to `table.csv`

or

rename table `table` to `table.csv`

[peterlaursen]

Another example:

===========

1)

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 4 to server version: 5.1.9-beta-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test;

Database changed

mysql> show tables;

+

---

+

| Tables_in_test |

+

---

+

| dummy.ext |

| ggg |

| t1 |

| tablename1 |

| tbldependents |

| test |

| yesno |

+

---

+

7 rows in set (0.00 sec)

mysql> rename table `dummy.ext` to dummy;

Query OK, 0 rows affected (0.09 sec)

mysql> rename table tabel1 to `tabel1.ext`;

ERROR 1017 (HY000): Can't find file: '.testtabel1.frm' (errno: 2)

mysql> rename table `dummy.ext`to `tabel1.csv`;

ERROR 1017 (HY000): Can't find file: '[email protected]' (errno: 2)

mysql> show tables;

+

---

+

| Tables_in_test |

+

---

+

| dummy |

| ggg |

| t1 |

| tablename1 |

| tbldependents |

| test |

| yesno |

+

---

+

7 rows in set (0.00 sec)

mysql> rename table t1 to `testtest.test`;

ERROR 7 (HY000): Error on rename of '.testt1.MYI' to '.testtesttest@002etes

.MYI' (Errcode: 2)

mysql> rename table t1 to `testtest.csv`;

ERROR 7 (HY000): Error on rename of '.testt1.MYI' to '.testtesttest@002ecsv

MYI' (Errcode: 2)

mysql> rename table ggg to `dummy.www`;

ERROR 7 (HY000): Error on rename of '.testggg.MYI' to '[email protected]

I' (Errcode: 2)

mysql> rename table yesno to `mine.csv`;

Query OK, 0 rows affected (0.41 sec)

mysql> show tables;

+

---

+

| Tables_in_test |

+

---

+

| dummy |

| ggg |

| mine.csv |

| t1 |

| tablename1 |

| tbldependents |

| test |

+

---

+

7 rows in set (0.00 sec)

mysql> select * from mine.csv limit 5;

ERROR 1146 (42S02): Table 'mine.csv' doesn't exist

mysql>

2)

mysql> rename table tablename1 to `test.yog`;

Query OK, 0 rows affected (0.35 sec)

mysql> select * from test.yog limit 5;

ERROR 1146 (42S02): Table 'test.yog' doesn't exist

mysql>

After that opened in SQLyog

See attached

first table was innoDB, second myISAM.

@Ritesh

was that a question for Manoj or for me?

I cannot remember in detail what I did three days ago. But you can see what I just did!

I can add that after closing command-line client and reconnection with SQLyog the correct tables show up. However the InnoDB table is still garbled.

Now:

1) are tablesnames with '.' legal ?

2) will client need to be closed for changes to be flushed?

[peterlaursen]

OK .. backticks do some of it

mysql> rename table dummy to `peter.tst`;

Query OK, 0 rows affected (0.38 sec)

mysql> select * from `peter.tst` limit 5;

+—-+

---

+

---

+

| Id | t | f |

+—-+

---

+

---

+

| 0 | d | NULL |

| 1 | a | 77.777 |

| 2 | b | 88.888 |

| 3 | c | 99.888 |

| 7 | e | NULL |

+—-+

---

+

---

+

5 rows in set (0.00 sec)

Next opening another cmd-line client and SQLyog.

cmd-line client shows the change – SQLyog not

[peterlaursen]

I want to add that I am not sure about the garbled table.

I now remember how that came into existence.

Table was created with beta6

(Migration Access yes/no >> BIT '0'/'1' thing)

[peterlaursen]

MySQL 5.0 and previous:

mysql> rename table yesno to `maybe.not`;

ERROR 1103 (42000): Incorrect table name 'maybe.not'

mysql>

because:

http://dev.mysql.com/doc/refman/5.1/en/legal-names.html

Quote:

There are some restrictions on the characters that may appear in identifiers:

No identifier can contain ASCII 0 (0x00) or a byte with a value of 255.

The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible.

Database, table, and column names should not end with space characters.

Before MySQL 5.1.6, database names cannot contain ‘/’, ‘’, ‘.’, or characters that are not allowed in a directory name.

Before MySQL 5.1.6, table names cannot contain ‘/’, ‘’, ‘.’, or characters that are not allowed in a filename.

… but does not look like the new implementation of MySQL 5.1 is very successfull.

[peterlaursen]

I felt safe enough to report this one myself:

http://bugs.mysql.com/bug.php?id=19874

[peterlaursen]

Now .. one aspect more

let's chenc the use of backsash in identifiers

rename table `test`.`jklddfd` to `test`.`jkl\ddfd`;

show tables;

/*

results in

Tables_in_test

---

jkl\ddfd

And

*/

rename table `test`.`jkl\ddfd` to `test`.jklddfd; (from SQLpane – not GUI)

and 'table does not exist'. Now 'd' should mean 'd' according to general rusles for escaping ….

Solid garbage MySQL did here IMO!!

[Ritesh]

You can probably post a link of this page to the bug list.

[peterlaursen]

done!

[peterlaursen]

@manoj

Please note that there are new developments here:

http://bugs.mysql.com/bug.php?id=19874

And with MySQL 5.1.11 I cannot reproduce any problems with the “.” (punctuation) character anymore.

But there is some small bugs with SQLyog when BACKTICK is used in a database or table identifier.

(and would probably be the same with ” in ANSI-mode)

1)

I can create the database `db…7`and table `t`2` (by entering `t“2` as the table name)

However the 'double backticking' should ideally be done transparantly by SQLyog (though not important!).

2) and more important:

when activating the table and clicking the DATA tab, SQLyog does

show full fields from `db…7`.`t`2`;` — raises error 1064

It should be

show full fields from `db…7`.`t“2`;

It affects probably every SQL command executed on the table. Also 'describe', 'show full keys' etc

3)

I can create a database `t`g` (also here I must enter `t“g` myself) .

But also some database related SQL commands executed by SQLyog are wrong.

For instance it is not possible to create tables in this database with SQLyog GUI.

The database also cannot be DROPped from GUI.

(but does not affect those statements of the type SELECT .. FROM INFORMATION_SCHEMA … for some reason)

4)

And of course STORED PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS are affected as well.

You can create SP 'n`n' like CREATE PROCEDURE `test`.`n“n` ()

.. but DROP and ALTER PROCEDURE raises an error too (and an error without a number ??)

[Author]

Posts