Infinite Loop (in ajax?) redisplaying rows on changing "Sort Order" dropdown

[rwhirn]

Doesn't happen on all tables. Accessing a Drupal 8 Database, many, many tables and they have comments on tables and columns, somewhat long table names, somewhat long key names. I will try to find a pattern.

Steps to reproduce

1. browse a table
2. click on Sort Order dropdown
3. select "PRIMARY (DESC)

Expected behaviour

Show page from last record backwards

Actual behaviour

Shows "Loading" and shows page and then "Loading" and shows page, on and on forever. Unstoppable without going to a different URL in address bar (I hit back button and then enter in the address bar).

Server configuration

Operating system:
CentOS 7

Web server:
nginx/1.8.1
Database:
5.5.52-MariaDB - MariaDB Server
PHP version:
7.0.3
phpMyAdmin version:
4.8.0

Client configuration

Browser:
Firefox and Chrome
Operating system:
Win 7

[reFX-Mike]

I just encountered the same problem. This makes the browse function for that table unusable. I can't even find where this information is stored, so I can't reset it back to "none".

This needs to be fixed ASAP.

[williamdes]

@reFX-Mike Can you reproduce on https://demo.phpmyadmin.net/QA_4_8/ or https://demo.phpmyadmin.net/master-config/ ?
ID: root, no password

If you find a way to help reproduce this issue, I will help you :)
Maybe import a table or create a test table to reproduce this issue.

[reFX-Mike]

I've created a database called "refresh_bug" and inside a table called "bug". Simply click on "browse" and select "PRIMARY (ASC)" in the "Sort by key" drop-down.

You will see a "Loading" pop-up that will re-load about once a second and it becomes impossible to select something else unless you click on a different tab/table and then ctrl-F5. Just clicking elsewhere doesn't do anything as the Javascript queue is flushed with updates.

[Kaldor37]

We've got the exact same problem here since version 4.8.*.
We rolled back to 4.7.5 since it's a critical feature.

[williamdes]

@reFX-Mike or @Kaldor37 Can one of you recreate the database on demo server ?
Send an export if possible :)
Thank you 👍

I can not find my export of this database on my server...

This issue was reproduced correctly on demo server, I remember.
I think this is related to a missing index.

[reFX-Mike]

What happened to that database/table I've already created?

Sorry, but I can't spend time re-creating this problem on your demo server over and over. It's straightforward to do:

Create a table with 2-3 columns. Make all of them INT.
Select the first two columns and create a primary-index out of them (one index for both).
Insert some dummy data

Now when browsing the table, select the drop-down for the sorting to ascending or descending. The problem will appear.

This has nothing to do with missing indexes.

[williamdes]

Demo server gets reset every ? day/hour

Thank you for the instructions !, reproduced 100%.

SQL

CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `a` (`a`, `b`, `c`, `d`) VALUES
(1, 2, 3, 4),
(11, 22, 33, 44);

ALTER TABLE `a`
  ADD PRIMARY KEY (`a`,`b`);

[rwhirn]

I see this issue has not been addressed in 4.8.2.

I believe that it might have something to do with complex Primary Index.

[drm-wntd]

This bug appears to occur only when the key used for sorting has multiple columns. If the key is only on one column, it works fine.

[drm-wntd]

@rwhirn : Doesn't matter if the key is a primary key or not, if it has more than one column it bugs.

[drm-wntd]

So far, all I could find is that it has something to do with line 1452 and 1463 of file Results.php :

$local_order == $asc_sort

When there is more than one column in the key, $local_order only contains the first column (instead of all the columns), so the condition " $local_order == $asc_sort " never occurs and the option is never "selected" in the drop down menu.

[drm-wntd]

I think I found a fix, if anyone wants to submit a pull request with it, thats fine by me.

The problem starts here (file Results.php) :
https://github.com/phpmyadmin/phpmyadmin/blob/STABLE/libraries/classes/Display/Results.php#L4215

By calling _getUnsortedSqlAndSortByKeyDropDown with only the first element ($sort_expression[0]), the _getSortByKeyDropDown function cannot set the "selected" element in the drop down list when the key has more than one column and that causes the refresh bug.

Here's the fix :

1. Replace $sort_expression[0] by $sort_expression on this line :

https://github.com/phpmyadmin/phpmyadmin/blob/STABLE/libraries/classes/Display/Results.php#L4221

You get :

            list($unsorted_sql_query, $sort_by_key_html)
                = $this->_getUnsortedSqlAndSortByKeyDropDown(
                    $analyzed_sql_results, $sort_expression
                );

2. Replace the last $sort_expression by implode(', ',$sort_expression) on this line :

https://github.com/phpmyadmin/phpmyadmin/blob/STABLE/libraries/classes/Display/Results.php#L1418

You get :

$local_order = (isset($sort_expression) ? implode(', ',$sort_expression) : '');

Works fine on my local machine. Let me know if it works for you or if it causes other bugs.

[rwhirn]

@drm-wntd Works like a charm for me. Many thanks for the debug!