[MDEV-7363] bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE Created: 2014-12-23  Updated: 2022-09-12  Resolved: 2022-09-12

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Daniel Black Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: eits

Issue Links:
Relates
relates to MDEV-7829 Could not execute Update_rows_v1 even... Closed
relates to MDEV-7901 re-implement analyze table for low im... Closed
relates to MDEV-7829 Could not execute Update_rows_v1 even... Closed

 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.

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

I'm not exactly sure what is the bug here, but the behavior is certainly as described, thus confirming.

Comment by Daniel Black [ 2015-11-02 ]

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?

Comment by Sergei Petrunia [ 2015-12-04 ]

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 |;

Comment by Sergei Petrunia [ 2015-12-04 ] 

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

Comment by Sergei Golubchik [ 2022-09-12 ]

10.0 was EOLed in March 2019

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