I had just installed Drupal. Most stuff was working - I could add new stories, comments, etc. But on certain updates involved in that process, I got a database error like:

user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.

user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.
warning: Cannot modify header information - headers already sent by (output started at c:htdocsp0drupalincludescommon.inc:32) in c:htdocsp0drupalincludescommon.inc on line 324.

Drupal.org user Ax suggested I look here: http://www.mysql.com/doc/en/Access_denied.html#c968? which says:

One case not mentioned here and you get an 1045 "Access denied" error is when you don't have the privilege to alter system tables using "LOCK TABLES". The connect succeeds, but a "LOCK TABLES" query fails. Then you should ask the MySQL administrator to extend your privileges. The "LOCK TABLES" command is writing to the system tables. If the MySQL administrator decided to grant you all privileges only on your database, say foo.*, this won't be sufficient. You will need write access to 'mysql' database. If you don't have that, you will get a MySQL error "#1045 access denied" at the "LOCK TABLES" query. So, the case of "LOCK TABLE" privilege in MySQL breaks a privilege system where administrators decided to grant privileges ordered by database hierarchy.

I granted all to mysql.* as suggested in the MySQL description above. After restarting mysql, that cleared it up.
I can make the problem appear again by revoking all from mysql.*. Then to get it going again -- grant all to mysql.* and restart mysql.

My system is using:

  • Drupal 4.1.0
  • Apache 1.3.27
  • PHP 4.3.0
  • MySql 4.0.5-beta-max-nt (originally), 4.0.10-gamma-max-nt (now)
  • Windows XP Pro

Does this sound like a MySQL bug I should be reporting to them? Or is it a documentation issue for drupal installation -- one more set of database privs required? Thanks.

Comments

kclarke99’s picture

kclarke99 commented

The Drupal install instructions for the database work great with MySql 3.x -- I just tried with MySql 3.23.55-max-nt and everything works exactly as it should.

But with MySql 4.x (both 4.0.5-beta-max-nt and 4.0.10-gamma-max-nt)-- After install, most stuff was working - I could add new stories, comments, etc. But on certain updates involved in that process, I got a database error like:

user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.

user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.
warning: Cannot modify header information - headers already sent by (output started at c:htdocsp0drupalincludescommon.inc:32) in c:htdocsp0drupalincludescommon.inc on line 324.

Drupal.org user Ax suggested I look here: http://www.mysql.com/doc/en/Access_denied.html#c968? which says:

One case not mentioned here and you get an 1045 "Access denied" error is when you don't have the privilege to alter system tables using "LOCK TABLES". The connect succeeds, but a "LOCK TABLES" query fails. Then you should ask the MySQL administrator to extend your privileges. The "LOCK TABLES" command is writing to the system tables. If the MySQL administrator decided to grant you all privileges only on your database, say foo.*, this won't be sufficient. You will need write access to 'mysql' database. If you don't have that, you will get a MySQL error "#1045 access denied" at the "LOCK TABLES" query. So, the case of "LOCK TABLE" privilege in MySQL breaks a privilege system where administrators decided to grant privileges ordered by database hierarchy.

I granted all to mysql.* as suggested in the MySQL description above. After restarting mysql, that cleared it up.
I can make the problem appear again by revoking all from mysql.*. Then to get it going again -- grant all to mysql.* and restart mysql.

My system is using:

  • Drupal 4.1.0
  • Apache 1.3.27
  • PHP 4.3.0
  • MySql 4.0.5-beta-max-nt (originally), 4.0.10-gamma-max-nt (now)
  • Windows XP Pro

Does this sound like a MySQL 4.x bug I should be reporting to the MySql folks? Or is it a documentation issue for drupal installation -- one more set of database privs required? Sounds like a MySql bug -- not many ISPs will want to grant my user full access to mysql.* I guess.

Anonymous’s picture

(not verified) commented
Priority: Major » Normal

MySQL configuration issue.