How to import a stored procedure from mysql

How to import a stored procedure from mysql this article provides two methods for importing a stored procedure from mysql to mysql and how to export a stored procedure from mysql. Let's take a look at the example of the exported stored procedure, let's look at a detailed method for importing stored procedures. Export functions or stored procedures in mysql

How to import a stored procedure in the mysql tutorial
This article provides two methods for importing a stored procedure from mysql to mysql and how to export a stored procedure from mysql. Let's take a look at the example of the exported stored procedure and look at a detailed method for importing a stored procedure.
Export functions or stored procedures in mysql

Syntax:
[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

-Ntd indicates the export stored procedure;-r indicates the export function.

Import mysql Stored Procedure

1 # mysqldump-u database tutorial username-p-n-t-d-r Database Name> file name

-D indicates -- no-create-db,-n indicates -- no-data,-t indicates -- no-create-info, and-r indicates exporting function and procedure. Therefore, the above Code only exports functions and stored procedures, and does not export table structures and data. However, the exported content contains the trigger. When importing data to mysql, the following error occurs:

Error 1235 (42000) at line **: this version of mysql doesn't yet support 'Multiple triggers with the same action time and event for one table'

Therefore, trigger must be disabled during export. The code is

1 # mysqldump-u database username-p-n-t-d-r -- triggers = false Database Name> file name

In this way, a new problem occurs:

Errorcode: 1418

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

The solution is to find [mysqld] in/etc/my. cnf and add the following line under it:

1 log-bin-trust-function-creators = 1

 

Import Stored Procedure Method

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, organization org where username = account and userinfo. orgid in (select orgid from organization where orgdepth like concat ('%', @ pmsc, '% '));
End if;
End $
Delimiter;

Then mysql runs commands on the local machine to import the stored procedures in the database
Mysql-uroot crs2 <file. SQL

If you want to check whether the creator of the definer field in the mysql. proc table is root @ localhost

Select db, created, definer from mysql. proc;

If the definer field has a non-root @ localhost value, make sure to correct it!

Update mysql. proc set definer = 'root @ localhost ';