MySql: Some queries produce: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user

Question

I've just moved applications to a replicated database and for some, but not all, queries I get the following error message:

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user

Many other queries work and reviewing the credentials are indeed correct as they work for most queries.

Any ideas what might be causing this error message, given that most queries are successful?

Answer 1

The answer:

The "tables" that were being queried and failing were in fact views.

The views had been created with

CREATE DEFINER="user"@"10.10.%.%" SQL SECURITY DEFINER VIEW view_name ...

After changing the application to the other replicated database, the DEFINER didn't exist with the same host on this new database the user was "user"@"10.20.%.%" instead of "user"@"10.10.%.%"

So the definer didn't exist in this database, AND because the SQL SECURITY was set to DEFINER, the view could not be accessed.

In this case, I re-created the view with a different user and set the security to

... SQL SECURITY INVOKER ...

which in my db permission scheme environment the invoker will have adequate rights, so now all queries work.