When I create a table like this:
create table t(e enum ('a','b','c'));
and then run the following
insert into t(e) values (null); insert into t(e) values ('a'); insert into t(e) values ('x');
the table ends up having 3 rows, where
'x' was substituted with the empty string. I would in this case actually have expected an error as many other RDBMS systems supporting enums do. MySQL 8.0 and 5.7 throw an exception (
Error: WARN_DATA_TRUNCATED: Data truncated for column 'e' at row 1), whereas MySQL 5.6 and 5.5 show the same behavior as MemSQL.
Please also note that I also tested with
set global data_conversion_compatibility_level = '6.5', but that didn’t make any difference.
Is there any other setting to get the database to throw an exception for illegal enum values?