How to create a mysql stored procedure through linux terminal

Question

In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures. I understood the command delimiter for only the mysql client(client side command).

but I am using the mysql client which does not have the command delimiter like dbslayer, on this kind of clients how can I define the procedures.

for now consider:

create procedure test_pro()
begin
select 'hello pro';
end

I tried the following :

mysql -u root -pmypass  test < proc_file

where proc_file contains the above procedure;

but that is giving me the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Event I tried the following

 create procedure test_pro()
    begin
    select 'hello pro';
    end;

(added the trailing semicolon) but am getting the same error .

Same I am experiencing with the dbslayer , if I am able to define the above procedure through terminal I think I should be able to do that through the dbslayer

Answer 1

There is no way around it. You must use the DELIMITER command. Why ?

If you ever perform a mysqldump of the stored procedures, each stored procedure begins with

DELIMITER ;;

and ends with

DELIMITER ;

Here is a post where I mentioned this before : DROP PROCEDURE IF EXISTS not included in mysqldump

Try dumping one stored procedure with mysqldump and see for yourself

I also wrote some code to do this:

• how to dump a single stored procedure from a database
• Dump only the Stored Procedures in MySQL

As for the answer posted by @altmannmarcelo, it directly answers your question (+1 for his answer). Otherwise, mysqldumps could never restore stored procedures.

There are two things you can do to accommodate a new DELIMITER:

TRY THIS #1

Give the delimiter on the command line itself

mysql -u root -pmypass --delimiter="//" test < myproc.sql

It is a command line option for the mysql client program

[root@****]# mysql --help | grep -i delimiter
  --delimiter=name    Delimiter to be used.
delimiter                         ;

TRY THIS #2

You can write the code into a text file and execute against the text file as @altmannmarcelo suggested

echo "DELIMITER //" > myproc.sql
echo "create procedure test_pro()" >> myproc.sql
echo "begin" >> myproc.sql
echo "select 'hello pro';" >> myproc.sql
echo "end" >> myproc.sql
echo "//" >> myproc.sql
mysql -u root -pmypass  test < myproc.sql

GIVE IT A TRY !!!

Answer 2

You need to change the delimiter to create a procedure, otherwise MySQL will try to commit your query on select 'hello pro';

Change your procedure to:

DELIMITER //
create procedure test_pro()
begin
select 'hello pro';
end
//

From MySQL Documentation:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

Answer 3

This worked for me:

Contents of mysqlfile.sql

USE `dbschemaname`;
DROP procedure IF EXISTS `myprocname`;

DELIMITER $$
USE `dbschemaname`$$
CREATE DEFINER=`myusername`@`%` PROCEDURE `myprocname`(IN myvar VARCHAR(3500))
BEGIN
     DECLARE my_id INT;

     SELECT id INTO veh_id FROM mytable WHERE something = myvar;

END$$

DELIMITER ;

and on the commandline:

$ mysql -u user -pmypass dbschemaname < mysqlfile.sql

Answer 4

I know this is an old question but the following might be useful for someone in future:

#!/bin/bash

echo "Started ..."
date
host=localhost
user=dbusername
password=dbpassword
database=databasename

time /usr/bin/mysql --host=$host --user=$user --password=$password --database=$database<<EOFMYSQL
DELIMITER $$
create procedure test_pro()
BEGIN
SELECT 'hello pro';
END$$
DELIMITER ;
SHOW WARNINGS;
SHOW COUNT(*) WARNINGS;
EOFMYSQL

echo "Finished "
date

Tested and working against Maria DB.

After it finished you can just call the procedure using:

call test_pro();

Hope that helps :)