[MDEV-13849] spider engine select count error Created: 2017-09-20  Updated: 2017-11-30  Resolved: 2017-11-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.2.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: michael_xu Assignee: Andrii Nikitin (Inactive)
Resolution: Incomplete Votes: 0
Labels: None


 Description   

my mariadb version :5.5.5-10.2.8

backend db:mysql-5.7.19

I used the spider engine to create eight db, 8 tables, partition type hash. When I import a billion data to a table, the implementation of select count (*) from history; will be an error. The error message is as follows:

mysql> select count (*) from history;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

Backend db mysql log

2017-09-19T19: 35: 45.405795Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4218ms. The settings might not be optimal. (Flushed = 1630 and evicted = 0, during the time.)
 
2017-09-19T19: 47: 35.882019Z 0 [Note] InnoDB: page_cleaner: 1000ms deploy loop took 5312ms. The settings might not be optimal. (Flushed = 200 and evicted = 0, during the time.)
 
2017-09-19T19: 47: 53.736353Z 0 [Note] InnoDB: page_cleaner: 1000ms deploy rack took 13651ms. The settings might not be optimal. (Flushed = 1017 and evicted = 0, during the time.)
 
2017-09-20T01: 16: 07.534076Z 507 [Note] Aborted connection 507 to db: 'bsbackend4' user: 'spider' host: 'localhost' (Got an error reading communication packets)
 
2017-09-20T01: 16: 07.534114Z 433 [Note] Aborted connection 433 to db: 'bsbackend4' user: 'spider' host: 'localhost' (Got an error reading communication packets)
 
2017-09-20T01: 28: 34.177313Z 508 [Note] Aborted connection 508 to db: 'bsbackend4' user: 'spider' host: 'localhost' (Got an error reading communication packets)

How to deal with this problem, thank you

 Comments   
Comment by Andrii Nikitin (Inactive) [ 2017-09-20 ]

There may be many reasons for 'Got an error reading communication packets' . One of them is incorrect (insufficient) value for max_allowed_packet (on either side) or wait_timeout configuration.
Does the error occur every time for the same table or happens occasionally?
Does the error occur for small table, with few simple rows on each node?

Comment by michael_xu [ 2017-09-21 ]

Yes, it will always happen. If the number of rows in the table is relatively small, this is not the problem. The mysql parameter is max_allowed_packet = 16M. wait_timeout = 28800

The table structure is
/*history */
DROP TABLE IF EXISTS history;
CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT 0,
`value` double(16,4) NOT NULL DEFAULT 0.0000,
`ns` int(11) NOT NULL DEFAULT 0,
KEY `history_1` (`itemid`,`clock`)
) ENGINE=spider DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "history"'
PARTITION BY HASH (itemid + clock)
(
PARTITION pt1 COMMENT = 'srv "bsbackend1"',
PARTITION pt2 COMMENT = 'srv "bsbackend2"',
PARTITION pt3 COMMENT = 'srv "bsbackend3"',
PARTITION pt4 COMMENT = 'srv "bsbackend4"',
PARTITION pt5 COMMENT = 'srv "bsbackend5"',
PARTITION pt6 COMMENT = 'srv "bsbackend6"',
PARTITION pt7 COMMENT = 'srv "bsbackend7"',
PARTITION pt8 COMMENT = 'srv "bsbackend8"'
);

Comment by Andrii Nikitin (Inactive) [ 2017-09-21 ]

You should try to simplify test case, e.g. use few rows in simplest table with single backend server and make sure that it works. Then make it more complex and try to determine conditions when problem triggers.

From my side I did ensure that basic example with local cluster (root node 10.2.8 with three 5.7.19 backends) does work flawlessly - feel free to try script https://github.com/AndriiNikitin/bugs/blob/master/MDEV-13849.sh , which produces output below:

$ bash ~/bugs/MDEV-13849.sh 
Setup default Environs cluster if needed
 
get plugins
 
Already up-to-date.
 
Already up-to-date.
 
generate templates
 
Process 25369 still exists, sleeping 1 sec
 
Process 25409 still exists, sleeping 1 sec
 
Process 25445 still exists, sleeping 1 sec
 
Process 25479 still exists, sleeping 1 sec
 
download and unpack tar packages
 
generate my.cnf and install datadirs
 
m1 :
 
o1 :
 
o2 :
 
o3 :
 
m1 :Installing MariaDB/MySQL system tables in '/home/a/env1/m1-10.2.8/dt' ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/bin/mysqladmin' -u root password 'new-password' '/home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/bin/mysqladmin' -u root -h UBINTI password 'new-password' Alternatively you can run: '/home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8' ; /home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/bin/mysqld_safe --datadir='/home/a/env1/m1-10.2.8/dt' You can test the MariaDB daemon with mysql-test-run.pl cd '/home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/
 
o1 :2017-09-21 15:54:06 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
 
o2 :2017-09-21 15:54:23 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
 
o3 :2017-09-21 15:54:41 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
 
start up servers
 
m1 :Waiting for MySQL server to answer2017-09-21 15:54:57 140011656914432 [Note] /home/a/env1/m1-10.2.8/../_depot/m-tar/10.2.8/bin/mysqld (mysqld 10.2.8-MariaDB) starting as process 10984 ...
 
2017-09-21 15:54:57 140011656914432 [Warning] Changed limits: max_open_files: 1024  max_connections: 151  table_cache: 431
 
 
 
mysqld is alive
 
o1 :Waiting for MySQL server to answer
 
mysqld is alive
 
o2 :Waiting for MySQL server to answer
 
mysqld is alive
 
o3 :Waiting for MySQL server to answer
 
mysqld is alive
 
initialize root node
 
CREATE TABLE `history` (
 
`itemid` bigint(20) unsigned NOT NULL,
 
`clock` int(11) NOT NULL DEFAULT 0,
 
`value` double(16,4) NOT NULL DEFAULT 0.0000,
 
`ns` int(11) NOT NULL DEFAULT 0,
 
KEY `history_1` (`itemid`,`clock`)
 
) ENGINE=InnoDB
 
PARTITION BY HASH (itemid + clock)
 
(
 
PARTITION pt1,
 
PARTITION pt2,
 
PARTITION pt3
 
);
 
create tables
 
m1 :CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` double(16,4) NOT NULL DEFAULT 0.0000, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB ENGINE SPIDER COMMENT 'wrapper "mysql", table "history" ' PARTITION BY HASH (itemid + clock) ( PARTITION pt1 comment 'host "127.0.0.1", port "3607", user "root"' , PARTITION pt2 comment 'host "127.0.0.1", port "3608", user "root"' , PARTITION pt3 comment 'host "127.0.0.1", port "3609", user "root"' );
 
ok
 
o1 :CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` double(16,4) NOT NULL DEFAULT 0.0000, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB;
 
ok
 
o2 :CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` double(16,4) NOT NULL DEFAULT 0.0000, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB;
 
ok
 
o3 :CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` double(16,4) NOT NULL DEFAULT 0.0000, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB;
 
ok
 
check tables are here on each node
 
m1 :0
 
o1 :0
 
o2 :0
 
o3 :0
 
populate 100000 rows through root node
 
check rows on each node
 
m1 :100000
 
o1 :33333
 
o2 :33333
 
o3 :33334
 
m1 :10.2.8-MariaDB
 
o1 :5.7.19
 
o2 :5.7.19
 
o3 :5.7.19

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