Is there a way to view past mysql queries with phpmyadmin?

Question

I'm trying to track down a bug that's deleting rows in a mysql table.

For the life of me I can't track it down in my PHP code, so I'd like to work backwards by finding the actual mysql query that's removing the rows.

I logged in to phpmyadmin, but can't find a way to view the history of past sql operations.

Is there a way to view them in phpmyadmin?

Can't you search your code for `DELETE` statements and debug that way? — webbiedave, Jul 14 '10 at 14:43
Listen to baol and webbiedave. You should enable query logging is to see what's going, but then you still have to find where the SQL statements are coming from in your code. Start grepping for "delete", there can't be *that* many delete statements in your project. — joeynelson, Jul 14 '10 at 14:50
yeah, i actually began by grepping for delete, but my eyes must be glossed over b/c none of the deletes seem problematic ... which is why i'm hoping the query log will help. thanks to you both for your help. — chris1979, Jul 14 '10 at 14:57
Check for any triggers in your database that might do the deletions as well — Mark Baker, Jul 14 '10 at 15:36
possible duplicate of [where can I find mysql logs in phpmyadmin?](http://stackoverflow.com/questions/3039799/where-can-i-find-mysql-logs-in-phpmyadmin) — outis, Sep 26 '12 at 19:12

score 116 · Answer 1 · edited Jun 20 '20 at 09:12

116

Ok, so I actually stumbled across the answer.

phpMyAdmin does offer a brief history. If you click on the 'sql' icon just underneath the 'phpMyAdmin' logo, it'll open a new window. In the new window, just click on the 'history' tab.

That will give you the last twenty or so SQL operations.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 14 '10 at 15:21

chris1979

2,667
3
16
6

2

You can configure phpMyadmin to save more. I have mine setup to save the last 100000 queries. – Mixologic Feb 23 '14 at 18:34
BUT... each time you update (eg: load a previous version) your database you lose your history (so you can't re-install a database to check it history) – JinSnow Aug 20 '14 at 15:15
1

Doesn't log index changes, and it would exactly be useful for that thing, since it doesn't properly log them when you execute them either… – o0'. Apr 08 '15 at 08:28
82

I don't see this SQL icon, where is it in the current version? – Joseph Astrahan Apr 30 '16 at 17:20
4

@JosephAstrahan click the `Query window` icon next to `Home` – amallard Jun 08 '16 at 00:37
1

The icon seems to be mission in newer versions. – Victor Marcoianu Aug 09 '21 at 11:51

score 95 · Answer 2 · answered Oct 28 '15 at 10:08

95

There is a Console tab at the bottom of the SQL (query) screen. By default it is not expanded, but once clicked on it should expose tabs for Options, History and Clear. Click on history.

The Query history length is set from within Page Related Settings which found by clicking on the gear wheel at the top right of screen.

This is correct for PHP version 4.5.1-1

answered Oct 28 '15 at 10:08

alleyoopster

1,194
7
6

8

Length is now under **Home | Settings | SQL queries | Query history length**. And maddeningly "Value must be equal or lower than 25!" – Bob Stein Apr 12 '16 at 21:17
Wonderful. I have always wanted that, assumed it was not possible, given "Get Autosaved Query" did something different. – Tuntable May 11 '21 at 02:01
You can see only the user query not all the queries – G M May 03 '23 at 15:40

score 70 · Answer 3 · edited Oct 22 '19 at 08:52

70

You just need to click on console at the bottom of the screen in phpMyAdmin and you will get the Executed history:

edited Oct 22 '19 at 08:52

simhumileco

31,877
16
137
115

answered May 04 '18 at 09:31

Samarth Saxena

1,121
11
18

1

I was unable to find the page in the accepted answer (maybe it's from a different version), and this worked for me, thanks. – felwithe Aug 17 '18 at 14:02
10

This shows only Queries which were run by user from `phpmyadmin` itself. It is not show complete records of all performed queries. – Fusion Mar 20 '19 at 20:27

score 15 · Answer 4 · edited Feb 18 '23 at 20:28

15

To view the past queries simply run this query in phpMyAdmin.

SELECT * FROM `mysql`.`general_log`

if it is not enabled, run the following two queries before running it.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

edited Feb 18 '23 at 20:28

Community

1
1

answered Jan 20 '16 at 05:45

Syed Waqas Bukhary

5,130
5
47
59

8

very usefull thanks. to anyone else SELECT * FROM `general_log` must be queried inside mysql table else you must include tablename: SELECT * FROM mysql.`general_log` – siggi_pop Aug 17 '17 at 12:59
#1146 - Table 'general_log' doesn't exist – xpredo Jan 15 '23 at 17:43

score 9 · Answer 5 · edited Jan 07 '20 at 18:24

9

I don't think phpMyAdmin lets you do that, but I'd like to hear I'm wrong.

On the other hand you can enable query logging in MySQL: The General Query Log

edited Jan 07 '20 at 18:24

Zeeshan Hassan Memon

8,105
4
43
57

answered Jul 14 '10 at 14:43

baol

4,362
34
44

score 8 · Answer 6 · edited Oct 22 '19 at 08:45

8

Yes, you can log queries to a special phpMyAdmin DB table.

See SQL_history.

edited Oct 22 '19 at 08:45

simhumileco

31,877
16
137
115

answered Dec 02 '10 at 11:21

blueyed

27,102
4
75
71

This complements the answer given above by @Dhinakar. – Meetai.com May 27 '15 at 04:27

score 7 · Answer 7 · edited Oct 22 '19 at 08:40

7

I am using phpMyAdmin Server version: 5.1.41.

It offers possibility for view sql history through phpmyadmin.pma_history table.

You can search your query in this table.

pma_history table has below structure:

enter image description here

edited Oct 22 '19 at 08:40

simhumileco

31,877
16
137
115

answered Jul 08 '13 at 05:09

Dhinakar

4,061
6
36
68

score 3 · Answer 8 · answered Apr 10 '14 at 07:04

3

You have to click on query window just below the phpMyAdmin logo, a new window will open. Just click on SQL History tab. There you can see history of SQL Queries.

answered Apr 10 '14 at 07:04

Anurag Prashant

995
10
31

score 3 · Answer 9 · answered Oct 29 '16 at 15:22

3

OK so I know I'm a little late and some of the above answers are great stuff.

As little extra though, while in any PHPMyAdmin page:

Click SQL tab
Click 'Get auto saved query'

this will then show your last entered query.

answered Oct 29 '16 at 15:22

Jackherer

47
1
8

score 2 · Answer 10 · answered Jul 14 '10 at 15:04

2

I may be wrong, but I believe I've seen a list of previous SQL queries in the session file for phpmyadmin sessions

answered Jul 14 '10 at 15:04

Mark Baker

209,507
32
346
385

Apologies... I'd misread your question: the list in session is the list of previous statements issued through phpmyadmin, so it doesn't contain any SQL statements issued by your own PHP scripts, so probably not that helpful. – Mark Baker Jul 14 '10 at 15:28

score 2 · Answer 11 · answered Feb 11 '21 at 05:41

Here is a trick that some may find useful:

For Select queries (only), you can create Views, especially where you find yourself running the same select queries over and over e.g. in production support scenarios.

The main advantages of creating Views are:

they are resident within the database and therefore permanent
they can be shared across sessions and users
they provide all the usual benefits of working with tables
they can be queried further, just like tables e.g. to filter down the results further
as they are stored as queries under the hood, they do not add any overheads.

You can create a view easily by simply clicking the "Create view" link at the bottom of the results table display.

score 0 · Answer 12 · answered Oct 10 '18 at 18:58

0

you can run your past mysql with run /PATH_PAST_MYSQL/bin/mysqld.exe

it run your last mysql and you can see it in phpmyadmin and other section of your system.

notice: stop your current mysql version.

S F My English.

answered Oct 10 '18 at 18:58

Omid Ahmadyani

1,430
13
15

score 0 · Answer 13 · answered May 21 '19 at 04:29

0

why dont you use export, then click 'Custom - display all possible options' radio button, then choose your database, then go to Output and choose 'View output as text' just scroll down and Go. Voila!

answered May 21 '19 at 04:29

Display Name

1
2

score -5 · Answer 14 · answered Jan 31 '15 at 13:33

-5

There is a tool called Adminer which is capable of doing all phpmyadmin job packed in single tiny php file. http://www.techinfobit.com/how-to-import-export-database-without-any-extra-installation/

answered Jan 31 '15 at 13:33

Nishant Kumar

1
1

Is there a way to view past mysql queries with phpmyadmin?

14 Answers14

Linked