Happy 25th Birthday MySQL!

Today, May 23-rd 2020, MySQL turns 25 years old. I first got acquainted with it during my university years as I was searching for a free and open RDBMS system to learn and experiment with SQL. And I used it ever since then. I thus followed the evolvement of "The world's most popular open source database" over the past about 20 years since 3.2x versions. It's a big anniversary. So what does MySQL mean for me?

• First and foremost, MySQL is the relational (and also non-relational) database of choice for both my personal and professional projects. For me it's easier to think new database schemes in the terms of MySQL and architect new systems on top of MySQL as a database system;
• MySQL is getting better and better with the years, which keeps me interested. I still remember the arrival of:
• 4.0 (with support for unions, etc.),
• then 4.1 (sub-queries and prepared statements, etc.),
• then 5.0 (stored routines, triggers and views, distributed transactions, etc.),
• then 5.1 (table partitioning, XML and event scheduler, etc.),
• then 5.5 (SIGNAL and RESIGNAL, PERFORMANCE_SCHEMA),
• then 5.6 (Full-text for InnoDB, etc.),
• then 5.7 (JSON, named triggers, generated columns, optimizer hints, etc.),
• and more recently 8.0 (CTEs, window functions, regular expressions improvements, GROUPING function, document store, CHECK constraints, LATERAL, functional key parts and defaults and many, many more);
• I'm building MySQL server (and other products) for Slackware Linux since 2005, because at first I needed newer versions than the ones provided by the official repository and then, because Slackware switched to MariaDB in 2013, but I choose to stick with MySQL. This doesn't mean I do not use MariaDB at all and in some aspects I believe it's ahead;
• I'm reporting bugs for MySQL and related products, so I could get problems resolved (see the story of some of my bug reports);
• I'm blogging about MySQL for the past 2 years since the release of MySQL 8.0, which helps me get familiar with the new features;
• I'm also presenting about MySQL from time to time, which helps me engage with the community, learn from and exchange ideas with the more experienced then me;
• Last, but definitely not least, there is a great and very strong community around MySQL, with which I got the chance to engage more in the past 3 years. This is what makes a project like MySQL strong over so many years.

To put it simply for me MySQL is a lot more than a database. MySQL has been with me for a significant part of my life so far and it makes me happy to see the database continues to evolve. Still I believe that improved SQL standard support and new development features should have arrived earlier, but better later than never. Also the development could be more open, but I'm confident that the future is open and bright.

Here's to the next 25 years and beyond! Happy birthday MySQL! Cheers to all that use and love MySQL!

MySQL 8.0.20 released

On Monday (April 27-th) Oracle released MySQL 8.0.20 Server and related products. You could check the release notes and official blog post. The focus of this release is binary log transaction compression to save disk space and network bandwidth, so there are no much new features for developers. Still here are the tings I noticed in the fore mentioned resources.

• New index-level optimizer hints for existing index hints (mind the difference!) that include the following.
• GROUP_INDEX/NO_GROUP_INDEX as equivalent to FORCE INDEX FOR GROUP BY/IGNORE INDEX FOR GROUP BY index hints;
• INDEX/NO_INDEX as equivalent to FORCE INDEX/IGNORE INDEX index hints;
• JOIN_INDEX/NO_JOIN_INDEX as equivalent to FORCE INDEX FOR JOIN/IGNORE INDEX FOR JOIN index hints;
• ORDER_INDEX/NO_ORDER_INDEX as equivalent to FORCE INDEX FOR ORDER BY/IGNORE INDEX FOR ORDER BY index hints;
• Hash join optimizations now supports also outer, anti and semi join types, which means that for the hash join to work it is no longer necessary to have at least one equi-join condition.
• There are several improvements to EXPLAIN statement:
• with FORMAT=TREE includes new inversion information for windowing functions. Unfortunately, I found no further information on this;
• with FORMAT=TREE provides more information about evaluated window functions matching that supplied for regular aggregates. You should now see lines like "Window aggregate with buffering";
• for ANALYZE there is now the possibility to stop the execution with KILL QUERY or CTRL+C key combination on the command line;
• ANALYZE should now support the FORMAT option with the only possible value of TREE as reflected in the release notes and the manual, but for me it results in error.
  
  mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT 1+1;
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORMAT=TREE SELECT 1+1' at line 1
  
  Anyway, I hope this is the first step in implementing support for this command in Workbench in future (see bug 97282).

There are of course several deprecations that include:

• Some improvements and deprecations to the SQL syntax related to INTO and UNION clauses (see SQL Syntax Notes from the release notes);
• VALUES function for INSERT … ON DUPLICATE KEY UPDATE statement is now deprecated as I guessed in my post for MySQL 8.0.19 release;
• ON ERROR before ON EMPTY clauses for column definitions in JSON_TABLE function;
• The mentioned above index hints.

Nothing changed in MySQL 8.0.20 Workbench, but there is still release and release notes.

In waiting for the next release you could update your code to avoid surprises from removed previously deprecated features.