Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-6993

Protect against possible problems with EXPLAIN of stored functions in MySQL

Details

    • Yes
    • Yes
    • [obsolete] Server Integrations

    Description

      See  https://bugs.mysql.com/bug.php?id=67632 

       

      The problem: MySQL has a strange way of executing EXPLAIN. This might be dangerous for people to execute if they have some stored functions in DB.

       

      See also: blog post on percona.com named  "Uncommon Sense MySQL - When EXPLAIN can trash your database"  (not posted as of now )

       

      Proposed solution:

      • use  BEGIN; ROLLBACK for EXPLAIN commands PMM run against DB 
        • It can be manual Explain executed from UI (QAN) against the query 
        • pmm-agent executes EXPLAIN to get the list of the tables included in the table 

      Acceptance Criteria

      • PMM running Explain commands  and collecting metrics for "harmful" query - doing no farm to the system 

      How to test

      • Log in into the Mysql database monitored by PMM
      • Create database, table and stored function according to https://www.percona.com/blog/2020/11/23/uncommon-sense-mysql-when-explain-can-trash-your-database/
      • Run query `select * from (select cleanup()) as t1clean;` at least once so it gets shown in PMM Query Analysis Dashboard. Fill the table with data again.
      • Navigate to the QAN dashboard, find and click at the query `select * from (select cleanup()) as t1clean;` and navigate to Explain tab.
      • Verify that data in the table did not get deleted.
         

      Attachments

        Activity

          People

            jan.prukner Jan Prukner (Inactive)
            roma.novikov Roma Novikov
            Nurlan Moldomurov Nurlan Moldomurov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist