MySQL Import stored procedure method

MySQL tutorial how to import a stored procedure
This article provides two kinds of methods about the MySQL import stored procedure and the MySQL stored procedure Export method, below let us take a look at the export stored procedure instance, then look at a detailed import stored procedure method.
Export a function or stored procedure inside MySQL

The syntax is:
[Root@localhost bin]# mysqldump-uroot-p-hlocalhost-p3306-n-d-t-R dbname > Procedure_name.sql
Parameter description:
-N:--no-create-db
-D:--no-data
-T:--no-create-info
-r:--routines dump stored routines (functions and procedures)

Mysqldump-hhostname-uusername-ppassword-ntd-r databasename > Backupflie.sql

Mysqldump-hlocalhost-uroot-ntd-r hqgr > Hqgr.sql

The-NTD is to represent the exported stored procedure;-R is to represent the exported function

Importing MySQL stored procedures

1 # mysqldump-u Database Tutorial user name-p-n-t-d-r database name > filename

Where-D represents--no-create-db,-N is--no-data,-T represents--no-create-info, and-R indicates export function and procedure. So the code above indicates that only functions and stored procedures are exported, and the table structure and data are not exported. However, the contents of this export include trigger. When you import into MySQL, you will have problems with the following error:

Error 1235 (42000) at line * *: This version of the MySQL doesn ' t yet support ' multiple triggers with the same action time and Event for one table '

So you need to turn the trigger off when you export. Code is

1 # mysqldump-u Database user name-p-n-t-d-R--triggers=false database name > FileName

A new problem occurs when you import this way:

errorcode:1418

This function has none of deterministic, NoSQL, or reads SQL data inits declaration and binary are enabled (you logging Ght* want to use the less safe log_bin_trust_function_creators variable)

The workaround is to find [mysqld] in/etc/my.cnf and add a line below it:

1 Log-bin-trust-function-creators=1

Importing stored procedure methods

Delimiter $$

CREATE PROCEDURE ' Pms_authuser ' (in account varchar (255), in Vorgid varchar (255))
Not deterministic
Contains SQL
SQL Security Definer
Comment '
Begin
Declare CNT integer;
DECLARE pmsc varchar (100);
Select @pmsc: =orgid from organization where Pmscode=vorgid;
Select @cnt: =count (*) from UserInfo as UserInfo where userinfo.username=account and userinfo.orgid = @pmsc;
If @cnt >0
Then
Select Userinfo.*,org.orgid as orgdepth from UserInfo as userinfo, organization as org where Userinfo.username=account and Userinfo.orgid = @pmsc and Userinfo.orgid=org.orgid;
Else
Select userinfo.*,org.orgdepth from userinfo userinfo,organization org where username=account and Userinfo.orgid in ( Select OrgID from organization where orgdepth like Concat ('% ', @pmsc, '% '));
End If;
End $$
Delimiter

Then the MySQL native runs the command to import the stored procedures in the database
Mysql-uroot Crs2 < File.sql

Check if the creator of the Mysql.proc table's Definer field is root@localhost if the stored procedure imported by the non-native MySQL tool

Select DB, created, definer from Mysql.proc;

If the Definer field has a root@localhost value, be sure to correct it!

Update Mysql.proc Set definer = ' root@localhost ';