Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Did you see the question here‌​? The answer using ALTER VIEW looks like it might be applicable if you wanted to change the "definer" to something generic like root@localhost. –  Gord Thompson Mar 24 '13 at 9:52

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.