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
- Recommeded. Update your ORM mapping with lowercase table/aliases/view names. Alternatively lowercase all SQL in your code.
- If you are consistently using uppercase table names in your code then you may replace table/view/alias names in your dump.sql to upper case, then drop, create and upload your corrected dump.
- If you still need mixed case for a reason, make sure table/view/alias are exactly the same in your Java code and in SQL.
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.
