Mysql – In thesql I want to set lower_case_table_names=1 on existing databases to avoid cases-sensitivity issues accross multiple platforms

MySQL

In mysql I want to set lower_case_table_names=1 on existing databases to avoid cases-sensitivity issues accross multiple platforms.

A) What are the risks ?( besides show table issue)

B) After setting lower_case_table_names=1, will I be in position to query databases across
multiple platforms consistantly ?
select * from USERS == select * from users;

C) How the triggers + stored procedure + functions + views + events
will be affected in this regards. I know lower_case_table_names is only for "TABLE"
names but how about triggers other database objects . Will they remain case-insensitive
How about views ?

D) Do I need to rename all tables before/after this configuration setting or this will do the miracle in one step (i.e lower_case_table_names=1 neutralize table names) ?

E) What will be the exact steps WRT:mysqd / my.ini ?

Best Answer

Our servers were Mysql on Windows and we had to change them to Linux. On the new servers we set lower_case_table_names=1 in my.cnf and then copied the schemas with mysqldump old-server | mysql new-server (I don't remember if we copied the mysql and information_schema schemas too, or only the ones with our data, you should try both ways).

Everything is working the same as it was before. Apart from tables we use views, stored procedures and stored functions (but not triggers)

Related Solutions

Mysql – How to create one update query (thesql) on multiple databases

Use transactions.

START TRANSACTION;
  UPDATE  `db_name1`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
  UPDATE  `db_name2`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
  UPDATE  `db_name3`.`mdl_modules` SET `visible`=0 
    WHERE `name` IN ("survey","feedback","audio","testing");
COMMIT;

Mysql – Create multiple thesql users and databases

Script it in bash, probably. Take a list of users, and do something like the following (you will need to modify this heavily):

for line in $ (cat users.txt) do
   mysql < create database $line;
   mysql < create user $line@localhost identified by defaultpassword;
   mysql < grant all privileges on $line to $line@'localhost;
done

Best Answer

Related Solutions

Mysql – How to create one update query (thesql) on multiple databases

Mysql – Create multiple thesql users and databases

Related Topic