Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.14
-
None
-
CentOS Linux release 7.5.1804 (Core)
Description
Default configuration (engine-dependent) statistics do not update correctly on slave for InnoDB.
CREATE TABLE `simple` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`val` int(10) unsigned DEFAULT NULL,
|
`blah` varchar(511) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `simple_modified` (`modified`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
Having this table in a 3 node MariaDB 10.3.14 Galera cluster, and a slave (configurations attached) with the following (default) stats configuration on all servers:
+--------------------------------------+-------------+
|
| Variable_name | Value |
|
+--------------------------------------+-------------+
|
| innodb_stats_auto_recalc | ON |
|
| innodb_stats_include_delete_marked | OFF |
|
| innodb_stats_method | nulls_equal |
|
| innodb_stats_modified_counter | 0 |
|
| innodb_stats_on_metadata | OFF |
|
| innodb_stats_persistent | ON |
|
| innodb_stats_persistent_sample_pages | 20 |
|
| innodb_stats_sample_pages | 8 |
|
| innodb_stats_traditional | ON |
|
| innodb_stats_transient_sample_pages | 8 |
|
+--------------------------------------+-------------+
|
as rows are inserted all statistics update correctly except for non-unique key stats on the slave, which incorrectly show unique key cardinality.
Galera node / master:
MariaDB [test]> show index from simple;
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| simple | 0 | PRIMARY | 1 | id | A | 14391 | NULL | NULL | | BTREE | | |
|
| simple | 1 | simple_modified | 1 | modified | A | 2055 | NULL | NULL | | BTREE | | |
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
2 rows in set (0.000 sec)
|
|
|
MariaDB [test]> select count(*) from simple;
|
+----------+
|
| count(*) |
|
+----------+
|
| 14722 |
|
+----------+
|
1 row in set (0.005 sec)
|
|
|
MariaDB [test]> select count(distinct modified) from simple;
|
+--------------------------+
|
| count(distinct modified) |
|
+--------------------------+
|
| 362 |
|
+--------------------------+
|
1 row in set (0.001 sec)
|
(this error is statistical & not relevant)
Slave:
MariaDB [test]> show index from simple;
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| simple | 0 | PRIMARY | 1 | id | A | 14383 | NULL | NULL | | BTREE | | |
|
| simple | 1 | simple_modified | 1 | modified | A | 14383 | NULL | NULL | | BTREE | | |
|
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
2 rows in set (0.000 sec)
|
|
|
MariaDB [test]> select count(*) from simple;
|
+----------+
|
| count(*) |
|
+----------+
|
| 14722 |
|
+----------+
|
1 row in set (0.005 sec)
|
|
|
MariaDB [test]> select count(distinct modified) from simple;
|
+--------------------------+
|
| count(distinct modified) |
|
+--------------------------+
|
| 362 |
|
+--------------------------+
|
1 row in set (0.001 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-