Bug #72018 SHOW CREATE VIEW output inccorrect

• Submitted: 12 Mar 2014 11:02
• Modified: 25 Jul 2014 15:18
• Reporter: David Vaughan
• Status: Closed
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.5.33-cll-lve, 5.6.16
• OS: Linux (RHEL 6.4)
• 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 <[e-mail]>
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