MySQL Forums
Forum List  »  Stored Procedures

SPs in this version are not very useful
Posted by: Ilia Ratchinski
Date: December 02, 2005 02:14PM

Hello all!

I've tried to use the stored procedures and functions and had some errors,
which I solved in the last 10 hours. Maybe it can be helpful for you!

I tried to create and use the stored procedures as a customer without
root privileges and through client API of MySQL, because all of things,
which are described in the MySQL5 description, work good with command line, but not with clients!

1. First problem:
if a user tries to create a procedure, he gets an error:
#1044 - Access denied for user U to database D.

The solution is - the admin opens the table mysql.user in a client (phpmyadmin, navicat etc.),
and sets Create_view_priv,Show_view_priv,Alter_routine_priv,Create_routine_priv and Execute_priv to 'Y'.

2. Second problem:
the 1. solution is not enough, the user gets an error:
#1418 - This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)

To solve it - admin has to execute: mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

3. Third problem:
the user can create one statement routines from client, e.g.
CREATE PROCEDURE test() SELECT 'Say Hello';
but he cannot call this routine - 'CALL test()' fails with error:
#1312 - PROCEDURE test can't return a result set in the given context

To solve it I had to read the source code of MySQL5 and found the errorcode ER_SP_BADSELECT,
and then the place where the error will be sent - sql_parse.cpp - checking if CLIENT_MULTI_RESULTS is set.
I have found the values of the constants and passed them to MySQL - it works!

The example code you can find here: http://www.ratchinski.com/dev/mysql5/test.php.txt

4. Fourth problem:
The PHP scripts can call the routine, but PHPMyAdmin not (2.7.0-rc1).
I found in the source code of PMA how to solve this problem:
the file with the solution you can find here: http://www.ratchinski.com/dev/mysql5/mysql.dbi.lib.php.txt

5. Fifth problem:
the multi statement procedures cannot be created through the client API - DELIMITER problem.
The user must use DELIMITER command for creation of a multistatement stored procedure,
which is only accessable from command line as root and has no SQL statement.
This is a MySQL Bug, which makes the stored procedures not usable for hosting users, who have no root privileges.

In addition the administrators cannot use the comfortable clients and must use the command line for own routines.

The solution - the admin write for users a script, which saves the procedure directly to mysql.proc table,
but it can be a security risk, because the procedure will not be checked.


6. Sixth problem:
The stored procedures and functions in this version of MySQL are simple textentries in the database,
they are not binary compiled for fast execution, but only stored as text(datatype blob) in mysql.proc.body
and bring too few performance benefits (if you have large query only delivery time between host and client).

This problem I could not solve - it must be made by MySQL developers, and internal query structure serialized to the field.

In this version are the stored routines all but unusable, but I hope we get it properly in one of the next versions.

Ilia Ratchinski

Used software:
MySQL 5.0.15-standard-log, PHP 4.4.1, Debian, Apache, PHPMyAdmin 2.7.0-rc1

Options: ReplyQuote


Subject
Views
Written By
Posted
SPs in this version are not very useful
5162
December 02, 2005 02:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.