mysql_upgrade tool fails mariadb-10.6.x to mariadb-10.11.x

Message

guid0 · Post by **guid0** » Mon Mar 31, 2025 10:13 am

I recently upgraded several machines from dev-db:mariadb-10.6.17 to dev-db:mariadb-10.11.11 or closely similar.

Normally, when jumping major versions, I run the mysql_upgrade tool without any errors. However this time im getting:

Code: Select all

# mysql_upgrade 
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK

Repairing tables
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 489: Table 'mysql.innodb_index_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed

Which feels somewhat uncomfortable. Running the mariadb-upgrade equivalent gives me a similar result:

Code: Select all

# mariadb-upgrade 
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK

Repairing tables
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 489: Table 'mysql.innodb_index_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed

Any tips on how to best proceed with this?

kind regards,
guid0

Post by **Banana** » Mon Mar 31, 2025 11:39 am

Not really, but found this: https://stackoverflow.com/questions/378 ... esnt-exist

I do hope you have a backup...

grknight · Post by **grknight** » Mon Mar 31, 2025 12:32 pm

Is the InnoDB engine failing to start or having some other issue?
These tables are stored there and may indicate a different problem.
Please look at your logs.

guid0 · Post by **guid0** » Mon Mar 31, 2025 12:44 pm

Logs below. Please note that although the logs refer to a slave_pos this is not a failover/replicator setup. Nothing really fancy.

Code: Select all

2025-03-31 12:04:02 0 [Note] /usr/sbin/mysqld: Shutdown complete
2025-03-31 12:04:03 0 [Note] Starting MariaDB 10.11.11-MariaDB source revision e69f8cae1a15e15b9e4f5e0f8497e1f17bdc81a4 server_uid CsyEK60fYhJVPieei6/8Ir6INos= as process 5908
2025-03-31 12:04:03 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2025-03-31 12:04:03 0 [Note] InnoDB: Number of transaction pools: 1
2025-03-31 12:04:03 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2025-03-31 12:04:03 0 [Note] InnoDB: Using liburing
2025-03-31 12:04:03 0 [Note] InnoDB: Initializing buffer pool, total size = 2.000GiB, chunk size = 32.000MiB
2025-03-31 12:04:03 0 [Note] InnoDB: Completed initialization of buffer pool
2025-03-31 12:04:03 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2025-03-31 12:04:03 0 [Note] InnoDB: Upgrading redo log: 512.000MiB; LSN=329429911054
2025-03-31 12:04:03 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2025-03-31 12:04:03 0 [Note] InnoDB: 128 rollback segments are active.
2025-03-31 12:04:03 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-03-31 12:04:03 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-03-31 12:04:03 0 [Note] InnoDB: log sequence number 329429911054; transaction id 200942
2025-03-31 12:04:03 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-03-31 12:04:03 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2025-03-31 12:04:03 0 [Warning] 'innodb-log-files-in-group' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-03-31 12:04:03 0 [Warning] /usr/sbin/mysqld: unknown option '--loose-federated'
2025-03-31 12:04:03 0 [Note] Server socket created on IP: '127.0.0.1'.
2025-03-31 12:04:03 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(141).
2025-03-31 12:04:03 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2025-03-31 12:04:03 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2025-03-31 12:04:03 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.11.11-MariaDB'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Source distribution
2025-03-31 12:04:05 0 [Note] InnoDB: Buffer pool(s) load completed at 250331 12:04:05
2025-03-31 12:04:07 8 [ERROR] Invalid (old?) table or database name 'lost+found'
2025-03-31 12:08:36 15 [ERROR] Invalid (old?) table or database name 'lost+found'
2025-03-31 12:08:49 22 [ERROR] Invalid (old?) table or database name 'lost+found'
2025-03-31 12:11:03 29 [ERROR] Invalid (old?) table or database name 'lost+found'
2025-03-31 12:14:11 31 [ERROR] Invalid (old?) table or database name 'lost+found'

guid0 · Post by **guid0** » Fri Apr 04, 2025 8:47 am

its breaking on all machine-setups here. For now I see no other option than to mask >=10.11

grknight · Post by **grknight** » Fri Apr 04, 2025 12:25 pm

The most important breaking change that started in mariadb 10.7 InnoDB was that compression providers, besides zlib, moved to new plugins that may need to be loaded manually.
If you have played with different compression at rest options in the past, then it may be possible that the tables in question just cannot open.

This option is set by innodb_compression_algorithm. Plugin overview is covered in the documentation.

guid0 · Post by **guid0** » Fri Apr 04, 2025 1:49 pm

I have 3 systems with a masked 10.11 now. All of them on:

Code: Select all

mysql  Ver 15.1 Distrib 10.6.20-MariaDB, for Linux (x86_64) using readline 8.2

Forcing mysql-upgrade using --force throws no errors. The only thing that wonders me is the "x$host"-part in:

Code: Select all

sys.x$host_summary                                 OK
sys.x$host_summary_by_file_io                      OK
sys.x$host_summary_by_file_io_type                 OK
sys.x$host_summary_by_stages                       OK
sys.x$host_summary_by_statement_latency            OK
sys.x$host_summary_by_statement_type               OK
sys.x$innodb_buffer_stats_by_schema                OK
sys.x$innodb_buffer_stats_by_table                 OK
sys.x$innodb_lock_waits                            OK
sys.x$io_by_thread_by_latency                      OK

but i have seen this more often and never had any issues

Compression is something I have not fiddled with every in mariadb..

Code: Select all

MariaDB [(none)]> show variables like 'innodb_compr%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| innodb_compression_algorithm             | zlib  |
| innodb_compression_default               | OFF   |
| innodb_compression_failure_threshold_pct | 5     |
| innodb_compression_level                 | 6     |
| innodb_compression_pad_pct_max           | 50    |
+------------------------------------------+-------+

Since the remainder of the systems are in production environments I have for now little option to upgrade and stick to 10.6.x

guid0 · Post by **guid0** » Fri Apr 04, 2025 1:54 pm

grknight wrote:Is the InnoDB engine failing to start or having some other issue?

Sorry, did not answer this. The engine starts and seems to work.

The problem i see is the report from the upgrade tool and the damaged (at least) innodb_index_stats table.

10.6.20-MariaDB and I can happily select and get results from the table.

Emerge upgrade to 10.11.x + restart service + run mariadb_upgrade and things start to break.

guid0 · Post by **guid0** » Mon Oct 06, 2025 9:13 am

Similar issue again. Now while upgrading mariadb version 10.11.13 to 11.4.7-r1

emerge -upvDN world produces:

Code: Select all

Calculating dependencies  .... done!
Dependency resolution took 7.86 s (backtrack: 0/20).

[ebuild     U  ] dev-db/mariadb-connector-c-3.4.5-r1:0/3::gentoo [3.3.8:0/3::gentoo] USE="curl -gnutls -kerberos -static-libs -test (-ssl%*)" ABI_X86="(64) -32 (-x32)" 1400 KiB
[ebuild  NS    ] dev-db/mariadb-11.4.7-r1:11.4/18::gentoo [10.11.13:10.11/18::gentoo] USE="backup pam perl server -bindist -columnstore -cracklib -debug -extraengine -galera -innodb-lz4 -innodb-lzo -innodb-snappy -jdbc -jemalloc -kerberos -latin1 (-mroonga) -numa -odbc -oqgraph -profiling -rocksdb -s3 (-selinux) -sphinx -sst-mariabackup -sst-rsync -static (-systemd) -systemtap -tcmalloc -test -xml -yassl" 113273 KiB
[uninstall     ] dev-db/mariadb-10.11.13:10.11/18::gentoo  USE="backup pam perl server -bindist -columnstore -cracklib -debug -extraengine -galera -innodb-lz4 -innodb-lzo -innodb-snappy -jdbc -jemalloc -kerberos -latin1 (-mroonga) -numa -odbc -oqgraph -profiling -rocksdb -s3 (-selinux) -sphinx -sst-mariabackup -sst-rsync -static (-systemd) -systemtap -tcmalloc -test -xml -yassl" 
[blocks b      ] dev-db/mariadb:11.4 ("dev-db/mariadb:11.4" is soft blocking dev-db/mariadb-10.11.13)
[blocks b      ] dev-db/mariadb:10.11 ("dev-db/mariadb:10.11" is soft blocking dev-db/mariadb-11.4.7-r1)
[blocks b      ] <dev-db/mariadb-11.4.7-r1 ("<dev-db/mariadb-11.4.7-r1" is soft blocking dev-db/mariadb-connector-c-3.4.5-r1)

Total: 2 packages (1 upgrade, 1 in new slot, 1 uninstall), Size of downloads: 114672 KiB
Conflict: 3 blocks (all satisfied)

Then after install I tried to run mariadb-upgrade. It complained about missing TLS so i restarted the mysql service and reran the upgrade tool:

Code: Select all

# /etc/init.d/mysql stop
# /etc/init.d/mysql start
 * Checking mysqld configuration for mysql ...                                                                                                              [ ok ]
 * Starting mysql ...                                                                                                                                       [ ok ]
# mariadb-upgrade 
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed

Repairing tables
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 501: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed

So the upgrade script fails to complete and bails at step 3 of 8.. Fortunately the mariadb service and associated databases seem to remain available as if there are no problems. Which is hopefully the case..

Cheers,
guid0

tld · Post by **tld** » Mon Oct 06, 2025 2:16 pm

I was considering upgrading to MariaDB from 10.6 to 10.11 when I saw this thread. MariaDB clearly has something strange going on here that I'm just NOT understanding.

I use all MyISAM tables for everything I'm doing at the moment. However in the mysql database the three tables innodb_index_stats, innodb_table_stats, and transaction_registry use the InnoDB engine, but it's not being recognized:

Code: Select all

MariaDB [mysql]> desc innodb_table_stats;
ERROR 1286 (42000): Unknown storage engine 'InnoDB'

I don't recall ever seeing this. When I run "SHOW PLUGINS" the InnoDB plugin is there but it's status is "INACTIVE". I see all sorts of information about installing plugins but nothing about "activating"??

I don't get it and I'm at a complete impasse. There don't appear to be any USE flags to enable InnoDB as far as I can see.

I've been using this forever and this is all new for me. What on EARTH is going on here??

Tom

tld · Post by **tld** » Mon Oct 06, 2025 2:37 pm

Wow this just keeps getting stranger.

The machine I'm on now is the one that doesn't recognize InnoDB. My MythTV backend, which is an old x86 machine, is running the same version of MariaDB (dev-db/mariadb-10.6.22), but that DOES recognize it:

Code: Select all

MariaDB [mysql]> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+---------------------+-------------------------------+
| Field                    | Type                | Null | Key | Default             | Extra                         |
+--------------------------+---------------------+------+-----+---------------------+-------------------------------+
| database_name            | varchar(64)         | NO   | PRI | NULL                |                               |
| table_name               | varchar(199)        | NO   | PRI | NULL                |                               |
| last_update              | timestamp           | NO   |     | current_timestamp() | on update current_timestamp() |
| n_rows                   | bigint(20) unsigned | NO   |     | NULL                |                               |
| clustered_index_size     | bigint(20) unsigned | NO   |     | NULL                |                               |
| sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL                |                               |
+--------------------------+---------------------+------+-----+---------------------+-------------------------------+

The ONLY difference in USE flags from this machine to that MythTV system is that this one has the "extraengine" enabled...which certainly shouldn't matter. It's hard to imagine that the x86 vs this machine being amd64 would matter.

The MythTV machine's "SHOW PLUGINS" shows InnoDB with a status of "ACTIVE".

Any suggestions would be welcome. I'm at a total loss. I'd almost guarantee my upgrade would fail just as guid0's is. Insane.

UPDATE: Just to make sure, I recompiled mariadb without that extraengine USE flag and that made no difference. There's nothing in my my.cnf files on either machine that would affect InnoDB at all.

Tom

grknight · Post by **grknight** » Mon Oct 06, 2025 4:10 pm

tld wrote:I don't recall ever seeing this. When I run "SHOW PLUGINS" the InnoDB plugin is there but it's status is "INACTIVE". I see all sorts of information about installing plugins but nothing about "activating"??

Read your server logs (often /var/log/mysql/mysqld.err by default or similar naming). Some error is preventing the engine from starting when MariaDB is started.

grknight · Post by **grknight** » Mon Oct 06, 2025 4:32 pm

guid0 wrote:So the upgrade script fails to complete and bails at step 3 of 8.. Fortunately the mariadb service and associated databases seem to remain available as if there are no problems. Which is hopefully the case.

Do those erroring tables exist as files in the data directory (often /var/lib/mysql/mysql/ by default) as both an .frm and .ibd?
Also, make sure that innodb_file_per_table is enabled. (This will be forced in future versions anyway)

guid0 · Post by **guid0** » Mon Oct 06, 2025 5:13 pm

grknight wrote:Do those erroring tables exist as files in the data directory (often /var/lib/mysql/mysql/ by default) as both an .frm and .ibd?
Also, make sure that innodb_file_per_table is enabled. (This will be forced in future versions anyway)

Yes, they seem to be there. However they seem to be seriously outdated.

Code: Select all

# ls -laF /var/lib/mysql/mysql/ | egrep -i "innodb|gtid"
-rw-rw---- 1 mysql mysql    1027 May 31  2023 gtid_slave_pos.frm
-rw-rw---- 1 mysql mysql   65536 May 31  2023 gtid_slave_pos.ibd
-rw-rw---- 1 mysql mysql    5407 Mar 27  2025 innodb_index_stats.frm
-rw-rw---- 1 mysql mysql   65536 Mar 27  2025 innodb_index_stats.ibd
-rw-rw---- 1 mysql mysql    1912 May 31  2023 innodb_table_stats.frm
-rw-rw---- 1 mysql mysql   65536 May 31  2023 innodb_table_stats.ibd

Settings from config:

Code: Select all

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:8G:autoextend:max:16G
innodb_buffer_pool_size = 48G
innodb_log_file_size = 16G
innodb_log_files_in_group = 8
innodb_file_per_table = 1
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT
innodb_fast_shutdown = 3
innodb_flush_log_at_trx_commit=2
innodb = FORCE
innodb_strict_mode = 1

tld · Post by **tld** » Mon Oct 06, 2025 7:18 pm

grknight wrote:Read your server logs (often /var/log/mysql/mysqld.err by default or similar naming). Some error is preventing the engine from starting when MariaDB is started.

Wow...yes you're correct. Though I don't understand it and so far can't find a fix. I have this in the mysqld.err log:

Code: Select all

2025-10-06 15:10:15 0 [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.11.6.
2025-10-06 15:10:15 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

I'm especially thrown by the "The redo log was created with MariaDB 10.11.6." part, because I've NEVER used any version higher than what I have now which is 10.6.22!

Again I'm not actually using any InnoDB tables myself. I simply am NOT finding any answers as to how to fix that at all.

I should probably create a new topic around this, but does anyone have a clue how to fix that?

UPDATE! Scratch all the above. I have no idea how, but at some point it appears I inadvertently manage to downgrade mariadb from 10.11 back to 10.6. So yea...I've got my own mess here...sorry for the noise.

Thanks.
Tom

guid0 · Post by **guid0** » Thu Dec 04, 2025 6:57 pm

Many thanks for all the feedback. Fortunately there is a manual fix for those (probably myself again

) running into this issue:

Stop the MariaDB-server:

Code: Select all

/etc/init.d/mysql stop

Remove the corrupt tables away to a backup location (you may have other tables with an issue)

Code: Select all

cd /var/lib/mysql/mysql
mv gtid_slave_pos.* innodb_table_stats* /your-backup-location

Start the MariaDB-server:

Code: Select all

/etc/init.d/mysql start

Recreate the broken tables. Please note that for me only gtid_slave_pos and innodb_table_stats were broken..

Code: Select all

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

Code: Select all

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

Code: Select all

CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

Code: Select all

CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

Code: Select all

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

-- Source - https://forums.freebsd.org/threads/cann ... adb.64116/
-- Source - https://stackoverflow.com/questions/37856155

cheers,
guid0