|
Same behaviour on 10.6.7
|
|
Reproduceable also with 10.6.8, 10.6.9 and latest 10.6.10. However, the behavior is different: After a restart it is working for some time, once it fails at 10.6.7 every further request will fail, but with 10.6.8, 10.6.9, 10.6.10 further requests will work again but it will fail again afterwards.
Tested with the following shell-script:
#!/bin/bash
|
|
systemctl restart mariadb
|
mysql -V
|
while :
|
do
|
mysql -e 'DROP DATABASE IF EXISTS testdb;CREATE DATABASE testdb;'
|
mysql testdb < dump1.sql
|
echo `date` - `mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G' | grep Rows`
|
sleep 10
|
done
|
Attached output of 10.6.7, .8, .9 and latest .10: metadata-test.txt
|
|
A similar problem is repeatable with 10.7.6:
The row statistics is empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 0:
SET UNIQUE_CHECKS=0;
|
SET FOREIGN_KEY_CHECKS=0;
|
|
DROP TABLE IF EXISTS `t1`;
|
CREATE TABLE `t1` (
|
`id` int(11) NOT NULL,
|
`a` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
|
INSERT INTO `t1` VALUES (1,2),(2,3),(3,4);
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';
|
+------------+----------------+
|
| TABLE_ROWS | AVG_ROW_LENGTH |
|
+------------+----------------+
|
| 0 | 0 |
|
+------------+----------------+
|
The row statistics is not empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 1:
SET UNIQUE_CHECKS=1;
|
SET FOREIGN_KEY_CHECKS=1;
|
|
DROP TABLE IF EXISTS `t1`;
|
CREATE TABLE `t1` (
|
`id` int(11) NOT NULL,
|
`a` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
|
INSERT INTO `t1` VALUES (1,2),(2,3),(3,4);
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';
|
+------------+----------------+
|
| TABLE_ROWS | AVG_ROW_LENGTH |
|
+------------+----------------+
|
| 3 | 5461 |
|
+------------+----------------+
|
|
|
Repeatable with 10.6.10 and 10.7.6 with the following MTR test:
--source include/have_innodb.inc
|
|
CREATE DATABASE db1;
|
CREATE TABLE db1.t1 (id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB;
|
INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4);
|
|
--let $file = $MYSQLTEST_VARDIR/tmp/dump.sql
|
|
--exec $MYSQL_DUMP db1 t1 >$file
|
|
DROP DATABASE IF EXISTS db1;
|
CREATE DATABASE db1;
|
|
--exec $MYSQL db1 < $file
|
--remove_file $file
|
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES
|
WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';
|
|
OPTIMIZE TABLE db1.t1;
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES
|
WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';
|
|
DROP DATABASE db1;
|
This is the output:
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
|
CREATE DATABASE db1;
|
CREATE TABLE db1.t1 (id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB;
|
INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4);
|
DROP DATABASE IF EXISTS db1;
|
CREATE DATABASE db1;
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES
|
WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';
|
TABLE_ROWS AVG_ROW_LENGTH
|
0 0
|
OPTIMIZE TABLE db1.t1;
|
Table Op Msg_type Msg_text
|
db1.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
db1.t1 optimize status OK
|
SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES
|
WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';
|
TABLE_ROWS AVG_ROW_LENGTH
|
3 5461
|
DROP DATABASE db1;
|
main.AAA 'innodb' [ pass ] 82
|
|
|
The problem is not repeatable in 10.6.11 after this patch:
commit f70960c3482073d2edd4a809899adee56c94ec24 (HEAD -> 10.6)
|
Author: Thirunarayanan Balathandayuthapani <thiru@mariadb.com>
|
Date: Tue Oct 25 12:12:33 2022 +0530
|
|
MDEV-28327 InnoDB persistent statistics fail to update after bulk insert
|
So it appeared that this problem duplicates MDEV-28327.
|
|
Hi marko, can you please review a patch?
https://github.com/MariaDB/server/commit/aa1a9e21895b675c01133a74493c3abaa3b6dee1
Thanks.
|
|
OK to push after addressing my review comments.
|
|
Here's an updated version:
https://github.com/MariaDB/server/commit/14fdd571ad59c51d9ab00a6d6ec249afd1c70761
|
|
Thank you! I think that it was good to extend the test to cover all innodb_page_size.
|
|
It appeared that this issues was fixed earlier. Added tests only.
|
|
It looks like MDEV-28327 is very similar to this issue. However, I can reproduce this issue also with the latest version 10.6.11.
I have also tried the test in MDEV-28327 which is reproduceable in 10.6.10 but not in 10.6.11, as it has been fixed.
@bar You wrote that it is not reproduceable with 10.6.11, have you tried my shell-script?
Attached is a new test-report with 10.6.10 and 10.6.11, where it can be seen that the issue is still existing.
So can you please reopen it.
metadata-test2.txt
|
metadata-test.txt