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.
MySQL Idiosyncrasies That Bite
MySQL Idiosyncrasies That Bite
View more presentations from Ronald Bradford.
Roland Bouman 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.
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.
Isotopp 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.
ronald says
Nice review by Dustin Marx of my presentation at MySQL Sunday – Oracle Open World 2010
http://www.javaworld.com/community/node/5159