MySQL- incorrect data type behaviour

I recently spent a very odd 45 – 60 minutes dealing with a custom built PHP / MySQL CMS which had been working for years without error but had recently stopped saving subject categories correctly.

The problem

A drop down box for a category selection saves some changes correctly but not all.  The changes it doesn’t save correctly default to the same incorrect option.

So I could re-create the problem in my own browser so the customer wasn’t the issue.  The drop down menu for the category would save some changes but not all, “what the hell” I thought, “some save but others default to one particular category??”

OK so where is it going wrong.

I started at the MySQL and PHP error logs, anything going wrong in the background will be listed there but all clear.  Next the drop down box, nope all OK, the options were all correct.  Now the form submission, is something interfering with the POST data, nope all OK.  So now the MySQL query its self, so I sent the query to a log file so I could see what was being sent to MySQL.  And guess what, all OK there as well.

And my next option… MySQL its self.

How could the form be submitted correctly, the query built correctly and sent to MySQL without error but save something completely different for some but not all options.  Simple answer is the ‘Data Type’.  The field containing the category was set to ‘tinyint’, this has a possible value of 0 – 255.  The incorrect default category had an ID of 255 so when a category with an ID above 255 was saved MySQL used the highest number available which is 255.  So any category below 255 saved OK but any ID above 255 was saved at 255 without triggering an error.

And the fix.

Obviously an oversight of the developer who set the field type to ‘tinyint’.  So after a quick look at the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html I change the field to ‘smallint’ giving it another 65535 possible ID’s and seeing as they’ve used 255 ID’s in 4 years this should see them well into into the future.

It would have helped if MySQL saw this as an error and left an entry in the error log but hey that’s what I’m paid for.

No Comments