Mysqldump stored procedures

What are stored procedures?

• From MySQL documentation: “A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports “routines” and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi().”
• The information on the MySQL developer site is very helpful: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-proc.html

Why do I even?

• If you’re dumping databases by hand for any reason it’s worth checking for and transferring any stored procedures. If stored routines are there on the source, they probably will need to be on the destination.

How do I know if there are stored procedures?

• It’s easy to check for stored procedures and functions. From the linux command prompt type:

mysql -Ns -e 'SHOW PROCEDURE STATUS;'
mysql -Ns -e 'SHOW FUNCTION STATUS;'

• If the above commands return any output, there are stored procedures and/or functions associated with one of the databases.

How do I copy stored procedures?

• Just add the ‘–routines’ flag to your mysqldump command.
• Examples:

Change:

mysqldump database_name > database_name.sql

To:

mysqldump --routines database_name > database_name.sql

/scripts/pkgacct

• cPanel handles stored procedures in /scripts/pkgacct
  • From /scripts/pkgacct at line 1026

            my $old_mysql_version = Cpanel::MysqlUtils::mysqlversion($user);
            print "...mysql version: $old_mysql_version...";
            my @downgrade_options = downgrade_mysql( $old_mysql_version, $new_mysql_version );
            my $mysqldump_ver = Cpanel::MysqlUtils::mysqldump_version();
            print "...mysqldump version: $mysqldump_ver...";

            my @mysql5_options = ();
            if ( $mysqldump_ver >= 5.0 && $old_mysql_version >= 5.0 ) {
                push @mysql5_options, '--routines';
            }
            elsif ( $mysqldump_ver < 5.0 && $old_mysql_version >= 5.0 ) {
                print "\nLocal mysql tools are version 4.x and remote mysql is 5.x. Unable to backup stored procedures.\n";
            }

• cPanel adds the routines flag for mysqldump versions greater than 5.0 even though MySQL documentation says it was added in 5.0.13 but there may not be a lower version of 5.0

Final Sync

• Since /scripts/pkgacct is already handling stored procedures, we will be most concerned about MySQL stored procedures in standard cPanel to cPanel migrations when we are performing the mysqldumps during the final sync. Should the customer be editing their stored procedures during the migration process? Probably not. Might they? Nothing is stopping them.
• Check the mysqldump version on the source server:

mysqldump --version

• In the output, look for the mysql version listed after Distrib. If it is 5.0.13 or greater, just add ‘–routines’ to your mysqldump command(s) and it will grab any stored procedures that may exist.