Access denied; you need the RELOAD privilege for this operation

Hi everyone --

I'm pretty new to MySql, but not many years ago I was an ISAM guy so I understand the issues with indexes and on-the-fly inserts.

I've seen many questions around this error message, "Access denied; you need the RELOAD privilege for this operation." But I'm not grokking the answers.

Is it true that I cannot get all the privs I need on a database that I have created?

If so, then what's a reasonable way for MySql users to get around this restriction?

In my particular case, cPanel tells me "MySQL version 5.0.81-community." I have a table "usrs" in my ISAM DB into which I want to insert a row. I execute this C-connector code before the insert:

err = mysql_query( pmysql, "lock table usrs write;" );
err = mysql_query( pmysql, "flush table usrs;" );

The error return from "flush table" is "Access denied; you need the RELOAD privilege for this operation"

After the insert (which completes without error) I execute the "flush table" call again prior to issuing "unlock tables;" The error return to the second "flush table" is likewise "Access denied."

I think I understand (from reading the net) that only root can grant RELOAD privs. So of course the many responses to this question everywhere often begin, "It's so very simple! Just login as root and ..." :-)

Since I'm on a shared-host ISP, I need to get the admin to grant me RELOAD privs. But it seems that RELOAD priv is a MySql-wide priv, not a per-database or per-user priv. So no admin will go for that.

The other alternative, I guess, is for me to ask the admin to execute a "flush table" on my table from time to time, every few days, say.

Do I have the correct understanding of the situation? And how do people usually deal with this error?

Thanks so much! I apologize for the newb-level question and really appreciate your help.

-- Pete




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Access denied; you need the RELOAD privilege for this operation

In the last episode (Sep 20), Pete Wilson said:
> I'm pretty new to MySql, but not many years ago I was an ISAM guy so I
> understand the issues with indexes and on-the-fly inserts.
>
> I've seen many questions around this error message, "Access denied; you
> need the RELOAD privilege for this operation." But I'm not grokking the
> answers.
>
> Is it true that I cannot get all the privs I need on a database that I
> have created?
>
> If so, then what's a reasonable way for MySql users to get around this
> restriction?
>
> In my particular case, cPanel tells me "MySQL version 5.0.81-community." I
> have a table "usrs" in my ISAM DB into which I want to insert a row. I
> execute this C-connector code before the insert:
>
> err = mysql_query( pmysql, "lock table usrs write;" );
> err = mysql_query( pmysql, "flush table usrs;" );
>
> The error return from "flush table" is "Access denied; you need the RELOAD
> privilege for this operation"
>
> After the insert (which completes without error) I execute the "flush
> table" call again prior to issuing "unlock tables;" The error return to
> the second "flush table" is likewise "Access denied."

The only time a table flush is needed is when you are looking at the raw
mysql data files and want to make sure there are no dirty blocks that
haven't been written to disk yet (for hot backup purposes, for example), or
if you are debugging a performance problem and want to make sure all caches
are emptied. If you're just inserting rows, there's no need to flush
anything.

Also, there probably isn't a reason to explicitly lock the entire table,
either. If you were previously using that to emulate atomic transactions on
myisam tables, you can replace your lock/unlock table operations with a
START TRANSACTION/COMMIT pair, because innodb supports transactions (myisam
doesn't). That will let other users access the "usrs" table at the same
time you're doing your insert. If you're just inserting a single row, you
can even drop the transaction calls. A single insert is always atomic, no
matter what table type you are using.

Your ISAM experience might actually be a drawback here, if it's more at the
operational level of old Berkely DB 1.x-style databases (where one has to
manually maintain the index, there are no transactions, etc). SQL-based
databases sit on top of the lower-level table engines and handle all that
work for you.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org