This is a blog post about why you should dump MySQL (or any forks...) and choose another database for your projects. It is not exhaustive at all, it just reflects my opinion about this database that I have formed so far after many years using it. Many are going to hate this post, many will probably love it. Feel free to comment below in any case. I have blogged about MySQL in the past (read here and here), but things have changed since then.
You should dump it. No, seriously. Dump it. Below are a few reasons why you SHOULD do that (from my perspective):
- Unexpected server behavior, depending on stupid server variables, like sql_mode or group_concat_max_len. The server behaves differently, depending on what the values of these server variables are. So, you want to deploy your nice project that works perfectly well in your setup to another server that has different server variables set? It might work for you, it might not. It might work for your initial data set, but when new data will be inserted, you might notice things are screwed up because of silenced server warnings that could be data truncations and other stuff like that. Notice the word might. Then you start to look inside your code, to see WTF is going on with your data and skip MySQL entirely (because you think it works fine). This fact alone should make you avoid it. Think about it why.
- When I took the relational database sources during my bachelor degree, I was told that a database system must comply to the ACID acronym. Oh well, MySQL could be ACID compliant, but you must choose the correct table engine for your tables. Using MyISAM? Good luck with it. At least, if you still want to use MySQL for your projects, choose InnoDB. MySQL uses different storage engines for its database tables, which at first sight seems like a nice feature, but you should realize that referential integrity in not enforced by the database server itself, but by its storage engine. You can also try to think what could happen when you mix tables with different storage engines inside your database. Good luck with it. At least MySQL 5.5 defaults with the InnoDB storage engine.
- Data loss? Maybe. At some time, I was involved in a project where two MySQL servers were replicated (master - slave scenario). Aside from the replication stuff, there was an issue of lost records already committed inside a transaction. In this transaction, data to table A were saved and data to detail table B were also saved. But after some time, data in the detail table B that were already saved before, were lost. We never found the real cause of this issue, but I'm pretty confident, that during development, while MySQL versions were upgraded, we should have dumped the databases and create from scratch (delete the datafiles from the previous version, create new ones and import the database dump). I'm sure this must have been some bug, somewhere. This issue was the first cause to make me hate MySQL and think twice before using it in a project of mine.
- Data integrity? The default configuration accepts 0000-00-00 as a valid datetime. This is just an example of how bad options it includes in its default configuration. It's so bad that many programmers depend on these "features" to make their code work and simply break if the database is moved to another server that has stricter/different defaults. Try to think of them about porting their database to another DBMS...
See examples of the weird and dangerous behavior of MySQL. This is MariaDB 5.5.40 (I doubt the official MySQL behaves differently, please share your experiences in different case). You can also watch this video on Youtube: MySQL vs PostgreSQL - Why you shouldn't use MySQL (examples taken from it).
MariaDB [mytest]> create table test1(id integer not null auto_increment,
col1 varchar(50) not null, col2 varchar(50), primary key(id));
Query OK, 0 rows affected (0.16 sec)
MariaDB [mytest]> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` varchar(50) NOT NULL,
`col2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [mytest]> insert into test1(col2) values ('Hello there!');
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'col1' doesn't have a default value
MariaDB [mytest]> select * from test1;
+----+------+--------------+
| id | col1 | col2 |
+----+------+--------------+
| 1 | | Hello there! |
+----+------+--------------+
1 row in set (0.00 sec)Look at the example above. A NOT NULL column is inserted the empty string value ('') although declared as NOT NULL. WTF is that? The value is inserted normally, you just get a warning. This is dangerous.
Another example:
MariaDB [mytest]> select 'this is a string'/0; +----------------------+ | 'this is a string'/0 | +----------------------+ | NULL | +----------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect DOUBLE value: 'this is a string'
Ok, a string value is converted to numeric...? Plus, a division with zero throws a warning, not an error. This is extremely dangerous behavior.
Another last example:
MariaDB [mytest]> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` varchar(50) NOT NULL,
`col2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [mytest]> alter table test1 add column col3 numeric(8,2) not null after col2;
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [mytest]> insert into test1(col3) values ('a string');
Query OK, 1 row affected, 2 warnings (0.00 sec)
Warning (Code 1364): Field 'col1' doesn't have a default value
Warning (Code 1366): Incorrect decimal value: 'a string' for column 'col3' at row 1
MariaDB [mytest]> select * from test1;
+----+------+--------------+------+
| id | col1 | col2 | col3 |
+----+------+--------------+------+
| 1 | | Hello there! | 0.00 |
| 2 | | NULL | 0.00 |
+----+------+--------------+------+
2 rows in set (0.00 sec)See what's going on here? You insert a string in a numeric column and the string is automatically converted to 0! As hard as it seems to MySQL developers, 0 is a perfectly correct number that stands on its own. They probably thought that 0 is a ok value to substitute a string. Who knows?
These are simple examples that show how dangerous are the default configuration from MySQL. "Ok", you say, "you consider yourself a capable developer without tweaking the database?". Yes, but when the database leaves so many options available that alter its behavior in ways that it is unexpected (not mentioning that MySQL is not very SQL compliant...), then this is not a good DBMS to go with.
Do yourself a favor and use another DBMS. Yes, MySQL is the most popular open source database, it has good documentation, many guides and howtos, but it's unstable with regards to its SQL behavior. You will have a learning curve when you choose another DBMS, but on the long term, you will realize you should have done this earlier.
