Back in August I reported a bug with the win32 version of MySQL. I never heard back from any of the developers as to if they were going to fix it; but a number of MySQL list readers all agreed this was a problem and needed to be fixed. I posted this on the main Mysql forum; but now I'm going to try to post it on the win32 forum to see if I can get a response from the MySQL engineers. I would be a nice to have if we could use MySQL under win32. Certain developers would like to be able to run our application under windows while doing development. With this bug still in place, an no possible work around, the win32 MySQL can't be used.

Attached is the original message I posted on 8/31/2000. Here's another quick synopsis of the problem; I tried it again today in MySQL 3.23.27-beta and it still happened.

The problem is on the Win32 version of MySQL creating a table with uppercase letters gets mangled. The result is the "show tables like" command is then useless. However, case does NOT get mangled with database names; and show databases like works fine.

The attached message includes a dialog as to why this mangling does not need to occour and why it should not according to the MySQL documentation about case. Keep in mind, case of database names works 100% fine. And with this case mangling it is impossible to determine if a table exists before creating it; because if you create a table with the same name but a different case, it will fail (yet 'show tables like' will say there is no existing table).

---

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.27-beta-debug

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

mysql> create database FooBar; Query OK, 1 row affected (0.00 sec)

mysql> show databases like "FooBar"; +-------------------+ | Database (FooBar) | +-------------------+ | FooBar | +-------------------+ 1 row in set (0.05 sec)

mysql> use FooBar; Database changed mysql> create table FooNuts (i int); Query OK, 0 rows affected (0.05 sec)

mysql> show tables like "FooNuts"; Empty set (0.06 sec)

mysql>

Thanks, -Joe

------ Original message: ------ Subject: Mysql 3.23.22 Win32 Bug or Documentation Glitch, Date: Thu, 31 Aug 2000 16:13:13 -0400 From: Joe Kislo <kis...@athenium.com> To: mys...@lists.mysql.com

I think I've discovered a problem with the Win32 implementation of Mysql, or a very annoying "feature" which is misdescribed in the Mysql manual.

We do all of our development on linux. I just tried loading our application on a win32 box, out of curiosity, to see how well it would work. Everything on our application worked, except for a inconsitency in the mysql database. It causes our application to do some nasty stuff. Here's the jist of the problem.

When our application inserts an object into the database, it tests for the existance of the Database table first, EG:

show tables like 'User';

if it does not exist, it creates it:

create table User (tinyint userID);

then inserts the record. If the table already exists, it just inserts the record.

This doesn't work on win32, and this is because of a case problem.

We create a table "User" (with the U capitalized), however Mysql for win32 does NOT store the table with the capital U on the filesystem, even though it could. [Win32 does support case you know... just for not uniqueness]. Then when our database layer tests for the existance of the table, it thinks it doesn't exist:

show tables like 'User';

[Returns nothing]

Then it tries to create it, and it fails saying the table already exists.

I realize you're going to say that in the manual it says case sensitivity is based on underlying OS, and under win32 things are "not case sensitive", so that's why it creates table "User" in table as table "user".

I would say this is a misfeature. I would think anything that works on Unix, should work on windows, since we're going from MORE specific, to less specific. [Except for two actual tables named the same thing with different case] There's no reason why the win32 version can't do one of the following: 1) Acctually be case insensitive, in which case on Win32 queries against Table names [like 'show tables like'] would NOT be binary, which makes them case insensitive fields like they really are 2) Acctually store the appropriate case on the filesystem [since it's possible], and not arbitrarily convert my table name to lowercase, in which case my query would work 3) Since it works for create database and show database like, why is it arbitrarily different for tables?

The exact manual entry is here: "This means database and table names are case sensitive in Unix and case insensitive in Win32. "\

Even if you don't implement any of my suggestions above, you should definately clarify that you will convert the table names to lowercase, and the show tables like command *still* uses a binary comparison.

I would, however, highly suggest implementing something to get around this. I would -think- it would be highly invaluable to MySQL to maintain a "if it works on unix, it will work on win32" profile.

Also any hints on how to make this code portable to both would also be appreciated, even if none of my suggestions are implemented. Changing the table names is NOT an option.

Thanks, -Joe