Menu
We have a camelCase naming convention on everything we do - from database tables to object properties, columns, database indexes and constraints.
We have been working with these conventions for two months now on a new project and everything was going well, when all of a sudden last night all relations on only one of our 6 databases converted from camelCase to lowercase. It is important to note that only the constraints converted - the indexes themselves stayed camelCase.
So if we had a column called someColumn and another, someTable.otherColumn, it went from this:
someColumn => someTable.otherColumn ON DELETE CASCADE ON UPDATE CASCADE
to this:
someColumn => sometable.otherColumn ON DELETE CASCADE ON UPDATE CASCADE
What could cause this? We were unable to reproduce this issue - we tried changing a random constraint to see if it would change them all and we tried re-importing the structure and it went fine, keeping the camelCase from the import.
We work on OSX and deploy to CentOS.
Edit: One developer uses a case insensitive OSX. He tried re-importing the database from an export on his own machine, and it was still fine, thus: importing a dump from a case insensitive machine into the case sensitive CentOS did not break things. Restarting mysqld also failed to reproduce this bug. All force-lowercase mysql settings are off. To date we have been unable to make it happen again.
Edit2: Note that this only happened on our CentOS development server - the developer who uses a case insensitive OS has imported his database before from others who are on case sensitive systems, and everything was fine every time.
722
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names.
To set the collation for the entire database, you can use: CREATE DATABASE test_database CHARACTER SET utf8 COLLATE utf8_general_cs; You can also change the collation on an existing database via ALTER DATABASE. (For more information see the MySQL Database Character Set and Collation manual entry.)
Column, index, stored routine, and event names are not case-sensitive on any platform, nor are column aliases. However, names of logfile groups are case-sensitive. This differs from standard SQL.
If you want case-insensitive distinct, you need to use UPPER() or LOWER().
Bug report #55897 suggests that this is by-design and documented under Limits on InnoDB Tables:
On Windows,
InnoDBalways stores database and table names internally in lowercase.
See also Case insensitive constraint names in MySQL.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
