[MDEV-16254] Window Functions in Sproc cause crash Created: 2018-05-23  Updated: 2019-10-09  Resolved: 2019-10-09

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 10.2.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Greg Herrell Assignee: Alice Sherepa
Resolution: Duplicate Votes: 0
Labels: None
Environment:

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


Attachments: File BugTables-Main.sql     File BugTables.sql     File my.cnf    
Issue Links:
Duplicate
duplicates MDEV-13170 Database service (MySQL) stops after ... Closed

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



 Comments   
Comment by Alice Sherepa [ 2018-05-23 ]

Please add your .cnf file(s) and output of
SHOW CREATE PROCEDURE ... and
SHOW CREATE TABLE for this 3 tables.

Comment by Greg Herrell [ 2018-05-23 ]

I have uploaded two sets of table definitions. BugTables-Main should have just the tables involved while BugTables hopefully can recreate all the tables referenced by the key relations of the tables involved. I wasn't sure what you might need.

Comment by Alice Sherepa [ 2018-05-24 ]

I was not able to reproduce the exact crash, but it looks like it is the same problem as MDEV-13170

Comment by Greg Herrell [ 2018-05-24 ]

These statements worked fine on our development server. They only crashed during our production launch.

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