16

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.

Swader
  • 11,387
  • 14
  • 50
  • 84
  • I'd check what you use for deployment, and verify whether one of your MacOSX's has lowercase tables in its file system. – LSerni Sep 23 '12 at 16:56

2 Answers2

6

Bug report #55897 suggests that this is by-design and documented under Limits on InnoDB Tables:

On Windows, InnoDB always stores database and table names internally in lowercase.

See also Case insensitive constraint names in MySQL.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Hmm, thanks. We never touched Windows, though. All our development is on OSX, and deployed to CentOS (updated with this info in main question as well). – Swader Sep 15 '12 at 12:04
  • @Swader: Case sensitivity arises from the underlying filesystem on which MySQL's data files are stored. See [Identifier Case Sensitivity](http://dev.mysql.com/doc/en/identifier-case-sensitivity.html). By default, OSX uses case-insensitive HFS partitions and so behaves the same way as Windows FAT/NTFS in this regard. – eggyal Sep 15 '12 at 12:24
  • Just read that chapter, good info, but up until now, everything was fine, this suddenly happened last night without any obvious input from anyone. Also of note is the fact that the developer who uses a case insensitive OSX just tried re-importing the database, and the constraints are fine. – Swader Sep 15 '12 at 12:28
  • @Swader: At a guess, even though they are represented internally in lowercase perhaps they continue to be displayed as originally entered until *`mysqld`* is restarted? – eggyal Sep 15 '12 at 14:08
  • Nope, I have just restarted mysqld and everything is still fine. Really the most baffling part of all this is how it suddenly happened, and why on only one database - others have constraints with camelCase too. – Swader Sep 15 '12 at 15:38
4

You need to examine two variables in MySQL on the OSX server's my.cnf

According to the MySQL Documentation on lower_case_table_names

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

Even though the above excerpt refers to MyISAM, one could only guess what hoops MySQL jumps through with InnoDB given the default value of lower_case_table_names in OSX. In light of this, if you have lower_case_table_names=0 or lower_case_table_names=1 in your OSX's my.cnf, mysqld is probably a little confused.

Therefore, don't be surprised if any mysqldumps from the OSX server moved into CentOS does not change the situation.

This is most likely what you battling with right now.

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Interesting, thank you! I'll look into this. This definitely brings me one step closer to a theoretical cause of the problem. – Swader Oct 01 '12 at 09:13
  • This is as close as we'll get to the actual description of the bug's cause, so I'll award the bounty to this answer, thank you – Swader Oct 01 '12 at 19:10