?
Solved

Can't create stored proc on remote MySQL 5.0 database

Posted on 2007-07-20
6
Medium Priority
?
322 Views
Last Modified: 2008-01-09
I am having trouble createing a stored procedure on a remote database hosted by my ISP and am getting the following error:

Access denied for user 'ABCDEF'@'%' to database 'test_db'

I can take the following script and run it locally just fine.  I'm assuming it's permissions, but the database managment/configuration tool provided by my ISP for setting up MySQL does not allow me to change permissions.

   use 'test_db;
   DROP PROCEDURE `R_TEST`;
   DELIMITER $$
   CREATE PROCEDURE `R_TEST`()
   BEGIN
       SELECT PLAN_ID   FROM 'test_db.PLAN;
   END $$
   DELIMITER ;

I'm new to MySql and this the first time trying to deploy to my hosting provider.
Thanks,
Ray
Tampa, FL
0
Comment
Question by:VBert
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Rurne earned 750 total points
ID: 19535482
Hi Ray,

It does indeed sound like a permissions issue.  You need to be able to grant CREATE ROUTINE and ALTER ROUTINE to your user ABCDEF in order to be able to add procedures/functions.

Is ABCDEF your main MySQL account, or do you have another account that has ALL permissions on the database?  Also, what configuration tool do you have deployed?
0
 
LVL 1

Author Comment

by:VBert
ID: 19538725
Hey Rurne,

I tried GRANT CREATE ROUTINE ON 'test_db'.* TO 'ABCDEF'@%';  But I get the error "SQL Error: Access denied for user "ABCDEF'@'%' to database 'test_db'.  It is definitely a permissions error.  If I do a SHOW GRANTS for ABCDEF; it does not show the CREATE/ALTER ROUTINE permission.  My problem is that this database is hosted by Register.com and their Database Manager is less that desirable.  It allows me to create users, but not grant permissions. In fact, it does not even allow you to execute scripts so I've been connecting with both MySql Administrator and HeidiSQL.  Both give me the SQL Error mentioned above.  I've called their tech support and they are escaling the issue.  Very strange.
0
 
LVL 4

Assisted Solution

by:dkaisla
dkaisla earned 750 total points
ID: 19545442
Hey Ray,

Some hosting providers (especially the low-cost ones) are in a shared-hosting environment. Please verify with them if they allow mysql remote connection using SSH, or better yet run the mysql query in their provided phpmyadmin software in your control panel.

Dave
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:VBert
ID: 19546352
Dave,

This hosting provider does not even include phpMyAdmin in their control panel.  I can however connect remotely via HeidiSql and MySql Administrator.  It is definitely a grants thing.  If I do SHOW GRANTS FOR [Acct]; I can see that the CREATE ROUTINE/ALTER ROUTINE grants are not there.  I escaled this to their tech support and I just received email notification that they cannot grant administrative rights to the database.  No kidding.  I just want the above grants.  Their support is clueless when I talk to them on the phone and most don't even know what a stored procedure is.

This is my first time deploying a MySQL database and also my first time deploying to a server with a hosting company.  Is it common for low cost hosting providers to not allow the creation of stored procedures?  Again, they are allowing remote access.  

Thanks,
Ray
0
 
LVL 9

Expert Comment

by:Rurne
ID: 19549110
I've yet to experience a hosting provider that would allow remote access and general data-defniition rights without allowing procedures as well.  It's not as if you can obtain access to data for which you didn't already have privileges.  

You may want to get your ticket escalated to someone who handles reprovisioning, as they generally can tell you why you are not allowed to run stored procedures.  General helpdesk support can't do much past a script and a few automated tools, but deployment and reprovisioning are two departments that will get you where you need to be.
0
 
LVL 1

Author Comment

by:VBert
ID: 19568247
After multiple calls and escalation to their Tier 2 support, the hosting provider came back and finally admitted to me that they do not allow stored procedures to be created in their MySql databases.  Not sure I understand the security implications why, but there is no changing their policy.  Thanks for all the input.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question