Dev::Blog

Here’s a fun one: I was troubleshooting some text import code that takes a pipe delimited text file and stuffs it into a database, but only if there’s been an update to the given row. The thing was, it was updating a lot more rows than it should have - for example, running the job twice in a row on the same file (which should have made for 0 updates on the second run) came up with about 50% of the rows updated.

It turns out trailing whitespace is ignored in MySQL, but only in versions prior to 5.0.3:

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Technorati Tags: MySQL

Karl Seguin’s got a post on a topic near and dear to my heart - MySQL on Windows. We’re running ASP.NET with a MySQL back end, which happens to be the same kind of setup that he’s looking at for an upcoming project. When we made the decision, it was the early days in the company and we couldn’t afford SQL Server licenses but needed to support some legacy ASP code, so we were driven by necessity. Karl actually took some basic metrics, so, uh, good for him.

Shortening his short version of the story, InnoDB on 5.x might be problematic, but ASP.NET connects at about the same speed as PHP does, and MySQL on Linux blew away SQL Server on Windows. It was a simple test that shouldn’t provide any real conclusions, but hey, it gives me a much stronger position than “uh, we run it because we had to.”

For the record, I love the MySQL/ASP.NET combo, at least if you’re tied to IIS like we are. For code, while we do have some PHP applications, I’m quicker and more comfortable (and thus more productive) with C#. For the db, there are a lot more web developers out there running MySQL and posting stuff online about it, so I’ve got a broader knowledge base to draw from when I have a riddle to solve.

Technorati Tags: MySQL, ASP.NET

I just spotted a (long-awaited!) update to the MySQL Connector/Net - version 1.08 RC is now available for download. I was about to patch my own to resolve the “Character set ‘utf8′ is not supported” issue, but now I might be able to get away with the RC version - it’s probably safer than anything I’d patch, in any event.

So far things look good, with only one small fix required - this falls into the “I was getting away with it until now” category, but in a fit of copying and pasting, it looks like I had a few parameters in a query with the same name. It was working before 1.08, but it looks like a change (possibly this one) made the system a little bit more strict.

I’m going to keep the system under load for a day or so, and if all looks good, I’ll deploy it. At this point I’ll take the risk of an RC-related bug over the known character set error problem.

Technorati Tags: MySql