bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE

I've been trying to get engine-independent-index-stats from an inactive server to an active server.

ANALYZE TABLE is a little to invasive to do on a production server so running it on an offline slave is desirable.

ANALYZE LOCAL TABLE is desirable to run on an inactive master in a master<->master (both replicating off each other) set up so the ANALYZE TABLE doesn't hit the active master. It does however not replicate any of the mysql.

_stats) .

With what we have right now this leads to the case where engine independent index stats can be generated with mysqlanalyze --skip-write-binlog --all-databases (effectively SQL_LOG_BIN=0).

If it was an inactive master we could replicate it using:

MariaDB [mysql]> set sql_log_bin=1;

MariaDB [mysql]> create temporary table tmp_table_stats like mysql.table_stats;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> insert into tmp_table_stats select * from table_stats;

Query OK, 1054 rows affected (0.02 sec)

Records: 1054  Duplicates: 0  Warnings: 0

MariaDB [mysql]> replace into mysql.table_stats select * from tmp_table_stats;

ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types

So the 1428 ER_WRONG_LOCK_OF_SYSTEM_TABLE error occurs as a result of locking a system and non-system table at the same time. Most other SQL variants of the same things resulted in the same error.

While the page suggests that editing statistics is possible https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/engine-independent-table-statistics/ it would be useful if some editing beyond manual row(s) at a time was possible.

I even considered doing this on a slave to allow a forced connection to the master where it could be pushed up, however I failed on the (previously) undocumented limitations of the connect engine.

MariaDB [mysql]> create table column_stats_remote engine=CONNECT table_type=MYSQL connection='mysql://stat_sync:hidden_password@192.168.149.5/mysql/column_stats';

ERROR 1105 (HY000): Column hist_type unsupported type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB')

Which leads me to the only single table solution I could think of:

MariaDB [test]> select * from mysql.table_stats into outfile '/tmp/table_stats.txt';

Query OK, 1054 rows affected (0.00 sec)

MariaDB [test]> load data infile '/tmp/table_stats.txt' REPLACE  INTO TABLE mysql.table_stats;

The engine-independent stats tables where explicitly set to TABLE_CATEGORY_SYSTEM in sql/table.cc:is_system_table_name however the innodb ones where not. Is there a need for this to occur?

Is the sql/lock.cc:lock_tables_check returning ER_WRONG_LOCK_OF_SYSTEM_TABLE still a valid thing to do?

Regardless of the reason, bulk manipulation of engine-independent index statistics is limited by this error.