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:
None 
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:02] David Vaughan
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.
[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.