Month: March 2014


MySQL versus Firebird

Firebird is an open-source DBMS with a long history and good SQL support. Although I measured Firebird’s importance as smaller than MySQL’s or MariaDB’s, it exists, and might grow a bit when Firebird becomes the default LibreOffice DBMS.

I decided to compare the current versions of MySQL 5.6 and Firebird SQL 2.5. I only looked at features that end users can see, without benchmarking. Preparing for the comparison was easy. I looked at the instructions for downloading Firebird with Ubuntu and within 15 minutes I was entering SQL statements. The documentation is disorganized but when in doubt I could guess what to do, since the Firebird folks care about standards.

I’ll organize the discussion according to the ANSI/ISO feature list. I’ll skip the features which both products support equally, which means I’ll skip the great majority, because both products are very similar. Then: For every feature where MySQL is better, MySQL gets a point. For every feature where Firebird is better, Firebird gets a point. The only way to be objective is to be arbitrary.

firebirddolphin

Mandatory Features

Feature Firebird Point MySQL Point Explanation
E011-03 DECIMAL and NUMERIC types 1 Firebird maximum number of digits = 18. MySQL maximum = 65.
E021-02 CHARACTER VARYING data type 1 Firebird maximum VARCHAR length = 32767. MySQL maximum = 65535.
E021-07 Character concatenation 1 Firebird concatenation operator is ||. MySQL has CONCAT() by default.
E031-01 Delimited identifiers 1 Firebird delimiter “” means case-sensitive. MySQL has no viable equivalent.
E041-04 Basic foreign key 1 In Firebird one can use syntax variants that MySQL/InnoDB ignores.
E101-03 Searched UPDATE statement 1 Firebird has fixed a small flaw. MySQL still has the flaw.
E141-08 CHECK clause 1 MySQL doesn’t have this.
F031-03 GRANT statement 1 1 Only Firebird can grant to PUBLIC. But only MySQL can grant TRIGGER. One point each.
F051-03 TIMESTAMP data type 1 Firebird can have 4 digits for seconds precision, MySQL can have 6.
T321-01 User-defined SQL functions 1 Firebird only has procedures, and they’re PLSQL syntax. MySQL has standard syntax.
9075-11 information_schema 1 Firebird doesn’t have this.

firebirddolphin

Optional Features

Feature Firebird Point MySQL Point Explanation
F121-01 GET DIAGNOSTICS 1 Firebird doesn’t have this.
F200 TRUNCATE 1 Firebird doesn’t have this.
F201 CAST function 1 In Firebird I can cast to (among other data types) VARCHAR, SMALLINT, INT, BIGINT, NUMERIC, TIMESTAMP. In MySQL, I can’t.
F221 Explicit defaults 1 Both products allow DEFAULT in CREATE statements, but only MySQL allows DEFAULT in UPDATE or INSERT statements.
F251 Domain support 1 MySQL doesn’t have this.
F312 Merge statement 1 MySQL doesn’t have a MERGE statement, although it does have near equivalents.
F391 Long identifiers 1 Firebird maximum length = 31. MySQL maximum length = 64. Both are sub-standard.
F401-02 Full outer join 1 MySQL doesn’t have this.
F461 Named character sets 1 See Note #1.
F531 Temporary tables 1 Firebird allows CREATE GLOBAL TEMPORARY. MySQL has temporary tables in a non-standard way.
F690 Collation support 1 See Note #2.
T021 BINARY and VARBINARY data types 1 Firebird doesn’t have this, although it does allow specifying a character set as OCTETS,which is comparable to MySQL’s “binary”.
T121 WITH (excluding RECURSIVE) in query 1 Firebird recently added WITH support. MySQL doesn’t have it. Incidental note: misinformation has been spread about MySQL’s history for this feature, for example the first comment on this blog post. The fact is that in 2004 MySQL decided to support CONNECT BY, not WITH. Then it didn’t implement it.
T131 Recursive query 1 MySQL doesn’t have this.
T141 SIMILAR predicate 1 MySQL doesn’t have this.
T171 LIKE clause in table definition 1 Firebird doesn’t have this.
T172 AS clause in table definition 1 Firebird doesn’t have this.
T176 Sequence generator support 1 Firebird only supports a “CREATE SEQUENCE sequence-name” statement, but that’s enough for a point.
T211 Basic trigger capability See the table in an earlier post but MySQL has more standard CREATE TRIGGER syntax. Can’t decide.
T281 SELECT privilege with column granularity 1 Firebird doesn’t have this.
T331 Basic roles 1 Firebird has 50% support. MySQL has nothing. MariaDB roles are beta.

Note #1: Re character sets: Ignoring duplicates, alias names, and slight variations: Firebird multi-byte character sets are gbk, sjis, utf-8, big5, gb2312, eucjp, ksc 5601, gb18030. MySQL multi-byte character sets are gbk, sjis, utf-8, big5, gb2312, eucjp, euckr, utf-16, utf-32. As I put it in an earlier blog post, “The UTF-8 world is not enough”. MySQL has more, so it gets the point.

Note #2: Re collations: Although Firebird deserves special mention for supporting a slightly later version of the Unicode Collation Algorithm, the only multi-byte character set for which Firebird has many collations is UTF-8. Firebird has only four collations for UTF-8: ucs_basic (code point order), and unicode / unicode_ci / unicode_ci_ai (variations of UCA order with and without case sensitivity or accent sensitivity). MySQL has additionally: Croatian, Czech, Danish, Estonian, German, Hungarian, Iceland, Latvian, Lithuanian, Persian, Polish, Romanian, Sinhala, Slovak, Slovenian, Spanish, Swedish, Turkish, Vietnamese. Incidental side note: guess whom the Firebird manual points to for extra documentation about its character sets? None other than the guy who coded MySQL’s collations, a certain A. Barkov, formerly of MySQL, now of MariaDB.

Total points for Firebird: 16. Total points for MySQL: 16.

Update: in an earlier edition of this post, I broke the tie by giving Firebird a delay-of-game penalty on the basis that the makers started talking about version 3.0 in 2011, and version 3.0 is still alpha, and I concluded “MySQL wins by a score of 16 to 16”. That wasn’t serious. The fair way to break the tie is to decide which features should have more weight.

Deprecating MySQL Features

If you haven’t read the Wikipedia article on deprecation, you might not know it’s from the Latin word deprecare, meaning “to ward off (a disaster) by prayer.” That pretty well describes the quandary for any software product that has a big established user base. Is it better to keep junk features, in case removing them will cause disasters for the old users? Or is it better to remove junk features, to avoid ridicule and to make the manual slimmer for the new users?

Historically MySQL has deprecated with caution, and usually MySQL has followed these steps:
(1) In version X, for any SQL statement that appears to use the feature, return a warning that the feature “has been deprecated and may be removed in a future release”.
(2) Also in version X, put a notification in the manual. Notifications have a regular pattern so they’re easy to find by looking in the “What’s new” section, such as http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html, and http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html.
Alternatively, one can look for the “nutshell” pages, such as https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html and https://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html. Or, in googlespeak, site:dev.mysql.com/doc “what’s new” “deprecated”. Notice that I am pointing only to the MySQL manual, because MariaDB does not appear to have an equivalent (the MariaDB “deprecation policy” page only talks about supported platforms).
(3) In version X+1 or X+2, actually remove the feature and start throwing syntax errors.

My all-time favourite deprecation was of the TIMESTAMP(M) data type before version 4.1. The M stood for “display width”, for example, with TIMESTAMP(6) you’d only see the first few digits. We realized that was in conflict with the ANSI/ISO standard, so it became deprecated in 4.x, became a syntax error in 5.x, and then — years after the original warning — the syntax became legal again with a different meaning, so now the 6 in TIMESTAMP(6) means “fractional seconds”. Because there was a period when the syntax was simply illegal, it was practically impossible to move an old application to the new format without noticing anything.

These are my nominations for favourite deprecations in 5.6 and 5.7.

SHOW AUTHORS.
SHOW AUTHORS was an “Easter egg”. Naturally I reported it as such at the time because Easter eggs are more than bugs, they are vandalism. Eventually the feature got documented, but the smell of rotten eggs was in my nose for years afterward, so I’m relieved it’s gone.

INFORMATION_SCHEMA.PROFILING
Faithful readers of my blog posts will of course recall me saying in 2008

A bit of a caveat: I was totally against adding this feature, and some of my bias might show. But I’m not trying to argue about it. I do believe that once we add a feature we should explain it, and all I’m trying to do is explain here.

… which had nothing to do with the quality of the user contribution. People who know me as the original crusader for PERFORMANCE_SCHEMA might guess that I saw this feature as out of step.

INSERT DELAYED
INSERT DELAYED wasn’t junk per se. The problem with little non-standard performance-enhancing niblets is that they’re always non-orthogonal. People sense that. So the bug report / feature request for — why not? — “UPDATE DELAYED” got about 15 votes. Adding the EVENTS feature didn’t quell the protesters, because any rational person could see that, if INSERT DELAYED was okay syntax, UPDATED DELAYED should also be. (This makes two posts in a row where I’m railing about lack of orthogonality — I brought it up in my blog post last week about MariaDB’s DELETE … RETURNING feature. By the way, I updated that post to show what decent standard-fearing folk would do instead of copying PostgreSQL.) So, to stop the protests that there’s a precedent, get rid of the precedent. Well done.

What these deprecations are telling me is that somebody at MySQL at last has the authority and determination to clean the garage. The product is not just getting new good features, it’s losing old bad ones.