This site requires JavaScript to be enabled
Welcome|
Recent searches
IE BUMPER

Drop and Recreate A Stored Procedure

Number of views : 9
Article Number : KB0019308
Published on : 2023-06-13
Last modified : 2023-06-13 18:14:51
Knowledge Base : IT Public Self Help

Note: The procedure described in this KB article only needs to be performed by specific ITS MySQL database customers. These customers have been notified separately. If you were not notified, you do not need to perform the steps in this KB article.

An incompatible change was introduced in MySQL 8 that removes the NO_AUTO_CREATE_USER sql_mode (see Changes in MySQL 8.0.11). In some cases, this mode is included in the definition of stored procedures and must be removed before migrating to MySQL 8. The NO_AUTO_CREATE_USER sql_mode option can be removed by dropping and recreating the stored procedure.

If the NO_AUTO_CREATE_USER option is not removed, the migration of an affected database to MySQL 8 will fail.

If an affected stored procedure is no longer required, only perform this procedure through Step 4. If an affected stored procedure is still required, complete all the steps.

In the steps below, the following sample database will be used:

Database Name itsy002_sample
Administrative User itsy002
Stored Procedure test_proc
MySQL Environment mysqldev01.austin.utexas.edu:3306
  1. Log in to your MySQL 5.7 database as your administrative user.
    mysql -u itsy002 -p -h mysqldev01.austin.utexas.edu -P3306
  2. Select your database.
    mysql> use itsy002_sample
  3. Display the affected stored procedure. Copy and paste the procedure's code, which is located in the Create Procedure field, to a text editor. Note that the NO_AUTO_CREATE_USER option is present in the sql_mode field.
    mysql> show create procedure itsy002_sample.test_proc\G
    *************************** 1. row ***************************
               Procedure: test_proc
                sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
        Create Procedure: CREATE DEFINER=`itsy002`@`%.utexas.edu` PROCEDURE `test_proc`()
    BEGIN
        SELECT * FROM departments;
    END
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: latin1_swedish_ci
  4. Drop the stored procedure.
    mysql> drop procedure if exists itsy002_sample.test_proc;

    If the stored procedure is no longer required, stop here. If the stored procedure is still required, complete the remaining steps to recreate it without the NO_AUTO_CREATE_USER sql_mode.

  5. Recreate the stored procedure using the original procedure's code copied from Step 3. In this example, the SQL delimiter is changed to a double-slash (//) to allow the procedure to be read in properly and then it is changed back to a semi-colon (;).
    mysql> DELIMITER //
     
    CREATE DEFINER=`itsy002`@`%.utexas.edu` PROCEDURE `test_proc`()
    BEGIN
      SELECT * FROM departments;
    END//
     
    DELIMITER ;
  6. Display the stored procedure again. Verify the procedure's code is the same as before and the NO_AUTO_CREATE_USER option is no longer present in the sql_mode field.
    mysql> show create procedure test_proc\G
    *************************** 1. row ***************************
               Procedure: test_proc
                sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
        Create Procedure: CREATE DEFINER=`itsy002`@`%.utexas.edu` PROCEDURE `test_proc`()
    BEGIN
        SELECT * FROM departments;
    END
    character_set_client: utf8mb4
    collation_connection: utf8mb4_general_ci
      Database Collation: latin1_swedish_ci

Thank You! Your feedback has been submitted.

Feedback