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:

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