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


    • Yes
    • Yes
    • [obsolete] Server Integrations


      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.




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



              Smart Checklist