sadmysqluser

If you read pippy's statement carefully

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

you'll see we're in agreement - PostgreSQL's design and feature set makes it clearly superior to MySQL in preventing the spread of faulty data. But pippy would rather a garbage database rather than be slowed down by preventive measures. It's costing him development time after all...

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

sadmysqluser

14 anni fadiscuss

You're mistaken if you think MySQL and PostgreSQL and the other databases are simply going to stand still during that time and you're also mistaken if you think applications which work with the SQL model will be abandoned before a better model is found.

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.

sadmysqluser

14 anni fadiscuss

Commercial support for MySQL is evolving in a similar way. Oracle, Percona, SkySQL and others offer it (emphasizing their particular value-added flavor).

sadmysqluser

14 anni fadiscuss

Personally I prefer PostGreSQL over MySQL for a drop in replacement on Linux based systems. It’s far superior in many ways, and something you should consider if you’re able to. Personally if I were to start a new business or had a Linux based environment and was in the planning phase I would choose PostGreSQL from the start. But it’s not always the best option for most people.

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

sadmysqluser

14 anni fadiscuss

Somethings's up with your site. All I see are black boxes where your examples should be: http://imgur.com/N8vdzGX

sadmysqluser

14 anni fadiscuss

If you would rather have faulty data than none when something's wrong with your application, then MySQL is your DB of choice.

sadmysqluser

14 anni fadiscuss

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.

sadmysqluser

14 anni fadiscuss

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.

comments