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.
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.
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.
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.
Nice review by Dustin Marx of my presentation at MySQL Sunday – Oracle Open World 2010
http://www.javaworld.com/community/node/5159