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

Stored Procedure Crashes Server

    XMLWordPrintable

    Details

      Description

      The following procedure crashes the server if it is called twice, even with no data, it succeeds if you add a limit:

      CREATE TABLE `TEST` (
        `objectid` int(11) NOT NULL,
        `submissionid` int(11) DEFAULT NULL,
        `objectname` varchar(255) DEFAULT NULL,
        `objecturl` varchar(1000) DEFAULT NULL,
        `reviewercomment` varchar(1000) DEFAULT NULL,
        `isactive` tinyint(1) DEFAULT NULL,
        `createdon` datetime DEFAULT NULL,
        `createdby` varchar(255) DEFAULT NULL,
        `updatedon` datetime DEFAULT NULL,
        `updatedby` varchar(255) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      MariaDB [mytest]> DELIMITER $$
      MariaDB [mytest]> CREATE OR REPLACE PROCEDURE `testsp_doesnot_works`()
           BEGIN
           SET SESSION group_concat_max_len = 100000000000;
           
           SELECT JSON_EXTRACT(
             IFNULL(
               (
                 SELECT CONCAT(
                   '[',
                   GROUP_CONCAT(
                     JSON_OBJECT(
                       'objectId', objectId,
                       'objectName', objectName,'objectUrl', objectUrl,'reviewerComment', reviewerComment,'createdOn', DATE_FORMAT(createdOn,"%Y-%m-%dT%H:%i:%sZ")
                     )
                   ),
                   ']'
                 )     
               ),
               '[]'
             ),
             '$'
           ) AS comments
           FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body;
           END$$
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [mytest]> DELIMITER ;
      

      It succeeds if you replace:

       FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body;
       
      with 
       
      {code:sql}
         FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST` LIMIT 50 ) AS body;
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            mpflaum Maria M Pflaum
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.