Bug #75851 SHOW CREATE VIEW should retain original view formatting and definition
Submitted: 10 Feb 2015 19:21 Modified: 4 Aug 2015 9:43
Reporter: Justin Swanhart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.6.22, 5.6.25, 5.6.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: data representation, DDL, formatting, Views

[10 Feb 2015 19:21] Justin Swanhart
Description:
I have this pretty view definition in sys_history:
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$processlist (
  ts,
  thd_id,
  conn_id,
  user,
  db,
  command,
  state,
  time,
  current_statement,
  lock_latency,
  rows_examined,
  rows_sent,
  rows_affected,
  tmp_tables,
  tmp_disk_tables,
  full_scan,
  last_statement,
  last_statement_latency,
  last_wait,
  last_wait_latency,
  source
) AS
SELECT pps.ts,
       pps.thread_id AS thd_id,
       pps.processlist_id AS conn_id,
       IF(pps.name = 'thread/sql/one_connection',
          CONCAT(pps.processlist_user, '@', pps.processlist_host),
          REPLACE(pps.name, 'thread/', '')) user,
       pps.processlist_db AS db,
       pps.processlist_command AS command,
       pps.processlist_state AS state,
       pps.processlist_time AS time,
       pps.processlist_info AS current_statement,
       esc.lock_time AS lock_latency,
       esc.rows_examined,
       esc.rows_sent,
       esc.rows_affected,
       esc.created_tmp_tables AS tmp_tables,
       esc.created_tmp_disk_tables AS tmp_disk_tables,
       IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0,
          'YES', 'NO') AS full_scan,
       IF(esc.timer_wait IS NOT NULL,
          esc.sql_text,
          NULL) AS last_statement,
       IF(esc.timer_wait IS NOT NULL,
          esc.timer_wait,
          NULL) as last_statement_latency,
       ewc.event_name AS last_wait,
       IF(ewc.timer_wait IS NULL AND ewc.event_name IS NOT NULL,
          'Still Waiting',
          ewc.timer_wait) last_wait_latency,
       ewc.source
  FROM ps_history.threads AS pps
  LEFT JOIN ps_history.events_waits_current AS ewc ON pps.thread_id = ewc.thread_id and pps.ts = ewc.ts
  LEFT JOIN ps_history.events_statements_current as esc ON pps.thread_id = esc.thread_id and pps.ts = esc.ts
 GROUP BY pps.ts,pps.thread_id
 ORDER BY pps.ts,pps.processlist_time DESC, last_wait_latency DESC;

This is what SHOW CREATE VIEW displays:
mysql> show create view x$processlist\G
*************************** 1. row ***************************
                View: x$processlist
         Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `x$processlist` AS select `pps`.`ts` AS `ts`,`pps`.`THREAD_ID` AS `thd_id`,`pps`.`PROCESSLIST_ID` AS `conn_id`,if((`pps`.`NAME` = 'thread/sql/one_connection'),concat(`pps`.`PROCESSLIST_USER`,'@',`pps`.`PROCESSLIST_HOST`),replace(`pps`.`NAME`,'thread/','')) AS `user`,`pps`.`PROCESSLIST_DB` AS `db`,`pps`.`PROCESSLIST_COMMAND` AS `command`,`pps`.`PROCESSLIST_STATE` AS `state`,`pps`.`PROCESSLIST_TIME` AS `time`,`pps`.`PROCESSLIST_INFO` AS `current_statement`,`esc`.`LOCK_TIME` AS `lock_latency`,`esc`.`ROWS_EXAMINED` AS `rows_examined`,`esc`.`ROWS_SENT` AS `rows_sent`,`esc`.`ROWS_AFFECTED` AS `rows_affected`,`esc`.`CREATED_TMP_TABLES` AS `tmp_tables`,`esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`,if(((`esc`.`NO_GOOD_INDEX_USED` > 0) or (`esc`.`NO_INDEX_USED` > 0)),'YES','NO') AS `full_scan`,if((`esc`.`TIMER_WAIT` is not null),`esc`.`SQL_TEXT`,NULL) AS `last_statement`,if((`esc`.`TIMER_WAIT` is not null),`esc`.`TIMER_WAIT`,NULL) AS `last_statement_latency`,`ewc`.`EVENT_NAME` AS `last_wait`,if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',`ewc`.`TIMER_WAIT`) AS `last_wait_latency`,`ewc`.`SOURCE` AS `source` from ((`ps_history`.`threads` `pps` left join `ps_history`.`events_waits_current` `ewc` on(((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`) and (`pps`.`ts` = `ewc`.`ts`)))) left join `ps_history`.`events_statements_current` `esc` on(((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`) and (`pps`.`ts` = `esc`.`ts`)))) group by `pps`.`ts`,`pps`.`THREAD_ID` order by `pps`.`ts`,`pps`.`PROCESSLIST_TIME` desc,`last_wait_latency` desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

How to repeat:
There are two problems here.  First, this section is missing from the definition stored in the database:
(
  ts,
  thd_id,
  conn_id,
  user,
  db,
  command,
  state,
  time,
  current_statement,
  lock_latency,
  rows_examined,
  rows_sent,
  rows_affected,
  tmp_tables,
  tmp_disk_tables,
  full_scan,
  last_statement,
  last_statement_latency,
  last_wait,
  last_wait_latency,
  source
) 

Secondly, the view definition has lost whitespace formatting.

Suggested fix:
Store the original, unaltered view definition alongside the fully qualified whitespace stripped version.  Present the unaltered version to the SHOW CREATE VIEW statement and make it available in INFORMATION_SCHEMA as well.
[13 Jun 2015 7:45] Justin Swanhart
I've had a number of occasions where having the original view information is important.  As a backup tool, mysqldump can not properly back up and restore the exact original version of a view.  This is really not acceptable.  You need to be able to restore the objects as you constructed them.

Changing from FR to S2.
[4 Aug 2015 9:43] Umesh Shastry
Hello Justin,

Thank you for the report.

Thanks,
Umesh