MySQL 5 - Incorrect integer value: '' for column 'id' at row 1

I recently found that one of my applications no longer works when I install it. Bit of a bummer as you always discover this only when the it is urgently needed.

A lot of head scratching ensued, as the same code is working still working perfectly elsewhere. I checked the php versions and configs, checked the apache setups, all similar enough not to cause any problems. The only clue to what was going on was a mysql log file entry of:

Incorrect integer value: '' for column 'id' at row 1

After much searching I discovered a difference in setups. The MySQL versions were different, previously I had been using mysql 4 and more recently I have been installing MySQL 5.

Now, I'm not sure if it was a security enhancement or just a change in the default level of data-validation, but in MySQL 4, if you set an int_column = '', (that's 2 single quotes) it would be converted to the zero value. In MySQL 5, by default, it causes this error "Incorrect integer value".

Now, this isn't a bug, as int_column = '', is basically saying set the int_column to be a string of no value or a NULL value, which is not an integer, so it shouldn't be allowed to be inserted into the table, but previously MySQL would have converted this to the empty value and auto-incremented my int_column.

You can test this using the following:

CREATE TABLE `int_column_test` (
`int_column_id` INT NOT NULL
) TYPE = MYISAM ;

INSERT INTO int_column_test SET int_column_id = '';

You should get the Incorrect integer value: '' for column 'int_column_id' at row 1 error.

This is an sql-mode issue, the mode defines what SQL syntax should be supported and what kind of data validation should be performed. In my problem MySQL is trying to assign an empty string to an auto-increment INT field and, as we should all know, strings into INTs don't go. Cue errors and the script dies.

Longer term I am going to have to re-work my code to fix this issue, but in the short term, I am going to reduce the sensitivity of the control. To lower the level of data validation we can set the sql-mode to a lower level or comment it out altogether.

Edit the my.cnf (my.ini in windows) file and find and comment out the line:

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The STRICT_TRANS_TABLES or it might be STRICT_ALL_TABLES is the bit that is causing the problem for me and with that line gone, STRICT mode is disallows invalid or missing values in a statement and aborts the command.

With this line commented out, STRICT mode is no longer applied and MySQL should now insert adjusted values for invalid or missing values and more specifically, it should allow my empty string to be put into the int column.

Longer term, I think, I will have to remove all mentions of the id field from my INSERT commands, so that a value is not assigned and the auto-increment is left to do its own thing.

CREATE TABLE `int_column_test` (
`int_column_id` INT NOT NULL AUTO_INCREMENT,
`other_column` CHAR(30) NOT NULL
) TYPE = MYISAM ;

INSERT INTO int_column_test (other_column) VALUES ('Stuff');

Comments

This is the first time I've been to your website. Thanks for providing more information.

I know this if off topic but I'm looking into starting my own weblog and was wondering what all is needed to get setup? I'm
assuming having a blog like yours would cost a pretty penny?

I'm not very web savvy so I'm not 100% certain. Any tips or advice would be greatly appreciated. Thank you

You could see my other post about setting up the demo site for the boldy theme. It pretty much outlines how to set up a blog type website.

It doesn't really cost a pretty at all if you have time and can put the effort in.

Hey there just wanted to give you a quick heads up.

The text in your content seem to be running
off the screen in Chrome. I'm not sure if this is a format issue or something to do with web browser compatibility but I thought I'd post
to let you know. The design and style look great though!
Hope you get the problem solved soon. Cheers

There are a lot of things wrong with this theme. The code blocks are one I particularly dislike, but I didn't really get round to ever finishing this theme. What you are looking at is kind of a half hearted attempt to do something.

When I get round to moving all the content into Drupal 7 I'll put more effort into the theme. Thanks for the heads up though.

I've been browsing online more than 2 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the net will be a lot more useful than ever before.

Funny, I've been moderating spam comments more than 2 hours today, yet I've read many other interesting comments almost identical to yours. But thanks for spending the time to start your bot that visited my site.

Wow that was strange. I just wrote an incredibly long comment but
after I clicked submit my comment didn't appear. Grrrr... well I'm
not writing all that over again. Regardless, just wanted
to say great blog!

Sorry about that, as you can tell I've been getting a lot of spam comments, so I have switched off auto-publish on them for moderation. I'm sure I'll get round to finding your missing comment at some point, unless it looked really spammy and I deleted it by accident, in which case sorry.

Hello, i read your blog from time to time and i own a similar one and i was just wondering
if you get a lot of spam feedback? If so how do you prevent it, any plugin or anything you
can suggest? I get so much lately it's driving me crazy so any assistance is very much appreciated.

I constantly emailed this website post page
to all my friends, for the reason that if like
to read it after that my friends will too.

Fascinating blog! Is your theme custom made or did you download it
from somewhere? A design like yours with a few simple adjustements would
really make my blog jump out. Please let me know where you got your theme.

Cheers

I'm impressed, I have to admit. Seldom do I come across a blog that's both educative and interesting, and let
me tell you, you've hit the nail on the head. The problem is something that too few people are speaking intelligently about. I'm very happy
that I stumbled across this in my search for something relating to this.

I know this if off topic but I'm looking into starting my own blog and was curious what all is needed to get set up? I'm
assuming having a blog like yours would cost a pretty penny?
I'm not very web smart so I'm not 100% sure. Any tips or advice would be greatly appreciated. Cheers

Its like you read my mind! You appear to know so much about this, like you
wrote the book in it or something. I think that you could do with some pics to drive the message home
a little bit, but instead of that, this is magnificent blog.
A fantastic read. I'll certainly be back.

Error: Incorrect integer value: '' for column 'monthlyincome' at row 1

If your monthlyincome row is not an autocomplete row, I don't think it is the same issue.

More likely is you are trying to submit a currency symbol into the column or maybe a comma such as $100,000 into an integer only column. Try stripping all non integer symbols or at least checking that the value is an integer before submitting it to the database.

I'm glad that it turned out so effectively and I hope it will continue in the future because it is so worthwhile and meaningful to the community. dagcdcdkadae

Some genuinely great information, Glad I discovered this. Good teaching is onefourth preparation and threefourths theater. by Gail. caaekafedgkb

Thanks man!

Thanks. A great solution!!

You can also setup the PDO connexion with the option parameter like this :
<code>
new PDO('mysql:dbname='.DEFAULT_SQL_DTB.';host='.DEFAULT_SQL_HOST,DEFAULT_SQL_USER ,DEFAULT_SQL_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8; SET sql_mode="HIGH_NOT_PRECEDENCE"'));
</code>
The sql mode HIGH_NOT_PRECEDENCE allows MySQL to have the same behaviour as MySQL 4