CODEWITHSUNDEEP
phpmysql
Chris Cooper
Chris Cooper

Reputation: 899

Inserting empty string into auto_increment column in MySQL 5

I've inherited a project which we are trying to migrate to MySQL 5 from MySQL 4.0(!) and from myISAM to InnoDB. Queries are now falling down because they are being constructed using an ADODB connection's ->qstr() on all parameters, including ints. Where no value is provided I end up with:

INSERT INTO tablename VALUES ('', 'stuff'...)

where the first column is an auto_increment. This causes an error (fair enough since '' isn't an int). Is there a switch in MySQL to make it behave as it used to (I assume it just silently converted to 0?)

Upvotes: 2

Views: 2613

Answers (1)

nickf
nickf

Reputation: 546333

Edit:

I just ran a few tests and what I wrote below won't help you at all. The error is because of the wrong datatype, and the SQL setting I suggested doesn't change that. I'll leave this answer here though, since it might be helpful to someone else.

Firstly, double check that the column really is auto increment - a couple of times I've had CREATE TABLE files where the fact that a column is auto_increment was sadly missing.

The other thing which might help is to check that NO_AUTO_VALUE_ON_ZERO is not turned on.

SET SQL_MODE='' should turn it off.

Upvotes: 1

Related Questions