I broke my Mysql View query. How do I fix it?

Question

A few months ago I created a VIEW that gives me last month's transactions. I use that view within other queries. The problem is, I changed some table names of tables that are referenced inside the VIEW and have broken the VIEW query in the process. Now my other queries don't execute anymore.

Is there any way to see what the VIEW statement is and edit it to reflect the new table names? I'm using phpmyadmin, but everytime I try to go to the view table, it gives me an error saying it is invalid, but it won't show me what it is...

Answer 1

Providing you know the name of the view you can run this and it'll show you the query that creates the view. If phpMyAdmin doesn't let you run it, then you will probably have to do it at the command line.

SHOW CREATE VIEW view_name

You should also that it says something like:

SQL SECURITY DEFINER VIEW `myViewName` AS

Directly after that you will find the query that creates this view. Copy and paste it into a text editor and you can alter it so it works with your new table names.

Run it as a normal query to ensure it works, then when you're ready to replace it with the broken original you can run this query (beware this will delete the original view!):

DROP VIEW IF EXISTS myViewName;

CREATE VIEW myViewName AS ...(your new query here)...

Hope that helps!

References:

1. http://dev.mysql.com/doc/refman/5.1/en/show-create-view.html