Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
10.0(EOL)
Description
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.
{table|column|index}_stats updates either which would be quite useful as an option (and the flush tables {table|column|index}_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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-7901 re-implement analyze table for low impact
-
- Closed
-
-
-
- Closed
-
- links to
Activity
the bug/misunderstanding is why does TABLE_CATEGORY_SYSTEM exist on engine independent statistics tables
The table.h definition is:
/**
|
System table, maintained by the server.
|
These tables do honor:
|
- LOCK TABLE t FOR READ/WRITE
|
- FLUSH TABLES WITH READ LOCK
|
- SET GLOBAL READ_ONLY = ON
|
Typically, writes to system tables are performed by
|
the server implementation, not explicitly be a user.
|
System tables are cached in the table cache.
|
*/
|
TABLE_CATEGORY_SYSTEM=3,
|
If engine independent stats are documented to be user editable is this category applicable?
Confirm. Attempting to insert data by first putting it into a temporary table doesn't work either (it produces the same error).
Besides LOAD DATA, one can use an SP:
delimiter |;
|
|
|
create procedure p1()
|
begin
|
declare _db_name varchar(64);
|
declare _table_name varchar(64);
|
declare _cardinality bigint unsigned;
|
|
|
declare done int default 0;
|
|
|
declare c cursor for
|
select db_name, table_name, cardinality
|
from table_stats_bck;
|
|
|
declare continue handler for sqlstate '02000' set done = 1;
|
|
|
open c;
|
repeat
|
fetch c into _db_name, _table_name, _cardinality;
|
if not done then
|
replace into mysql.table_stats
|
values (_db_name, _table_name, _cardinality);
|
end if;
|
until done end repeat;
|
close c;
|
end |;
|
MariaDB [test]> create table mysql_proc_bck as select * from mysql.proc;
|
Query OK, 4 rows affected (0.44 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> replace into mysql.proc select * from mysql_proc_bck;
|
ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types
|
But not all tables in mysql.* are system tables.
table.cc: is_system_table_name() says that these tables are system:
help_category, help_keyword, help_relation, help_topic,
|
proc, event
|
time_zone, time_zone_leap_second, time_zone_name, time_zone_transition,
|
time_zone_transition_type
|
why mysql.proc is a system table while mysql.func is not? No idea, will discuss this on the next optimizer call.
Checked is_system_table_name
I'm not exactly sure what is the bug here, but the behavior is certainly as described, thus confirming.