Stored procedures

[biolotov]

Hi,

 

I am using the latest uniform server with MySQL 5.1 and want to use stored procedures in my databases. But when i try to execute CREATE PROCEDURE questions in phpMyAdmin's SQL window i get an error message saying that the table mysql.proc does not exist. That's true when i check the mysql database, but my question now is: are procedures not available in uniform server? I would really like to use the advantages with procedures (and maybe triggers also), can i do something to get it to work?

 

Regards,

 

Christoffer

[Ric]

I have never used stored procedures however that is no excuse. I had a quick look and as you rightly pointed out “table mysql.proc” does not exist. Without this you cannot run procedures.

 

Well always game for a laugh I thought I would have a go at breaking a new installation of UniServer by fixing the mysql privilege table. By the way this also adds the mysql.proc table and a few other things.

 

I have not fully tested this so please perform this on a new installation and test it to see if it meets your expectation it may break other things. However if you are prepared to get your hands dirty here is a blow-by-blow account.

 

1) You need a copy of mysql_fix_privilege_tables.sql

 

The only way I found of obtaining this was to download a complete up to date MySQL file set.

 

Go to : http://dev.mysql.com/downloads/mysql/5.0.html#downloads

And download the file associated with this name “Without installer (unzip in C:\)” it is a large download (50M) save it in any folder you wish.

 

2) Unzip the file mysql-noinstall-5.0.27-win32.zip accept the defaults (unzips to the folder you saved it in.

 

3) Run both your servers.

 

4) Create a new folder in w:usr\local\mysql\ named scripts

 

5) Copy the file mysql_fix_privilege_tables.sql

 

From: *\mysql-noinstall-5.0.27-win32\mysql-5.0.27-win32\scripts\

To: W:\ usr\local\mysql\scripts\

 

Note: * is thefolder you performed the unzip operation.

 

6) Open a command prompt window (start>run>cmd) and type in:

 

w:

followed by

cd usr\local\mysql

 

You will see the prompt looks like this W:\usr\local\mysql> this just informsyou where you are.

 

7) Type the following where highlighted (bold). I have included some of the results you will receive.

 

W:\usr\local\mysql>bin\mysql -u root -p mysql

Enter password: **** Note:The default is root[/i]

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 20 to server version: 5.0.17

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> SOURCE scripts/mysql_fix_privilege_tables.sql

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected, 1 warning (0.09 sec)

 

ERROR 1060 (42S21): Duplicate column name 'File_priv'

Query OK, 0 rows affected (0.00 sec)

 

+------------------+

| @hadGrantPriv:=1 |

+------------------+

| 1 |

| 1 |

+------------------+

2 rows in set (0.00 sec)

 

ERROR 1060 (42S21): Duplicate column name 'Grant_priv'

ERROR 1060 (42S21): Duplicate column name 'Grant_priv'

 

ERROR 1054 (42S22): Unknown column 'Type' in 'columns_priv'

ERROR 1060 (42S21): Duplicate column name 'type'

Query OK, 0 rows affected (0.00 sec)

 

+-------------------+

| @hadShowDbPriv:=1 |

+-------------------+

| 1 |

| 1 |

+-------------------+

2 rows in set (0.00 sec)

 

ERROR 1060 (42S21): Duplicate column name 'Show_db_priv'

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

Query OK, 0 rows affected (0.00 sec)

 

ERROR 1054 (42S22): Unknown column 'Create_view_priv' in 'where clause'

Query OK, 1 row affected (0.11 sec)

Records: 1 Duplicates: 0 Warnings: 0

 

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

 

 

mysql> exit

Bye

 

W:\usr\local\mysql>

 

8) Stop and restart your MySQL server. Thats it have fun.

 

I am not sure if that will help and what bits I have missed, would be nice for some one to come back and explain any other bits that are required.

 

Note: I had PHPGlossar Version 0.8 installed in the database at the time of upgrading it did not kill this application off.

Note: Tested on OS Windows XP home SP2 did you know that 40% (Mostly corporate) still use SP1 and that support for this has been withdrawn?

 

All the best

Mike

[Ric]

PS I deleted your second topic post to keep the subject in one place

 

[biolotov]

Great! It works just fine! And not that complicated at all!

 

The only problem that i've noticed is that you can't run and test your created procedures in phpMyAdmin, because you get an MySQL error #1312 saying "PROCEDURE x.x can't return a result set in the given context". I've tried to find some simple solution to this but it seem a bit tricky. I found these sites for temporarily fixing phpMyAdmin to start working with these kinds of result sets:

 

http://www.ratchinski.com/dev/mysql5/test.php.txt

http://www.ratchinski.com/dev/mysql5/mysql.dbi.lib.php.txt

 

But i used php instead when trying out my stored procedures and it works fine. IF you enable mysqli as discussed in the thread:

 

http://forum.uniformserver.com/index.php?s...3&hl=mysqli

 

Thanks a lot Mike! I really appreciate it!

 

/Christoffer

 

 

NOTE: I've found out that, though MySQL now supports procedures and other VERY nice stuff when creating applications, MySQLs procedure handling isn't that easy and doesn't offer the same benifits as other database systems. BUT if you want to create good applications with for example different layers etc procedures are a good and sound way, so i really hope that MySQL makes procedures, triggers and views work more easily in future