SQLSTATE[22003]: Numeric value out of range

A client came to me with a rather strange issue today. They were trying to delete a product from their Magento store and nothing they did would work. They could update the product and make changes to it, but just could not delete it. There were getting the error:

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(database.q.items_count – 1)’

(Note database is generic for the database name, which I removed for their security.)

Let’s break the error message down first.

  • SQLSTATE[22003] is an obvious SQL error and not overly helpful here
  • BIGINT UNSIGNED value is out of range tell us that we have a column defined as an unsigned integer, so negative numbers are not allowed. Make sense.
  • database is the name of our Magento database
  • q is our table name having the issue
  • items_count is the column defined as unsigned bigint
  • -1 is the value attempting to be put in the column, which as we saw above, can’t put a negative value in this column of this table

Great, we know that we have a problem, but we already knew that. The question now is simply, how do we fix it? What’s going on here? We crack open the database with our handy SQL tools, phpMyAdmin works and is what I used in this instance, but any SQL tool will be fine for our purposes of course.

First thing we notice in browsing the database is that there is no table named ‘q’. What’s going on here? How can we get an error message for a table that doesn’t exist? Well, it turns out that this is a temporary table created during the deletion process and it is having the issue. This was the easy part to figure out. Of course it’s a temp table. Now, what data is going into this table? That’s an awesome question!

Well, it has a column called items_count, that seems like a good place to start hunting. Let the hunt begin!

Did you find it yet?

No?

Ok, well, I’ll give you a hint…I did end up cheating just a bit here. and found another post out there somewhere that said they needed to clear out some of the data in the table sales_flat_quote. Their instructions, while they worked to get rid of the data, did not solve my issue, so my hunt continued.

I use a text editor call Komodo Edit which I have found invaluable for my programming work, but I’m sure there are others out there that have the same functionality and maybe even better. This isn’t a discussion about the text editor, but Komodo Edit has the functionality to search all of the files in a project for certain text. So, I started looking for the table ‘q’ first. The letter q is surprisingly popular in programming after finding some where north of 10,000 lines of code with ‘q’ in it, I decided to go in a different direction and search for ‘items_count’. This proved very useful. I didn’t follow the entire code process through what happens, but I found this little snippet of code in the file:  /app/code/core/Mage/Sales/Model/Resource/Quote.php

This looks very promising indeed! Remembering that we found code regarding the sales_flat_quote table previously, and this code says substractProductFromQuotes this seems like a good indication of what’s going on. Then look at that last line there. That looks like our table q, our column, items_count and our value -1. Hmmm…could this be related? I really don’t know for certain if this is where the problem lies as I said, I didn’t follow the full logic through, but I have a sneaky suspicion. Now, I could have done more testing here to see if this was the code causing the issue, but I didn’t feel the need to. I see the problem if this is the code causing the issue (or if there is code somewhere else like it…).

The table sales_flat_quote happens to have a column items_count. I don’t know what all data this table stores, but I know that if the code above is running against the data in this table AND items_count is 0 (that’s a big fat zero people), there are going to be problems. Problems exactly like the issue we are trouble-shooting! Oh cool! We can finally get to the “solution”.

I will not be held responsible for any damage you do to your database or site by following my instructions that follow. Thank you.

A quick SELECT will let us know if we have an issue:

 (NOTE: if your Magento site is using a table prefix, you may need to add it to your table name.)

If you get any results here, they could very well be your problem. I’m not going to go so far as to say for sure this is the problem in every case, but it made sense to me based on everything else. So, I had all of these rows, in my case about 120 rows, which I don’t know for certain what this data is, and I don’t want to do more research at this point, I just want the problem fixed. I take a backup of these rows. In phpMyAdmin it’s just a matter of making sure all of your rows are on the screen (edit your limit statement…or do it with the GUI…I’m not here to teach you phpMyAdmin and SQL) and do a Check All and then with Checked -> Export. This will give you a SQL backup of the specific rows of data. Then with that same data loaded again from the SELECT statement (make sure your row counts match, people) Check All and Delete the data. 

Once your data is deleted, you should magically be able to delete your product from the catalog. If not, well, bummer, I don’t know what your problem is…maybe you have a very busy site and more data got put in the table by the time you got over to deleting it? Run the SELECT again and see if any rows show up in there…Other than that, you can feel free to contact me and hire me to figure it out for you, or have fun on your own.

Once the delete is successful, I recommend putting the data back into the table. Take that SQL file you created earlier, and load it into a text editor and remove the lines like these at the bottom (or don’t and accept the errors you are likely to receive):

Then Import your newly saved file using phpMyAdmin.

I do recommend running the SELECT statement again after the import, just to make sure all of your data was reloaded properly as well, but you don’t have to do anything I say.

Best of  luck and I hope this helps you out!

This entry was posted in Coding, Magento, PHP and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">