Bug #72018 SHOW CREATE VIEW output inccorrect
| Submitted: | 12 Mar 2014 11:02 | Modified: | 25 Jul 2014 15:18 |
|---|---|---|---|
| Reporter: | David Vaughan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.5.33-cll-lve, 5.6.16 | OS: | Linux (RHEL 6.4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqldump | ||
[12 Mar 2014 11:10]
David Vaughan
Further to this, I altered the view using this:
alter view dbv_dbms_uptime_stat_h
as
select
period AS period,
min(event_date) AS db_started,
max(event_date) AS last_connection,
TIME_TO_SEC(timediff(max(event_date), min(event_date)))/(60*60*24) AS days_up
from
dbw_dbms_uptime_data
where
event_name in ('DBMS started','last check')
group by
period
union select
period AS period,
min(event_date) AS db_started,
max(event_date) AS last_connection,
TIME_TO_SEC(timediff(max(event_date), min(event_date)))/(60*60*24) AS days_up
from
dbw_dbms_uptime_histr
where
event_name in ('DBMS started','last check')
group by
period
...this rectified the problem.
[14 Mar 2014 8:39]
MySQL Verification Team
Hello David, Thank you for the report. I can not repeat described behavior with dummy schema. Please provide the complete repeatable test case ( tables, views etc and please make the data private) and the exact error details that you are receiving at the time of restore. Thanks, Umesh
[14 Mar 2014 17:47]
MySQL Verification Team
Hello David, Thank you for providing the test case. Verified as described on 5.6.16. Thanks, Umesh
[25 Jul 2014 15:18]
Paul DuBois
Noted in 5.5.39, 5.6.20 changelogs. For a view defined on a UNION, the server could create an invalid view definition.
[25 Jul 2014 15:19]
Paul DuBois
For 5.7, this was already fixed in 5.7.0 (Bug#65388)
[1 Aug 2014 16:35]
Laurynas Biveinis
5.5 $ bzr log -r 4665 ------------------------------------------------------------ revno: 4665 committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com> branch nick: mysql-5.5-18405221 timestamp: Wed 2014-06-25 16:33:04 +0530 message: BUG#18405221: SHOW CREATE VIEW OUTPUT INCORRECT Fix: --- The issue reported is same as the BUG#14117018. Hence backporting the patch from mysql-trunk to mysql-5.5 and mysql-5.6
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.062 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When trying to restore a database I noticed that the import was failing due to a view definition. Further investigation pointed to this definition: CREATE ALGORITHM=UNDEFINED DEFINER=`dbwatch`@`%` SQL SECURITY DEFINER VIEW `dbv_dbms_uptime_stat_h` AS select `dbw_dbms_uptime_data`.`period` AS `period`, min(`dbw_dbms_uptime_data`.`event_date`) AS `db_started`, max(`dbw_dbms_uptime_data`.`event_date`) AS `last_connection`, (time_to_sec(timediff(max(`dbw_dbms_uptime_data`.`event_date`),min(`dbw_dbms_uptime_data`.`event_date`))) / ((60 * 60) * 24)) AS `days_up` from `dbw_dbms_uptime_data` where (`dbw_dbms_uptime_data`.`event_name` in ('DBMS started','last check')) group by `dbw_dbms_uptime_data`.`period` union select `dbw_dbms_uptime_histr`.`period` AS `period`, min(`dbw_dbms_uptime_histr`.`event_date`) AS `min(event_date)`, max(`dbw_dbms_uptime_histr`.`event_date`) AS `max(event_date)`, (time_to_sec(timediff(max(`dbw_dbms_uptime_histr`.`event_date`),min(`dbw_dbms_uptime_histr`.`event_date`))) / ((60 * 60) * 24)) AS `TIME_TO_SEC(timediff(max(event_date), min(event_date)))/(60*60*24)` from `dbw_dbms_uptime_histr` where (`dbw_dbms_uptime_histr`.`event_name` in ('DBMS started','last check')) group by `dbw_dbms_uptime_histr`.`period` Which fails due to the column definition on the fourth column of the second (union) select. I checked what the original definition was: alter view dbv_dbms_uptime_stat_h(period, db_started, last_connection, days_up) as select period, min(event_date), max(event_date), TIME_TO_SEC(timediff(max(event_date), min(event_date)))/(60*60*24) from dbw_dbms_uptime_data where event_name in ('DBMS started','last check') group by period union select period, min(event_date), max(event_date), TIME_TO_SEC(timediff(max(event_date), min(event_date)))/(60*60*24) from dbw_dbms_uptime_histr where event_name in ('DBMS started','last check') group by period Possibly due to the column names being defined at the view name level? Underlying table definitions below: mysql> desc dbw_dbms_uptime_data; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | event_name | varchar(64) | YES | | NULL | | | event_date | datetime | YES | | NULL | | | period | int(11) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ mysql> desc dbw_dbms_uptime_histr; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | event_name | varchar(64) | YES | | NULL | | | event_date | datetime | YES | | NULL | | | period | int(11) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ How to repeat: SHOW CREATE VIEW dbv_dbms_uptime_stat_h or a mysqldump of the view Suggested fix: Have the SHOW CREATE VIEW code recreate the view as originally defined.