browse doesn't show all records

[bm322741]

Describe the bug

Browsing a table, click the >> to go to the last row. The last page isn't the last records in the database. 66,080 records, last calculated page is 2607. Should be 2644 [25 records/page] based on record count.

To Reproduce

Steps to reproduce the behavior:

1. Browse table
2. Click on '>>'
3. Last Page is 2607

Expected behavior

Last page should be 2644.

Screenshots

Server configuration

• Operating system: Fedora 29
• Web server: Apache 2.4.39
• Database version: MariaDB 10.1.37
• PHP version: 7.2.19
• phpMyAdmin version: 4.8.4

Client configuration

• Browser: Chrome 75.0.3770.100
• Operating system: Windows 10 1903

Additional context

Add any other context about the problem here.

[williamdes]

#15100

[williamdes]

#15077

[williamdes]

Fixed by #15094
Thanks for reporting @bm322741
Can you please confirm that the issue does not exist anymore on version 4.9.0.1, you can download 4.9.0.1 on our website

[bm322741]

The same with 4.9.0.1. I tried a repair just in case [no errors reported] but still doesn't get all the records. The page count/last record are exactly the same.

[williamdes]

@bm322741 Can you try to set $cfg['MaxExactCount'] = 100000; https://docs.phpmyadmin.net/en/latest/config.html#cfg_MaxExactCount

[bm322741]

Yes. that did it. Is that a bug or just a config issue? I have other tables with 2,000,000 rows.

[williamdes]

I think this is a config issue, counting rows can take too long on very large tables.

@ibennetch what do you think about it ?

[ibennetch]

I don't think this is quite a bug. It's unfortunate that we have to find some threshold at which to switch from an exact count to an approximate one, but otherwise we run out of resources (especially on shared hosting situations). The solution, as you have seen, is to increase the limit further if your server can support that behavior.

Actually, a bug/feature enhancement that we should investigate here is letting the user go to the very end of records in this case; since this effectively restricted you from the last ~925 records.

I'm not sure off hand what we can do about this, but perhaps on the last page of results we should always perform an exact count.

[bm322741]

I'm not sure how you handle it internally, but I don't know why you need 80,000 something when I am only showing 25, 50, 100 records at a time. We do it all the time with our web sites. 80,000 records / 25 = 3,200 pages, but I only need to show 1 page so that is 25 items.

For me, most of the time I am interested in the last records inserted so it makes browsing kind of useless for large tables.

[williamdes]

Caused my trouble in production where I missed rows, I think the page counter is maybe ok but not the limit of the shown data.

[edit] can not reproduce.

[williamdes]

I need a way to reproduce it.

You can refer to data on demo servers (root, no password)

• 5.0.0-dev
• 4.9.x

If you reproduce the issue and post you will accelerate the fixing process ;)

[bm322741]

Issue 1: I'm loading a lot of data manually (pasting inserts from export). I added 76 records, checked to see if they were loaded. Default was 25/page so 4 pages. I set to 100/page, the pager disappeared, but I was left with the last [76th] records shown [page 4 of 25/page]. I would expect to see all 76 records [ie when the pager is changed, reload the data being displayed according to the new pager count]. Set back to 25/page, went to page 4. No matter where I set the paging option it only showed me the last page of data. It would be easy to assume this is the only data when no pager is shown. Issue 2: When pasting large sql text [300 lines] I routinely get error #413 - lost connection to server. Smaller chunks of sql worked. When I got to down around 170 lines from the 300 it worked. It seems to be based on the quantity of text vs # of lines [inserting blobs] Issue 3: When changing pager from 25 to 500 (659 records) it stopped responding. Took about 2 minutes to respond with the 2 pages). Page 1 -> Page 2 took about 45 seconds, could be some internal beta sanity checking? Issue 4: When inserting blobs, sometimes I get not enough fields. Seems to be related to amount of blob data. I set blob to null on the insert and it works. I'm assuming the server is periodically wiped out? I couldn't load enough data to test the record limit and the database is gone. Is there a programmatic interface? It's kind of hard to load 80,000+ records via the phpmyadmin interface unless they are 1 field records or similar.' I will try with a smaller record to populate enough data.

[williamdes]

Thank you so much, I will try the instructions you provided

[shucon]

I'm not able to reproduce the issue on a 900000 row table. Could it be because the data in each row is not much?

[shucon]

I was able to reproduce the issue after restarting the server. In my case it estimated the number of rows to be more than actual and clicking the >> button, it took me an empty row page.

[williamdes]

Could you update your screenshot because it is too small to be usable, that said thanks you for the debug you made !

If possible please upload your test table here so other devs can also test it. And let us know your DB and DB version.

[shucon]

For eg: I had 99999 rows.
Here are the screenshots:

On clicking >> button:

On manually going to last possible page:

[shucon]

Here is the database file:
employee_db.zip

[shestakov-vladyslav]

Oh, I have the same problem