Bug #27162 Edit Table menu item on table starting with upper case letter results in error
Submitted: 15 Mar 2007 2:10 Modified: 4 Feb 2009 15:24
Reporter: Rob Warner Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.2.10 OS:Windows (Windows XP v2000 sp2)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: cannot fetch table information, Edit Table, menu item

[15 Mar 2007 2:10] Rob Warner
Description:
After upgrading to version 1.2.10, trying to edit most tables via the "Edit Table" right-mouse-button menu item results in an error dialog indicating "A MySQL error was encountered.  The message is: Cannot fetch table information.  The following error occured:  (0)".  Why some tables can be edited, but others cause this error dialog is unknown.  All tables can still be edited via alter table statements.  All tables were editable via previous versions of the MySQL Query Browser.  Other people in my office who have upgraded to this version are experiencing the same problem, but not always with the same tables.

How to repeat:
1. Launch MySQL Query Browser
2. Open a schema
3. Select a table
4. Right-mouse-click over the table and select "Edit Table"

This should cause the error dialog to appear.  If it doesn't, repeat but with different tables until the error dialog appears.  (The error can appear on some tables but not on others within the same schema.)
[15 Mar 2007 2:23] MySQL Verification Team
Thank you for the bug report. Could you please provide the dump
of one table which is consistent the behavior reported? Thanks
in advance.
[15 Mar 2007 17:32] Rob Warner
I created a table as follows and then tried to do Edit Table on it and got the error described.

CREATE TABLE `test`.`TestTable` (
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
)
ENGINE = InnoDB;
[15 Mar 2007 17:40] MySQL Verification Team
Thank you for the feedback.
[15 Mar 2007 23:06] Rob Warner
Figured it out!  If the table name starts with an upper case letter, Edit Table fails.  If it starts with a lower case letter, Edit Table succeeds.
[15 Mar 2007 23:08] Rob Warner
Changed synopsis to reflect new understanding of the bug.
[16 Mar 2007 11:44] Juga Paazmaya
I have the same issue but failure occurs also with lowercase names.
[20 Mar 2007 11:02] Telmo Carlos
Hi, I hope this helps as a workaround for Windows with MySQL Query Browser 1.2.10.

I have changed, in my.cnf filem under [mysqld]

lower_case_table_names=2

and now I can edit the tables using the right click over the table and selectiong "Edit Table".
[26 Mar 2007 21:07] Zeb Ulon
Despite the Verified status, it seems according to svn repository that this bug has been solved treated. However I give results of own inquiry. The problem is that the mysql-administrator on its windows version converts table name to lower case in a request to the server. So if the server does not match the name in lower case a error is returned, and the mysql-administrator show the famous error.
Here is mysql network protocol conversation from Ethereal :
  /*!40101 SET @old_collation_connection=@@collation_connection, @@collation_connection=utf8_general_ci */
  SHOW TABLE STATUS FROM `db_example` LIKE 'tbl_mixedcase'
In file myx_database_model.c of mysql-gui-tools commons, in function myx_dbm_retrieve_table_data, this code causes the error :
  // Get table options.
  mysql_query(mysql, "/*!40101 SET @old_collation_connection=@@collation_connection, @@collation_connection=utf8_general_ci */");
#ifdef _WINDOWS
  lower_case_table= g_utf8_strdown(table, -1);
  query= g_strdup_printf("SHOW TABLE STATUS FROM `%s` LIKE '%s'", schema, lower_case_table);
  g_free(lower_case_table);
#else
  query= g_strdup_printf("SHOW TABLE STATUS FROM `%s` LIKE '%s'", schema, table);
#endif
[26 Sep 2007 8:52] media forest
>The problem is that the mysql-administrator on its windows version converts table
>name to lower case in a request to the server.
I really don't understand how query browser could show on the screen the right names with the right case for all tables and wouldn't be able to send exactly what it shows when requesting to the server !
[26 Sep 2007 9:20] media forest
I also tried to analyse what's happening using ethereal and the result is a bit strange :
there's a first query which is correct (with the correct case for table name) :

SHOW CREATE TABLE `my_database`.`MYTABLE`

then a second which is wrong :
SHOW TABLE STATUS FROM `my_database` LIKE 'mytable'
[4 Feb 2009 15:24] Mike Lischke
By default all objects that are represented as file system objects (e.g. schemata, tables etc.) are converted to lower case by the server to avoid trouble with the non-case-sensitive nature of Windows. QB follows this example now and works only with lower case names. I agree, this is not a perfect solution but that's how it works.
[4 Feb 2009 22:28] Jeremiah Gowdy
The database server he's using is running on Solaris.  Solaris (like Linux) allows case sensitive folders to be created, and does so when you create mixed-case databases.  It seems like you're designing the Windows Query Browser to assume that it is connecting to MySQL for Windows and applying MySQL for Windows server specific fixes to the client.

mysql> CREATE DATABASE AbCdEfGg;
Query OK, 1 row affected (0.03 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| AbCdEfGg           | 
+--------------------+
45 rows in set (0.01 sec)

mysql> 

Since this database is created on a Solaris server, the mixed case database name is fine.  There's no reason the Windows based MySQL Query Browser can't handle this properly, which is to not convert the database name to lowercase.  It seems like you need to detect if the server you're connecting to is database / other file object case sensitive, and adjust your behavior accordingly, instead of crippling the entire Windows Query Browser for the case of MySQL for Windows Server.

Quite a few of us are doing Windows based development, but connecting to UNIX based MySQL servers.
[4 Feb 2009 22:31] Jeremiah Gowdy
From what I can see, you need to be checking:

| lower_case_file_system          | OFF                                           | 
| lower_case_table_names          | 0                                             |

to determine if you need to convert the object name to lower case before sending it to the server, rather than just trying all upper case and then trying all lower case.
[5 Feb 2009 10:00] Mike Lischke
Seems I wasn't clear enough. The conversion to lower case table names is a Windows only thing. When you connect to a server which supports case-sensitive file objects then of course you can have also mixed-case tables in your schema. Tested from Windows Vista against a server running under OS X 10.5.6.
[5 Feb 2009 10:20] media forest
It isn't true : the example I gave :
>I also tried to analyse what's happening using ethereal and the result is a bit >strange :
>there's a first query which is correct (with the correct case for table name) :
>
>SHOW CREATE TABLE `my_database`.`MYTABLE`
>
>then a second which is wrong :
>SHOW TABLE STATUS FROM `my_database` LIKE 'mytable'

occured while connecting from windows XP to a Linux Debian Server,
so there is a problem
[5 Feb 2009 11:06] media forest
I think it should be the work of Windows MySQL server to change the case in the queries it receive, this way the query browser wouldn't have to make any change and could send correct queries to the real MySQL server (those which are case sensitive)
[10 Feb 2009 18:25] Jeremiah Gowdy
That's not true.  I am the maintainer of the MySQL server that the original poster is discussing.  We are using MySQL 5.0.38 enterprise on Solaris 10 x86-64 (AMD64).  We are using the Windows based query browser to attach to MySQL running on Solaris and the result is the error he describes above.

Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
You have new mail.
[root@sql-alpha]:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 511840754
Server version: 5.0.38-enterprise-gpl-log MySQL Enterprise Server (GPL)
[11 Feb 2009 10:18] media forest
to Mike Lischke
You know, even if OS X 10.5.6 is case sensitive, it isn't a "real" *nix,
and It is possible that it's OSX which is able to correct the bad syntax in queries...

You should make you tests on a real Unix or Solaris...