There are many new features in MariaDB 5.3. I’m looking forward to many of them, but one of the ones I’m most excited about is Progress Reporting.

It’s a fact of life in the database world that some commands take longer to run than others. Commands like ALTER TABLE, LOAD DATA INFILE, and adding and dropping an index simply take time to run, depending (of course) on your data and schema. I always have hated having to wait for those commands to run with no indication of how much progress has been made or how much is left to do. All of that changes with the upcoming release of MariaDB 5.3.

In MariaDB 5.3 there is a new “Progress” column in the output of SHOW PROCESSLIST (this can be turned off) which shows the progress, and there are three new columns (STAGE, MAX_STAGE, and PROGRESS_DONE) in INFORMATION_SCHEMA.PROCESSLIST which allow you to see which process stage you are in, the number of stages, and how much of the current stage is completed. Additionally, the mysqld server sends progress reports to clients at a specified interval (the default is every 5 seconds, 0 will disable it). In the MariaDB 5.3 mysql command-line client, these progress reports look like this:

Currently, the following commands can send progress report messages to the client:

  • ALTER TABLE
  • ADD INDEX
  • DROP INDEX
  • LOAD DATA INFILE (not LOAD DATA LOCAL INFILE, as in that case we don’t know the size of the file).

Some Aria storage engine operations also support progress messages:

  • CHECK TABLE
  • REPAIR TABLE
  • ANALYZE TABLE
  • OPTIMIZE TABLE

Third-party clients and storage engines can easily add support for receiving and sending (respectively) these progress reports. Details are on the Progress Reporting page in the AskMonty Knowledgebase. One application which has been updated to support progress reports (in addition to the MariaDB mysql command-line client) is the ‘mytop‘ program, which will be included in source and binary packages of MariaDB 5.3.

Progress reporting doesn’t speed up ALTER TABLE or any of the other supported commands, but at least I now have some visibility into them, and that is “a very good thing”.

In the #maria IRC channel the other day I fielded a question someone had about a DATETIME column where they wanted to SELECT by the year. The answer (which is probably obvious to many of you) is to use the YEAR() function like so:

(The above assumes the table is named “t1” and the DATETIME column is named “d“.)

In my reply I provided a link to the Date and Time Functions section of the AskMonty Knowledgebase, but when I looked at the entry for the YEAR() function, I noticed that the example given (which originated from the file scripts/fill_help_tables.sql which is found in the MySQL and MariaDB source) was not very helpful:

The above is certainly a valid use of the YEAR() function, but it’s not a real-world use (at least, I don’t know why anyone would use the above SELECT statement in a real application). So I added an example which I feel is more useful, especially to someone unfamiliar with using SQL functions:

Looking at the entries for other functions in both the Date and Time functions section and elsewhere there is a pattern of examples that are useful, but — like the original YEAR() example — useless in the real world.

I have now added better examples to several of the date and time functions but they can probably be improved even more and there are many more entries in the Knowledgebase that would also benefit from better examples. The good news is adding examples to Knowledgebase entries is very easy to do. The bad news is that if I think of all of the examples on my own they will be similar and probably boring, so I’d like to ask for some help.

If you’ve ever wanted to contribute to MariaDB, but didn’t know where to begin, I have a suggestion: Look under the Functions and Operators section of the AskMonty Knowledgebase, find a function or two in need of better examples, and add some (either directly or via a comment). Don’t worry if you are unfamiliar with editing the Knowledgebase. I can clean things up and make your examples look pretty after the fact. Just make sure your awesome new examples actually work. :)

Thanks!

Baby fur seal, South Georgia

As Hakan mentioned previously, the full text of SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, is now in the AskMonty.org Knowledgebase. Importing the text and formatting it for the Knowledgebase was a major project and I’m glad that it’s done.

Having the full text of this book freely available is a great thing for anyone who uses SQL because the book is about the SQL-99 standard and not about any particular database implementation. They do talk about different implementations, but those sections are clearly marked as such, and serve as examples of  how some databases implement (or diverge from) the standard.

The question now is: What’s next? Both for the SQL-99 section, and for the Knowledgebase in general?

First, we want to integrate the SQL-99 text into the rest of the Knowledgebase. At present, it is largely isolated from the other main section (the MariaDB section). We’ll start by cross-linking between SQL-99 and MariaDB articles. Going forward we will incorporate the SQL-99 text into new and existing MariaDB-related articles, where appropriate.

Beyond SQL-99, we also want to grow the Knowledgebase into a repository of all sorts of database-related information. To that end, we’re on the lookout for more ready-made content to add to the Knowledgebase. If you own or control the rights to a database-related book, and would like to see it added, please contact us. We’re even willing to purchase the rights, if necessary. Bonus points if the book is directly about MariaDB or MySQL, but we’re also interested in other databases.

Speaking of the Knowledgebase, we just rolled out some updates to make it easier for anyone who wants to to participate in creating and editing content to get started. If you’d like to help out, please do! If you have questions on how to get started you can almost always find me and other Knowledgebase admins in #maria on Freenode (my Freenode nick is dbart) or you can join the maria-docs mailing list and ask there.