After upgrading a client from MySQL 5.4 to 5.7 I started getting "BIGINT UNSIGNED value is out of range..." errors. I edited my.cnf and set sql_mode = "NO_UNSIGNED_SUBTRACTION" and used SHOW VARIABLES to ensure it was indeed using that mode. That did fix the initial problem in the application but today an unsigned int column is throwing the same error (BTW inventory is 2)...
UPDATE table SET inventory = inventory - 1
I don't understand why since I thought the new sql_mode would revert behavior to pre 5.5? I don't want to start casting everything in my statements just to make simple arithmetic work so the logical solution in my mind is just to convert all of my unsigned int cols to signed ints with the exception of primary auto increment keys.
Is this the best solution to escape these out of range errors?
I did some more digging and found my answer and wanted to share here for anyone stuck on this in the future. The SQL mode NO_UNSIGNED_SUBTRACTION was indeed working and had not reverted for this table/column. In short, the problem was that there was a trigger on that table and I discovered from a somewhat related MySQL bug report from 2009 that as stated in user comments by Davi Arnaut that triggers and prepared statements use the SQL mode that was in use when they were created... aaah! So, I redefined the trigger and everything works as expected.