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.
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.