[MDEV-28744] Random server crashes on select queries Created: 2022-06-03  Updated: 2024-02-02  Resolved: 2022-10-30

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Data Manipulation - Subquery, Platform Debian, Storage Engine - InnoDB
Affects Version/s: 10.4.22, 10.5.15
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Sylvain ARBAUDIE Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: crash
Environment:

debian buster, 128G memory, 274G temporary space

Attachments: Text File error.log_202206032137.log     Text File mysql.cnf.txt    

 Description   

Mariadb keeps crashing for no apparent reasons at random times.

 Comments   
Comment by Sylvain ARBAUDIE [ 2022-06-03 ]

Additional info :

this query makes the server go down 100% 

MariaDB [spartoo]> explain SELECT o.orders_id,         COUNT( DISTINCT og.id ) AS cmd_w_mp,         sum(op.products_quantity),         SUM(op.final_price)/(1+(v.rate/100)),         count(op.orders_products_id),         count(distinct(o.orders_id)),         IF(ROW_NUMBER() OVER (  PARTITION BY o.orders_id ORDER BY sum(op.products_quantity) DESC) = 1, 1, 0) FROM orders o FORCE INDEX( date_purchased ) JOIN orders_status os         ON os.orders_status_id = o.orders_status         AND o.customers_id > 0         AND o.not_in_CA = 0         AND os.include_for_CA = 1         AND o.date_purchased BETWEEN '2021-03-01 00:00:00' AND '2021-03-31 23:59:59' JOIN mb_partnership_groups_members_M pgm         ON pgm.partnership_id = o.partnership_id JOIN vat v         ON o.vat_id = v.vat_id JOIN orders_general og         ON o.orders_id = og.orders_id JOIN mb_partnership pa         ON pa.partnership_id = o.partnership_id         AND pa.partnership_type = 1 join orders_products op on op.orders_id=o.orders_id join products_stock ps on ps.products_stock_id=op.products_stock_id join products p on p.products_id=ps.products_id WHERE         o.orders_id IN (32036547,32036546,32036548 )         AND pgm.partnership_group_id = 6 GROUP BY o.orders_id, p.products_type_id ORDER BY o.orders_id, p.products_type_id;
 
 
 
| id   | select_type | table | type   | possible_keys                           | key            | key_len | ref                            | rows   | Extra                                                               |
 
 
 
|    1 | SIMPLE      | o     | range  | date_purchased                          | date_purchased | 10      | NULL                           | 317400 | Using index condition; Using where; Using temporary; Using filesort |
 
|    1 | SIMPLE      | pa    | eq_ref | PRIMARY,partnership_type                | PRIMARY        | 2       | spartoo.o.partnership_id       | 1      | Using where                                                         |
 
|    1 | SIMPLE      | os    | eq_ref | PRIMARY,orders_status_id,include_for_CA | PRIMARY        | 2       | spartoo.o.orders_status        | 1      | Using where                                                         |
 
|    1 | SIMPLE      | v     | eq_ref | PRIMARY                                 | PRIMARY        | 1       | spartoo.o.vat_id               | 1      | Using where                                                         |
 
|    1 | SIMPLE      | pgm   | eq_ref | PRIMARY                                 | PRIMARY        | 3       | const,spartoo.o.partnership_id | 1      | Using index                                                         |
 
|    1 | SIMPLE      | og    | eq_ref | orders_id                               | orders_id      | 4       | spartoo.o.orders_id            | 1      | Using index                                                         |
 
|    1 | SIMPLE      | op    | ref    | orders_id_2,orders_id,products_stock_id | orders_id_2    | 4       | spartoo.o.orders_id            | 1      |                                                                     |
 
|    1 | SIMPLE      | ps    | eq_ref | PRIMARY,products_id_3                   | PRIMARY        | 4       | spartoo.op.products_stock_id   | 1      |                                                                     |
 
|    1 | SIMPLE      | p     | eq_ref | PRIMARY                                 | PRIMARY        | 4       | spartoo.ps.products_id         | 1      |                                                                     |
 
 
 
9 rows in set (0.048 sec)

Comment by Sylvain ARBAUDIE [ 2022-06-03 ]

Also this statemetn has been spotted along with some other crashes : 

SELECT oc.orders_id 
FROM orders_M.orders_en oc
 
  JOIN orders o ON oc.orders_id = o.orders_id
 
  JOIN orders_status os ON os.orders_status_id = o.orders_status
 
WHERE os.include_for_CA = 0
 
    AND o.date_purchased BETWEEN '2022-05-01' AND '2022-05-31 23:59:59';

Comment by Alice Sherepa [ 2022-06-03 ]

Could you please provide the error log and SHOW CREATE TABLE for the involved tables (orders_M.orders_en, orders, orders_status)

Comment by Sylvain ARBAUDIE [ 2022-06-03 ] 

MariaDB [(none)]> show create table orders_M.orders_en\G
 
 
 
      Table: orders_en
 
Create Table: CREATE TABLE `orders_en` (
 
 `orders_id` int(11) unsigned NOT NULL,
 
 `date_purchased` date NOT NULL,
 
 `track_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `track_id_categorie` smallint(5) unsigned NOT NULL,
 
 `track_id_categorie_parent` smallint(5) unsigned NOT NULL,
 
 `order_new` tinyint(3) unsigned NOT NULL,
 
 `order_payante` tinyint(3) unsigned NOT NULL,
 
 `total` double(8,2) unsigned NOT NULL,
 
 `total_ht` double(8,2) unsigned NOT NULL,
 
 `total_euros` double(8,2) NOT NULL,
 
 `total_euros_ht` double(8,2) NOT NULL,
 
 `ca_produit` double(8,2) NOT NULL,
 
 `nb_produit` tinyint(3) unsigned NOT NULL,
 
 `nb_returns` tinyint(3) unsigned NOT NULL,
 
 `customers_id` int(11) unsigned NOT NULL,
 
 `payment_method` tinyint(3) unsigned NOT NULL,
 
 `payment_price` double(8,2) unsigned NOT NULL,
 
 `shipping_id` smallint(5) unsigned NOT NULL,
 
 `shipping_price` double(8,2) unsigned NOT NULL,
 
 `coupon_id` int(11) unsigned NOT NULL,
 
 `marge` double(8,2) NOT NULL,
 
 `cmd_unique` tinyint(3) unsigned NOT NULL,
 
 `cmd_mp` float unsigned NOT NULL,
 
 `retail_website` smallint(5) unsigned NOT NULL,
 
 PRIMARY KEY (`orders_id`),
 
 KEY `customers_id` (`customers_id`),
 
 KEY `date_purchased` (`date_purchased`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 
1 row in set (0.000 sec)

Comment by Sylvain ARBAUDIE [ 2022-06-03 ] 

MariaDB [(none)]> show create table spartoo.orders\G
 
 
 
      Table: orders
 
Create Table: CREATE TABLE `orders` (
 
 `orders_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
 `orders_id_crypt` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_id` int(10) unsigned NOT NULL DEFAULT 0,
 
 `customers_firstname` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_lastname` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_company` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_street_address` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_suburb` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_city` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_postcode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_state` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_country` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_country_id` smallint(5) unsigned NOT NULL,
 
 `customers_country_id_new` smallint(5) unsigned DEFAULT NULL,
 
 `customers_telephone` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_email_address` varchar(96) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `customers_address_format_id` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
 `delivery_firstname` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_lastname` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_company` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_street_address` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_suburb` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_city` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_postcode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_state` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_country` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `delivery_country_id` smallint(5) unsigned NOT NULL,
 
 `delivery_country_id_new` smallint(5) unsigned DEFAULT NULL,
 
 `delivery_address_format_id` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
 `payment_method` tinyint(3) unsigned NOT NULL,
 
 `payment_price` decimal(10,2) NOT NULL DEFAULT 0.00,
 
 `last_modified` datetime DEFAULT NULL,
 
 `date_purchased` datetime DEFAULT NULL,
 
 `orders_status` smallint(5) unsigned NOT NULL DEFAULT 0,
 
 `order_date_sent` datetime DEFAULT NULL,
 
 `order_date_trac` datetime DEFAULT NULL,
 
 `currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
 `currency_id` tinyint(4) NOT NULL DEFAULT 0,
 
 `currency_rate` decimal(10,4) NOT NULL DEFAULT 1.0000,
 
 `vat_id` tinyint(3) unsigned NOT NULL DEFAULT 1,
 
 `test_AB` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
 `test_AB2` tinyint(3) unsigned NOT NULL,
 
 `shipping_id` smallint(5) unsigned NOT NULL,
 
 `shipping_price` decimal(10,2) NOT NULL,
 
 `normal_total` decimal(10,2) NOT NULL COMMENT 'Sum products final price + Shipping price + Payment Price',
 
 `coupon_id` int(11) unsigned NOT NULL,
 
 `sub_total` decimal(10,2) NOT NULL COMMENT 'Sum products final price',
 
 `total` decimal(10,2) NOT NULL COMMENT 'Sum products final price + Shipping price + Payment price - cheque cadeau',
 
 `order_origin` int(11) unsigned NOT NULL DEFAULT 0,
 
 `order_first_origin` int(11) unsigned NOT NULL,
 
 `not_in_CA` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0:cmd normale et dupliquée-1:cmd issue de retour',
 
 `explication_orders_back` text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 
 `reason_orders_back` tinyint(3) unsigned DEFAULT 0,
 
 `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'deprecated, utiliser ip_number à la place',
 
 `ip_number` int(11) unsigned NOT NULL,
 
 `accesslog_id` int(11) unsigned NOT NULL,
 
 `language_id` tinyint(3) unsigned DEFAULT NULL,
 
 `partnership_id` smallint(5) unsigned NOT NULL DEFAULT 0,
 
 `retail_website` smallint(5) unsigned NOT NULL,
 
 `warehouse_id` smallint(5) unsigned NOT NULL DEFAULT 1,
 
 PRIMARY KEY (`orders_id`),
 
 KEY `date_purchased` (`date_purchased`),
 
 KEY `customers_id_2` (`customers_id`,`orders_status`,`date_purchased`),
 
 KEY `ip_number` (`ip_number`),
 
 KEY `accesslog_id` (`accesslog_id`),
 
 KEY `retail_website` (`retail_website`,`orders_status`),
 
 KEY `coupon_id` (`coupon_id`),
 
 KEY `order_origin` (`order_origin`),
 
 KEY `order_date_sent` (`order_date_sent`),
 
 KEY `order_first_origin` (`order_first_origin`),
 
 KEY `vat_id` (`vat_id`),
 
 KEY `customers_email_address` (`customers_email_address`),
 
 KEY `customers_firstname` (`customers_firstname`),
 
 KEY `customers_lastname` (`customers_lastname`),
 
 KEY `orders_id_crypt` (`orders_id_crypt`),
 
 KEY `order_date_trac` (`order_date_trac`),
 
 KEY `warehouse_id` (`warehouse_id`),
 
 KEY `shipping_payment` (`shipping_id`,`payment_method`),
 
 KEY `orders_status` (`orders_status`),
 
 KEY `partnership_id` (`partnership_id`,`delivery_country_id`) USING BTREE
 
) ENGINE=InnoDB AUTO_INCREMENT=36417856 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 
1 row in set (0.000 sec)

Comment by Sylvain ARBAUDIE [ 2022-06-03 ] 

MariaDB [(none)]> show create table spartoo.orders_status\G
 
 
 
      Table: orders_status
 
Create Table: CREATE TABLE `orders_status` (
 
 `orders_status_id` smallint(5) unsigned NOT NULL DEFAULT 0,
 
 `include_for_CA` tinyint(1) NOT NULL,
 
 `include_for_stock` tinyint(4) NOT NULL,
 
 `include_for_accounting` tinyint(4) NOT NULL DEFAULT 0,
 
 `include_for_37m` tinyint(4) NOT NULL DEFAULT 0,
 
 `payed_but_not_sent` tinyint(4) NOT NULL,
 
 `payed` tinyint(4) NOT NULL,
 
 `a_deduire` tinyint(3) unsigned NOT NULL,
 
 `rectif` tinyint(3) unsigned NOT NULL,
 
 PRIMARY KEY (`orders_status_id`),
 
 KEY `include_for_accounting` (`include_for_accounting`,`include_for_CA`),
 
 KEY `orders_status_id` (`orders_status_id`,`include_for_CA`),
 
 KEY `include_for_CA` (`include_for_CA`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 
1 row in set (0.000 sec)

Comment by Sylvain ARBAUDIE [ 2022-06-03 ]

The error log has also been added. the crashes happens a 10:42:07

Comment by Sylvain ARBAUDIE [ 2022-06-03 ]

i can't help but notice some occurences of pending fsync preventing file closure as in MDEV-25215

i dont think it crashes the instance tho

Comment by Sylvain ARBAUDIE [ 2022-06-16 ]

Some additional informations :
1. statement crashes the server even with an empty table
2. percona server doesn't crash with same statement/tables.

Comment by Sylvain ARBAUDIE [ 2022-06-30 ]

Hi there

can we have another eye on this issue please ?

Comment by Elena Stepanova [ 2022-07-25 ]

Are all crashes of the same kind?

2022-06-03 21:38:05 0x7eed537fe700  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.5.15/storage/innobase/dict/dict0dict.cc line 1248
 
InnoDB: Failing assertion: table->can_be_evicted

In addition to coredumps, whoever analyzes them will need at least the exact binary which produced them and corresponding libraries.

Comment by Marko Mäkelä [ 2022-09-26 ]

In error.log_202206032137.log there is evidence of 2 crashing threads: first the SIGSEGV and then 4 seconds later (while the stack trace for the first crash has not yet been produced) the InnoDB assertion failure.

Comment by Marko Mäkelä [ 2022-09-26 ]

SylvainArbaudie, where are the copies dynamic libraries that were linked with the server while the core dump was produced? Where was the server downloaded from, and what is the corresponding dbgsym package?

Comment by Sylvain ARBAUDIE [ 2022-11-04 ]

marko i will have the clientn answer this question.

i may have a very similar case but i am still waiting to have access to the logs

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