Yesterday, we transferred the database of a custgomer from his Windows machine to our linux-based database server. At the same time, we wanted to get this administration frontend working again. It was also deployed on that Windows machine, running on a Sun Application Server against the local MySQL database.
The problem with MySQL is, besides some other issues, that it is case-sensitive. Yes, MySQL treates database and table names case-sensitive if it runs on a case-sensitive file system. Hence, copying the database from a Windows machine to a Linux machine by exporting and reimporting created mixed-case mysql data files. Hence, the applications who access this database only used lower-case names. The SQL standard clearly states that identifiers are not case-sensitive. MySQL breaks that. There is a configuration parameter which makes mysql do auto-conversion. It will then automatically lower-case tablenames in the SQL statements.
However, it is not a good idea to do that with existing databases as this will break existing applications. Mysql won't find the files which are already mixed-case any more.
So, we needed to set up a second mysql instance with the case-sensitivity configuration parameter set to the value 1. That means, it will lower-case all tablenames and filenames automatically (on creation and within SQL statements).
To start a second mysql instance on a Gentoo-based system, edit /etc/conf.d/mysql
mysql_slot_0=()
mysql_slot_0_1=(
"mycnf=/etc/mysql/my_second.cnf"
"server-id=3"
"port=3307"
)
The my_second.cnf contains the configuration parameter:
[mysqld]
...
set-variable=lower_case_table_names=1
...
Now, import your existing backup into the second mysql instance. You should probably use the
--host and
--port parameters, so your data won't accidently being imported into your primary mysql installation. Specify
127.0.0.1 instead of
localhost if he still connects to the primary instance.
I used my free time today to clean up my server and to work on some issues I planned for some time. My mailserver setup had served 300000 mails in the last three days (that's one mail per second).
My name is Mike Haller and I'm a software developer and architect at Bosch Software Innovations in Germany. I love programming, playing games and reading books. I like good food, making photos and learning and mentoring about the craftsmanship of commercial software development.
