System Upgrade introduced an unusual mysql privilege error

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.

Debian 5 = Mysql 5.0.51a
Debian 6 = Mysql 5.1.49

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?

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.

System Upgrade introduced an unusual mysql privilege error

Related

Recent Posts