Fun MySQL fact of the day: a CHECKered past

2019-03-06 20:22UTC

A Check Constraint is a table constraint, described in SQL-89, that allows a database index to remain consistent based on a specified search criteria or expression. For example, we could ADD CONSTRAINT is_even_ck CHECK(my_int_col % 2 = 0) to ensure that the value of the my_int_col column in every row is an even number. This is fantastic: we can put validation logic into our table data definition! So long, server-side validation!

But this is a fun MySQL fact, so there must be a catch! And there is: for almost 2 decades, up until the not-yet-released MySQL 8.0.16, CHECK constraints have been silently ignored. How silently, you ask? Sneakily silently. When a DDL (Data Definition Language) command, like a CREATE or ALTER, attempts to create a CHECK constraint, MySQL will accept the constraint as if valid, but then it will simply choose not to create the constraint, and all without reporting any warnings (did you know you can SHOW WARNINGS after a command is run? Try it!). And, well, the next thing you know, your database is overflowing with odd numbers and your legacy application that causes a kernel panic when given any odd number becomes the DoS vector you oh-so-carefully tried to avoid in the first place with the CHECK constraint.

(n.b. the suggestion to remove server-side validation was sarcasm. You should absolutely not eliminate server-side validation, but you should use database constraints to improve your data consistency)