Details
Major
Description
Hi,
I am upgrading from MySQL 5.6.14 to MariaDB 10.0.18 and ran into a bug. After upgrading the mysql_upgrade script upgrades all the views, enabling the general query log showed me the last query is executed when upgrading the second view (upgrading the first view succeeds but upgrading the second view crashes the thread):
REPAIR NO_WRITE_TO_BINLOG VIEW `click_url_view` FROM MYSQL; ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [db]> REPAIR NO_WRITE_TO_BINLOG VIEW `click_url_view` FROM MYSQL; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 5 Current database: db
The view definition (according to MySQL 5.6):
root@localhost [db] > show create view click_url_view\G
*************************** 1. row ***************************
View: click_url_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `click_url_view` AS select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`ad_part` `AP` join `asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = 'promoted_video') and (`AP`.`status` = 'Active') and (`A`.`status` = 'Active')) group by `AP`.`ad_id`
character_set_client: utf8
collation_connection: utf8_unicode_ci
1 row in set (0.00 sec)
The view definition according to MariaDB seams to be the same:
MariaDB [db]> show create view click_url_view\G
*************************** 1. row ***************************
View: click_url_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `click_url_view` AS select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`ad_part` `AP` join `asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = 'promoted_video') and (`AP`.`status` = 'Active') and (`A`.`status` = 'Active')) group by `AP`.`ad_id`
character_set_client: utf8
collation_connection: utf8_unicode_ci
The view .frm file on MySQL 5.6:
TYPE=VIEW query=select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`promo2`.`ad_part` `AP` join `promo2`.`asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = \'promoted_video\') and (`AP`.`status` = \'Active\') and (`A`.`status` = \'Active\')) group by `AP`.`ad_id` md5=3adf1c6392abe74c377f6b22dd80b658 updatable=0 algorithm=0 definer_user=root definer_host=localhost suid=2 with_check_option=0 timestamp=2014-11-18 19:25:55 create-version=1 source=SELECT ad_id, click_url, GREATEST(A.created, AP.created) AS created, GREATEST(A.modified, AP.modified) AS modified\nFROM ad_part AP JOIN asset A ON AP.ad_part_id = A.ad_part_id\nWHERE click_url IS NOT NULL AND asset_type = "promoted_video" AND AP.status = \'Active\'AND A.status = \'Active\'\nGROUP BY ad_id client_cs_name=utf8 connection_cl_name=utf8_unicode_ci view_body_utf8=select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`promo2`.`ad_part` `AP` join `promo2`.`asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = \'promoted_video\') and (`AP`.`status` = \'Active\') and (`A`.`status` = \'Active\')) group by `AP`.`ad_id`
The view .frm file on MariaDB (after trying to run REPAIR VIEW):
TYPE=VIEW query=select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`promo2`.`ad_part` `AP` join `promo2`.`asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = \'promoted_video\') and (`AP`.`status` = \'Active\') and (`A`.`status` = \'Active\')) group by `AP`.`ad_id` md5=3adf1c6392abe74c377f6b22dd80b658 updatable=0 algorithm=0 definer_user=root definer_host=localhost suid=2 with_check_option=0 timestamp=2014-11-18 19:25:55 create-version=1 source=SELECT ad_id, click_url, GREATEST(A.created, AP.created) AS created, GREATEST(A.modified, AP.modified) AS modified\nFROM ad_part AP JOIN asset A ON AP.ad_part_id = A.ad_part_id\nWHERE click_url IS NOT NULL AND asset_type = "promoted_video" AND AP.status = \'Active\'AND A.status = \'Active\'\nGROUP BY ad_id client_cs_name=utf8 connection_cl_name=utf8_unicode_ci view_body_utf8=select `AP`.`ad_id` AS `ad_id`,`A`.`click_url` AS `click_url`,greatest(`A`.`created`,`AP`.`created`) AS `created`,greatest(`A`.`modified`,`AP`.`modified`) AS `modified` from (`promo2`.`ad_part` `AP` join `promo2`.`asset` `A` on((`AP`.`ad_part_id` = `A`.`ad_part_id`))) where ((`A`.`click_url` is not null) and (`A`.`asset_type` = \'promoted_video\') and (`AP`.`status` = \'Active\') and (`A`.`status` = \'Active\')) group by `AP`.`ad_id`
Please let me know if you need any more information.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Thank you, this issue may be closed.
Thanks for the report. This problem is being tracked in
MDEV-8115.