MySQL Bugs: #99331: The SDI files of the MyISAM tables are not updated after server upgrade

Bug #99331	The SDI files of the MyISAM tables are not updated after server upgrade
Submitted:	23 Apr 2020 8:33	Modified:	20 Jul 2020 8:49
Reporter:	Roberto Castellino	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	CentOS (7)
Assigned to:		CPU Architecture:	x86
Tags:	SDI

Description:
I upgrade the server from 8.0.18 to 8.019 version.
As documented in release notes SDI version number was incremented.
However sdi files for old MyISAM tables are not upgraded and the sdi version inside is not updated.

If sdi version is not correct it is not possible to use IMPORT TABLE statement.

I tried CHECK TABLE with FOR UPGRADE option, but doesn't work (the result was tables are OK).

My solution was to restructure tables adding (and then removing) a field.
This seems to force regeneration of sdi files with the right version.

How to repeat:
Create a MyISAM tables in 8.0.18 server, upgrade server to 8.0.19, open sdi file to see the version

Hi Mr. Castellino,

Thank you for your feedback.

I have one important question. When you have updated our server to 8.0.19, how exactly have you run mysql_upgrade program ???

Thanks in advance.

Hi Mr. Castellino,

MySQL upgrade does not have to be used anymore. 

On the other hand, I repeated your behaviour.

Verified as reported.

Hi Sinisa,
I simply do an update via yum (my server run on a Centos7 distro).
After the update i check tables with Mysql Workbench and manually using CHECK table statements. 
The tables were fine, with no need for upgrades.
I found the problem when i tried to restore a table from files, using IMPORT TABLE, and i saw the version in sdi files was not correct.

Ciao Roberto,

I have repeated this behaviour and this bug is verified now.

Seems that this is a regression bug, from the fixed bug Bug #30326020, Bug #96943.

Thank you.

Posted by developer Sivert Sorumgaard:
======================================

The SDI files are not re-generated on upgrade for performance reasons. After
upgrading from e.g. 8.0.18 to 8.0.19, we will see the following contents of
the sdi file:

{"mysqld_version_id":80018,"dd_version":80017,"sdi_version":80016,"dd_object_t
ype":"Table","dd_object":{"name":"t" ...

If we try to import a table like this into a 8.0.19 server, we get the error
message:

  ERROR 3613 (HY000): Imported sdi version (80016) is not compatible with
current (80019)

To fix this issue, we can execute 'CHECK TABLE t FOR UPGRADE', while running
the upgraded server, i.e., 8.0.19 in this case. We get the following output:

  +--------+-------+----------+----------+
  | Table  | Op    | Msg_type | Msg_text |
  +--------+-------+----------+----------+
  | t.     | check | status   | OK       |
  +--------+-------+----------+----------+
  1 row in set (0.00 sec)

Now, the SDI file should have been re-generated (note that the server and SDI
versions have changed. There were no changes to the DD tables between these
two server versions, so the DD version stays the same):

{"mysqld_version_id":80019,"dd_version":80017,"sdi_version":80019,"dd_object_t
ype":"Table","dd_object":{"name":"t" ...

If we now try to import it, we should succeed:

  mysql> import table from 't_340.sdi';
  Query OK, 0 rows affected (0.01 sec)

Hence, as far as we can see, this is working the way it was designed.

Thank you, Erlend.