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

Local routine MEDIUMBLOB variable overwritten while asigning value to another MEDIUMBLOB variable using SELECT INTO

Details

    Description

      Using SELECT INTO local routine variable, value is assigned to two distinct variables.

      Test cast to reproduce issue;

      Two tables with one row with different values


      CREATE TABLE `test`.`A`(  
        `idA` BIGINT NOT NULL AUTO_INCREMENT,
      		 
        `messageA` MEDIUMBLOB,
      		 
        PRIMARY KEY (`idA`)
      		 
      );
      		 
       
      		 
      CREATE TABLE `test`.`B`(  
        `idB` BIGINT NOT NULL AUTO_INCREMENT,
      		 
        `messageB` MEDIUMBLOB,
      		 
        PRIMARY KEY (`idB`)
      		 
      );
      		 
       
      		 
      INSERT INTO A(messageA) VALUES (COMPRESS('messageA'));
      		 
      INSERT INTO B(messageB) VALUES (COMPRESS('messageB'));


      Stored procedure:


      DELIMITER $$
      		 
       
      		 
      CREATE PROCEDURE `procC`()
      		 
      BEGIN
      		 
      	DECLARE pA MEDIUMBLOB;
      		 
      	DECLARE pB MEDIUMBLOB;
      		 
      	
      	SElect messageA
      		 
      	into pA 
      	FROM A;
      		 
      	
      	SELECT messageB
      		 
      	into pB
      		 
      	from B;
      		 
      	
      	select UNCOMPRESS(pA), UNCOMPRESS(pB);
      		 
          END$$
      		 
       
      		 
      DELIMITER ;

      Calling procedure:

      CALL procC();


      Expected result:

      messageA, messageB

      Result:

      messageB, messageB

      Attachments

        Activity

          elenst Elena Stepanova added a comment -

          The bug was fixed in 5.5.48 by the following commit (merged from upstream):

          commit 863f7cebd79e76f90bd8f1e3e0c1a1de5fe77d07 3d1306f7b74077cfa197c8fa23baeb96c535af67
          		 
          Author: Sreeharsha Ramanavarapu <sreeharsha.ramanavarapu@oracle.com>
          		 
          Date:   Fri Jan 8 06:46:59 2016 +0530
          		 
           
          		 
              Bug #22232332: SAVING TEXT FIELD TO TEXT VARIABLE IN A
          		 
                             PROCEDURE RESULTS IN GARBAGE BYTES
          		 
              
              Issue:
          		 
              -----
          		 
              This problem occurs under the following conditions:
          		 
              
              a) Stored procedure has a variable is declared as TEXT/BLOB.
          		 
              b) Data is copied into the the variable using the
          		 
                 SELECT...INTO syntax from a TEXT/BLOB column.
          		 
              
              Data corruption can occur in such cases.
          		 
              
              SOLUTION:
          		 
              ---------
          		 
              The blob type does not allocate space for the string to be
          		 
              stored. Instead it contains a pointer to the source string.
          		 
              Since the source is deallocated immediately after the
          		 
              select statement, this can cause data corruption.
          		 
              
              As part of the fix for Bug #21143080, when the source was
          		 
              part of the table's write-set, blob would allocate the
          		 
              neccessary space. But this fix missed the possibility that,
          		 
              as in the above case, the target might be a variable.
          		 
              
              The fix will add the copy_blobs check that was removed by
          		 
              the earlier fix.

          elenst Elena Stepanova added a comment - The bug was fixed in 5.5.48 by the following commit (merged from upstream): commit 863f7cebd79e76f90bd8f1e3e0c1a1de5fe77d07 3d1306f7b74077cfa197c8fa23baeb96c535af67 Author: Sreeharsha Ramanavarapu <sreeharsha.ramanavarapu@oracle.com> Date: Fri Jan 8 06:46:59 2016 +0530   Bug #22232332: SAVING TEXT FIELD TO TEXT VARIABLE IN A PROCEDURE RESULTS IN GARBAGE BYTES Issue: ----- This problem occurs under the following conditions: a) Stored procedure has a variable is declared as TEXT/BLOB. b) Data is copied into the the variable using the SELECT...INTO syntax from a TEXT/BLOB column. Data corruption can occur in such cases. SOLUTION: --------- The blob type does not allocate space for the string to be stored. Instead it contains a pointer to the source string. Since the source is deallocated immediately after the select statement, this can cause data corruption. As part of the fix for Bug #21143080, when the source was part of the table's write-set, blob would allocate the neccessary space. But this fix missed the possibility that, as in the above case, the target might be a variable. The fix will add the copy_blobs check that was removed by the earlier fix.

          People

            Unassigned Unassigned
            ljubomir.djokic Ljubomir Đokić
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.