Details
-
New Feature
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
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.
Attachments
Issue Links
- is duplicated by
-
MDEV-13805 SHOW CREATE VIEW don't provide formating in .frm file
-
- Closed
-
-
MDEV-32091 SHOW CREATE VIEW does not show original view definition
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-32053 New features requested by customer on 2023-08-28
-
- Open
-
- links to
Activity
> 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 
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.
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.
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
The formatting we do makes the view unreadable and hard to understand (said by customer).
When doing an ALTER VIEW to create a new view, they will provide new formatting, that should be preserved.
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.