For many years, MySQL and PostgreSQL were competing databases, which addressed slightly different audiences. In my judgement (which is of course a bit biased) PostgreSQL always served my professional users, while MySQL had a strong standing among web developers and so on. But, after Oracle took over MySQL I had the feeling that the star of MySQL was somewhat fading.
Table of Contents
Since the introduction of MariaDB I have the personal feeling that the technology is not really finished yet and that people are still asking for MySQL / MariaDB and are interested in comparing PostgreSQL to those technologies. Many people are not happy with statements such as “PostgreSQL is just better”. Arguments like that tend to be no brainers and we (the PostgreSQL community) are really able to do a lot better. So I thought I'd bring up some good ideas, why it is actually more desirable to use PostgreSQL in favor of MySQL / MariaDB.
The idea of this post is to collect some examples, which show on a technical level, why you might want to go for PostgreSQL. Keep in mind: This posting is simply a “best of” and by far not a complete list. However, it should be enough to convince people and help decision makers:
| MySQL / MariaDB | PostgreSQL (any version) |
| MariaDB [test]> CREATE TABLE data (
id integer NOT NULL, data numeric(4, 2) ); Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> INSERT INTO data VALUES (1, 1234.5678); Query OK, 1 row affected, 1 warning (0.01 sec)
MariaDB [test]> SELECT * FROM data; +----+-------+ | id | data | +----+-------+ | 1 | 99.99 | +----+-------+ 1 row in set (0.00 sec) | test=# CREATE TABLE data (
id integer NOT NULL, data numeric(4, 2) ); CREATE TABLE test=# INSERT INTO data VALUES (1, 1234.5678); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 2 must round to an absolute value less than 10^2. |
What you see here, is that PostgreSQL errors out because the data you are trying to put into the table does not conform to the data type of the column, while MySQL / MariaDB will silently change your data. Yes, MySQL / MariaDB do issue a warning but at the end of the day the data in the table is not what it is supposed to be.
Why does that matter? Suppose you have won the lottery. Instead of one million you just ended up getting 9999.99 because the field was not large enough. Unfortunately nobody noticed. Would you be angry? I would. The behavior shown by PostgreSQL in this example is correct for a variety of reasons:
If you shot yourself in the head there is no point in having a warning that “you might be dead now” - it is better to prevent the thing from happening in the first place.
Lesson learned: PostgreSQL takes your data structures seriously.
| MySQL / MariaDB | PostgreSQL (any version) |
| MariaDB [test]> ALTER TABLE data
MODIFY data numeric(3, 2); Query OK, 1 row affected, 1 warning (0.06 sec) Records: 1 Duplicates: 0 Warnings: 1
MariaDB [test]> SELECT * FROM data; +----+------+ | id | data | +----+------+ | 1 | 9.99 | +----+------+ | test=# INSERT INTO data VALUES (1, 34.5678);
INSERT 0 1 test=# SELECT * FROM data; id | data ----+------- 1 | 34.57 (1 row)
test=# ALTER TABLE data ALTER COLUMN data TYPE numeric(3, 2); ERROR: numeric field overflow DETAIL: A field with precision 3, scale 2 must round to an absolute value less than 10^1. |
Again, MySQL / MariaDB will actually change your data. Remember, the original value inserted into the table was 1234.5678. Now we got 9.99. Do you notice the "minor difference"? If this was your bank account? Would you care? If this database was powering the machine keeping a sick child alive - would you still trust it, or would you demand a replacement? If your life depends on data – would you accept a silent change in the content of a table? Maybe I am too conservative, but I would not.
PostgreSQL does what I personally expect from a database. It will error out and inform us that the change is really not possible because there is data, which simply has to be protected. If you ever want the data in the table to be changed (in case the new rules are violated), you have to tell PostgreSQL explicitly what you want:
| PostgreSQL |
| test=# ALTER TABLE data ALTER COLUMN dataTYPE numeric(3, 2)USING (data / 10);ALTER TABLE |
In this case we told PostgreSQL extactly how data has to be treated. PostgreSQL does not try to be smart (and fail such as MySQL / MariaDB), it does not try to do something fancy – it simply does what YOU want and what is good for your data.
| MySQL / MariaDB | PostgreSQL (any version) |
| MariaDB [test]> UPDATE data
SET id = NULL WHERE id = 1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1
MariaDB [test]> SELECT * FROM data; +----+------+ | id | data | +----+------+ | 0 | 9.99 | +----+------+ 1 row in set (0.00 sec) | test=# UPDATE data
SET id = NULL WHERE id = 1; ERROR: null value in column "id" violates not-null constraint |
Do you remember that there is actually a NOT NULL constraint on the “id” column? MySQL / MariaDB don't care and just set the value to 0. The point: 0 and NULL are definitely not the same thing. NULL means “undefined” or “we don't know the value”. This makes a lot of sense in the real world: If you don't know how much money you got in your pocket, it definitely does not mean that your pocket is empty. If you don't know how many beers you had, it does not mean that there is “zero” alcohol in your blood – you might actually have just fallen off a chair.
PostgreSQL carefully distinguishes between 0, empty string, and NULL. Those things are simply not the same and PostgreSQL will always respect your constraints.
For me there is no question whether to use MySQL / MariaDB or PostgreSQL. The arguments outlined in this posts are strong evidence for me personally that MySQL is not an option. However, this is a free world and people can make decisions for themselves and will hopefully decide wisely.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
From what I know:
* MySQL doesn't support cursors
* MySQL doesn't support functional indexes
* MySQL doesn't support partial indexes
* There is no real full text search in MySQL
* You can't work with geographical data in MySQL ("Find me 10 KFS's around", "In which country am I now?")
* Not sure if MySQL has an analogue of JSONB
* MySQL can't optimize queries like SELECT ... WHERE x = 1 or x = 2 (bitmap scans)
* You can't write procedures for MySQL in Python
* There is no NOTIFY/LISTEN in MySQL
With all do respect to MySQL and MariaDB communities, I don't know a single reason why these days anyone will choose MySQL or MariaDB instead of PostgreSQL.
* MariaDB does support cursors, but they are read-only, non-scrollable, and asensitive. Obviously inferior to PostgreSQL, but it does in fact have basic support for cursors.
* The equivalent of functional indexes can be generated from virtual columns.
* You can limp along with a poor man's partial index using various hacks, but on this one, MariaDB fails.
* PostgreSQL's full text search support is obviously superior, but it's not accurate to say MariaDB has no full text search any more than to say PostgreSQL has no full text search because Solr is obviously superior.
* Regarding GIS, see note about full text search.
* MariaDB does not support check constraints (silently ignores, which is for SQL-99 compatibility but very poor behavior), but these can be emulated (barely) with triggers.
* MySQL/MariaDB still handles clustering/sharding better though PostgreSQL is rapidly closing that gap.
I agree that PostgreSQL kicks MySQL/MariaDB's butt in almost all areas, but fair is fair.
thank you for pointing out the real differences , what brought me here was postgis.
I would be really interested in reading a blog post written by you covering those differences and their practical usages ( like postgis example you put above.)
Hi,
Actually I wrote one back in 2013 http://eax.me/postgresql-vs-mysql/ It's in Russian but I think that Google Translate should help with that. If not I think I could translate it for my blog in English.
The only question I have here is this: which storage engine are you using for these examples? I haven't used MySQL in ages (or MariaDB, ever) but apparently the use of Innodb storage engine solves a lot of these kinds of issues.
this is the default version coming with Fedora core 23 (mariadb.x86_64, 1:10.0.28-1.fc23).
if correct data type handling depends on the storage engine you use, there is clearly something very wrong. i assume that the rules of mathematics should be independent of any storage engine.
This is true for the InnoDB engine.
MariaDB [test]> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_NAME='data' AND TABLE_SCHEMA='test';
--------
| ENGINE |
--------
| InnoDB |
--------
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM data;
---- -------
| id | data |
---- -------
| 1 | 99.99 |
---- -------
1 row in set (0.00 sec)
I love PostgreSQL, but unfair comparisons without strict mode enabled in MariaDB (on by default in >10.2.3) only serve to preach to the converted. https://mariadb.com/kb/en/mariadb/sql-mode/#strict-mode
One of my personal pet peeves with mysql: It silently ignores
checkconstraints. You can add them in mysql, and it will happily accept them, but it will never actually *check* that the data conforms to your constraint or do anything except just throw away your request while telling you that it's been applied.Next release of mariadb has check constraints finally.
I wish mariadb and mysql developers will join postgres on its great mision :-)))
Latest version of MySQL8.x has check constraints.
I don't think that is unfair at all. most people will use the default setup and just start coding / working. if something behaves so badly in default mode, many people will face the horrible consequences. it is like using a gun, which is designed to blow up in your face, unless you explicitly add a safety device. isn't it better to NOT blow up by default? even that already shows that the MySQL philosophy is flawed. In my world data is correct by default - not wrong by design.
I think a good design does good things by default - having a "bugs off" switch feels somewhat wrong.
The default for strict mode is now true, so "bugs off." Whether a distro is up to date is secondary to the point that the current, stable release of MariaDB is strict by default. I have always preferred PostgreSQL over MySQL/MariaDB, but fair is fair.
if your washing machine used to blow up for 20 years by default but it is now ok - would you still buy from this vendor? one more master piece from the docs: "CHECK constraints are parsed but ignored." - now: how do you see that?
I'm not making value judgements about MariaDB. Why do you feel that I need to defend it, especially when I've already made it clear that I strongly prefer PostgreSQL? I was merely highlighting current features. I would do the same if someone trotted out the old canard that PostgreSQL was slow or that it didn't support replication.
By the way, here is a citation of my advocacy for PostgreSQL from 2002. In other words, I've been in the PG camp about as long as you have. No argument that you have contributed far more than I however. https://developers.slashdot.org/comments.pl?sid=46453&cid=4785557
I am a long-time fan of PostgreSQL, both the software and the community. That said, and speaking as someone who has actually purchased a couple of your books, you'll get a lot better response from people by being polite instead of just trying to point out how they're wrong or how their using a metaphorical washing machine that blows up. You're the CEO of a PG-based company after all. While I agree with the admiration for quality code, I can't help but feel your advocacy skills require some attention. Thank goodness Lane, Momjian, et al. do not have the same demeanor. I practically learned SQL from one of Momjian's early edition PostgreSQL books, and yet I have never seen him take such a strident and alienating tone, even to those who prefer MySQL let alone those who share his preference for PostgreSQL.
As the saying goes, "You catch more flies with honey than vinegar." MySQL/MariaDB have more than enough deficiencies and PostgreSQL enough advantages that civil, reasoned discourse should be easier. There is more to PostgreSQL than just a good database engine. PostgreSQL is also about a healthy, productive, and polite community. I'm not going anywhere, but your strident, dismissive tone could very well drive newcomers away. That's certainly not good business.
Agree. Why do I have to config so many optoins to use such mandatory constraints. With PosgreSQL , my application have strong ultra-thin full-controll business rule and datbase constraint baclend while frontend focus on UI
I have to agree with Miles above -- I'm a fierce advocate of Postgres in almost every case above MySQL, but most companies these days use strict mode; and strict mode catches all of your examples...
maybe for my samples but it does not fix things. consider the following: "CHECK constraints are parsed but ignored." ... so, how does that feel?
Agreed... but your examples are a little exaggerated for the typical MySQL set up today.
In general, MySQL's horrific management of schema changes; its obtuse and difficult to manage binlog syncing and lots of other things make it far worse than Postgres... but you'll alienate the audience you want to convince if you use cheap examples... that's all I'm saying....
"most" is subjective... was randomly looking for an alternative to GA and piwik (used it for a bit, but it left a bad taste based on some experience)... came across this : https://github.com/padams/Open-Web-Analytics/wiki/Technical_Requirements
so I agree w/ author that the default install should be used (though as others have said, default mode is changing, which is a good thing)
Why should you stay on MySQL/MariaDB ?
--------------------------------------------------
Because this kind of behaviour can be easily avoided by configuring the system variable: SQL_MODE (see below)
In a productive environment, you can't go live without having it configured because so many tests are made with the application during the test phase.
mysqld7-(root@localhost) [employees]>create table data(id integer not null, data numeric(4,2));
Query OK, 0 rows affected (0.04 sec)
mysqld7-(root@localhost) [employees]>insert into data values (1,1234.5678);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysqld7-(root@localhost) [employees]>show warnings;
--------- ------ -----------------------------------------------
| Level | Code | Message |
--------- ------ -----------------------------------------------
| Warning | 1264 | Out of range value for column 'data' at row 1 |
--------- ------ -----------------------------------------------
1 row in set (0.00 sec)
mysqld7-(root@localhost) [employees]>set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.01 sec)
mysqld7-(root@localhost) [employees]>insert into data values (1,1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1
By the way, in MySQL 8, this variable is now set per default
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)
mysql> show variables like 'sql_mode';
--------------- -------------------------------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
--------------- -------------------------------------------------------------------------------------------------------------------------------------------
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
--------------- -------------------------------------------------------------------------------------------------------------------------------------------
I was a new developer using MySQL 3 years ago. I don't think it's not good idea I have to config to get such mandartoy constraint live above samples. I change from MySQL to PosgreSQL after I realize it do not suport recursive call as it claim. That's main issue I can not go with MySQL since that day.
"Easily"?
Which SQL modes do I need to set, exactly, in order for it to never change my data? I just looked at the "SQL Modes" section of the MySQL manual, and it's over 6000 words long. You mentioned one mode, and then listed 7 others, and it's not at all clear which of these I need to set for my data to be safe. It looks like there's a few others I need, in addition to those 1 7.
This doesn't seem easy at all to me.
"so many tests are made with the application during the test phase"
So during the test phase I need to also exhaustively test my database? Does your application test suite also contain tests for your compiler, your C library, your kernel, and your hardware, too? I find it much easier to build upon systems that don't require me to do their testing work for them.
"By the way, in MySQL 8, this variable is now set per default"
MySQL 8 hasn't been released yet (and certainly wasn't 4 months ago when you said that). You must have meant to say "In the future, this variable set WILL BE the default".
Or I could just use Postgres, which never modifies my data without my permission, and has behaved this way for decades. Somehow, that seems a lot easier to me.
I like myql, but you make a @#$@#$ good point.
It is default in 5.7
yes, they figured that allowing the 126th of december is not a good idea by default ...
Exactly. If the guy doesn't know how to use the database, he shouldn't be writing about it. Yes, there are legacy options that have to be set. Read the manual and move on. Typical PostgreSQL idiocy.
of course there is a setting for that. however, "bug = on" is not a wise default mode. is it? and yes, the changes it in recent versions but it was definitely broken in the default installation. it is like a car blowing up its gastank unless a certain switch is set ... so - in my judgement the stupid default setting, which has been there for years, says a lot about how MySQL thinks ...
If you want to argue that a particular database setting should be flipped the other way, that's a different debate than claiming the entire database is broken and is incapable of functioning with strong checks. The latter is simply wrong and your article is disingenuous. Either you knew that the switch existed, in which case this article is intentionally misleading, or you didn't know it existed, in which case my point stands that you shouldn't be writing about things you know nothing about. Either way, I notice that haven't made any corrections to the article.
And this is all too typical for PostgreSQL advocates. If you have to baldfaced lie about how MySQL works, what does that say about your advocacy of PostgreSQL? What is it about PostgreSQL that makes advocates so insane about making inane claims about MySQL?
Your database is broken by design. It's like a submarine whose default atmospheric setting is "cyanide" and whose fanboys scream "But you can switch it to oxygen!" Would you want to trust your life on that submarine?
When I'm sending data to a MySQL database, how do I know it's been configured for oxygen and not cyanide? As the programmer's saying goes, "The problem with defaults is that people use them." How do I know that sane options aren't being overridden on a per-connection basis? Or in the particular backend engine being used?
The whole design is just unnecessary danger and madness. There's never, ever, ever, ever a reason to accept the 126th of December as a date. None. And humans simply can't breathe cyanide and never will.
Even with all of these settings, 7 / 0 returns NULL. The only idiocy is using a product originally intended to be a dumb data store as a relational database.
For me the number one deciding factor is that mariadb has tablespace encryption built in and Postgres does not. As others have pointed out strict mode nullifies the authors examples.
no, it does not. because it says something about the philosophy behind a product. if the default setting is consistently "bugs on" for 20 years or so, changing that does not fix the underlying problem. even the fact that "bugs on" exists tells me that MySQL cannot be taken seriously. it is like having a washing machine explode and adding a button to turn deadly explosions off ...
Perhaps it's not a "bug on" setting but a backward compatibility setting. Both databases have a different philosophy and history, and if apply pg's philosophy on mysql and call it a bug, it kind if sounds biased and manipulative. I ended up here looking for unbiased factual balanced information. Being shamed into the authors opinions don't cut it for me.
"Silently change data" doesn't sound like a thing one would want backward compatibility with, and a "philosophy" that is incompatible with a RDBMS.
I'd take this seriously if it didn't just stink of fanboism.
I could cherry pick features where MariaDB is superior to Postgres like replication and clustering, but I realise both databases have their place.
Would you try to defend an inept cop saying "he sucks at gun safety, but he's an expert at donuts"?
If MySQL and MariaDB fail at crucial things (i.e. handling data correctly) then it really doesn't matter how well they manage those auxiliary tasks.
that is the point. even the mere existence of a "bugs on mode" is already a disaster. and, guys: this has been the DEFAULT setting for how many years now?
Bug setting => comparability setting. We can get your point after reading it once. Why you have to repeat yourself so many times? Insecurity? Move your lazy fingers and set the bloody strict mode. Totally agree with Ian, this page is a waste of time. (BTW I'm a pg user)
"The problem with defaults is that people use them".
Compatibility with what? Reminds me of when Microsoft explained Excel's leap year function giving the wrong answer for 1900 as having been done on purpose for backward compatibility with Lotus 1-2-3 for DOS (!!!), whose leap year function had the same bug. A computer science journal sarcastically praised them for having achieved "bug-for-bug compatibility" with Lotus 1-2-3 for DOS.
Compatibility with older MySQL, DUH
You're living in prehistory man, strict mode is default for MANY years now.
What good is replication to me, if the data is incorrect? "rm" is faster and has a better compression ratio than "gzip", too.
I'd say excusing a database that can't reliably store data is more "fanboism" than this article.
It's unfortunate that you did not do any research on this topic. MySQL can be easily configured to work this way: https://www.percona.com/blog/2016/10/18/upgrading-to-mysql-5-7-beware-of-the-new-strict-mode/
Three years ago I start new project using MySQL at its beggining, I end up change to PosgreSQL instantly after realize it do not support recursive as it claim. Posgresq support recursive perfectly. I am so happy the decison was extreme correct. Today our product of cloase-base legal system using PosgreSQL 9.6 work fine .
I wrote a little piece in 2010 about this ...
http://marksverbiage.blogspot.de/2010/05/mysql-what-are-you-smoking.html
Essentially, error-handling behaviour is inconsistent in multi-row inserts, it depends on whether it's the first or subsequent rows.
I agree with others. This is not a good example for why someone might/should favour Postgresql over MySQL.
That above would be one argument.
Other real arguments might be things like special features (materialized view, json data handling) or the community itself (how many), quality of documentation etc.
For a lot of people it does not matter at all what you are using. Espeically there are a lot of people who not even using indize properly. Why should they care about some minor differences?
On another level you have people who know there stuff. They know there stuff on mysql, oracle, db2 or postgresql. And they should know why they are using on or the other.
Try your examples again with MySQL 5.7's STRICT mode (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict), which is turned on by default. You'll see they work as expected.
https://eng.uber.com/mysql-migration/
A key point is that MySQL guarantees relatively few things, but it offers compatibility modes to a number of rdbmss, so you can enforce these or not. But the application is in charge. This is a major difference: Use MySQL if you want to have a database for your application. Use PostgreSQL if you want to have an application for your database.
How about you stop these comparisons and dedicate a blog on the article https://eng.uber.com/mysql-migration/
I think it is very unlikely that you would
it is about the default mode - not about configuration. if your car blows up by default, it is not good either.
Do you have any performance measurements? Something like that?
Or scalability features?
Good to seem some factual comparisons versus this comical "in-depth" comparison: https://blog.panoply.io/postgresql-vs-mariadb Especially the "which is right for you" section, where there are "facts" like "powerful", "time-tested", "popular", "fierce dedication". Not a single fact whatsoever, just useless marketing word salad.
is it still valid? I have checked the above issues on my 10.2.14-MariaDB and it gave the correct error reports:
mysql> select version();
-----------------
| version() |
-----------------
| 10.2.14-MariaDB |
-----------------
1 row in set (0.00 sec)
mysql> insert into data values(1,1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1
mysql> update data set id = NULL where id = 1;
ERROR 1048 (23000): Column 'Id' cannot be null
PostgreSQL is a piece of crap.
A: Outdated article.
B: set sql_mode = "STRICT_ALL_TABLES,STRICT_TRANS_TABLE"; and use transactions in your queries.
basically, the entire premise of the article can be debunked just by the RTFM process.
one very simple problem in MySQL is that you CANNOT use group by with order by queries!
this is one very stupid bug that has been reported and discuss many many times!
also in performance and availability wise, with the same hardware; pg is always faster and stabler compare to MySQL.
For anyone coming to this article who's wondering about this in modern (2021) times, this doesn't appear to be an issue with MariaDB, though I can't speak for MySQL. I just tested it on a copy of MariaDB installed via Homebrew on MacOS 11.2.3, the reported version of MariaDB is "10.5.9-MariaDB Homebrew". Here's the error I get from the top example, I didn't bother with the rest
MariaDB [awillisson]> INSERT INTO data VALUES (1, 1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1
What led me to choose PostgreSQL over MySQL (MariaDB didn't exist, but while it has improved on MySQL in this regard, it still lies a long ways behind) back when data integrity wasn't one of MySQL's priorities wasn't just that, but a lot of it was MySQL's anaemic type system.
MySQL doesn't even have a boolean type. The closest seems to be BIT(1), but everyone uses TINYINT(1) which means MySQL "booleans" have one false value and 255 different true values.
It could be forced by declaring a domain type (which is where you specify a base type and then add constraints (to the type, not columns that contain the type)). But MySQL doesn't have Domain types.
MySQL doesn't have array types. It doesn't have row (i.e. struct) types. This obviously means its support for JSON is forced to be weaker as it can't turn a JSON array into a native SQL array, or vice versa, because it doesn't support SQL arrays; and can't convert between a JSON objects and rows.
Because MySQL doesn't do domains, it can't and doesn't have a distinct JSONpath type. Range types are beyond its capabilities. Durations ditto.
The lack of array and domain types mean its SETs have to be declared on a table-by-table basis (and are typed as just strings). And then its lackadaisical approach to type safety rears its ugly head again:
"If you set a SET column to an unsupported value, the value is ignored and a warning is issued:"
Sure, you get a warning (if you're sitting at a console and not on the other end of some API), but it still adds a row, just not the row you tried to add.
Ditto ENUMs. MySQL doesn't have an ENUM type as a first-class citizen, so every time you want to use the same ENUM in more than one table you have to write out the entire list of valid values (the same values in the same order) in every one. And, once again:
"The index value of the empty string error value is 0. This means that you can ... find rows into which invalid ENUM values were assigned:"
How did you manage to insert invalid values in the first place? Because the ENUM type explicitly allows you to; inserting invalid values isn't a mistake, it just means you insert an empty string instead, because that's what you really wanted to do when you tried to insert invalid data, right? Seems you don't even get a warning this time. And if knowing what the "invalid value" was might have helped diagnose where it came from — well, you've lost that information.
And one more thing, because I didn't mean to rant but the list turned out longer than I thought and when I checked the MySQL documentation my eyes started rolling. While I've personally never had to write a low-level extension (though I've used a couple that were contributed by others, most notably "cube" (which introduces a hypercube type) and "hstore" (i.e. declare a column as containing a key/value store for each row) and I don't know what MySQL/MariaDB supports along such lines, but the fact is that PostgreSQL has had a user-extensible type system longer than it has had SQL.