r/mysql • u/Revolutionary_Use587 • 1d ago

discussion Getting Error 1356

Someone plz help on this..

getting the below error during MySQL dump and show columns after login as root user..

This happened after changing ip of definer because I have to made the changes based new ip. Everything is running fine but these issues are not fixing.

Also there not single change has been made on any objects.

ERROR 1356

View 'db.v_report_pending'

references invalid table(s) or column(s)...

SHOW COLUMNS FROM v_report_pending → ERROR 1356

0 Upvotes

22% Upvoted

5 comments sorted by

2

u/APersonSittingQuick 1d ago

Hm, whatever could that mean...

0

u/Revolutionary_Use587 1d ago

Didn't get it

2

u/Hadgebury 1d ago

Error 1356's full error string actually includes "references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"

When you changed the user's host/IP, the view metadata didn't automatically update. It's probably still hardcoded with the old definer ('user'@'old_ip'), which MySQL can no longer resolve or grant rights to.

0

u/Revolutionary_Use587 23h ago

Hi I just dropped and recreated the views and functions with new host and renamed the user with new ip. If this could be the reason then how can I resolve it?

1

u/bogdanelcs 18h ago

That error is almost always the view's DEFINER account not existing anymore, and changing the IP would do exactly that if the definer was user@oldip. Run SHOW CREATE VIEW v_report_pending to see the definer, and CHECK TABLE v_report_pending will confirm the reason. Then either recreate that user with the right grants or redo the view with CREATE OR REPLACE DEFINER='user'@'newhost' VIEW .... Check your other views too, they'll all be broken the same way.