List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 21 2009 1:14am
Subject:Re: Access denied; you need the RELOAD privilege for this operation
View as plain text  
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@stripped
Thread
Access denied; you need the RELOAD privilege for this operationPete Wilson21 Sep
  • Re: Access denied; you need the RELOAD privilege for this operationDan Nelson21 Sep