Skip to main content

MySQL: Case-sensitivity Hell

After narrowly escaping Encoding Hell, I fell into Case-sensitivity Hell.

I work with data in big batches. This time around, I was starting with an empty database and importing some data. I noticed that it said that some rows were deleted, which is a funny thing to see when importing data into an empty database. I came up with a plausible explanation which I won't get into and just moved on.

During my batch processing, I pull down all the URLs from a table into a Python dict. My program was getting a KeyError because it couldn't find the URL "http://www.example.com/layout/keywords/Broken%20heart". I did a select statement in the MySQL shell, and I could see it.

Hmm, that's weird. I looked at the code for pulling down all the rows, and it looked fine. I did a count(*) on the table, and it matched the size of my dict. Very weird. I couldn't figure out why my dict didn't have the URL even though it should clearly be there.

After an agonizing, multi-hour debugging session, I finally explained the situation to my wife using an analogy, and she gave me a solution for my analogy.

I finally figured out that since the column was a TEXT column with the database-wide COLLATE set to utf8_unicode_ci, it was returning "http://www.example.com/layout/keywords/broken%20heart" even though I had asked for ""http://www.example.com/layout/keywords/Broken%20heart". See the difference in b vs. B? I didn't ;)

Then it dawned on me, MySQL was "deleting" rows during the MySQL import because it was ignoring URLs that only differed in case. Later, it was giving me one URL in my select statement even though I had searched for a different URL that differed by case. However, my Python dict was definitely not case insensitive, which is why I was getting KeyErrors.

Well, how do you tell MySQL that you want a column to be unique in a case sensitive manner? It turns out there is no COLLATE utf8_unicode_cs (cs stands for case-sensitive). Instead you have to use COLLATE utf8_bin which causes the sort order to behave weirdly as explained here. Apparently, Case-sensitivity Hell and Encoding Hell are next door neighbors.

Ugh, painful. The sad thing is that if you went back in time 30 years ago, you could probably find some other programmer griping about the fact that he just spent a day in Case-sensitivity Hell ;)

Comments

flow said…
mysql made a very bad choice when the decided for collation latin1_swedish_ci and encoding latin1 to be the default values.

frankly, i believe 80% of all encoding problems that pop up around mysql would never happen or be easier to resolve had they chosen a stupid utf8 encoding with case-sensitive behaviour. instead, in their default setting, i can't even store "1€".

Popular posts from this blog

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within Tunes.org , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo

Haskell or Erlang?

I've coded in both Erlang and Haskell. Erlang is practical, efficient, and useful. It's got a wonderful niche in the distributed world, and it has some real success stories such as CouchDB and jabber.org. Haskell is elegant and beautiful. It's been successful in various programming language competitions. I have some experience in both, but I'm thinking it's time to really commit to learning one of them on a professional level. They both have good books out now, and it's probably time I read one of those books cover to cover. My question is which? Back in 2000, Perl had established a real niche for systems administration, CGI, and text processing. The syntax wasn't exactly beautiful (unless you're into that sort of thing), but it was popular and mature. Python hadn't really become popular, nor did it really have a strong niche (at least as far as I could see). I went with Python because of its elegance, but since then, I've coded both p