I attended Ronald Bradford's MySQL Sunday presentation MySQL Idiosyncrasies that Bite and this post is a summary of what I got out of it. Please keep in mind that I am more familiar with the Oracle database and the PostgreSQL database than I am with MySQL, so any errors in my summary are probably my own misinterpretations of what Bradford stated. The last time I used MySQL was in my Ruby/Rails days.
Bradford's presentation focused on idiosyncrasies of the MySQL database, especially as contrasted to the Oracle database behavior. One of his first slides was titled "Oracle != MySQL" and that was a pretty concise description of his presentation, that had many more details. On the slide titled "Oracle != MySQL," Bradford listed five bullets describing overall explanations why they are different: age, development philosophy, target audience, developer practices, and installed versions.
Bradford stated that the storage engine concept is likely the most foreign MySQL concept for Oracle DBAs. He recommended using InnoDB as the storage engine, but MyISAM is the default storage engine for MySQL until InnoDB takes that role with MySQL 5.5.
One of Bradford's slides listed five RDBMS characteristics (data integrity, transactions, ACID, data security, and ANSI SQL) that are not supported in MySQL by default. Bradford outlines several options that should be specified by setting SQL_MODE appropriately. These included STRICT_ALL_TABLES (disallow out of range values without silent truncation), NO_ZERO_DATE (disallows zero dates), NO_ZERO_IN_DATE, NO_ENGINE_SUBSTITUTION, and ONLY_FULL_GROUP_BY. On one his final slides, Bradford references http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html for a list of available SQL_MODE options and includes some options to avoid (such as PIPES_AS_CONCAT and POSTGRESQL) in red. Bradford warned that SQL_MODE settings should be set correctly initially rather than once in production because changing them too late can lead to less than desirable results.
One potential gotcha when first using MySQL is the misuse of variable scopes. The default scope is GLOBAL starting with 5.0.2, but was SESSION before that version. It was also noted in this presentation that Oracle's READ COMMITTED is different than MySQL's READ-COMMITTED. InnoDB's current default is REPEATABLE-READ. Bradford's main point here is that a DBA should not use MySQL's READ-COMMITTED simply because he or she is used to using Oracle's READ COMMITTED. Although both Oracle and MySQL have isolation levels, the various levels are not necessarily exactly the same.
Bradford warned of a couple other common mistakes he sees people new to MySQL make. One is not fully understanding case sensitivity dependent on underlying platforms. A second mistake is using the command GRANT ALL ON *.* TO user@’%’ which grants even the SUPER privilege. As is common in presentations given by DBAs, the anecdotal story of this gone wrong involved a developer who should not have been allowed to cause havoc if this permission had not been accidentally set.
Although I'm not very familiar with MySQL, this presentation was developed and delivered in such a way that I was able to stay involved with it and understand the majority of Bradford's points and examples. Bradford has presented this presentation before and those slides are available for download. Although I don't use MySQL regularly, I found the presentation to be interesting and informative and expect it to be useful when I do use MySQL in the future.
No comments:
Post a Comment