6

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?

4
  • Just tested on Mysql 5.1.47 (AWS) with those grants. Worked just fine on InnoDB tables.
    – h0tw1r3
    Jul 26, 2011 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, 2011 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, 2011 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, 2011 at 18:39

1 Answer 1

3

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .