As you can probably imagine, and as you might have already noticed, I am far from unbiased when it comes to databases. However, I don't want to turn this into blind MySQL/MariaDB bashing. Our real goal is to see why MySQL and MariaDB can be such a pain in the long run. I am biased for a reason and I really want to point out why this is the case.

All of the things we are going to see are deeply scary and have serious implications on the migration process in general. I have already pointed out that MySQL is somewhat special, and this section will try to prove my point.

Again, the following examples assume that we are using a version of MySQL/MariaDB that doesn't have strict mode on, which was the case when this chapter was originally written (as of PostgreSQL 9.6). As of PostgreSQL 10.0, strict mode is already on, so most of what we are going to read here only applies to older versions of MySQL/MariaDB.

Let's get started by creating a simple table:

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) 

So far, there's nothing special here. We have created a table consisting of two columns. The first column is explicitly marked as NOT NULL. The second column is supposed to contain a numeric value, which is limited to four digits. Finally, we have added a simple row. Can you see a potential landmine about to blow up? Most likely not. However, check the following listing:

MariaDB [test]> SELECT * FROM data; 
+----+-------+ 
| id | data  | 
+----+-------+ 
| 1  | 99.99 | 
+----+-------+ 
1 row in set (0.00 sec)

If I remember correctly, we have added a four-digit number, which shouldn't have worked in the first place. However, MariaDB has simply changed my data. Sure, a warning has been issued, but this is not supposed to happen since the content of the table doesn't reflect what we have actually inserted.

Let's try to do the same thing in PostgreSQL:

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.

The table was created, just like it was previously, but in stark contrast to MariaDB/MySQL, PostgreSQL will error out because we are trying to insert into the table a value that is clearly not allowed. What is the point in clearly defining what we want if the database engine just doesn't care? Suppose you have won the lottery—you might have just lost a couple of million because the system has decided what is good for you.

I have been fighting commercial databases all my life, but I have never seen similar things in any of the expensive commercial systems (Oracle, Db2, Microsoft SQL Server, and so on). They might have issues of their own, but in general, the data is just fine.