Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
Description
SHOW CREATE VIEW don't reflect the formating information from the original create statement,
even they are available in the .frm file.
For really huge view create bodys it is heavily readable.
to reproduce.
CREATE TABLE `t1` ( |
`id` INT NULL |
)
|
;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_t1` AS SELECT * |
FROM |
t1;
|
SHOW CREATE VIEW `view_t1`;
give the following output
CREATE ALGORITHM=UNDEFINED DEFINER=`richard`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select `t1`.`id` AS `id` from `t1
|
But the formating information with \n are available in the .frm file
Output of more view_t1.frm
(source= section)
TYPE=VIEW
|
query=select `try`.`t1`.`id` AS `id` from `try`.`t1` |
md5=c60d386caaf21a6ba36d48fb4561c351
|
updatable=1 |
algorithm=0 |
definer_user=richard
|
definer_host=localhost
|
suid=1 |
with_check_option=0 |
timestamp=2017-09-14 15:12:16 |
create-version=2 |
source=SELECT *
|
\nFROM
|
\nt1
|
client_cs_name=utf8mb4
|
connection_cl_name=utf8mb4_general_ci
|
view_body_utf8=select `try`.`t1`.`id` AS `id` from `try`.`t1` |
mariadb-version=100207 |
Attachments
Issue Links
- duplicates
-
MDEV-7406 get from INFORMATION_SCHEMA original SQL code creating VIEW
-
- Open
-
- is duplicated by
-
MDEV-32091 SHOW CREATE VIEW does not show original view definition
-
- Closed
-
Activity
An additional column would be the best way to solve this, even for user with huge create statements.
serg what about an extended show create statement, like show full create as done for the processlist? For create statements it would show both, the modified and original statement. Could also be used for show create table, view, ....
it wouldn't work for tables, because we don't store the original `create table` statement.
but we can do it for views, yes.
The original view source is not always safe to reapply. That's why SHOW CREATE VIEW shows normalized view definition.
We can add another column to INFORMATION_SCHEMA.VIEWS with the original view source, it should be used purely for information purposes, not for reapplying.
Optionally we can make SHOW CREATE VIEW to return the original view depending on some system setting, but the risk of unintentionally using the wrong view definition will be higher.