A satisfying bug fix.

As any website developer will know, as well as actually building new websites and developing new features we are constantly having to fix things that go wrong. It isn’t just me, we are all the same. We write scripts and build applications just the way we think they should be, only to find bugs and then spend twice as long tweaking them to actually get them working. As a freelance website designer I can spend quite a bit of time getting to the bottom of problems that occur from time to time. Sometimes this is frustrating, but fixing them and getting everything running smoothly again is a satisfying experience.

Today I discovered that the attaching of image files on one of my sites had stopped working. Now, I know it was working a couple of weeks ago as a PHP upgrade had rendered it broken for a while and I’d fixed it and tested it thoroughly. All was working fine. Until today that was.

First stop therefore was the code I’d fixed a couple of weeks ago, all seemed OK there and after some investigation all seemed to be working fine. In fact, the images were being uploaded and added to the database as they should be, it seemed therefore as though the problem was further down the line when it came to displaying these images within a post.

I looked at some older posts and the images were being displayed as they should, the images for newer posts were being uploaded but were not being displayed… This puzzled me for a while as they use the same files and database, but then I noticed a problem with the database structure.

The post_id column in the image attachment table had been assigned a type ‘SMALLINT’. For those of you who don’t know, this means that the maximum value this column can have is 32767. The post_id column in the posts table was however correctly assigned as an INT, allowing it to contain much higher values

The site had recently exceeded 32,767 posts so when a new post was added with an image attached to it, the scripts were correctly trying to insert a post_id in the image attachment table with a value that exceeded 32767. The MySQL database didn’t like this and simply entered the maximum value it could which was 32767. This meant that the image was being added, but when someone viewed a post with an id of higher than 32767, and image associated with it had the wrong post_id so never got shown.

Simply changing the column type from SMALLINT to INT fixed the problem. Easy when you know how, but it had me puzzled for a little while.

It was satisfying to get to the bottom of it though, which means I can now have some lunch!

3 Responses

  1. Avatar forComment Author Andrew says:

    The moral is to never use a number as an ID. Is that WordPress? They should know better!

  2. Avatar forComment Author Dan says:

    IDs should never be signed either. Why would you want a negative number as an ID? changing the field to unsigned smallint would have doubled the capacity without increasing memory usage.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Avatar forComment Author

Alan Cole

Alan is a Freelance Website Designer, Sports & Exercise Science Lab Technician and full time Dad & husband with far too many hobbies: Triathlete, Swimming, Cycling, Running, MTBing, Surfing, Windsurfing, SUPing, Gardening, Photography.... The list goes on.

You may also like...