I'm not sure what is wrong here, I'm guessing at some point someone had run an incorrect MySQL command or something, but have't found a solution to fix this.
Basically, users cannot use their database. I even tried creating a new mysql user as a test, gave it rights to a database, and it has the same error.
The user can "show databases", but when trying to use a database the error is:
(the xxxx's are just to mask out the real names)
mysql> use database xxxxxx;
ERROR 1044 (42000): Access denied for user 'xxxxxxx'@'%' to database 'database'
There is no database named 'database' on this server, there never was. For the heck of it I tried to create a database named 'database' just to see what would happen and it won't let me create a database called database anyway:
mysql> create database database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'database' at line 1
So I have no idea why suddenly it's expecting MySQL users to be able to access a database named 'database'.
I checked the 'db' table in the 'mysql' database, and there's no sign of this database database.
Here's somewhere else where I do get the error - when trying to use the database 'information_schema':
mysql> use database information_schema;
ERROR 1049 (42000): Unknown database 'database'
(above as done as the MySQL root user)
Any ideas how to repair this?