PMA Glitch, forgetting query after insert

[GoWilkes]

I'm using phpMyAdmin 4.9.7

I always keep 3 PMA tabs open with queries that I use regularly to select, then copy to insert. Historically, after I insert then it would go back to the select query results.

But for the last few weeks, about 75% of the time it does not remember the select query, and instead loads the entire table!

I've triple checked that, when I insert, the menus at the bottom say "Insert as new row... and then... Go back to previous page".

Any suggestions?

 

[cPRex]

Hey hey! If you perform this same work in a fresh tab, does the same behavior happen?

 

[GoWilkes]

Yup. This has been happening for several weeks, really. I've closed the browser, and even restarted the computer, several times since it started.

 

[cPRex]

Can you confirm if this was user-level PMA or root-level? Once I know that I'll do some testing.

 

[GoWilkes]

User level, I haven't tried it at root level. It doesn't happen every time, though, and didn't happen at all yesterday! And I haven't seen any pattern to make it happen on command.

The process I use is:

1. SELECT * FROM table WHERE colA = 'foo'

2. Copy

3. Make a modification on the form, then click "Go".

It will insert properly, but the result page runs "SELECT * FROM table" without the WHERE.

 

[cPRex]

Awwwwww if it's intermittent that doesn't help us, as you know how those things go.

Is there anything in the MySQL or cPanel logs when it does happen?

 

[GoWilkes]

Next time it happens I'll have to look. Where do I find these logs?

 

[cPRex]

/usr/local/cpanel/logs/error_log and the default location for MySQL logs is /var/log/mysqld.log although it can be changed in /etc/my.cnf, so check that configuration file if you don't see recent information in /var/log/mysqld.log

 

[GoWilkes]

I'll keep an eye on them and get back to you. Right now the cPanel log goes back to Dec 5, but it's mostly the same few lines over and over that don't appear to have anything to do with it.

mysqld.log has waaaay too much to narrow it down without a date and time. It looks like someone was trying to hack in to MySQL at some point, so I have tens of thousands of "Access denied" errors in there. Along with a ton of InnoDB errors that started a few years ago and have never been resolved. I don't see anything on Dec 5-6 (when I started this thread) that's not related to those two issues, but it's hard to be 100% with so much to sort through.

 

[GoWilkes]

It just now happened again, at about 12:53am EST.

The most entries in the cPanel error_log don't appear to be related:

[2022-12-16 00:36:49 -0500] info [cpsrvd] version 11.106.0.11 online
==> cpsrvd 11.106.0.11 started
==> cpsrvd: loading security policy....Done
==> cpsrvd: Setting up SSL support ... Done
==> cpsrvd: transferred port bindings: 10,11,12,3,4,5,6,7,8,9
==> cpsrvd: bound to ports
[2022-12-16 00:48:08 -0500] info [cpsrvd] version 11.106.0.11 online

And same for mysqld.log, which is 3 hours before the glitch happened:

2022-12-15 22:31:36 15272774 [Warning] Access denied for user 'example_'@'localhost' (using password: NO)

Sorting the logs by last modified, though, I saw that I had this in the slow log:

# Time: 221216  0:53:54

# [email protected]: example[example] @ localhost []
# Thread_id: 15368308  Schema: example_db  QC_hit: No
# Query_time: 6.365424  Lock_time: 0.000103  Rows_sent: 35  Rows_examined: 8600
# Rows_affected: 0  Bytes_sent: 5922
SET timestamp=1671170034;
SELECT *  FROM table WHERE
    colA   = 'foo'
    AND
    colB = 'bar'
   
   
   
ORDER BY postdate DESC LIMIT 0, 100;

I changed the database name, db username, table name, colA, and colB for the post, but otherwise it's the same. The SELECT query is exactly what I had entered with the exceptions of the line breaks and the LIMIT 0, 100 at the end, and the query time of 6.36s was the time to load the entire table without the WHERE conditions.

With the WHERE conditions, there should have been 35 rows so the "Rows_sent" param is right. But, of course, when it displayed the full table it was more like 10 million rows :-/

 

[cPRex]

Do you have a reliable way to reproduce this behavior?

 

[GoWilkes]

Unfortunately, no. I tried submitting the exact same insert query the next day and it worked as expected.

 

[cPRex]

Without a good way to reproduce this, I'm afraid I don't have much I can offer on my end since it's so intermittent. If we had a way to reproduce it we could do an strace on the PMA process.

 

[Rutledge]

I'm using phpMyAdmin 4.9.7

I always keep 3 PMA tabs open with queries that I use regularly to select, then copy to insert. Historically, after I insert then it would go back to the select query results.

But for the last few weeks, about 75% of the time it does not remember the select query, and instead loads the entire table!

I've triple checked that, when I insert, the menus at the bottom say "Insert as new row... and then... Go back to previous page".

Any suggestions?bitlife

Here are a few potential causes and solutions to consider:

Server-side caching: If you are using a server-side cache (such as Memcached or APC), it is possible that the cache is not being updated correctly when you insert a new row. In this case, try clearing the cache or disabling it temporarily to see if it resolves the issue.

Browser-side caching: It is also possible that your browser is caching the previous query results and not reloading the updated data when you return to the page. To troubleshoot this issue, try clearing your browser's cache and cookies, or using a different browser to see if the issue persists.

PHP configuration: If you have recently made changes to your PHP configuration (such as increasing the value of the memory_limit setting), it is possible that this is causing issues with phpMyAdmin. Try restoring your PHP configuration to its default values to see if the issue is resolved.

Database corruption: If you have experienced a database error or corruption, it is possible that the issue is affecting the way phpMyAdmin is handling your queries. In this case, try repairing your database tables and see if the issue is resolved.