Mysql stored procedures traverse pits where values cannot be obtained

Undoubtedly, this must be a sad story. I have a requirement here. I need to migrate and backup some records and log tables. Under the current conditions, we do not use MaxCompute for backup, nor do we handle it gracefully through master-slave database relations or DTS and other technologies, so we should do it. A relatively simple way to deal with this problem is to wake up the stored procedure through a timed task for backup operations.

According to the above requirements, it is obvious that the content of stored procedure is the core, but it is relatively simple. It can only be divided into three steps, as follows:

  • Replication of source table structure
  • Backup data at specified time
  • Delete backup data from source table
    First, I will create a backup configuration table here, as follows:
-- Create backup configuration tables

CREATE TABLE `back_up_base` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `source_database` varchar(100) NOT NULL COMMENT 'Source sink',
  `source_table` varchar(100) NOT NULL COMMENT 'Source table',
  `destination_database` varchar(100) NOT NULL COMMENT 'Target Library',
  `destination_table` varchar(100) NOT NULL COMMENT 'Target table',
  `days` int(11) unsigned NOT NULL DEFAULT '15' COMMENT 'Number of days reserved',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The next step is to write stored procedures. First, look at what I wrote in the first edition, as follows:

DELIMITER $$
# Backup stored procedure
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN	
    # Declare variable information
    DECLARE source_database VARCHAR(100) DEFAULT '';
    DECLARE source_table VARCHAR(100) DEFAULT '';
    DECLARE destination_database VARCHAR(100) DEFAULT '';
    DECLARE destination_table VARCHAR(100) DEFAULT '';
    DECLARE days INT DEFAULT 15;
    DECLARE done int DEFAULT FALSE;
		
		

    # Get the database information that needs to be backed up
    DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
	# Define the loop end flag
   	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;


    # open
    OPEN cur_back_up;

			# Start traversing
			read_loop: LOOP
				FETCH cur_back_up INTO source_database,source_table,destination_database,destination_table,days;
				IF done THEN LEAVE read_loop; 
				END IF; 
				
				# Create tables
				# Setting Execution Statement
				SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',destination_database,'`.`',destination_table,'` (LIKE `',source_database,'`.`',source_table,'`)');
	     		# Preprocessing dynamic sql
				PREPARE stmt_create_table FROM @create_table;
				# Executing sql
				EXECUTE stmt_create_table;
				# Release pretreatment
				DEALLOCATE PREPARE stmt_create_table;

				# Backup data
                # Setting Execution Statement
                SET @back_up_data = CONCAT('INSERT INTO `',destination_database,'`.`',destination_table,'` SELECT * FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
				# Preprocessing dynamic sql
                PREPARE stmt_back_up_data FROM @back_up_data;
                # Executing sql
                EXECUTE stmt_back_up_data;
                # Release pretreatment
                DEALLOCATE PREPARE stmt_back_up_data;


                # Delete data
                # Setting Execution Statement
                SET @delete_data = CONCAT('DELETE FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
				# Preprocessing dynamic sql
                PREPARE stmt_delete_data FROM @delete_data;
                # Executing sql
                EXECUTE stmt_delete_data;
                # Release pretreatment
                DEALLOCATE PREPARE stmt_delete_data;

			END LOOP read_loop;
    # Close the cursor
    CLOSE cur_back_up;

END $$

DELIMITER ;

At first glance, there is nothing wrong with catching up. A quick operation is like a tiger, which is always executing errors. Because I use navicat, so I don't report debugging. A Duo only used oracle three years ago to debug stored procedures and debug them. Then I can only annotate a line of code to sort out the problem, and finally found that the problem is traversing, FETCH did not get metadata, which is a very difficult thing.

Depressed for a day, first use java to dynamically list all the tables that need to be backed up, then execute, temporarily support a field.

A Dao must not admit defeat, I must find the cause of this problem, after all, there is no end to learning, especially in our business, we must have the spirit of breaking the casserole to the end, so as not to abandon the spirit of not giving up.

Through reading a lot of the experience of predecessors, I found that the variable names defined in triggers can not be the same as those in cursors. Otherwise, it would cause confusion and lead to the situation that the value can not be obtained. It is embarrassing. After all, according to our programmer's thinking, this exception should be thrown in the compilation process, so A Dao encountered it. This problem is really depressing, but also blamed for the poor academic skills of A Duo. I hope I can record and share it with people in need, and take less detours.

The modified trigger SQL is as follows:

DELIMITER $$

# Create stored procedures
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN
		
    # Declare variable information
    DECLARE cur_source_database VARCHAR(100) DEFAULT '';
    DECLARE cur_source_table VARCHAR(100) DEFAULT '';
    DECLARE cur_destination_database VARCHAR(100) DEFAULT '';
    DECLARE cur_destination_table VARCHAR(100) DEFAULT '';
    DECLARE cur_days INT DEFAULT 15;
    DECLARE done int DEFAULT FALSE;
		
		

    # Get the database information that needs to be backed up
    DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
	  # Define the loop end flag
   	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;


    # open
    OPEN cur_back_up;

			# Start traversing
			read_loop: LOOP
				FETCH cur_back_up INTO cur_source_database,cur_source_table,cur_destination_database,cur_destination_table,cur_days;
				IF done THEN LEAVE read_loop; 
				END IF; 
				
				# Create tables
				# Setting Execution Statement
				SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',cur_destination_database,'`.`',cur_destination_table,'` (LIKE `',cur_source_database,'`.`',cur_source_table,'`)');
				# Preprocessing dynamic sql
				PREPARE stmt_create_table FROM @create_table;
				# Executing sql
				EXECUTE stmt_create_table;
				# Release pretreatment
				DEALLOCATE PREPARE stmt_create_table;

				# Backup data
        		# Setting Execution Statement
        		SET @back_up_data = CONCAT('INSERT INTO `',cur_destination_database,'`.`',cur_destination_table,'` SELECT * FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
				# Preprocessing dynamic sql
        		PREPARE stmt_back_up_data FROM @back_up_data;
        		# Executing sql
        		EXECUTE stmt_back_up_data;
        		# Release pretreatment
       			DEALLOCATE PREPARE stmt_back_up_data;


        		# Delete data
        		# Setting Execution Statement
        		SET @delete_data = CONCAT('DELETE FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
				# Preprocessing dynamic sql
        		PREPARE stmt_delete_data FROM @delete_data;
        		# Executing sql
        		EXECUTE stmt_delete_data;
       			# Release pretreatment
        		DEALLOCATE PREPARE stmt_delete_data;


			END LOOP read_loop;
    # Close the cursor
    CLOSE cur_back_up;

END $$

DELIMITER ;

Finally, the last step is to start the timed task regularly, as follows:

CREATE EVENT IF NOT EXISTS backup_record_event   
ON SCHEDULE EVERY 1 DAY DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) 
ON COMPLETION PRESERVE ENABLE   

-- Here we need to change the library. Now we set up the data for running at 1:00 a.m. every day.
DO CALL back_up_record();


This is the end.

Don't miss the offer when you need a cloud server

Aliyun's low-cost purchase of cloud services is worth seeing

Tags: SQL Database Stored Procedure Navicat

Posted on Wed, 04 Sep 2019 20:39:02 -0700 by nofxsapunk