MySQL - Do Not Pass This Way Again

I've been hearing bad things about MySQL, so I've been avoiding it as of late.

So far my experience has been subpar.

PostgreSQL is pedantic with data insertion, almost to a fault. This costs me development time. (Also I have no idea what my users will do, and I'd rather have faulty data inserted than none at all. If it's for a client asking about a product, this could cost money). Yet purists claim this is a great feature. It's also about twice as slow as MySQL (admittedly this is likely due to the maturity of the environment I'm working in).

I personally like PostgreSQL. However I see it more as a guilty nerd pleasure rather than a development time effective solution.

MSSQL is very nice, and my experience has been the best. Microsoft's tools are top quality. You'll find yourself very productive; creating advanced SQL views, mirroring, and snapshots. However MSSQL reeks of vender lockin, I had to virtualise the MSSQL tools, and getting the drivers to work on Linux took almsot two days of googling. Despite ease of use, the vendor lockin doesn't make MSSQL worth it.

"I'm going to blame myself only - not fileutils or bash - this has nothing to do with the database"

I disagree.

  UPDATE MyTab SET column = 'value' WHERE otherColumn = 1

is a valid SQL statement, while

   UPDATE MyTab SET column = 'value' WHERE otherColumn - 1

is not and should throw a syntax error and not perform a mass update due to some bullshit auto conversion.

What would rile me most is that even if you're a very careful person and do a

BEGIN TRAN

before going for the update (so you can ROLLBACK if in doubt), you may not notice that something went wrong and COMMIT anyway.

A database should always be fail safe; period.

Ronald Bradford's http://www.slideshare.net/ronaldbradford/my-sql-idiosyncrasi... is worth reviewing for anyone who runs MySQL.

I especially like how he explains SQL_MODE bit by bit and ends up recommending

        SQL_MODE =
           ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_ZERO,
           HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_CREATE_USER,
           NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE,
           NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS, 
           NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,
           ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH (5.1.20), PIPES_AS_CONCAT,
           REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES

I also recommend "MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation" http://blog.mozilla.org/it/2013/01/17/mysql-5-1-vs-mysql-5-5... for anyone who working with non-integer numerics.

I've never recommended MySQL for anything where the data is important, but I've had to help several businesses deal with existing installations. Here's a few teachable moments:

One customer who ran MySQL on windows experienced data loss after a historic MySQL crash left them with a corrupted MyISAM table, which caused mysqldump to SILENTLY exit before the dump was complete. Their dumps only had the tables up to the corrupted one. They had been saving those dumps for months, but never testing them. When the server eventually gave up the ghost they found that none of their dumps were good. Lesson: 1. ALWAYS test your dumps. 2. NEVER trust MyISAM.

Another customer, smart enough to use InnoDB but not smart enough to change the MySQL defaults let one database get too large and found they couldn't shrink any of their databases because unless you use innodb_file_per_table InnoDB won't ever relinquish space even if you delete all the rows in your tables even after "optimize table". Lesson: 1. ALWAYS use innodb_file_per_table if you plan to keep the database for a long time. 2. Periodically run mysqlcheck optimize or optimize table to actually reclaim deleted space.

I've also helped customers rewrite their applications to avoid the "Total number of locks exceeds lock table size" problem. What is that? Well, even if you use database level locks the number of rows a transaction can alter is still limited by the server's memory buffer pool size. Lesson: 1. Know your working set. Make sure your application checks the server's buffer pool size via SHOW VARIABLES and is smart enough to avoid large transaction updates. 2. Use a large buffer pool size whenever you can.

Coming from a PostgreSQL background, these came as total surprises to me. I've never been completely happy with PostgreSQL's transaction id wraparound issues but I have yet to experience anything like the problems I've observed with MySQL.

I worked at one data-heavy startup where things were on MySQL and even with a lot of consulting by Percona, eventually it just couldn't keep up with our needs, and the project was ported to PostgreSQL. I've worked on other projects that were less data intensive and MySQL worked fine though.

If given a choice, I'll take PostgreSQL any day, but I do understand that people are hesitant to change database whey they don't need to. If you are encountering trouble though, by all means, move on.