Case insensitive

[Anon]

Hi!

i'm converting some database from windows mysql to freebsd. Program
wich uses this database is written very bad (not mine) and did not
correctly call querries.

Problem is with case sensitivity of unix. Is there any way to tell
mysql on unix that SELECT FROM table is ste same as SELECT FROM
tABLe etc.

Thanks

bye Uros

[[deleted]]

No.

In MySQL, table names are case sensitive, fullstop.

http://www.mysql.com/doc/N/a/Name_case_sensitivity.html

"In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in Unix and case insensitive in Windows."

It's probably a lot of work, but if I were you I'd convert all table names to lowercase.

[majik-sheff]

It's not as terrible as it first seems, if you stop the MySQL daemon and then go into your data directory, you can directly rename the files that represent the tables.

For instance, say your database files are in /databases, your database name is "foo" and your table name is "bar", but your PHP code refers to a table named "BAR" in "FOO".

If you look in /databases you'll find a directory called "foo", you'll need to mv this to "FOO". once you've done that, look inside it to see the files representing the tables. For every table there are three files: .frm .MYI .MYD

In our example, you would rename bar.frm to BAR.frm, bar.MYI to BAR.MYI, and bar.MYD to BAR.MYD (note that the extension is case sensitive and should never change.)

If you have a ton of these things to change, I would recommend writing a shell script to do it. Alternatively, if you like the mousey approach, just pick up a copy of MySQLFront from www.mysqlfront.de It's an excellent GUI interface for the MySQL Database that supports renaming tables (can't rename databases though). Hope this helps some. 🙂
-Sheff

[[deleted]]

"It's not as terrible as it first seems, if you stop the MySQL daemon and then go into your data directory, you can directly rename the files that represent the tables."

whohow... hold on a minute there that's the 'elephant in a china-shop' approach.

A much cleaner and safer way is to do it through SQL (which does not require you to stop the engine)

ALTER TABLE old RENAME TO new

But I think the main problem is that sh*tty windows is not case sensitive, and so does not complain when you use the wrong case. So most likely the sourcecode is filled with queries that sometimes use the right case, and sometimes the wrong case.

You'll have to use a search-and-replace tool to find all occurences of the table names and replace them manually.

You cannot do this automatically because you have to verify that the tablename is being used in a query, and not somewhere else.

[majik-sheff]

LOL, true, true. I was just trying to throw an option out there. I've been repeatedly annoyed by the "lazy windows programmer syndrome" when trying to adapt other peoples databases, scripts, web folders, etc to *nix.

[[deleted]]

lol I hear you man, I hear you! :-)

[Anon]

yes this is the problem. One programer make some windows program (binary) so i have to move database from win to *nix.

But because of his stupidity i can't because sometimes he use caps sometimes he's not.

grrrr. So there is no way to tell mysql to work in case insensitive mode or can I.

[[deleted]]

MySQL is case sensitive, and that's that.

If I were you, I'd take a large aubergine and threaten to push it up that programmer's backside if he doesn't start using the correct case :-)