MySQL Idiosyncrasies That Bite

The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.

Comments

  1. says

    Hi!

    regarding slide 77: In my opinion, you should avoid ONLY_FULL_GROUP_BY because of bug #8510 and simply don’t write non-sense GROUP BY clauses.

  2. Matthew Montgomey says

    Error in slide 28 setting default storage engine clearly does not require a server restart.

    mysql> show global variables like ‘storage_engine';
    +—————-+——–+
    | Variable_name | Value |
    +—————-+——–+
    | storage_engine | MyISAM |
    +—————-+——–+
    1 row in set (0.00 sec)

    mysql> set global storage_engine=innodb;
    Query OK, 0 rows affected (0.00 sec)

    mysql> r
    Connection id: 8
    Current database: test

    mysql> create table t1 (a int);
    Query OK, 0 rows affected (0.10 sec)

    mysql> show create table t1;
    +——-+—————————————————————————————+
    | Table | Create Table |
    +——-+—————————————————————————————+
    | t1 | CREATE TABLE `t1` (
    `a` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +——-+—————————————————————————————+

    mysql> set session storage_engine=myisam;
    Query OK, 0 rows affected (0.00 sec)

    mysql> create table t2 (a int);
    Query OK, 0 rows affected (0.03 sec)

    mysql> show create table t2;
    +——-+—————————————————————————————+
    | Table | Create Table |
    +——-+—————————————————————————————+
    | t2 | CREATE TABLE `t2` (
    `a` int(11) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
    +——-+—————————————————————————————+
    1 row in set (0.00 sec)

    Slides 30-35 Changes to variables in global scope not automatically applying to session scope does not “bite” in my opinion.

  3. says

    In my opinion, MySQL should have an ANY() function, that selects a record from a GROUP-BY equivalence class and then by default show ONLY_FULL_GROUP_BY behavior.

    The example in 76 then can be written as “SELECT ANY(country), count(*) FROM flags;” and make obvious what happens. NB: min() and max() are both valid implementations of ANY(), but are not always efficient.

Trackbacks

  1. What should a young developer know about MySql?…

    Use the InnoDB storage engine, not MyISAM. InnoDB is the default storage engine in MySQL 5.5 and later. MySQL needs to be tuned for your server and your workload, because the defaults configurations are too low. See my presentation on tuning InnoDB: ht…