So I can not “clone and upgrade” with native MySQL cloning the same way. That’s unfortunate, though not the end of the world.

The mantra I have always used when upgrading has been “clone and upgrade” and that has been the way forward, allowing me to get testing in low risk environments these new versions and gradually update more systems until everything except the master is done, after which the master gets the same treatment. That has worked well. I even promoted it a few years back at OOW when moving to MySQL 5.7 from MySQL 5.6. Clearly once you move to a new major version on a master going back gets much more problematic, but that was always considered a once in a few years issue. With minor version changes compliance issues may come into play requiring you to upgrade much more frequently than this so not being able to go back if you find a problem is a greater concern as Oracle is releasing new minor versions every few months.

I remember discussing the “you can only clone from the exact same version” and the “you can not downgrade issue” with one of the senior Oracle managers at Percona Live in Amsterdam late last year and he confirmed the no downgrade path was intentional. I said I did not think that it was a good idea. For production systems you ALWAYS want a way out and this seems to prevent that which I think is very dangerous. He did not seem to think it was that important.

Right now with 5.7 if I find a bug in 5.7.29 that I don’t notice until I am using it on a master (and all slaves) nothing prevents me from downgrading a slave to 5.7.28 and swapping it with the master and then potentially downgrading all slaves back to 5.7.28 leaving me with a “fully working 5.7.28 cluster”. Clearly I do not expect that sort of thing to happen but it could. The load I see on masters is quite different to the load seen on slaves and query profiles change quite a bit too, so some unlucky combination of events could allow a bug to reach the hole cluster which would not be good. I run multiple clusters so that just adds to the chances I might miss some magic evil combination that bites me.

Returning to 8.0: let’s remember where we are. 8.0 has had many “small improvements” since going GA and that is very, very good: we get product improvements faster and new functionalities more quickly and that is really appreciated. However, I think that perhaps those who have been doing the developing have not been aware of the consequences of non-backwards compatible changes, or not consulted with enough users who might get bitten by such an event, even if it may be very unlikely.

I tend to think that what would be better is to have a MySQL 8.0 base and new features should be added in a way where either they can be turned off (even if on by default) or an older version of 8.0 can run on the DD as it is defined and it will simply ignore any new I_S data structures and the new code that uses new I_S data structures will not modify the existing ones in a backwards incompatible way.

So this is a bit more tricky but not impossible to do. Part of the problem here is that probably where this would be visible is in the _undocumented_ DD tables. The table contents are not exposed though we know there are ways to see them. (I’d like them to be visible all the time but I know that’s a lost battle.) As changes here are not documented we can’t see what’s changed and given the current official change logs are already sufficiently long digging into the code to look for such things is not something I really want to do.

Then again it may well be that in many cases no such actual requirement to prevent a downgrade really exists. I would bet that in many (but not all) cases this is not actually needed.

It would be convenient to add in the change logs WHAT has changed to prevent a minor version downgrade from being possible. That at least makes this more visible. It might be you want to hold off a master upgrade until more than one minor version has been released to be more comfortable with the master upgrade being safe.

I would also like to see a post that describes the incompatible changes that were made from 8.0.19 back to 8.0.11 as I suspect that it may have only been in a few of these updates that such a change actually happened, and I would guess they relate to incompatible DD structure or format changes which perhaps could have been implemented slightly differently to avoid this backwards incompatibility. If that were the case it might be worth considering for future minor version changes to allow them to be backed out if absolutely necessary and with this remove the restriction that it’s not possible to downgrade to a lower minor version than the version that was last used.

During my last years at Oracle, I was working on the Global Data Dictionary project, mostly on the InnoDB side. My implementation of the InnoDB prototype was never released as such. But my design seemed to live on.

One part of the design was that we would hard-code the format of a very small number of data dictionary tables, similar to how InnoDB always hard-coded the SYS_TABLES, SYS_COLUMNS, SYS_INDEXES, SYS_FIELDS in the system tablespace (which were supposed to be completely replaced by the Global Data Dictionary tables). The idea was that schema changes to the data dictionary tables would be extremely rare, and the data dictionary version would almost never be updated. Some dictionary tables or system tables could be created ‘normally’, with their metadata being written to the core tables.

So, I was wondering if that error message is related to the data dictionary version. It appears to be related to the InnoDB data files themselves. The message against downgrade is output in the function fil_ibd_open().

In MariaDB, while we do not officially guarantee that downgrades work, I have spent some effort to ensure that import/export of .ibd files between server versions work in a reasonable way. Sure, if you use the 10.3 instant ADD COLUMN, you will not be able to import the table to older versions. But, if you rebuild the table in 10.3, it will again be in the old compatible format, and you should be able to import it to MariaDB 10.0 or later, or MySQL 5.6 or MySQL 5.7. Similarly, if you export after using the 10.4 instant DROP COLUMN, an import to 10.3 or earlier will fail. Last but not least, files created in 10.4 with innodb_checksum_algorithm=full_crc32 will not be compatible with older versions.

When it comes to the data dictionary, I have changed my mind since the time the MySQL 8.0 dictionary was designed. First, I believe that instead of introducing a separate “ddl_log” table we should have extended the undo log format. MariaDB 10.2.19 supports crash-safe and Mariabackup-compatible RENAME TABLE and TRUNCATE TABLE inside InnoDB, only extending the existing undo and redo log formats. MariaDB also supports InnoDB undo tablespace truncation without introducing a separate log file for that. We still did not implement proper InnoDB undo logging for CREATE/DROP TABLE, so not all DDL operations are entirely crash-safe inside InnoDB, and we lack logging around renaming .frm files, which means that many DDL operations may require manual crash recovery.

Secondly, I believe in “if it is not broken, do not fix it”. There was nothing fundamentally broken with .frm files. Yes, some logging is needed to make transactions atomic and crash-safe. But the serialized, extensible format of .frm files per se was and is fine. MySQL 8.0 introduced another serialized format (JSON-based SDI files), instead of following SQLite’s lead and reusing CREATE TABLE statements, which are an already supported and well defined serialized format. And yes, FOREIGN KEY information is missing from .frm files, but .frm files can be extended.

Finally, I think that it was a bad idea to tie storage engines more tightly with the data dictionary, and to allow engine-private metadata to be stored in the data dictionary, sometimes even outside DDL operations. It is cleaner to have each table to correspond one or more data files, whose format is defined by the storage engine. So, the storage engine is free to store whatever extra information it needs, inside those data files. For example, the additional metadata for instant ADD/DROP COLUMN in MariaDB is stored inside the .ibd file, and export/import of data files simply works without additional tricks. I have not tried it, but my understanding is that in MySQL 8.0, the additional metadata is stored inside the data dictionary tables, and import/export would not work after using the MySQL 8.0 instant ADD COLUMN.

Unfortunately, MySQL 5.7 implemented CREATE TABLESPACE for InnoDB, which breaks the simple design of “one table is a bunch of files, with names derived from the table name”, and actually requires some engine-private details (like the start address of each index B-tree in a table) to be stored either in the tablespace file(s) or in the data dictionary. MySQL 5.7 and 8.0 chose the latter.

MariaDB Server deliberately does not support CREATE TABLESPACE for InnoDB tables. I think that it is possible to eliminate the InnoDB data dictionary tables, to store all data dictionary information in .frm files, and to store all engine-private data in the storage engine’s own files. For InnoDB tables, this would require writing down the secondary index root page numbers somewhere. A natural place for that could be the ‘metadata BLOB’ that was originally introduced for instant DROP COLUMN in MariaDB 10.4.

In any case this is major change which needs to be considered.