× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
tomconrad reported 2019-12-16T19:22:34Z  · AmarAccolite last modified 2021-03-15T14:07:11Z

MySQL 8 - Investigate why Information_schema.views is not displaying view definition data.


Priority Low
Complexity Unknown
Component DB - MySQL
Version 22.0
Dialog from the community site
 
---------------------------------------------------------------------------------
 
Hi,
 
If i create a view teste with a user let´s say rreis and then connect as root user i cannot alter view because it return only the following:
 
CREATE OR REPLACE VIEW `teste`
AS
 
If i connect again as rreis i can see the following:
 
CREATE OR REPLACE VIEW `teste`
AS
select `listaprodutos`.`IdProduto` AS `IdProduto`,`listaprodutos`.`Ordem` AS......
 
Any idea?
 
----------------------------------------------------------------------------------
 
Hi Rui,
 
Which database are you using?
 
Thanks, Tom
 
----------------------------------------------------------------------------------
 
MySQL 8.0.18
 
----------------------------------------------------------------------------------
 
Hi Rui,
 
It looks like root does not have access to the information in information_schema.views. We extract the view definition from information_schema.views like below. You can try that 
query and see if you get information back using root. Perhaps, you just need to grant access to root. 
 
SELECT
   table_schema,
   table_name,
   view_definition,
   check_option,
   is_updatable,
   definer,
   security_type 
FROM
   information_schema.views 
WHERE
   table_name = 'teste' 
 
-----------------------------------------------------------------------------------
 
If i run as root i get this:
TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER  SECURITY_TYPE
loja_abola teste select `listaprodutos`.`.... NONE NO root@% DEFINER
 
If i run as rreis i get:
TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE
loja_abola teste NONE NO root@% DEFINER
 
But in MySql Workbench i can run alter view with both user without problem. I´m not an expert on MySQL because i use a lot MS SQL, but it seems to me that the root user witch act as a superuser should be able to view and alter everything.
 
-----------------------------------------------------------------------------------
 
Your last scenario makes a little more sense since it looks like root is the definer of the view. User rreis will not be able to view the definition column because he is not the definer. I think this is an old MySQL bug that goes back many years and looks like it is still not fixed.  I don't know how Workbench has access other then it might be using "show create view" to get the view definition. Something like "show create view loja_abola.teste" This is currently not available in Aqua Data Studio. I opened issue #15766 in our tracking system to investigate this as a bug to be fixed in a future release.
 
Thanks, Tom
3 attachments

Issue #15766

Verified
Fixed
Resolved 2021-02-26T10:38:04Z
 
 
Completion
No due date
Fixed Build dev-11
No time estimate

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017