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
| Field | Original Value | New Value |
|---|---|---|
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Environment | Tested on ubuntu 16.04 and MariaDB 10.2.7 | |
| Issue Type | Bug [ 1 ] | Task [ 3 ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
An additional column would be the best way to solve this, even for user with huge create statements.
| Assignee | Max Mether [ maxmether ] |
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.
| Assignee | Max Mether [ maxmether ] |
| Assignee | Vicențiu Ciorbaru [ cvicentiu ] |
| Workflow | MariaDB v3 [ 82581 ] | MariaDB v4 [ 130713 ] |
| Link |
This issue is duplicated by |
| Fix Version/s | N/A [ 14700 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Zendesk Related Tickets | 200206 |
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.