[MDEV-9017] Disabling InnoDB on a slave results in slave warnings re: mysql.gtid_slave_pos Created: 2015-10-27  Updated: 2023-07-12  Resolved: 2023-07-12

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Chris Calender (Inactive) Assignee: Kristian Nielsen
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

All


 Description   

If you disable InnoDB (I used skip-innodb and default-storage-engine=MyISAM) in MariaDB (tested on 10.0.21) on a slave, then you will end up with the following warnings in your error log and SHOW SLAVE STATUS:

Unable to load replication GTID slave state from mysql.gtid_slave_pos: Unknown storage engine 'InnoDB'

Even if you delete the existing .frm file, and restart, you will still have the issue.

The only work-around is to create the table as MyISAM. However, you must delete the .frm first, and ensure you're in a clean state. Then re-create the table as follows:

CREATE TABLE `mysql`.`gtid_slave_pos` (
 
`domain_id` int(10) unsigned NOT NULL,
 
`sub_id` bigint(20) unsigned NOT NULL,
 
`server_id` int(10) unsigned NOT NULL,
 
`seq_no` bigint(20) unsigned NOT NULL,
 
PRIMARY KEY (`domain_id`,`sub_id`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

Then you must stop/start your slave to clear the error from the output.

If you're not using InnoDB nor GTID, then this should not be a requirement, and should not be in your SHOW SLAVE STATUS.

 Comments   
Comment by Elena Stepanova [ 2015-11-25 ]

ccalender, sorry for the delay.

The GTID position is always tracked and the table is always updated, even if currently the slave does not use it for replication; so, naturally, the table should always be accessible.

If you mean to question the design according to which the GTID position always being tracked, I can redirect the issue to Kristian for further discussion.

Comment by Chris Calender (Inactive) [ 2015-11-25 ]

The first problem is that the table, mysql.gtid_slave_pos, by default, is InnoDB (which is understandable).

However, if you run without InnoDB enabled (--skip-innodb), you will continuously (so monitoring and actually troubleshooting real problems is a nightmare)) receive errors in your SHOW SLAVE STATUS output:

Unable to load replication GTID slave state from mysql.gtid_slave_pos: Unknown storage engine 'InnoDB'

The only fix is to change this to a MyISAM table.

I don't see a fix for this in the actual mysql server code, but perhaps if you run wish --skip-innodb, then mysql_upgrade could check that, and convert any system InnoDB tables (as this is not the only possible one) to MyISAM tables.

Comment by Chris Calender (Inactive) [ 2015-11-25 ]

Or just not allow/silently ignore the error, "Unable to load replication GTID slave state from mysql.gtid_slave_pos: Unknown storage engine 'InnoDB'", if one is not using GTID replication.

Comment by Elena Stepanova [ 2015-11-25 ]

ccalender,

perhaps if you run wish --skip-innodb, then mysql_upgrade could check that, and convert any system InnoDB tables (as this is not the only possible one) to MyISAM tables.

If you upgrade the schema where mysql.gtid_slave_pos does not yet exist, mysql_upgrade does exactly that: it checks that InnoDB is not available, and creates the table as MyISAM.
If you upgrade the schema where mysql.gtid_slave_pos already exists as an InnoDB table, but the server is currently running with -skip-innodb, mysql_upgrade cannot alter or recreate the table as MyISAM, because the table already exists, but is unreadable due to the missing engine.

Or just not allow/silently ignore the error, "Unable to load replication GTID slave state from mysql.gtid_slave_pos: Unknown storage engine 'InnoDB'", if one is not using GTID replication.

I'll redirect this suggestion to knielsen.

Comment by Kristian Nielsen [ 2023-07-12 ]

I think I'll close this old issue.
I think the underlying issue is surprise that an InnoDB system table is automatically created at a time where InnoDB is enabled (server install or upgrade), and then becomes no longer readable after InnoDB is later disabled.
But it is more important to prevent creating a non-InnoDB mysql.gtid_slave_pos, and to warn about missing mysql_upgrade when the table is missing, I think.
I think there should be an easy work-around, to delete the FRM and re-run mysql-upgrade with InnoDB disabled (not tested).

It might be a valid feature request to disable updates to mysql.gtid_slave_pos (for performance), feel free to re-open as such, though a lot of things seem to expect GTID nowadays.

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