Published on Jul 27 2012 in Databases Java

MySQL case-sensitivity can cause create temporary table error for an application developed on Windows and run on Linux.

Tomcat log: java.sql.SQLException: Can't create table 'xxxxx_1.#sql-5dc6_2dd2f85' (errno: 121)

MySQL server log: [Warning] Invalid (old?) table or database name '#sql-5dc6_2dd2f85'

The problem may be caused by case-sensitivity and subsequent inability to create temporary table. By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The problematic application was created on Windows and installed on Linux server with MySQL 5.1.x (InnoDB). The __lower_case_table_names_ variable is set to 0 by default on most of shared Linux servers and we usually cannot change it because it might cause issues for existing databases/apps.

Following https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/identifier-case-sensitivity.html: "...To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use...".

In other words you need to make your Java code and SQL code case consistent. If you use uppercase in Java code then use uppercase in SQL. If you use lowercase in Java code, use lowercase in SQL.

Solutions

If you are on a VPS or dedicated server you may drop the database, add lower_case_table_names = 1 in /etc/my.cnf, restart MySQL server, create the database and upload the dump.

Note: The first suspect were duplicate key names but DDL review did not confirm it.