[MDEV-4461] Server uses all memory and CPU in remove_eq_conds Created: 2013-04-30  Updated: 2013-05-05  Resolved: 2013-05-05

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2, 5.5.30
Fix Version/s: 10.0.3, 5.5.31

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4336 left join hangs in optimizing state, ... Closed

 Description   

(from IRC, ekle86)

Query:

SELECT
 
        domains.domain_id,domains.domain_name,domain_checks.LAST
 
FROM
 
        projects2domains
 
JOIN
 
        domains ON ( projects2domains.domain_id = domains.domain_id )
 
JOIN
 
        projects ON (projects2domains.project_id = projects.id)
 
LEFT JOIN
 
        domain_checks ON (projects2domains.domain_id = domain_checks.domain_id AND domain_checks.searchengine_id = 50 )  
WHERE  projects.last_use > DATE_SUB( NOW( ) , INTERVAL 12 MONTH ) AND  ( domain_checks.LAST < DATE_SUB(CURDATE(),INTERVAL 30 DAY) OR ISNULL(domain_checks.LAST))  GROUP BY projects2domains.domain_id  ORDER BY domain_checks.LAST,domain_id DESC LIMIT 1;

MySQL dump:

-- MySQL dump 10.14  Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64)
 
--
 
-- Host: localhost    Database: crash
 
-- ------------------------------------------------------
 
-- Server version       5.5.30-MariaDB-mariadb1~squeeze-log
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
--
 
-- Current Database: `crash`
 
--
 
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crash` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
 
USE `crash`;
 
 
--
 
-- Table structure for table `domain_checks`
 
--
 
 
DROP TABLE IF EXISTS `domain_checks`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `domain_checks` (
 
  `domain_id` INT(11) UNSIGNED NOT NULL,
 
  `searchengine_id` INT(10) UNSIGNED NOT NULL,
 
  `last` DATE NOT NULL,
 
  UNIQUE KEY `domain_id` (`domain_id`,`searchengine_id`),
 
  KEY `searchengine_id` (`searchengine_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
--
 
-- Dumping data for table `domain_checks`
 
--
 
 
LOCK TABLES `domain_checks` WRITE;
 
/*!40000 ALTER TABLE `domain_checks` DISABLE KEYS */;
 
/*!40000 ALTER TABLE `domain_checks` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
--
 
-- Table structure for table `domains`
 
--
 
 
DROP TABLE IF EXISTS `domains`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `domains` (
 
  `domain_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `domain_name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
 
  `domain_rname` VARCHAR(255) COLLATE utf8_bin NOT NULL,
 
  `ip` VARCHAR(15) COLLATE utf8_bin NOT NULL,
 
  `country_code` CHAR(2) COLLATE utf8_bin NOT NULL,
 
  `last_country` DATE NOT NULL,
 
  `last_crawler` DATE NOT NULL,
 
  `last_rank` DATE NOT NULL,
 
  PRIMARY KEY (`domain_id`),
 
  UNIQUE KEY `domain_name` (`domain_name`),
 
  KEY `domain_rname` (`domain_rname`),
 
  KEY `lastdownload` (`last_crawler`),
 
  KEY `last_country` (`last_country`),
 
  KEY `last_rank` (`last_rank`),
 
  KEY `ip` (`ip`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
--
 
-- Dumping data for table `domains`
 
--
 
 
LOCK TABLES `domains` WRITE;
 
/*!40000 ALTER TABLE `domains` DISABLE KEYS */;
 
/*!40000 ALTER TABLE `domains` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
--
 
-- Table structure for table `projects`
 
--
 
 
DROP TABLE IF EXISTS `projects`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `projects` (
 
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `customer` INT(10) UNSIGNED NOT NULL,
 
  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
 
  `last_use` datetime NOT NULL,
 
  `googleAPIaccountEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,
 
  `googleAPIaccountPassword` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `googleAPIclientEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,
 
  `googleAPIdeveloperToken` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `googleAPIapplicationToken` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `googleAdwordsAllowChanges` tinyint(4) NOT NULL,
 
  `googleAdwordsCampaign` VARCHAR(64) COLLATE utf8_bin NOT NULL,
 
  `googleAnalyticsEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,
 
  `googleAnalyticsPassword` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `googleAnalyticsAccount` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `googleAnalyticsGoal` VARCHAR(32) COLLATE utf8_bin NOT NULL,
 
  `piwikURL` VARCHAR(255) COLLATE utf8_bin NOT NULL,
 
  `piwikAUTH` CHAR(32) COLLATE utf8_bin NOT NULL,
 
  `piwikSITE` INT(10) UNSIGNED NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`),
 
  UNIQUE KEY `reseller_id` (`customer`,`id`),
 
  KEY `reseller` (`customer`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
--
 
-- Dumping data for table `projects`
 
--
 
 
LOCK TABLES `projects` WRITE;
 
/*!40000 ALTER TABLE `projects` DISABLE KEYS */;
 
/*!40000 ALTER TABLE `projects` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
--
 
-- Table structure for table `projects2domains`
 
--
 
 
DROP TABLE IF EXISTS `projects2domains`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `projects2domains` (
 
  `project_id` INT(10) UNSIGNED NOT NULL,
 
  `domain_id` INT(11) UNSIGNED NOT NULL,
 
  `competitor` INT(1) NOT NULL,
 
  PRIMARY KEY (`project_id`,`domain_id`),
 
  KEY `domain_id` (`domain_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=FIXED;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
--
 
-- Dumping data for table `projects2domains`
 
--
 
 
LOCK TABLES `projects2domains` WRITE;
 
/*!40000 ALTER TABLE `projects2domains` DISABLE KEYS */;
 
/*!40000 ALTER TABLE `projects2domains` ENABLE KEYS */;
 
UNLOCK TABLES;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Stack trace after killing the hanging server with SIGABRT:

#0  0x0000000000808dc1 in Arg_comparator::is_owner_equal_func (this=0x7fd9fb4aff98) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:107
 
#1  0x00000000007f7391 in Arg_comparator::set_compare_func (this=0x7fd9fb4aff98, item=0x7fd9fb4afed0, type=TIME_RESULT) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:591
 
#2  0x00000000007f80b5 in Arg_comparator::set_cmp_func (this=0x7fd9fb4aff98, owner_arg=0x7fd9fb4afed0, a1=0x7fd9fb4aff60, a2=0x7fd9fb4aff68, type=TIME_RESULT) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:796
 
#3  0x000000000067a9f2 in Arg_comparator::set_cmp_func (this=0x7fd9fb4aff98, owner_arg=0x7fd9fb4afed0, a1=0x7fd9fb4aff60, a2=0x7fd9fb4aff68, set_null_arg=true) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:76
 
#4  0x000000000067aa39 in Item_bool_func2::set_cmp_func (this=0x7fd9fb4afed0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:376
 
#5  0x00000000007f7335 in Item_bool_func2::fix_length_and_dec (this=0x7fd9fb4afed0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:583
 
#6  0x0000000000820f19 in Item_func::fix_fields (this=0x7fd9fb4afed0, thd=0x31f1a00, ref=0x7fd9fb4b0140) at /home/elenst/bzr/5.5/sql/item_func.cc:231
 
#7  0x00000000008038e4 in Item_cond::fix_fields (this=0x7fd9fb4b0048, thd=0x31f1a00, ref=0x7fdb17a27fa0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:4299
 
#8  0x000000000065f793 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fd9fb4b0048, cond_value=0x7fdb17a28188) at /home/elenst/bzr/5.5/sql/sql_select.cc:13597
 
#9  0x000000000065f053 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fdad001ab28, cond_value=0x7fdb17a28298) at /home/elenst/bzr/5.5/sql/sql_select.cc:13453
 
#10 0x000000000065f053 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fdad004a360, cond_value=0x7fdad001b578) at /home/elenst/bzr/5.5/sql/sql_select.cc:13453
 
#11 0x000000000065fbce in remove_eq_conds (thd=0x31f1a00, cond=0x7fdad004a360, cond_value=0x7fdad001b578) at /home/elenst/bzr/5.5/sql/sql_select.cc:13700
 
#12 0x000000000065ef34 in optimize_cond (join=0x7fdad001b258, conds=0x7fdad004a360, join_list=0x31f4920, ignore_on_conds=false, cond_value=0x7fdad001b578, cond_equal=0x7fdad001b698) at /home/elenst/bzr/5.5/sql/sql_select.cc:13412
 
#13 0x000000000063f81a in JOIN::optimize (this=0x7fdad001b258) at /home/elenst/bzr/5.5/sql/sql_select.cc:1070
 
#14 0x00000000006469e1 in mysql_select (thd=0x31f1a00, rref_pointer_array=0x31f4a08, tables=0x7fdad0006e28, wild_num=0, fields=..., conds=0x7fdad001ac38, og_num=3, order=0x7fdad001aff0, group=0x7fdad001ae98, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fdad001b238, unit=0x31f40d8, select_lex=0x31f47b0) at /home/elenst/bzr/5.5/sql/sql_select.cc:3048
 
#15 0x000000000063d5ea in handle_select (thd=0x31f1a00, lex=0x31f4028, result=0x7fdad001b238, setup_tables_done_option=0) at /home/elenst/bzr/5.5/sql/sql_select.cc:318
 
#16 0x000000000061643d in execute_sqlcom_select (thd=0x31f1a00, all_tables=0x7fdad0006e28) at /home/elenst/bzr/5.5/sql/sql_parse.cc:4641
 
#17 0x000000000060ee8c in mysql_execute_command (thd=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_parse.cc:2195
 
#18 0x0000000000618cdc in mysql_parse (thd=0x31f1a00, rawbuf=0x7fdad00064f8 "SELECT\n        domains.domain_id,domains.domain_name,domain_checks.LAST\nFROM\n        projects2domains\nJOIN\n        domains ON ( projects2domains.domain_id = domains.domain_id )\nJOIN\n        projects O"..., length=627, parser_state=0x7fdb17a29550) at /home/elenst/bzr/5.5/sql/sql_parse.cc:5759
 
#19 0x000000000060c3dc in dispatch_command (command=COM_QUERY, thd=0x31f1a00, packet=0x32e1021 "SELECT\n        domains.domain_id,domains.domain_name,domain_checks.LAST\nFROM\n        projects2domains\nJOIN\n        domains ON ( projects2domains.domain_id = domains.domain_id )\nJOIN\n        projects O"..., packet_length=627) at /home/elenst/bzr/5.5/sql/sql_parse.cc:1068
 
#20 0x000000000060b61d in do_command (thd=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_parse.cc:794
 
#21 0x00000000007109c1 in do_handle_one_connection (thd_arg=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1266
 
#22 0x00000000007103a8 in handle_one_connection (arg=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1181
 
#23 0x00007fdb17035e9a in start_thread (arg=0x7fdb17a2a700) at pthread_create.c:308
 
#24 0x00007fdb16329cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Stack trace from:

revision-id: wlad@montyprogram.com-20130429222947-so2nbrsb1t03hnp6
 
revno: 3739
 
branch-nick: 5.5



 Comments   
Comment by Brian Evans [ 2013-04-30 ]

I found a simplified version of a query with the above schema that produces the same result.

SELECT * FROM projects2domains p2d LEFT JOIN domain_checks dc ON p2d.domain_id = dc.domain_id WHERE dc.LAST < '2013-04-01' OR ISNULL(dc.LAST)

Note: if either one of the where conditions is removed, the memory condition is not reached

Comment by Elena Stepanova [ 2013-04-30 ]

Complete test case using the simplified query from Brian is below.
Tables can be empty, but don't have to be to reproduce the problem, so in the test case they are populated with minimal data set.
Reproducible with the default optimizer_switch as well as with all OFF values.
EXPLAIN also fails.

DROP DATABASE IF EXISTS crash;
CREATE DATABASE crash;

USE `crash`;

CREATE TABLE `domain_checks` (
`domain_id` INT(11) NOT NULL,
`last` DATE NOT NULL
);
INSERT INTO domain_checks VALUES (1, '2013-04-29'),(2, '2013-04-30');

CREATE TABLE `projects2domains` (
`domain_id` INT(11) NOT NULL
);
INSERT INTO projects2domains VALUES (1),(2);

SELECT * FROM projects2domains p2d LEFT JOIN domain_checks dc ON p2d.domain_id = dc.domain_id WHERE dc.last < '2013-04-01' OR ISNULL(dc.last) ;

Comment by Elena Stepanova [ 2013-04-30 ]

This is yet another problem which appeared in 5.5 tree with 5.3->5.5 merge (revno 3671), but it's not reproducible on 5.3, so it's hard to say which particular change could have caused it, and whether it's fixed by now. I have to presume it's not.

Comment by Elena Stepanova [ 2013-05-01 ]

Found it eventually – it's most likely the same as MDEV-4336, we'll see when the other one is fixed.

Comment by Elena Stepanova [ 2013-05-04 ]

MDEV-4336 is supposedly fixed in 5.3 now (revno 3655), need to check that the provided scenarios work all right on 5.5 after the merge.

Comment by Elena Stepanova [ 2013-05-05 ]

Not reproducible any longer on 5.5 (revno 3747) after the merge from 5.3, fixed along with MDEV-4336

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