What mysql_upgrade really does in MariaDB, Part II, Bugs and Missing Features

Both /proc sampling and bpftrace are cool topics to write about, but I should not forget my third upcoming talk at FOSDEM 2021 in less than 2 weeks, "Upgrading to a newer major version of MariaDB", that is mostly devoted to mysql_upgrade internals and is based on this blog post. Today I am going to provide some more background details and, probably for the first time, act as MariaDB entomologist and study some bug reports and feature requests related to mysql_upgrade (a.k.a. mariadb-upgrade in 10.5+) that were added over last 10 months or so.

I tried different ways to search for MariaDB JIRA issues related to mysql_upgrade. For examle, this is how I tried to find any bugs in mysql_upgrade closed over last two months (I recall there were few):

text ~ mysql_upgrade AND project = "MariaDB Server" AND status = Closed AND createdDate >= "2020-12-01" ORDER BY createdDate DESC

I've got a list of 7 reports, with 2 relevant bugs that are already fixed:

• MDEV-24566 - "mysql_upgrade failed with "('mariadb.sys'@'localhost') does not exist" and mariadb 10.4/10.5 on docker". This is fixed in current docker images at https://hub.docker.com/r/mariadb/server. The problem was related to the Docker image only, and mysql_upgrade was actually affected by the initial database content there.
  
• MDEV-24452 - "ALTER TABLE event take infinite time which for example breaks mysql_upgrade". Now this was a real blocker bug in recent 10.5. If any event managed to start before you executed mysql_upgrade, the utility and proper upgrade process was blocked. Good to see this fixed in upcoming 10.5.9.

So, Monty really fixes related bugs when they are reported, as promised. Let's consider now the following query, still not closed issues reported since April 1, 2020:

text ~ "mysql_upgrade" AND project = "MariaDB Server" AND status != Closed AND createdDate >= "2020-04-01" ORDER BY createdDate DESC

I checked then one by one (as they may be related to upgrade process but not to mysql_upgrade specifically) and placed into two lists, bugs and feature requests. Let me start with tasks (feature requests), so you know what was kind of missing by design or has to be done differently:

• MDEV-24586 - "remove scripts/mysql_to_mariadb.sql". Actually proper logic is implemented in scripts/mysql_system_tables_fix.sql that forms part of mysql_upgrade, so separate script is no longer needed.
• MDEV-24540 - "Detect incompatible MySQL virtual columns and report in error log." MariaDB does not support migration from MySQL-generated physical database tables containing virtual columns, and produces column mismatch errors, failures in mysql_upgrade etc. It would be great to have proper error messages from mysql_upgrade in this case, explaining the real problem and possible solutions (dump, drop and reload or whatever).
• MDEV-24453 - "mysql_upgrade does not honor --verbose parameter". It is not passed to other binaries called and this may make debugging upgrade issues more complex.
• MDEV-24316 - "cross upgrade from MySQL - have precheck tool". According to the reporter, Daniel Black, the goal would be to check on a database/global scale looking at tables, at features used, at settings, at character sets in table and determine the "migratablilty" of a given MySQL instance. I voted for this feature!
• MDEV-24093 - "Detect during mysql_upgrade if type_mysql_json.so is needed and load it". After MDEV-18323, MYSQL_JSON type is available as a dynamically loadable plugin. To make mysql_upgrade runs seamlessly we need to make sure it is loaded appropriately and unloaded when done with upgrade). This is already in review, so will be implemented really soon.
• MDEV-23962 - "Remove arc directory support". I think only Monty (bug reporter) knows what is this about. I don't :)
• MDEV-23008 - "store mysql_upgrade version info in system table instead of local file". One of the really important feature requests from my colleague since 2005, Hartmut Holzgraefe.
• MDEV-22357 - "Clearing InnoDB autoincrement counter when upgrading from MariaDB < 10.2.4". CHECK TABLE ... FOR UPGRADE should work differently for InnoDB tables, for mysql_upgrade to work properly.
• MDEV-22323 - "Upgrading MariaDB". This is the "umbrella" task to cover all problematic  cases of MySQL to MariaDB, Percona Server to MariaDB and minor MariaDB server upgrades.
  
• MDEV-22322 - "Percona Server -> Mariadb Upgrades". Summary of all the related issues. See MDEV-22679 etc.
  

Now back to more or less serious bugs that are still waiting for the fix:

• MDEV-24579 - "Error table->get_ref_count() after update to 10.5.8". It seems DDL executed on mysql.* tables with InnoDB persistent statistics (like those executed by mysql_upgrade!) may cause problems for concurrent queries (up to assertion failure in this case). So we either should remove those tables (I wish!) or do something with mysql_upgrade, or (IMHO even better) do not let users connect and execute queries while mysql_upgrade is running, like MySQL 8 does when the server is started for the first time on older datadir and performs upgrade. Take care in the meantime...
• MDEV-23652 - "Assertion failures upon reading InnoDB system table after normal upgrade from 10.2". Now this is a real bug :) Assertion failure during mysql_upgrade, this is surely something to fix!
• MDEV-23636 - "mysql_upgrade [ERROR] InnoDB: Fetch of persistent statistics requested for table". I am not sure what's going on here, and why.
• MDEV-23392 - "main.mysql_upgrade_view failed in buildbot with another wrong result". MTR test case failures is something to care about.
• MDEV-22683 - "mysql_upgrade misses some changes to mysql schema". Over ttime and different versions, some structures in mysql schema get changed, but not all the changes make it to the scripts executed by mysql_ugrade. As a result a schema freshly created by mysql_install_db on a versions 10.x.y differs from a schema created on an earlier version and upgraded to 10.x.y by mysql_ugrade. The real diffs are listed, per version, in this bug report from Elena Stepanova.
• MDEV-22655 - "CHECK TABLE ... FOR UPGRADE fails to report old datetime format". That's my favorite, unfortunately. Makes running mysql_upgrade useless for some cases of upgrade from pre-5.6 MySQL versions and leads to problems for tables partioned by datetime etc columns. See also MDEV-24499 - "Server upgrade causes compound index and related query to fail.".
• MDEV-22645 - "default_role gets removed when migrating from 10.1 to 10.4". May have something to do with mydumper/myloader used, but still a problem.
• MDEV-22482 - "pam v2: mysql_upgrade doesn't fix the ownership/privileges of auth_pam_tool". No comments.
• MDEV-22477 - "mysql_upgrade fails with sql_notes=OFF". mysql_ugrade, or, more exactly mysql_system_tables.sql, uses @@warning_count variable in the upgrade logic. The variable, in turn, depends on the value of sql_notes. When it is OFF, @@warnings_count is not incremented, and mysql_upgrade doesn't work as expected.

After Monty's post in April 2020 many new mysql_upgrade bugs were reported, and some were already fixed. So, we are on the way...

* * *

To summarize:

1. There are still some bugs and missing features in mysql_ugrade.
2. MariaDB actively works on fixing them, as once promised by Monty.
3. Check the lists in this blog post if you plan to upgrade to recent MariaDB 10.x.y versions, carefully.
4. Please, report any problem with mysql_upgrade or upgrades in general to our JIRA.

Understanding Status of MariaDB Server JIRA Issues

In my previous blog post on MariaDB's JIRA for MySQL users who are familiar with MySQL bugs database (but may be new to JIRA) I've presented some details about statuses that JIRA issues may have. There is no one to one correspondence with MySQL bug's statuses that I once described in details here. In case of MariaDB Server bugs ("JIRA issues") one may have to check not only "Status" field, but also "Resolution" filed and even "Labels" field to quickly understand what is the real status and what MariaDB engineers decided or are waiting for. So, I think some additional clarifications may help MySQL users who check or report MariaDB bugs as well.

Let me present details of this statuses correspondence in a simple table, where the first column contains MySQL's bug status, while 3 other columns contain the content of corresponding MariaDB Server JIRA issue's fields, "Status", "Resolution" and "Labels". There is also "Comment" column with some explanation on what else is usually done in JIRA issue when it gets this set of values defining its status or what this may mean in MySQL bugs database etc. Most important MySQL bug statuses are taken from this my post (there are more of them, but others are rarely used, especially when real work on bugs was moved into internal bugs database by Oracle, or were removed since that post as it happened to "To be fixed later").

MySQL Bug Status	MariaDB JIRA Status	MariaDB JIRA Resolution	MariaDB JIRA Label	Comment
Open	OPEN	Unresolved		Typical status for just reported bug
Closed	CLOSED	Fixed		You should see list of versions that got the fix in the Fix Version/s field
Duplicate	CLOSED	Duplicate		So, in MariaDB it's "closed as a duplicate"
Analyzing	OPEN	Unresolved		Usually bug is assigned when some engineer is working on it, including analysis stage
Verified	CONFIRMED	Unresolved		CONFIRMED bugs are usually assigned in JIRA while in MySQL "Verified" bugs are usually unassigned
Won't fix	CLOSED	Won't Fix		Usually remains assigned
Can't repeat	CLOSED	Cannot reproduce		Unlike in MySQL, usually means that both engineer and bug reporter are not able to reproduce this
No Feedback	CLOSED	Incomplete	need_feedback	As in MySQL, bug should stay with "need_feedback" label for some time before it's closed as incomplete
Need Feedback	OPEN	Unresolved	need_feedback	Usually in the last comment in the bug you can find out what kind of feedback is required. No automatic setting to "No Feedback" in 30 days
Not a Bug	CLOSED	Not a Bug
Unsupported	CLOSED	Won't Fix		There is no special "Unsupported" status in MariaDB. Most likely when there is a reason NOT to fix it's stated in the comment.

In the table above you can click on some links to see the list of MariaDB bugs with the status discussed in the table row. This is how I am going to use this post from now on, as a quick search starting point :) It will also be mentioned on one of slides of my upcoming FOSDEM 2019 talk.

MariaDB JIRA for MySQL DBAs

These days several kinds and forks of MySQL are widely used, and while I promised not to write about MySQL bugs till the end of 2018, I think it makes sense to try to explain basic details about bug reporting for at least one of vendors that use JIRA instances as a public bug tracking systems. I work for MariaDB Corporation and it would be natural for me to write about MariaDB's JIRA that I use every day.

As a side note, Percona also switched to JIRA some time ago, and many of the JIRA-specific details described below (that are different comparing to good old https://bugs.mysql.com/) apply to Percona bugs tracking system as well.

Why would MariaDB bugs be interesting to an average MySQL community member who does not use MariaDB at all most of the time? One of the reasons is that some MySQL bugs are also reported (as "upstream") to MariaDB and they may be fixed there well before they are fixed in MySQL. Consider MDEV-15953 - "Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir" (reported by Chris Calender) as an example. It was fixed in MariaDB 5 months ago, while corresponding Bug #78164 is still "Verified" and got no visible attention for more that 3 years. The fix is 12 rows added in two files (test case aside), so theoretically can be easily used to modify upstream MySQL by an interested and determined MySQL user who already compiles MySQL from the source code (for whatever reason), if the problem is important in their environment.

Another reason is related to the fact that work on new features of MariaDB server and connectors is performed in an open manner at all stages. You can see current plans, discussions and decision making on new features happening in real time, in JIRA. Existing problems (that often affect both MySQL and MariaDB) are presented and analyzed, and reading related comments may be useful to understand current limitations of MySQL and decide if at some stage switching to MariaDB or using some related patches may help in your production environment. There is no need to wait for some lab preview release. You can also add comments on design decisions and question them before it's too late. Great example of such a useful to read (for anyone interested in InnoDB) feature request and work in progress is MDEV-11424 - "Instant ALTER TABLE of failure-free record format changes".

Yet another reason to use MariaDB JIRA and follow some bug reports and feature requests there is to find some insights on how MySQL, its components (like optimizer) and storage engines (like InnoDB) really work. Consider my Bug #82127 - "Deadlock with 3 concurrent DELETEs by UNIQUE key". This bug was originally reported to Percona as lp:1598822 (as it was first noticed with Percona's XtraDB emgine) and ended up in their JIRA as PS-3479 (still "New"). In MySQL bugs database it got "Verified" after some discussions. Eventually I gave up waiting for "upstream" to make any progress on it and reported it as MDEV-10962. In that MariaDB bug report you can find explanations of the behavior noticed, multiple comments and ideas on the root case and on how to improve locking behavior in this case, links to other related bugs etc. It's a useful reading. Moreover, we see that there are plans to improve/fix this in MariaDB 10.4.

I also like to check some problematic and interesting test cases, no matter in what bugs database it was reported, on both MariaDB Server, Percona Server and MySQL Server, as long as it's about some common features. But may be it's so because I work with all these as a support engineer.

Anyway, one day following MariaDB Server bugs may help some MySQL DBA to do the job better. So, I suggest all MySQL users to check MariaDB's JIRA from time to time. Some basic details about the differences comparing to MySQL's bugs database are presented below.

First thing to notice in case of MariaDB's JIRA is a domain name. It's jira.mariadb.org, so bug tracking system formally "belongs" to MariaDB Foundation - non-profit entity that supports continuity and open collaboration in the MariaDB ecosystem. Both MariaDB Foundation employees, MariaDB Corporation employees, developers working for partners (like Codership) and community members (like Olivier Bertrand, author of CONNECT storage engine I had written about here) work on source code (and bugs processing and fixing) together, at GitHub. Different users have different roles and privileges in JIRA, surely. But there is no other, "internal" bugs database in MariaDB Corporation. All work or bugs and features, time reporting, code review process, as well as release planning happen (or at least is visible) in an open manner, in JIRA.

Even if you do not have JIRA account, you still can see Jira Road Map, release plans and statuses. You can see all public comments and history of changes for each bug. If you create and log in into your account (this is needed to report new bugs, vote for them or watch them and get email notifications about any changes, obviously) you'll see also more details on bugs, like links to GitHub commits and pull requests related to the bug.

Unlike MySQL bugs database where bugs are split into "Categories" (where both "MySQL Server: Information schema" and "MySQL Workbench" are categories more or less of the same level) but are numbered sequentially over all categories, JIRA instances usually support "Projects", with separate "name" and sequential numbering of bugs per project.

At the moment there are 17 or so projects in MariaDB JIRA, of them the following public ones are most interesting for MySQL community users, I think:

• MariaDB Server (MDEV)
• MariaDB Connector/C (CONC)
• MariaDB Connector/J (CONJ)
• MariaDB Connector/ODBC (ODBC)
• MariaDB Connector/.NET (CONET) 
• MariaDB Connector/node.js (CONJS) 

Let's consider one MariaDB Server bug for example:

Unlike in MySQL bugs database, JIRA issues have "Type". For our case it's important that feature requests usually end up as "Task" vs "Bug" as a type for a bug. Some projects in MariaDB JIRA may also support a separate "New Feature" type to differentiate features from tasks not related to creating new code. In MySQL separate severity (S4, "Feature request") is used.

MariaDB JIRA issues have priorities from the following list:

• Trivial
• Minor
• Major
• Critical
• Blocker

By default MariaDB bugs are filed with intermediate, "Major" priority. Priority may be changed by the bug reporter or by JIRA users (mostly developers) who work on the bug, it often changes with time (priority may increase if more users are affected, or if the fix does not happen for long enough time etc, or decrease when the problem can be workarounded somehow for affected users). Usually a bug with "Blocker" priority means there should be no next minor release for any major version listed in "Fix Version/s" without the fix.

There are many fields in MySQL bugs database to define priority of the fix (including "Priority" itself), but only "Severity" is visible to public. Usually "Severity" of the MySQL bug does NOT change with time (if only before it's "Verified").

It is normal to list all/many versions affected by the bug in JIRA in "Affected Version/s". If the bug is fixed, in "Fix Version/s" you can find the exact list of all minor MariaDB Server versions that got the fix.

Each JIRA issue has a "Status" and "Resolution". In MySQL bugs database there is just "Status" for both. Valid statuses are:

• OPEN - this is usually a bug that is just reported or is not yet in the process of fixing.
• CONFIRMED - this status means that some developer checked bug report and confirmed it's really a bug and it's clear how to reproduce it based on the information already present in the report. More or less this status matches "Verified" MySQL bug. But unlike in MySQL, even "Open" bug may be assigned to a developer to further work on it.
• CLOSED - the bug is resolved somehow. See the content of the "Resolution" filed for details on how it was resolved.
• STALLED - this is a real bug and some work on it was performed, but nobody actively works on it now.
• IN PROGRESS - assignee is currently working on the fix for the bug.
• IN REVIEW - assignee is currently reviewing the fix for the bug.

The following values are possible for "Resolution" field:

• Unresolved - every bug that is not "CLOSED" is "Unresolved".
• Fixed - every bug that was fixed with some change to the source code. If you log in to JIRA you should be able to find links to GitHub commit(s) with the fix in the "Fixed" JIRA issue.
• Won't Fix - the problem is real, but it was decided not to fix it (as it's expected or may be too hard to fix). Consider my MDEV-15213 - "UPDATEs are slow after instant ADD COLUMN" as one of examples.
• Duplicate - there is another bug report about the same problem. You can find link to it in the JIRA issue.
• Incomplete - there is no way to reproduce or understand the problem based on the information provided. See MDEV-17808 for example.
• Cannot Reproduce - bug reporter himself can not reproduce the problem any more, even after following the same steps that caused the problem before. See MDEV-17667 for example.
• Not a Bug - the problem described is not a result of any bug. Everything works as designed and probably some misunderstanding caused bug reporter to think it was a bug. See MDEV-17790 as a typical example.
• Done - this is used for completed tasks (like MDEV-17886) or bugs related to some 3rd party stored engine where the fix is done, but it's up to MariaDB to merge/use fixed version of the engine (like MDEV-17212).
• Won't Do - it was decided NOT to do the task. See MDEV-16418 as one of examples.

In MySQL there are separate bug statuses for (most of) these. There are some tiny differences for the way some statuses like "Cannot reproduce" are applied by those who process bugs in MySQL vs MariaDB though.

Explanations above should be enough for any MySQL bugs database user to start using MariaDB's JIRA efficiently, I think. But I am open to any followup questions and I am considering separate blog posts explaining the life cycle of a MariaDB Server bug and some tips on efficient search in MariaDB JIRA.