MySQL Bugs: #27162: Edit Table menu item on table starting with upper case letter results in error

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

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.)

Thank you for the bug report. Could you please provide the dump
of one table which is consistent the behavior reported? Thanks
in advance.

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;

Thank you for the feedback.

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.

Changed synopsis to reflect new understanding of the bug.

I have the same issue but failure occurs also with lowercase names.

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".

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

>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 !

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'

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.

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.

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.

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.

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

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)

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)

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...