SQL General error: 1366 when using MySQL 5.7 and id and insert

Loren Maxwell

I recently upgraded from MySQL 5.6 to 5.7, which operates in STRICT mode.

I've noticed that now when I display a table that includes the id as a column and I enter a new record with Editor, I get the following error:

An SQL error occurred: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1

I do not get this error for an edit or delete action.

My guess is that Editor's insert SQL statement includes the id because I show it in a column and uses an empty string instead of a null on the insert statement. MySQL 5.7's STRICT mode flags the empty string as an incorrect integer value, since it's not an integer.

The obvious solution seems to be to use setFormatter( Format::ifEmpty( null ) ) on the id field in the php Editor instance.

However if I do that, the record will insert into the table but I get a different error:
Primary key element is not available in data set.

I'm not sure what to make of this error as there's not much here on the site for what it means.

Any thoughts?

allan

Hi Loren,

Assuming id is automatically generated by the database, then don't attempt to set it:

Field::inst('id')->set(false)

Allan

Loren Maxwell

Ah, how simple . . . I didn't even think of that.

Thanks!