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

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

Loren MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10
edited March 2019 in Editor

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    Answer ✓

    Hi Loren,

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

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

    Allan

  • Loren MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10

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

    Thanks!

This discussion has been closed.