[MDEV-4461] Server uses all memory and CPU in remove_eq_conds - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.2, 5.5.30
Fix Version/s: 10.0.3, 5.5.31
Component/s: None
Labels:
None

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

Attachments

Issue Links

relates to

MDEV-4336 left join hangs in optimizing state, consumes all memory and leads to crash

Closed

Activity

Ascending order - Click to sort in descending order

Brian Evans added a comment - 2013-04-30 19:53 - edited

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

Brian Evans added a comment - 2013-04-30 19:53 - edited 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

Elena Stepanova added a comment - 2013-04-30 20:40 - edited

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) ;

Elena Stepanova added a comment - 2013-04-30 20:40 - edited 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) ;

Elena Stepanova added a comment - 2013-04-30 22:11

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.

Elena Stepanova added a comment - 2013-04-30 22:11 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.

Elena Stepanova added a comment - 2013-05-01 00:08 - edited

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

Elena Stepanova added a comment - 2013-05-01 00:08 - edited Found it eventually – it's most likely the same as MDEV-4336 , we'll see when the other one is fixed.

Elena Stepanova added a comment - 2013-05-04 12:01

~~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.

Elena Stepanova added a comment - 2013-05-04 12:01 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.

Elena Stepanova added a comment - 2013-05-05 12:33

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

Elena Stepanova added a comment - 2013-05-05 12:33 Not reproducible any longer on 5.5 (revno 3747) after the merge from 5.3, fixed along with MDEV-4336

People

Assignee:: Unassigned

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2013-04-30 19:04

Updated:: 2013-05-05 12:33

Resolved:: 2013-05-05 12:33

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server