[MDEV-12499] ARCHIVE tables still don't show up in I_S.TABLES when storage engine is not loaded Created: 2017-04-13  Updated: 2017-04-17  Resolved: 2017-04-17

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Server, Storage Engine - Archive
Affects Version/s: 10.1.22
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

This is a followup to MDEV-11943

The BLACKHOLE specific aspects of that bug got fixed, the ARCHIVE specific one still remains though.

When having ARCHIVE tables, but not having the plugin loaded, these tables don't show up in I_S.TABLES, unlike BLACKHOLE tables, which do show up by name and with a "Unknown storage engine 'BLACKHOLE'" note in the TABLE_COLUMNS column.

So the fix for MDEV-11942 still doesn't work for archive tables, the ha_archive engine plugin can't be installed on demand by mysql_upgrade as it simply can't see that there are orphan ARCHIVE tables that would need this plugin at all.

How to reproduce:

On a 10.0.x instance do:

CREATE TABLE test.a1(id INT) engine=ARCHIVE;
 
CREATE TABLE test.b1(id INT) engine=BLACKHOLE;

Then upgrade to 10.1.22, and check I_S.TABLE contents.
Only one row is returned which shows information for the b1 BLACKHOLE table.
No row is shown for the a1 ARCHIVE table:

MariaDB [information_schema]> select * from information_schema.tables where table_schema='test';
 
+---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+------------------------------------+
 
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE    | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT                      |
 
+---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+------------------------------------+
 
| def           | test         | b1         | BASE TABLE | BLACKHOLE |    NULL | NULL       |       NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL            |     NULL | NULL           | Unknown storage engine 'BLACKHOLE' |
 
+---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+------------------------------------+
 
1 row in set, 1 warning (0.00 sec)

And so mysql_upgrade only installs the ha_blackhole engine plugin, but not the ha_archive one:

# mysql_upgrade
 
[...]
 
Phase 2/7: Installing used storage engines
 
Checking for tables with unknown storage engine
 
installing plugin for 'blackhole' storage engine
 
Phase 3/7: Fixing views
 
[...]



 Comments   
Comment by Sergei Golubchik [ 2017-04-17 ]

This only happens if you created an ARCHIVE table and never used it before the upgrade. No INSERT, no SELECT, not even SELECT * FROM INFORMATION_SCHEMA.TABLES — nothing.

ARCHIVE engine supports table discovery, that is, the engine is always the authoritative source of table metadata, while .frm files are merely a metadata cache. When you do CREATE TABLE the server does not know whether the engine wants the table metadata to be cached. This happens when the table is opened for the first time.

If there is no .frm file and the ARCHIVE engine is not loaded, the server has no way of knowing that the ARCHIVE table exists. That's why it's not shown anywhere.

If you do SELECT * FROM test.a1 before the upgrade, the engine will tell the server to create the .frm file and on the upgrade the plugin will be loaded. Even if you simply start mysql client on the test database, it'll be enough. In a practical use case, it's enough to have at least one ARCHIVE table that was accessed at least once after it was created, and the ARCHIVE plugin will be loaded on upgrade.

Generated at Thu Feb 08 07:58:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.