Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

After a box running mysql was upgraded a backup script is returning an error for databases with Innodb tables.

A system running Debian 5 (Lenny) has been upgraded to Debian 6 (Squeeze), and the system was running the stock mysql-server package from the Debian repository.

The backups are being performed by a script which backs up several mysql servers, and backs up the each individual database separately.

This is the command and error that is returned get when it is ran against a database of Innodb tables.

$ mysqldump --defaults-extra-file=creds.cnf 
            --lock-tables --flush-logs --force db_innodb > /dev/null
mysqldump: Got error: 1045: Access denied for user 'backup'@'%' 
           (using password: YES) when using LOCK TABLE
echo $?
2

When The same command is run against a database of Myisam tables on the same server using the same account there are no errors.

$mysqldump --defaults-extra-file=creds.cnf 
           --lock-tables --flush-logs --force db_myisam > /dev/null
echo $?
0

The regular backup account has the lock table privilege, and the backups where running fine before the system was upgraded. But I have also tried with the root account and see the same error.

mysql> show grants for 'backup'@'%';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW 
      ON *.* TO 'backup'@'%' IDENTIFIED BY PASSWORD '*...'

I know that I could probably simply not use the lock-tables option on the Innodb database, and use the --single-transaction option instead, but this will not work very well. A couple databases have tables with using both the Myisam, and Innodb storage engine, and the single transaction option wouldn't make the Myisam tables consistent. Also this is an older script and it would take a farly large amount of work to make it backup differently based on storage engine.

Since the script already passes the --force option to mysqldump, which means that I am getting data in the backup, and it isn't completely failing. There is just a chance that it will not be completely consistent, if some is up working in the middle of the night. The fact that I am actually getting data in my dump makes me thing that this is purely about the lock privilege.

So I want to fix the problem with the least amount of changes. Why am I only getting the lock tables error only on databases with Innodb based tables? Do I have to grant more privileges to lock a Innodb table?

share|improve this question
    
Just tested on Mysql 5.1.47 (AWS) with those grants. Worked just fine on InnoDB tables. –  h0tw1r3 Jul 26 '11 at 4:16
    
Read (drupal.org/node/1193) that WRITE permission may be needed on the mysql table. Seems weird, but worth a shot I suppose. –  h0tw1r3 Jul 26 '11 at 4:20
    
Just to update, I tested something that I should have tested earlier. I attempted to run the same backup command using the root account with the same error. I have also verified the schema of the mysql database is identical to the schema of a newly installed version of mysql. –  Zoredache Jul 26 '11 at 17:29
    
I have discovered an issue with an invalid definer on lots of views in these databases. I am not certain this is the problem yet. I have created a new question about modifying logs of views. dba.stackexchange.com/questions/4129/… –  Zoredache Jul 26 '11 at 18:39

1 Answer 1

up vote 2 down vote accepted

After more investigation I found an issue with the definer on some of of the VIEWS in the problem database.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

This account belonged to a developer that no longer was around, and had been removed. With the help of the guys over on dba.stackexchange I was able to build a script to replace my views with an account that actually existed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.