#4239 (ok 4.1.6) Missing LIMIT clause for some queries

4.1.5
fixed
Marc Delisle
None
1
2014-09-04
2014-01-18
slserpent
No

With certain queries, I'm getting an infinite loop in phpMyAdmin. I tracked it down using xdebug to the loop at line 2546 in DisplayResults.class.php. When I put in a break condition ( if ($loop_count++ > 1000) break; ), the infinite loops stopped, although the script's output was wrong. Instead of showing 25 rows, it was showing 1000 rows. Seems like it wanted to output all 178836 rows in the results on one page; so not a true infinite loop but might as well be.

Here's a query that gives the infinite loop:
SELECT sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC
ORDER BY num DESC

Here's a query that works fine:
SELECT Filename, count(CRC) as num FROM backups GROUP BY CRC
ORDER BY num DESC

Here's my table structure:
CREATE TABLE IF NOT EXISTS backups (
id int(8) NOT NULL AUTO_INCREMENT,
Backup tinytext NOT NULL,
Filename text NOT NULL,
Path text NOT NULL,
Size int(11) NOT NULL,
ModDate datetime NOT NULL,
CRC varchar(8) NOT NULL,
CanDelete tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
UNIQUE KEY new_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=302797 ;

The table has about 300K rows, as you can see from the auto_increment.

phpMyAdmin version is 4.1.5
PHP version is 5.4.22
Platform is Windows 7 64-bit

Discussion

  • Hi slserpent,

    I believe this might be due to the complexity of the nature of the queries, rather than an issue with the display class.
    For example
    SELECT Filename, count(CRC) as num FROM backups GROUP BY CRC
    ORDER BY num DESC
    will simply select the file name and count CRCs, which happens pretty fast.
    while
    SELECT sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC
    ORDER BY num DESC
    will have to sum all the sizes for a particular CRC which on a table of 300000 rows will take a long long time.
    If the table size is small. This actually works fine, but when the table is large, it takes ages.
    I've included a text file of inserts you can use to check this on a new table with the same table structure (try inserting a few records at first (50-60).
    Also the reason for the page displaying a lot of records when you break it, is because the code below that doesn't get executed, so it can't perform it's intended functionality.
    If you find anything else, let us know and I'll look into it.

     
  • I just noticed something strange, when I do
    SELECT sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC
    ORDER BY num DESC
    and have a page showing 25 results each (out of a total of ~75), and i click the ">" button to navigate, the results don't seem to get updated.
    In the case of the second query, it does...

     
  • slserpent
    slserpent
    2014-01-19

    I'll try to look into this more as I can, but from what I can tell so far (at least with the data I have), complex query + lots of results = exponential execution time.

    For example, I took that same query with the sum and count function but limited the results returned:

    Showing rows 0 - 24 (3067 total, Query took 5.4753 sec) -> 15 seconds total execution
    Showing rows 0 - 24 (11447 total, Query took 5.3873 sec) -> 2 minutes total execution

    And in both cases, it isn't showing 25 rows. It's showing all the rows in the results. Which is suggesting to me that the while loop at line 2549 is going over all the results instead of only 25 for some reason.

     
  • Marc Delisle
    Marc Delisle
    2014-01-19

    • Group: 3.3.7 --> 4.1.5
     
  • Marc Delisle
    Marc Delisle
    2014-01-19

    • summary: infinite loop in PMA_DisplayResults->_getTableBody --> Missing LIMIT clause for some queries
     
  • Marc Delisle
    Marc Delisle
    2014-01-20

    • assigned_to: Marc Delisle
     
  • Marc Delisle
    Marc Delisle
    2014-01-20

    • summary: Missing LIMIT clause for some queries --> (ok 4.1.6) Missing LIMIT clause for some queries
    • status: open --> resolved
    • Priority: 5 --> 1
     
  • slserpent
    slserpent
    2014-01-20

    Well, that change fixes when I'm using count but doesn't fix when I'm using a subquery. For example:

    SELECT * FROM (SELECT filename, crc, sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC ORDER BY num DESC) as dupes where num > 1

     
  • I know this bug is marked (ok) but when I run
    SELECT * FROM (SELECT filename, crc, sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC ORDER BY num DESC) as dupes where num > 1
    on MAINT_4_1_6
    I end up with 25 results on the page, with no way to navigate to the next ones.
    EDIT : Never mind. I didn't notice the last comment on the new page, sorry.

     
    Last edit: Sachith Seneviratne 2014-01-26
  • Marc Delisle
    Marc Delisle
    2014-01-26

    • Status: resolved --> fixed