[MDEV-24793] inner joins failing and potentially crashing mariadb server Created: 2021-02-05  Updated: 2021-03-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.8
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Christopher E Bowen Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

primary Production env 5x Rhel 8 24G ram 8 cpus galera cluster
primary dev 3x rhel 8 8G 8 CPU galera cluster
Both of the above are vms

developer dev single node Cent OS 7

Attachments: File 10.5id1    

 Description   

In the past week we have been having issues with some statements using inner joins, in one case a join will sit there pegging the CPU of the node it is on to 100% until stopped, we typically stop the process after 30m or so. The query for this is 

SELECT COUNT(*) FROM `resources` INNER JOIN `is_part_ofs_resources` ON `resources`.`id` = `is_part_ofs_resources`.`resource_id` WHERE `is_part_ofs_resources`.`is_part_of_id` = 39 AND `resources`.`discarded_at` IS NULL

This should have about 13k rows returned . The query has been tested to work fine in the latest versions of 10.1, 10.2, 10.3 and 10.4, and only hangs in 10.5

If we change the query to id = 1 it runs fine with only about 1k returned.

Attached is the json output of the query analizer for the smaller version of is_part_of_id=1

 Comments   
Comment by Alice Sherepa [ 2021-02-08 ]

Could you please add the output of
SHOW CREATE TABLE resources;
SHOW CREATE TABLE is_part_ofs_resources;

Comment by Christopher E Bowen [ 2021-02-08 ] 

| Table     | Create Table                                                                                                                                                                                                                                                                         
| resources | CREATE TABLE `resources` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `fileName` varchar(255) DEFAULT NULL,
 
  `batchNumber` varchar(50) DEFAULT NULL,
 
  `recordStatus` varchar(50) DEFAULT NULL,
 
  `itemNumber_local` varchar(50) DEFAULT NULL,
 
  `classification_id` int(10) DEFAULT NULL,
 
  `title` varchar(255) DEFAULT NULL,
 
  `title_source_id` int(10) DEFAULT NULL,
 
  `description` longtext DEFAULT NULL,
 
  `creator_id` int(10) DEFAULT NULL,
 
  `url` varchar(255) DEFAULT NULL,
 
  `digitalCreationDate` varchar(50) DEFAULT NULL,
 
  `digitalResolution` varchar(50) DEFAULT NULL,
 
  `internalNote` longtext DEFAULT NULL,
 
  `itemLocationNote` longtext DEFAULT NULL,
 
  `masterArchived` varchar(50) DEFAULT NULL,
 
  `accessArchived` varchar(50) DEFAULT NULL,
 
  `masterOnline` varchar(50) DEFAULT NULL,
 
  `accessOnline` varchar(50) DEFAULT NULL,
 
  `editiion` varchar(255) DEFAULT NULL,
 
  `location_identifier` varchar(255) DEFAULT NULL,
 
  `generalNotes` longtext DEFAULT NULL,
 
  `titleOtherSourceID` int(10) DEFAULT NULL,
 
  `originalFormatExtent` varchar(255) DEFAULT NULL,
 
  `recordCreateDate` timestamp NULL DEFAULT current_timestamp(),
 
  `oai` tinyint(1) NOT NULL DEFAULT 0,
 
  `pubSrvScan` tinyint(1) NOT NULL DEFAULT 0,
 
  `openWebRights` tinyint(1) NOT NULL DEFAULT 0,
 
  `flickrID` int(10) DEFAULT NULL,
 
  `flickrUploadDate` datetime DEFAULT NULL,
 
  `recordSource` varchar(55) DEFAULT NULL,
 
  `right_id` int(10) DEFAULT NULL,
 
  `repository_id` int(10) DEFAULT NULL,
 
  `oldTitleOtherSourceID` int(10) DEFAULT NULL,
 
  `digital_creator_id` int(10) DEFAULT NULL,
 
  `thumbnail_url` varchar(255) DEFAULT NULL,
 
  `original_stub_title` varchar(255) DEFAULT NULL,
 
  `unmappable_stub_attributes` text DEFAULT NULL,
 
  `jp2_path` varchar(255) DEFAULT NULL,
 
  `total_associated_objects` int(11) DEFAULT NULL,
 
  `created_at` datetime DEFAULT NULL,
 
  `updated_at` datetime DEFAULT NULL,
 
  `existing_audio` tinyint(1) DEFAULT 0,
 
  `project_identifier_note` text DEFAULT NULL,
 
  `touch_update_at` datetime DEFAULT NULL,
 
  `public_access` tinyint(1) DEFAULT 1,
 
  `selected` tinyint(1) DEFAULT 0,
 
  `selected_by` int(11) DEFAULT NULL,
 
  `selected_at` datetime DEFAULT NULL,
 
  `pdf` tinyint(1) DEFAULT 0,
 
  `luna_id` varchar(255) DEFAULT NULL,
 
  `archivesspace_archival_object_id` int(11) DEFAULT NULL,
 
  `redirect_target` varchar(255) DEFAULT NULL,
 
  `rights_statement_id` int(11) DEFAULT NULL,
 
  `cclicense_id` int(11) DEFAULT NULL,
 
  `wonda_id` int(11) DEFAULT NULL,
 
  `archivesspace_uri` varchar(255) DEFAULT NULL,
 
  `finding_aid_url` varchar(255) DEFAULT NULL,
 
  `discarded_at` datetime DEFAULT NULL,
 
  `discarded_by` int(11) DEFAULT NULL,
 
  `ocred_by` int(11) DEFAULT NULL,
 
  `ocred_at` datetime DEFAULT NULL,
 
  `archivesspace_container` text DEFAULT NULL,
 
  `ftp_id` int(11) DEFAULT NULL,
 
  `ftp_manifest` text DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `catalogingStatus_ID` (`recordStatus`),
 
  KEY `classification_resourceMain` (`classification_id`),
 
  KEY `fileName` (`fileName`),
 
  KEY `flickrID` (`flickrID`),
 
  KEY `LocationID` (`location_identifier`),
 
  KEY `Resource_ID` (`id`),
 
  KEY `titleSource_resourceMain` (`title_source_id`),
 
  KEY `index_resources_on_classification_id` (`classification_id`),
 
  KEY `index_resources_on_creator_id` (`creator_id`),
 
  KEY `index_resources_on_luna_id` (`luna_id`),
 
  KEY `index_resources_on_archivesspace_archival_object_id` (`archivesspace_archival_object_id`),
 
  KEY `index_resources_on_rights_statement_id` (`rights_statement_id`),
 
  KEY `index_resources_on_discarded_at` (`discarded_at`),
 
  KEY `index_resources_on_discarded_by` (`discarded_by`),
 
  KEY `index_resources_on_ocred_by` (`ocred_by`),
 
  KEY `index_resources_on_ocred_at` (`ocred_at`),
 
  CONSTRAINT `fk_rails_80f1442257` FOREIGN KEY (`rights_statement_id`) REFERENCES `rights_statements` (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1110190 DEFAULT CHARSET=utf8 |
 
 
 
 
 
| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                              |
 
| is_part_ofs_resources | CREATE TABLE `is_part_ofs_resources` (
 
  `is_part_of_id` int(10) DEFAULT NULL,
 
  `resource_id` int(10) DEFAULT NULL,
 
  KEY `isPartOfisPartOf_resource` (`is_part_of_id`),
 
  KEY `index_is_part_ofs_resources_on_resource_id` (`resource_id`),
 
  KEY `index_is_part_ofs_resources_on_is_part_of_id` (`is_part_of_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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