phpMyAdmin and complex VIEWs in MySQL 5.0: A dangerous combination

With release 2.7.0 the popular database web front-end phpMyAdmin starts to adopt more and more of the new features of MySQL 5.0. But there are still some hurdles on the way. If you use VIEWs on huge tables, phpMyAdmin can for example easily lock up your database.

The problem is, that phpMyAdmin tries to get an exact row count for MySQL VIEWs in many places. It achieves this by executing a SELECT COUNT(*) FROM view_name against the VIEW. This happens for example on the database overview page (that shows up by default whenever you select a database from the menu) or even for the list of tables in the left frame. Other database administration tools that use a similar strategy as phpMyAdmin could also be affected.

The disastrous consequences of the row count

This is of course a very dangerous feature for complex VIEWs on huge tables (particularly for those using ALGORITHM=TEMPTABLE). VIEWs could be designed to return very quickly if there is an additional WHERE clause restricting the result set. But the exact row count on the whole VIEW can take a long, long time to execute and build really huge temporary tables on the MySQL server.

As a result phpMyAdmin will be rendered useless on databases with such VIEWs as the overview pages never show up and time out in the browser. But furthermore this behaviour of phpMyAdmin can take down a MySQL server completely in the worst case: The process will consume CPU, memory and I/O resources and (depending on the engines of the underlying tables) lock even whole tables. As a consequence other queries on those tables can be locked as well and build up too many connections for the server to handle.

This is not just a nightmare scenario: I've seen it actually happen on a VIEW with many millions of rows and complex JOINs.

Work around the problem

The developers of phpMyAdmin accepted this as a bug report, so it will probably be fixed in a future release. A solution would be to drop the row counts for VIEWs completely or at least make this behaviour configurable (but the difficulty is that phpMyAdmin uses the row count to determine some settings for the user interface, for example to grey out the button to view a table).

In the meantime I see two ways to work around the problem: The first one is to just disable access to dangerous VIEWs for all accounts that use phpMyAdmin. The second one is to apply some patches to phpMyAdmin, as I did to release 2.7.0. I'm completely unfamiliar with the phpMyAdmin source code and didn't have time to really dig into it, so do this at your own risk.

First deactivate the row count in the database overview page for VIEWs only. Open the file db_details_structure.php and look for this part:

if ( $each_table['TABLE_ROWS'] === NULL
     || $each_table['TABLE_ROWS'] < $GLOBALS['cfg']['MaxExactCount']) {
    $each_table['TABLE_ROWS'] = PMA_countRecords( $db,
        $each_table['TABLE_NAME'], $return = true, $force_exact = true );
}

$table_encoded = urlencode($each_table['TABLE_NAME']);
// MySQL < 5.0.13 returns "view", >= 5.0.13 returns "VIEW"
$table_is_view = ( $each_table['TABLE_TYPE'] === 'VIEW'
                   || $each_table['TABLE_TYPE'] === 'SYSTEM VIEW' );

Rearrange the two paragraphs to have the check for VIEWs before the row count and add another restriction to the if clause:

// MySQL < 5.0.13 returns "view", >= 5.0.13 returns "VIEW"
$table_is_view = ( $each_table['TABLE_TYPE'] === 'VIEW'
                   || $each_table['TABLE_TYPE'] === 'SYSTEM VIEW' );

if ( ! $table_is_view
     && ($each_table['TABLE_ROWS'] === NULL
         || $each_table['TABLE_ROWS'] < $GLOBALS['cfg']['MaxExactCount']) ) {
    $each_table['TABLE_ROWS'] = PMA_countRecords( $db,
        $each_table['TABLE_NAME'], $return = true, $force_exact = true );
}

Now go to the file libraries/common.lib.php and deactivate the exact row count in the function PMA_getTableList. This function is called from the left frame to build the list of tables. Just comment out the following lines:

// check for correct row count
// if ( NULL === $table['Rows'] ) {
//     $table['Rows'] = PMA_countRecords( $db, $table['Name'],
//         $return = true, $force_exact = true );
// }

This could probably have some negative side effects. But I didn't notice any, at least not on the functionality I use in phpMyAdmin. Of course phpMyAdmin will still try to find the exact row count for a VIEW if you directly select it, but at least you're able again to select a database that contains such a dangerous VIEW.

Update (2005-02-04)

With the beta release 2.8.0 of phpMyAdmin this bug has been mitigated, so the above hotfix should no longer be necessary.