Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7363

bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0
    • Fix Version/s: 10.0
    • Component/s: None
    • Labels:

      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

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration