sadmysqluser

  Also I have no idea what my users will do,
  and I'd rather have faulty data inserted than none at all.

By the way - thanks for all the great work on PosgreSQL range types.

My prediction is that every SQL and NoSQL system you mention will evolve towards being producers and consumers of SQL and NoSQL interfaces. I.e. like SQLite4 over KV, or Oracle memcache over InnoDB.

What a crappy defense.

There is little I like about MySQL but here's a few reasons I still recommend people use it:

1. It's already part of something delivering value to your business. E.g. Nagios, MediaWiki or some other LAMP stack solution you've deployed. It's there. You're using it. Learn to live with it, and be sure to BACK IT UP!

2. You need to work with data someone gave you that came from a MySQL system. E.g. a Wikipedia dump. Someone else already lived the pain of collecting data into MySQL, now all you need to do is refine it.

3. You're publishing data for someone else to load into their MySQL system (e.g. a Pentaho warehouse).

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 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