MySQL Profiler - No entries in show profile

I am using MySQL profiler to help with diagnosis of some MySQL issues I am investigating. However when I issue the show profiles command I do not get any results.

This is how I am using it开发者_开发百科:

set profiling=1 --Enable profiling

-- Run some selects / inserts etc
select count(*) from mytable
insert into mytable (mydata) values ('wibble')

show profiles

I was expecting the show profiles command to give me the profile data for the selects etc that I performed after enabling profiling (Per the information from this MySQL dev page), however all I get back is an empty resultset. I do not see any errors when enabling/disabling profiling nor do I see any errors when attempting to view profiles.

The following may or may not be relevant:

1. The commands were issued via phpMyAdmin
2. I am running MySQL 5.0.77
3. select * from information_schema.profiling returns no results

Tested, this works.

You can do it from PhpMyAdmin if you can use a TRANSACTION (depending on your storage engines?)

Try this:

START TRANSACTION;

set profiling=1; --Enable profiling

-- Run some selects / inserts etc
select count(*) from mytable;
insert into mytable (mydata) values ('wibble');

show profiles;

COMMIT; --or rollback

Please note that I've also added a semicolon after each query!

In an answer to my own question...

It seems that the inbuilt MySQL profiling utilities described above operate on a MySQL session basis. phpMyAdmin seems to create a new connection everytime a command is issued. As such the command to switch on profiling and then the subsquent selects are disconnected from each other.

This is confirmed by connecting to the MySQL server via SSH and running the same commands. In this configuration profiling works.

However, it only profiles the commands issued within that session. This means that any other queries being performed against the DB from another source (i.e. a web application) are not included in the profile result.

I have not found any way of profiling all queries to the DB regardless of source (i.e. the way MSSQL profiler works). So at the moment I have to rely on setting the log configuration value, in my.cnf, and then execute the logged queries manually in an SSH session:

log=/var/log/mysqldquery.log

Not a perfect solution as it is somewhat laborious, but it works.

While the above may work it is still somewhat dangerous. You'll have to keep in mind that if you're using replication and/or if there are updates in those query logs, you'll be altering your datasets. Use with caution.

I have faced the same issue of my profile not showing my actual executed queries. Here is what I have got and it worked like a charm for me. You might need to login to the MySQL console from the below path F:\wamp64\bin\mysql\mysql8.0.27\bin - This is my folder you can select your path

Here open the cmd line and login to your MySQL console using the below command

mysql -u root[your username] -p

enter password hit enter

You are now login to MySQL, now select the database in which you have to execute the query. Once you execute the query fire the query show profiles; You will get your last executed query in the table with time and all.

Oops, one step we forget to mention that before doing this please enable profiling in MySQL. You can do that from here https://prnt.sc/XprPxgRlP1Jp

Hope this may help you out.