How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

Question

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the system. I assumed that this was caused because the user was not in the system, but looking in the users database of MySQL the user is listed correct with the password.

The dump was from an earlier version of MySQL (5.0) into the latest one that comes with Xamp 5.527. At first I thought it could be that the latest version was incompatible with the existing one.

Looking on this forum, there are some existing answers covering this problem that refer to making changes to the database and then dumping it again.

However, my on line database works fine, so I dare not make changes to it in case I kill it. That is assuming that I understood what changes I should make and how to do it. I have PHPmyadmin. Can I modify it on the existing dumped database,or do I have to change it on the on line one and then export it?

And exactly what do I need to do to modify it. The original database was a Microsoft Access one, converted to MySQL, and it has worked without error since 2003.

Answer 1

If you will recreate all your views this way (and will create all new ones with this syntax):

CREATE
SQL SECURITY INVOKER
    VIEW applications
AS     
SELECT id, product_id
FROM modules     WHERE item_type='application';

you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and SELECT on tables involved.

Typical database user in a web application will have SELECT, INSERT, UPDATE, DELETE, EXECUTE until your web application creates database objects.

You do not need 'fred' to be a database user anywhere in this case even though this database user created this view on your web site.

Answer 2

It took lots of thinking but I could not find a simple solution on the web forums, so I worked out the following procedure. 1. Recreate the missing user. 2. Log on as him. 3. Find the select code in the database that created the original view. 4. Create a new query using this code. Save it as a view. This sets the definer to the user who is logged on to username@localhost. (MySQL 5.5.27) 5. Drop the old view. 6. Rename the new one with the original view name. Run the code on the website - problem solved. Took a few attempts as I have not used this version before.