The mysql stored procedure has been inserted successfully only once. What is the reason for this?

Today, we encountered a strange problem. For the same batch insertion, one can be inserted successfully in batch, but the other cannot be inserted successfully. Please take a look at it.

the following is pseudo code 

-- A (SQL)( SQL )
CREATE PROCEDURE `A`()
  BEGIN
    REPLACE INTO `table` (`num`) 
      SELECT SUM(`num`) AS `num`
      FROM `table`;
  END;

-- B ( A)
CREATE PROCEDURE `B`()
  BEGIN
    WHILE 100 DO
      CALL `A`;
    END WHILE;
  END;

the above two stored procedures are my code logic, each time REPLACE INTO. SELECT. takes about 0.2 seconds.
when I call CALL `B` , only the first entry is inserted successfully, and no other entries are inserted and no error is reported. The SQL statements generated by
are error-free because printed statements can be inserted in bulk manually.

what"s even weirder is that I have other REPLACE INTO. With the same function. SELECT. (relatively short execution time, 0.01s), but it can be inserted successfully in batch.

is this caused by the lock? I use the InnoDB storage engine.

< hr >

there may be some things that I am not quite clear about:

  • the above code is pseudocode, and there is no problem with loop structure and generation and execution of SQL

  • three main issues are discussed here:

      Is
    • caused by a row lock (not supposed to be)
    • same table, same query structure, why short execution time can be inserted (0.001 level), long execution time can not be inserted (0.1level)
    • whether the execution procedure is called during execution, whether it is executed by Synchronize or asynchronously (that is, whether to go through the process after the completion of the CALL execution, or to move on to the next cyclic process until the completion of the execution)
Mysql stored-procedure sql
Mar.07,2021

you do not specify the primary key and unique key of the table, guess that id is the primary key, but the id substituted by replace into is always 0, so in the end, there can only be one record in the database. If the id is self-growing, try setting it to null, or use insert into, instead of replace into.

.

EDITED

I tried, and the stored procedure you gave me will fall into a dead loop, because while 100 has always been true.

you can try changing it to this: 

delimiter $$

CREATE PROCEDURE `A`()
  BEGIN
    REPLACE INTO `sumtable` (`id`,`mysum`) 
      SELECT 0 AS `id`,SUM(`mysum`) AS `mysum`
      FROM `sumtable`;
  END;
$$

-- B ( A)
CREATE PROCEDURE `B`()
  BEGIN
    DECLARE v1 INT DEFAULT 100;
    WHILE v1>0 DO
      CALL `A`;
    SET v1 = v1 - 1;
    END WHILE;
  END;
$$

run 

call B()

will get 100 records.

I think of a reason that I'm testing if CALL is called asynchronously and my SQL execution looks like this: 

      SET @sql = $sql;
      PREPARE STMT FROM @sql;
      EXECUTE STMT;
      DEALLOCATE PREPARE STMT;

@ sql overrides each other. Take a look at the test results.

now the code looks like this: 

CREATE PROCEDURE `A`()
  BEGIN
    SET $sql = 'REPLACE INTO `table` (`num`) 
      SELECT SUM(`num`) AS `num`
      FROM `table`;';
      SET @sql = $sql;
    PREPARE STMT FROM @sql;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  END;

CREATE PROCEDURE `B`()
  BEGIN
    WHILE 100 DO
      CALL `A`;
    END WHILE;
  END;

Previous: After the launch of Android, Youmian reported that the array would cross the line when it was in dispatchDraw. Is there the same situation that happens on Xiaomi's mobile phone?

Next: Phpword template

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e48ee9-4c3fb.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e48ee9-4c3fb.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?