← Back to team overview

maria-discuss team mailing list archive

Re: Creating a trigger on information_schema.processlist


On Tue, Feb 28, 2023 at 9:31 AM Gordan Bobic <gordan.bobic@xxxxxxxxx> 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?