error code 1356 View references invalid table(s) or column(s) or function(s)

[fik2024]

I am having this warning with one fo my views.

error code: 1356 View references invalid table(s) or column(s) or function(s) or defiber/invoker of view lack rights to use them.

"Select * from view" is working fine. All of the underlying tables nad columns are upated. I am using root user so no issue with the invoker/definer rights.
Whenever I re-create the view warning goes away and re-appear with the same/different view any time.

I am using workbench for mysql. Every time it says error loading schema, one of the view has this issue. I have also tried using Navicat, HeidiSQL but they all show this same warning.

I want to know if this is a known bug of mysql or how can I resolve this issue?

[jaydubu]

Hi @fik2024,

Welcome to Google Cloud Community!

While you already have a workaround, @ms4446 pointed out several valuable tips from this similar question. Adding additional information on mentioned items which could help resolve this:

• Verifying that user/definer have sufficient permissions: Related results from StackOverflow. Note that some of the solutions are pretty old and might be outdated:
• Check that the definer account can execute the select
• Grant rights base on what you want to achieve
• Recreating the view without the "SQL SECURITY DEFINER"
  
  
• Upgrading to the latest MySQL version: 
• Latest version is 8.0.38 which contains the latest updates and bug fixes.
• I found that the observed behavior is a confirmed bug in v8.0.22 and has been fixed in v8.0.28. 
• If you are already using the latest v8.0.38, this might be a regression.
• As last resort, revert back to the older versions.

Since it is replicable in Workbench, Navicat, HeidiSQL. I would recommend filing a bug against MySQL or if you have a support package, reach out to Google Cloud Support for a more in-depth analysis. When contacting them, be sure to provide detailed information along with relevant screenshots. This will give them a clearer understanding of your issue, enabling them to address it more effectively.

I hope the above information helps.