MySQL Create Procedure frustration

software development

#1

The upshot of this message is that I’m beginning to doubt whether DreamHost supports MySQL’s 5.x stored procedures and functions.

The details follow.

I’ve successfully run scripts that create my tables, constraints, indexes, and do inserts. But procedure creating scripts that I can run locally won’t run on Dreamhost.

I can’t create them “by hand” through dreamhost’s phpAdmin, either.

Here’s one variation I’ve tried in phpAdmin.

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_readelection $$
CREATE PROCEDURE sp_readelection(EID int)
BEGIN
Select * from Ballot where Election_ID = @EID;
END $$

DELIMITER ;

The result is …

/*
#1064 - 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 'DELIMITER $$

DROP PROCEDURE IF EXISTS sp_readelection $$
CREATE PROCEDURE’ at line 1
*/

I’m inferring that DreamHost’s installation of MySQL isn’t supporting stored procedures… That would be Bad News. Is it true? If not, what’s the trick I need to get this going? Can someone give me a working example?

I’ve hunted around and tried it all kinds of ways.

I notice that other people have asked similar questions on this forum without getting answers.

And the following comment on the DreamHost Wiki at http://wiki.dreamhost.com/index.php/Mysql worries me…

/*
MySQL limitations due to shared hosting

On Dreamhost shared accounts, some of the SQL commands listed in the MySQL documentation link below or in references such as the O’Reilly book Managing & Using MySQL by Reese, et al. are not available for scripting nor in phpMyAdmin.

Their functionality is available through the Dreamhost Control Panel described in the Creating a MySQL Database above.

Included are:

  • create database (works from shell on databases created in panel)
  • drop database (works from shell on databases created in panel)
  • grant
  • revoke
  • use
    */

The Control Panel gives no indication of support for stored procedures or functions. Are those features crippled?

Also, if I use TOAD for MySQL to even look at stored procedures or functions in my database, I get the error, SELECT command denied to user ‘…(myinfo)…’ for table ‘proc’." It won’t even show me the empty set. I consider that another bad sign.

Any advice is welcome.

Thanks,

craig [at] rkey [dot] com


#2

Sounds like you have the details that should be submitted to support for a definitive answer.

Wholly


#3

FYI followup…

DreamHost’s tech support admits that phpmyadmin doesn’t support this reliably. It gags on the delimiter. I’ve got no answer on why TOAD can’t deal with procedures on the host. And creating my procedures via script is still a puzzle (delimiter havoc again). But I’ve discovered that I can load procedures one by one with the MySQL Query Browser client.

(And I see the typo in my procedure, but that wasn’t the problem.)


#4

this issue has probably already been resolved, but, the I ran into the SELECT command denied for user on table ‘proc’ - this is a Toad issue I believe, if you want to see your list of procedures, look this table: information_schema.routines

I was able to create a very basic procedure and run it successfully.


#5

Have you tried SQLYog for creating procedures?

It beats the pants off phpMyAdmin!