Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8117

REPAIR VIEW crashes thread

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.18
    • 5.5.44, 10.0.19
    • Views
    • None
    • Ubuntu 12.04 "precise" LTS

    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.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              michaeldg Michaël de groot
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.