Investigate INSERT vs INSERT IGNORE in MySQL 5.7

[AnyhowStep]

trafficstars

This, https://www.db-fiddle.com/f/tJNBFe4ECTJcHzgAjKvTz4/4

vs, https://www.db-fiddle.com/f/tJNBFe4ECTJcHzgAjKvTz4/5

---

given the following table,

CREATE TEMPORARY TABLE `test` ( 
  `testId` BIGINT SIGNED NOT NULL PRIMARY KEY ,  
  `testVal` BIGINT SIGNED NOT NULL DEFAULT 1337 , 
  UNIQUE(`testVal`) 
);

This will throw an error,

INSERT INTO `test` ( `testVal` ) VALUES ( 444 );

This will insert a row,

INSERT IGNORE INTO `test` ( `testVal` ) VALUES ( 444 );

---

The error is thrown because testId is NOT NULL and no value for testId was specified... But INSERT IGNORE sets testId to zero by default...

So, INSERT throws, but INSERT IGNORE inserts (0, 444) !(&@($#&%#)(%&#$^&@

https://stackoverflow.com/questions/34906798/insert-ignore-with-invalid-field-value-actually-inserts-zero

[AnyhowStep]

Technically, this should never be a problem with this library, unless the application schema and database schema are different.

For example, saying the primary key is auto-increment on the application schema, but it is not on the database schema. Then, using INSERT IGNORE on the application, using this library.

Should this be treated as GIGO? (Garbage in, garbage out) Or should the MySQL adapter take extra steps to check that people aren't making this mistake?

[AnyhowStep]

https://mysqlserverteam.com/improvements-to-the-mysql-ignore-implementation/

Seems like a dumb "fix" to me >.>

[AnyhowStep]

One of the tests could not be unified because of this.

Basically, right now, we have GIGO, when we originally expected that lying about the existence of a default column should throw an error.

[AnyhowStep]

As usual, SQLite does the "right thing", and doesn't try its hardest to force a row with invalid values to be inserted.