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
View more presentations from Ronald Bradford.
Tags: data integrity, Kaleidoscope, MySQL, odtug, syntax, transactions
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
[...] to MySQL STRICT mode. It's one word on one slide really. Ronald Bradford has a great talk called MySQL idiosyncracies that bite. This one slide in my Drizzle talk pretty much encapsulates all of the issues Ronald goes through [...]
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…