Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4461

Server uses all memory and CPU in remove_eq_conds

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: