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

Window Functions in Sproc cause crash

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.15
    • N/A
    • None
    • Centos 7

      96GB (DDR4 2666MHz), GB Memory:
      Dell PowerEdge R740xd Linux
      Single Socket Octo Core Intel Xeon 4110 2.10GHz, #Processors: 1, #Cores per Proc: 8

    Description

      The following query crashes the database.

          SELECT contentItem.id_contentitem, contentItem.id_con, contentItem.userid, contentItem.content, contentItem.textonly, contentItem.datemodify, contentItem.id_feature, contentItem.id_region, contentItem.id_block, contentItem.id_list, contentItem.extradata
          FROM (
            SELECT contentItem.*, ROW_NUMBER() OVER (PARTITION BY id_region ORDER BY id_contentitem DESC) row
            FROM contentItem
              INNER JOIN content
                ON contentItem.id_con = content.id_con
              INNER JOIN contentTemplateRegion
                ON contentItem.id_region = contentTemplateRegion.id_region
              WHERE id_dd = v_id_dd
                AND
                content.id_con = v_id_con
                AND
                  CASE
                    WHEN v_region IS NOT NULL
                    THEN region = v_region
                    ELSE 1 = 1
                  END
          ) contentItem
            WHERE row = 1;
      

      Back Trace:
      ======= Backtrace: =========
      /lib64/libc.so.6(__fortify_fail+0x37)[0x7f26dbde86e7]
      /lib64/libc.so.6(+0x116862)[0x7f26dbde6862]
      /lib64/libc.so.6(+0x118647)[0x7f26dbde8647]
      /usr/sbin/mysqld(my_addr_resolve+0xda)[0x556d49841a6a]
      /usr/sbin/mysqld(my_print_stacktrace+0x1c2)[0x556d4982b132]
      /usr/sbin/mysqld(handle_fatal_signal+0x355)[0x556d492b6a05]
      /lib64/libpthread.so.0(+0xf6d0)[0x7f26dda326d0]
      /usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x5d8)[0x556d490e8438]
      /usr/sbin/mysqld(+0x50bf28)[0x556d49154f28]
      /usr/sbin/mysqld(_Z11setup_groupP3THD20Bounds_checked_arrayIP4ItemEP10TABLE_LISTR4ListIS2_ES9_P8st_orderPbb+0x9d)[0x556d4916dbad]
      /usr/sbin/mysqld(_Z13setup_windowsP3THD20Bounds_checked_arrayIP4ItemEP10TABLE_LISTR4ListIS2_ES9_RS7_I11Window_specERS7_I16Item_window_funcE+0x1aa)[0x556d49241b5a]
      /usr/sbin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit+0x857)[0x556d49170077]
      /usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP3THDP13select_resultm+0x9c4)[0x556d491bc524]
      /usr/sbin/mysqld(_Z21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST+0x1b5)[0x556d491071a5]
      /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x556d49107ed4]
      /usr/sbin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x556d4911dce7]
      /usr/sbin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit+0xc5)[0x556d4916f8e5]
      /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x90c)[0x556d4917cf6c]
      /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x254)[0x556d4917d2d4]
      /usr/sbin/mysqld(+0x4197f9)[0x556d490627f9]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6864)[0x556d4912ea24]
      /usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x556d493e4686]
      /usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x9d)[0x556d493ea8ad]
      /usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x205)[0x556d493eae85]
      /usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x7b0)[0x556d493e76d0]
      /usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5ef)[0x556d493e8e5f]
      /usr/sbin/mysqld(+0x4d7f44)[0x556d49120f44]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x33da)[0x556d4912b59a]
      /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2de)[0x556d491313ae]
      /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x2135)[0x556d49134445]
      /usr/sbin/mysqld(_Z10do_commandP3THD+0x165)[0x556d49135075]
      /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x556d491f730a]
      /usr/sbin/mysqld(handle_one_connection+0x3d)[0x556d491f742d]
      /lib64/libpthread.so.0(+0x7e25)[0x7f26dda2ae25]
      /lib64/libc.so.6(clone+0x6d)[0x7f26dbdcebad]
      

      Tables to recreate schema

      -- 
      -- Set character set the client will use to send SQL statements to the server
      --
      SET NAMES 'utf8';
       
      --
      -- Set default database
      --
      USE mu1dse388fuemd;
       
      --
      -- Create table `contentTemplateRegion`
      --
      CREATE TABLE contentTemplateRegion (
        id_region int(11) NOT NULL AUTO_INCREMENT,
        id_template int(11) NOT NULL,
        id_category int(11) DEFAULT NULL,
        default_id_feature int(11) DEFAULT NULL,
        defaultcontent mediumtext DEFAULT NULL,
        default_id_block int(11) DEFAULT NULL,
        default_id_list int(11) DEFAULT NULL,
        default_extradata text DEFAULT NULL,
        regionname varchar(255) NOT NULL,
        region varchar(255) NOT NULL,
        notes varchar(1000) DEFAULT NULL,
        properties text DEFAULT NULL,
        displayorder int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (id_region)
      )
      ENGINE = INNODB,
      AUTO_INCREMENT = 64,
      AVG_ROW_LENGTH = 4096,
      CHARACTER SET utf8,
      COLLATE utf8_unicode_ci;
       
      --
      -- Create check constraint
      --
      ALTER TABLE contentTemplateRegion 
        ADD CONSTRAINT CK_contentTemplateRegion CHECK (properties is null or json_valid(properties));
       
      --
      -- Create index `UK_contentTemplateRegion` on table `contentTemplateRegion`
      --
      ALTER TABLE contentTemplateRegion
      ADD UNIQUE INDEX UK_contentTemplateRegion (id_template, region);
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentTemplateRegion
      ADD CONSTRAINT FK_contentTemplateRegion_contentBlock_id_block FOREIGN KEY (default_id_block)
      REFERENCES contentBlock (id_block) ON DELETE SET NULL ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentTemplateRegion
      ADD CONSTRAINT FK_contentTemplateRegion_contentPartCategory_id_category FOREIGN KEY (id_category)
      REFERENCES contentPartCategory (id_category) ON DELETE SET NULL ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentTemplateRegion
      ADD CONSTRAINT FK_contentTemplateRegion_contentPart_id_feature FOREIGN KEY (default_id_feature)
      REFERENCES contentPart (id_feature) ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentTemplateRegion
      ADD CONSTRAINT FK_contentTemplateRegion_contentTemplate_id_template FOREIGN KEY (id_template)
      REFERENCES contentTemplate (id_template) ON DELETE CASCADE ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentTemplateRegion
      ADD CONSTRAINT FK_contentTemplateRegion_featureBoxList_id_list FOREIGN KEY (default_id_list)
      REFERENCES featureBoxList (id_list) ON DELETE CASCADE ON UPDATE CASCADE;
       
      --
      -- Create table `content`
      --
      CREATE TABLE content (
        id_con int(7) NOT NULL AUTO_INCREMENT,
        id_dd int(11) NOT NULL,
        slug varchar(255) NOT NULL,
        id_status int(11) NOT NULL DEFAULT 10,
        id_contenttype int(11) NOT NULL,
        id_skeleton int(11) DEFAULT NULL,
        id_template int(11) DEFAULT NULL,
        subadmin varchar(255) DEFAULT NULL,
        allowsearch tinyint(4) NOT NULL DEFAULT 1,
        pagetitle varchar(255) NOT NULL,
        metatitle varchar(150) DEFAULT NULL,
        metakeywords varchar(1500) DEFAULT NULL,
        metadescription varchar(1500) DEFAULT NULL,
        dateadd datetime NOT NULL DEFAULT CURRENT_TIMESTAMP (),
        datemodify datetime NOT NULL DEFAULT CURRENT_TIMESTAMP (),
        notes text DEFAULT NULL,
        userid varchar(100) DEFAULT NULL,
        content_route varchar(512) DEFAULT NULL,
        content_pagelink varchar(255) DEFAULT NULL,
        PRIMARY KEY (id_con)
      )
      ENGINE = INNODB,
      AUTO_INCREMENT = 1206,
      AVG_ROW_LENGTH = 1365,
      CHARACTER SET utf8,
      COLLATE utf8_unicode_ci;
       
      --
      -- Create index `IDX_content` on table `content`
      --
      ALTER TABLE content
      ADD INDEX IDX_content (id_dd, content_pagelink);
       
      --
      -- Create index `UK_content` on table `content`
      --
      ALTER TABLE content
      ADD INDEX UK_content (id_dd);
       
      --
      -- Create index `UK_content2` on table `content`
      --
      ALTER TABLE content
      ADD UNIQUE INDEX UK_content2 (id_dd, slug);
       
      --
      -- Create index `UK_content3` on table `content`
      --
      ALTER TABLE content
      ADD UNIQUE INDEX UK_content3 (id_dd, content_route);
       
      --
      -- Create foreign key
      --
      ALTER TABLE content
      ADD CONSTRAINT FK_content_contentTemplate_id_skeleton FOREIGN KEY (id_skeleton)
      REFERENCES contentTemplate (id_template) ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE content
      ADD CONSTRAINT FK_content_contentTemplate_id_template FOREIGN KEY (id_template)
      REFERENCES contentTemplate (id_template) ON DELETE NO ACTION ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE content
      ADD CONSTRAINT FK_content_ddserver1_id_dd FOREIGN KEY (id_dd)
      REFERENCES domain (id_dd) ON DELETE CASCADE ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE content
      ADD CONSTRAINT FK_content_id_contenttype FOREIGN KEY (id_contenttype)
      REFERENCES contentType (id_contenttype) ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE content
      ADD CONSTRAINT FK_content_id_status FOREIGN KEY (id_status)
      REFERENCES status (id_status) ON DELETE NO ACTION;
       
      --
      -- Create table `contentItem`
      --
      CREATE TABLE contentItem (
        id_contentitem int(7) NOT NULL AUTO_INCREMENT,
        id_con int(11) NOT NULL,
        userid varchar(100) DEFAULT NULL,
        content mediumtext DEFAULT NULL,
        textonly text DEFAULT NULL,
        datemodify datetime NOT NULL DEFAULT CURRENT_TIMESTAMP (),
        id_feature int(11) DEFAULT NULL,
        id_region int(11) DEFAULT NULL,
        id_block int(11) DEFAULT NULL,
        id_list int(11) DEFAULT NULL,
        extradata text DEFAULT NULL,
        PRIMARY KEY (id_contentitem)
      )
      ENGINE = INNODB,
      AUTO_INCREMENT = 2034,
      AVG_ROW_LENGTH = 1365,
      CHARACTER SET utf8,
      COLLATE utf8_unicode_ci;
       
      --
      -- Create check constraint
      --
      ALTER TABLE contentItem 
        ADD CONSTRAINT CK_contentItem CHECK (extradata is null or json_valid(extradata));
       
      --
      -- Create index `IDX_contentItem2` on table `contentItem`
      --
      ALTER TABLE contentItem
      ADD INDEX IDX_contentItem2 (id_con);
       
      --
      -- Create index `IDX_contentItem3` on table `contentItem`
      --
      ALTER TABLE contentItem
      ADD INDEX IDX_contentItem3 (id_con, id_region);
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentItem
      ADD CONSTRAINT FK_contentItem_contentPart_id_feature FOREIGN KEY (id_feature)
      REFERENCES contentPart (id_feature) ON DELETE CASCADE ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentItem
      ADD CONSTRAINT FK_contentItem_contentTemplateRegion_id_region FOREIGN KEY (id_region)
      REFERENCES contentTemplateRegion (id_region) ON UPDATE CASCADE;
       
      --
      -- Create foreign key
      --
      ALTER TABLE contentItem
      ADD CONSTRAINT FK_contentItem_content_id_con FOREIGN KEY (id_con)
      REFERENCES content (id_con) ON DELETE CASCADE ON UPDATE CASCADE;
       
      -
      

      Attachments

        1. BugTables.sql
          16 kB
        2. BugTables-Main.sql
          5 kB
        3. my.cnf
          4 kB

        Issue Links

          Activity

            People

              alice Alice Sherepa
              longbeard Greg Herrell
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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