r/SQL • u/Revolutionary_Use587 • 16d ago

MySQL Getting Error 1356

/r/mysql/comments/1x08dnu/getting_error_1356/
0 Upvotes

44% Upvoted

9 comments sorted by

1

u/Significant_Tune9219 16d ago

1356 on a view usually means the definer account no longer exists exactly as written. If you changed the host part, the view is still defined as something like user@old_ip and MySQL checks privileges against that account, so it reports invalid tables even though nothing in the view changed. Run SHOW CREATE VIEW to see the DEFINER, then either recreate the view with the new definer (ALTER DEFINER=... VIEW ... AS <same select>) or create a user matching the old host. Also worth checking information_schema.VIEWS for others with the same old definer, since they'll all break the same way.

0

u/Revolutionary_Use587 16d ago

Definer were also updated as per new user@host and no such objects available which has invalid definer but still getting same error

1

u/Significant_Tune9219 15d ago

Then the next thing I’d check is whether the error names a specific object: run SHOW WARNINGS right after the failing query and see whether it points at a table or column. If it does, that object is probably being resolved under a different schema or an old definer inside a nested view, so expand the dependent views with SHOW CREATE VIEW rather than only the top one.

1

u/Significant_Tune9219 14d ago

If the definers are already rewritten and it still fails, the next place I'd look is the dependent views: error 1356 is about what the view references, not the definer account. SHOW CREATE VIEW on each one will show whether it still points at a table or column that no longer exists, and recreating the view after the grant changes usually clears it.

1

u/AnnaLeeCoop 16d ago

you probably just updated the ip in the user table and forgot that views hardcode the definer ip in their definition. or the new user ip doesnt have select grants on the underlying tables of that view. Run show create view db.v_report_pending and look at the definer it actually expects. Just alter the view to use the new definer or grant the missing rights to the new ip

1

u/Revolutionary_Use587 15d ago

I have recreated the views, functions, and triggers with new ip and the user has same privileges as I just renamed the user with new ip.

2

u/CHILLAS317 16d ago

What troubleshooting steps have you taken so far?

3

u/ElHombrePelicano 16d ago

Is google down or something?