How to get result of ALTER TABLE prepared statement inside procedure in MYSQL?-CodePudding

So I have this stored procedure which checks to see if a foreign key exists and if not, creates it. I need to run it in a script about 600 times. At the end it should display the sum of skips, successes, and failures. I can't figure out how to count successes/failures from the prepared statement.

I tried storing EXECUTE stmt; to a var and also wrapping it with an IF/ELSE/END IF but no luck. The ALTER TABLE doesn't have to be a prepared statement if that helps.

DELIMITER //
DROP PROCEDURE IF EXISTS `add_constraint_if_not_exists`//

SET @skippedCount = 0;
SET @successCount = 0;
SET @failCount = 0;

CREATE PROCEDURE add_constraint_if_not_exists (sourceDB varchar(64), sourceTable varchar(64), sourceColumn varchar(64), constraintName varchar(64), targetDB varchar(64), targetTable varchar(64), targetColumn varchar(64))
    BEGIN        
        IF EXISTS (
            
            SELECT *
            FROM information_schema.table_constraints
            WHERE
            table_schema = sourceDB
            AND table_name = sourceTable
            AND constraint_name = constraintName
            AND constraint_type = 'FOREIGN KEY'
            LIMIT 1
        ) THEN
            SET @skippedCount = @skippedCount   1;
        ELSE
            SET @sql = CONCAT('ALTER TABLE `',sourceDB,'`.`',sourceTable,'` ADD CONSTRAINT ',constraintName,' FOREIGN KEY (`',sourceColumn,'`) REFERENCES `', targetDB,'`.`',targetTable, '` (`',targetColumn,'`)');

            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            
            /*
            IF @result = -1 THEN
                #SELECT @result;
                SET @failCount = @failCount   1;
            ELSE
                SET @successCount = @successCount   1;
            END IF
            */
        END IF;
    END //

DELIMITER ;

/*
ALTER TABLE `source_db`.`source_table` 
ADD CONSTRAINT `fk`
  FOREIGN KEY (`source_column`)
  REFERENCES `target_db`.`target_table` (`target_column`);
  */
  
CALL add_constraint_if_not_exists ('source_db', 'source_table', 'source_column', 'fk', 'target_db', 'target_table', 'target_column');

SELECT @skippedCount,@successCount,@failCount;

CodePudding user response：

You can use SELECT @@error_count, @@warning_count to get the number of errors or warnings generated from the last statement. This works for prepared statements.

Demo:

mysql> set @sql = 'select 1/0';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare s from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s;
 ------ 
| 1/0  |
 ------ 
| NULL |
 ------ 
1 row in set, 1 warning (0.00 sec)

mysql> select @@error_count, @@warning_count;
 --------------- ----------------- 
| @@error_count | @@warning_count |
 --------------- ----------------- 
|             0 |               1 |
 --------------- -----------------

But honestly, I wouldn't do what you're doing in a stored procedure at all. I'd do it from some application code that runs schema changes. Any other interface besides stored procedures give you easier functions to fetch the errors and warnings.

For what it's worth, I hardly ever use stored procedures in MySQL for any purpose. They are difficult to develop or debug, they have poor performance, and they lack a lot of features.

I tried this out, and I discovered that an error causes the procedure to exit by default, just like an exception in Java or other programming languages. So it never reaches the statement that increments @failCount.

To solve this, I tried declaring an error handler, which runs a statement to increment @failCount and then exits the procedure:

CREATE PROCEDURE ...
BEGIN        
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @failCount = @failCount   1;
  ...

Later in the procedure, I can assume if it gets past the EXECUTE stmt, then it succeeded, so I just increment the success count. If it failed, it would have triggered the exit handler.

  ...
  ELSE
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    SET @successCount = @successCount   1;
  END IF;
END//

Read https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html for more on this topic.

Like I said above, procedures in MySQL are difficult to develop and debug.