[MDEV-7406] get from INFORMATION_SCHEMA original SQL code creating VIEW Created: 2015-01-03  Updated: 2023-12-22

Status: Open
Project: MariaDB Server
Component/s: Views
Fix Version/s: 11.5

Type: New Feature Priority: Critical
Reporter: Mikhail Gavrilov Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 1
Labels: information_schema, upstream, verified, view

Issue Links:
Duplicate
is duplicated by MDEV-13805 SHOW CREATE VIEW don't provide format... Closed
is duplicated by MDEV-32091 SHOW CREATE VIEW does not show origin... Closed
Relates
relates to MDEV-6731 No ALGORITHM information in I_S.VIEWS Closed
relates to MDEV-32053 New features requested by customer on... Open

 Description   

I am use INFORMATION_SCHEMA for speedup getting database definition

And I found yet another problem:

Demonstration:

 
CREATE TABLE `test` (
 
  `a` MEDIUMTEXT,
 
  `b` MEDIUMTEXT
 
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
 
 
 
CREATE VIEW `view_test` AS
 
(SELECT * FROM test);
 
 
 
SELECT  `TABLE_NAME`
 
	,`TABLE_NAME`
 
	,`VIEW_DEFINITION`
 
FROM `information_schema`.`VIEWS` IS_V
 
	WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'
 

TABLE_NAME  TABLE_NAME  VIEW_DEFINITION                                                                
----------  ----------  -------------------------------------------------------------------------------
 
view_test   view_test   (SELECT `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` FROM `test`.`test`)
 

SHOW CREATE VIEW `view_test`
 

VIEW       CREATE VIEW                                                                                                                                              character_set_client  collation_connection  
---------  -------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------  ----------------------
 
view_test  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (SELECT `test`.`a` AS `a`,`test`.`b` AS `b` FROM `test`)  utf8                  utf8_general_ci

As you can see when I get SQL definition with "SHOW CREATE VIEW" I get body without database `test` as expected. When I get definition from INFORMATION SCHEMA I have definition with database `test`. If I will use this SQL code for create view in different database I would have problems.

 Comments   
Comment by Elena Stepanova [ 2015-01-04 ]

It's an upstream issue present in all active versions (5.1-5.7). Did you try to report it at bugs.mysql.com? It's our usual routine.
It might well be already reported there, but I can't find it.

Note: There are other problems which I suppose might not allow using I_S.VIEWS to re-create a view, e.g. there seems to be no ALGORITHM clause in there, does it not concern you? There used to be a bug about it http://bugs.mysql.com/bug.php?id=16832 which is said to be fixed in an ancient 5.0 version, but I still can't see ALGORITHM in current servers, so it might be broken again.

Comment by Mikhail Gavrilov [ 2015-01-05 ]

I fill new bug at mysql bugtreaker

Comment by Mikhail Gavrilov [ 2015-01-05 ]

> There used to be a bug about it http://bugs.mysql.com/bug.php?id=16832 which is said to be fixed in an ancient 5.0 version, but I still can't see ALGORITHM in current servers, so it might be broken again.

And yes, it is still not fixed

Comment by Sergei Golubchik [ 2015-01-06 ]

As far as ALGORITHM is concerned, it's MDEV-6731.

Comment by Sergei Golubchik [ 2023-11-03 ]

we could implement view comments. preliminary syntax example:

CREATE VIEW v1 AS SELECT * FROM t1 COMMENT 'some comment'

By default, if no comment was specified, it'll be set to the create view query text. When reading from .frm it'll be read from the new comment field, if it exists, otherwise from the source field. It will be seen in SHOW CREATE VIEW and in the new INFORMATION_SCHEMA.VIEWS.COMMENT column.

Open questions:

  • charset of the new column? suggestion: utf8
  • how SHOW CREATE will show it? suggestion: on the new line, for readability. and in /*M! ... */ comment, even.
Comment by Michael Widenius [ 2023-11-04 ]

Charset utf8 is ok.
Agree that we need the /*M! comment
After all, users can get it nicely from information schema if needed.
I still think we should store SQL_MODE in the .frm. If only for making things future proof. We can at least print the original mode in information_schema.
The main question if we do this, is what to do with SHOW CREATE VIEW. We should consider making this also available somehow.
Alternatively we can have mysqldump take the value from information_schema.

Comment by Sergei Golubchik [ 2023-11-29 ]

on the other hand, COMMENT is clearly an inconsistent hack and the only answer to "why you did it" is "we couldn't come up with anything better". It'd be good to have a proper solution

Generated at Thu Feb 08 07:19:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.