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