On Tue, Feb 28, 2023 at 9:31 AM Gordan Bobic <gordan.bobic@gmail.com> wrote:
A long standing annoyance has been that stored procedures are opaque when it comes to profiling them with more granularity than just the entire procedure.
SHOW FULL PROCESSLIST shows the individual queries while they are running, but polling SHOW FULL PROCESSLIST or SELECT * FROM information_schema.PROCESSLIST is not a practical solution because you can only poll it so many times per second without crippling the server.
So I had the idea to hook triggers to information_schema.PROCESSLIST to capture the changes to the INFO column and shunt them into mysql.slow_log.
But MariaDB informs them that root isn't privileged enough to crate a trigger in information_schema.
nothing can create triggers on information_schema tables as they aren't real tables. Data is populated from internal structures/functions when selected. There isn't an "insert" that can be triggered on.
Is there another way to augment slow logging granularity of queries that execute as part of the stored procedure?
Is SET profiling = 1; / SHOW PROFILE; the information you need? https://mariadb.com/kb/en/show-profile/