latin1_bin VS. latin1_general_ci - MySQL Database Collation - Case Sensitive
Written on February 5, 2008 – 2:50 pm | by jaygaulardcom |
|
|
As you may already know, for the past few weeks or months, I have been in the process of migrating all my websites over to a new hosting company. All the sites are now over and running smoothly. Among others, I have five websites that are almost identical…only the content and look is different. The database tables, structure and files are the same. I moved three of those sites over to the first server and migrated the other two over to the second server.
The first three websites were up and running very quickly and have had no issues. Strangely enough, when I went to import the backed up databases for the last two websites on the second server, I got an error reading something like, “Key length is too long on line 26.” The support guy at the hosting company was kind enough to change the database collation to “latin1_bin,” from “UTF8,” which allowed for the database import with no problem. Well, until a few days went by…
I began getting emails from my sister, Stephanie (who does all the customer support for those websites) about people complaining that they couldn’t login or properly search the websites anymore. For instance, if they tried to search, “Jay Gaulard,” about 5 results would return. If they tried to search, “jay gaulard,” about thirty results would return. In other words, the websites had become case sensitive.
I called the hosting company who was at a loss. Then, I submitted a support ticket to the developer of the software. The developers returned the ticket and informed me that the hosting company had changed the database collation to “latin1_bin,” which was binary and thus case sensitive. They told me to change the collation to a non-binary collation to fix the issue. Neither of us knew if this would really fix anything after the fact.
Now, one thing to remember is that you can easily change the collation of your MySQL database when it’s empty. You can even change it when it’s full. The problem is that you will only be changing any new tables that are put into that database. Any existing tables would need to be changed manually, along with all the table columns. So, that’s what I did yesterday…for about six hours. I changed both databases to “latin1_general_ci” (which stands for case insensitive). I also changed all 268 tables and over 1000 columns in those databases. This was not fun. Actually, it sucked.
The outcome is that both websites are now functioning properly, which is a nice thing.
Related posts
Tags: collation, database, linux, mysql
