"SHOW FULL TABLES FROM xxx" results always in error

[svaningelgem]

Describe the bug

When clicking on "structure" I always get the error window saying there is an SQL query error.
It sometimes appeared, so I think it might be related to my session or so?

Additional context

I was so fed up by it that I started to investigate this issue. But I don't see why it wouldn't be working for anyone else.
In Util.php@4483, there is this line:

            $db_info_result = $GLOBALS['dbi']->query(
                'SHOW FULL TABLES FROM ' . self::backquote($db) . $tblGroupSql,
                null, DatabaseInterface::QUERY_STORE
            );

This is the backtrace:
#0  PhpMyAdmin\DatabaseInterface->tryQuery(SHOW FULL TABLES FROM `database_123`, , 1, 1) called at [/usr/share/salvania-phpmyadmin/libraries/classes/DatabaseInterface.php:169]
#1  PhpMyAdmin\DatabaseInterface->query(SHOW FULL TABLES FROM `database_123`, , 1) called at [/usr/share/salvania-phpmyadmin/libraries/classes/Util.php:4483]
#2  PhpMyAdmin\Util::getTablesWhenOpen(database_123, mysqli_result Object ([current_field] => ,[field_count] => ,[lengths] => ,[num_rows] => ,[type] => )) called at [/usr/share/salvania-phpmyadmin/libraries/classes/Util.php:4318]
#3  PhpMyAdmin\Util::getDbInfo(database_123, _structure) called at [/usr/share/salvania-phpmyadmin/libraries/classes/Controllers/Database/DatabaseStructureController.php:91]
#4  PhpMyAdmin\Controllers\Database\DatabaseStructureController->_getDbInfo(_structure) called at [/usr/share/salvania-phpmyadmin/libraries/classes/Controllers/Database/DatabaseStructureController.php:140]
#5  PhpMyAdmin\Controllers\Database\DatabaseStructureController->indexAction() called at [/usr/share/salvania-phpmyadmin/db_structure.php:38]

And this is the $this-object in the tryQuery() function:

object(PhpMyAdmin\DatabaseInterface)#57 (12) {
  ["_extension":"PhpMyAdmin\DatabaseInterface":private]=>
  object(PhpMyAdmin\Dbi\DbiMysqli)#56 (0) {
  }
  ["_links":"PhpMyAdmin\DatabaseInterface":private]=>
  array(2) {
    [257]=>
    object(mysqli)#66 (19) {
      ["affected_rows"]=>
      int(-1)
      ["client_info"]=>
      string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $"
      ["client_version"]=>
      int(50012)
      ["connect_errno"]=>
      int(0)
      ["connect_error"]=>
      NULL
      ["errno"]=>
      int(0)
      ["error"]=>
      string(0) ""
      ["error_list"]=>
      array(0) {
      }
      ["field_count"]=>
      int(1)
      ["host_info"]=>
      string(23) "199.199.199.199 via TCP/IP"
      ["info"]=>
      NULL
      ["insert_id"]=>
      int(0)
      ["server_info"]=>
      string(10) "5.6.42-log"
      ["server_version"]=>
      int(50642)
      ["stat"]=>
      string(137) "Uptime: 9783  Threads: 5  Questions: 25913  Slow queries: 1  Opens: 478  Flush tables: 1  Open tables: 306  Queries per second avg: 2.648"
      ["sqlstate"]=>
      string(5) "00000"
      ["protocol_version"]=>
      int(10)
      ["thread_id"]=>
      int(4333)
      ["warning_count"]=>
      int(0)
    }
    [256]=>
    object(mysqli)#68 (19) {
      ["affected_rows"]=>
      int(1)
      ["client_info"]=>
      string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $"
      ["client_version"]=>
      int(50012)
      ["connect_errno"]=>
      int(0)
      ["connect_error"]=>
      NULL
      ["errno"]=>
      int(0)
      ["error"]=>
      string(0) ""
      ["error_list"]=>
      array(0) {
      }
      ["field_count"]=>
      int(4)
      ["host_info"]=>
      string(23) "199.199.199.199 via TCP/IP"
      ["info"]=>
      NULL
      ["insert_id"]=>
      int(0)
      ["server_info"]=>
      string(10) "5.6.42-log"
      ["server_version"]=>
      int(50642)
      ["stat"]=>
      string(137) "Uptime: 9783  Threads: 5  Questions: 25914  Slow queries: 1  Opens: 478  Flush tables: 1  Open tables: 306  Queries per second avg: 2.648"
      ["sqlstate"]=>
      string(5) "00000"
      ["protocol_version"]=>
      int(10)
      ["thread_id"]=>
      int(4334)
      ["warning_count"]=>
      int(0)
    }
  }
  ["_table_cache":"PhpMyAdmin\DatabaseInterface":private]=>
  array(0) {
  }
  ["_current_user":"PhpMyAdmin\DatabaseInterface":private]=>
  array(0) {
  }
  ["_lower_case_table_names":"PhpMyAdmin\DatabaseInterface":private]=>
  NULL
  ["_is_mariadb":"PhpMyAdmin\DatabaseInterface":private]=>
  bool(false)
  ["_is_percona":"PhpMyAdmin\DatabaseInterface":private]=>
  bool(false)
  ["_version_int":"PhpMyAdmin\DatabaseInterface":private]=>
  int(50642)
  ["_version_str":"PhpMyAdmin\DatabaseInterface":private]=>
  string(10) "5.6.42-log"
  ["_version_comment":"PhpMyAdmin\DatabaseInterface":private]=>
  string(28) "MySQL Community Server (GPL)"
  ["types"]=>
  object(PhpMyAdmin\Types)#58 (1) {
    ["_dbi":"PhpMyAdmin\Types":private]=>
    *RECURSION*
  }
  ["relation":"PhpMyAdmin\DatabaseInterface":private]=>
  object(PhpMyAdmin\Relation)#59 (0) {
  }
}

Now you see in the _links array, there is no 0, or "" entry. As such entering this function with a null will ALWAYS result in an error. Because of this line in tryQuery:

        if (! isset($this->_links[$link])) {
            return false;
        }

But sometimes it works, I think this _links[0] is disappearing somewhere. But not all the time.

Very strange and hard to find issue to me.

[svaningelgem]

I now added the following in the tryQuery():

      if ($is_show_tables && ! isset($this->_links[$link]) && count($this->_links) > 0) {
        $link = array_keys($this->_links)[1];
      }

The first entry [0] did not have enough privileges on the database (user pma -- set up in the config under 'controluser').
The second is my main user, and that one did have enough rights to list all the tables.

This pma user has ALL rights on the phpmyadmin database, but nothing else.

[Nitromethane]

I also encountered this problem. And happened when I click specific Database in left NodeTree.
Its hard to reproduce in the PMA Demo Page.

[Nitromethane]

Now you see in the _links array, there is no 0, or "" entry. As such entering this function with a null will > > ALWAYS result in an error. Because of this line in tryQuery:

    if (! isset($this->_links[$link])) {
        return false;
    }

But sometimes it works, I think this _links[0] is disappearing somewhere. But not all the time.

In class DatabaseInterface, the const CONNECT_USER is defined as 256 which is in the _links array.

[svaningelgem]

@Nitromethane : This is indeed defined, and is present in the _links array. The problem however comes from the fact that in Util::getTablesWhenOpen the query is ran with "NULL" (as you can see above). As such, it isn't looking for 256 or 257.

NULL is translated in 0 or "" in PHP arrays... Which entry isn't present in the _links array.

[Nitromethane]

@svaningelgem Thanks a lot, I have reproduce this bug.

Describe the bug

Click DB name in left node tree. It will open the error dialog occasionally

To Reproduce

Steps to reproduce the behavior:

1. Lock a table in any db, to make sure the result of run "show open tables from dbname" has tables In_use > 0;
2. The Click on db in the left NodeTree
3. It will show error dialog about “SQL Query Error”

Screenshots

Server configuration

• Operating system: Linux
• Database version: MySQL 5.5 5.6
• PHP version: PHP 5.6.3
• phpMyAdmin version: 4.8.3

Additional context

We can fix this problem by editting the getTablesWhenOpen function.
FROM:

        // Util.php Line: 4482
        $db_info_result = $GLOBALS['dbi']->query(
            'SHOW FULL TABLES FROM ' . self::backquote($db) . $tblGroupSql,
            null,
            DatabaseInterface::QUERY_STORE
        );

TO:

       $db_info_result = $GLOBALS['dbi']->query(
            'SHOW FULL TABLES FROM ' . self::backquote($db) . $tblGroupSql,
            DatabaseInterface::CONNECT_USER,
            DatabaseInterface::QUERY_STORE
        );

[svaningelgem]

Thanks for tracking this issue down @Nitromethane :-).

[nmilo]

@Nitromethane are you working on PR for this?

[williamdes]

@svaningelgem I can not reproduce this issue, can you please help me ?
Can you send your configuration please ?
Can you send the users and their grants ?

[svaningelgem]

@williamdes : I think it's better to ask @Nitromethane (see previous comment: #14811 (comment))

He had a very good description of the bug.

My tables are quite often "in use" because of all the different processes running on the server, so it's very feasible what (s)he said.

[williamdes]

Thank you for the response @svaningelgem
Maybe @Nitromethane you can help me reproduce the issue :)

[svaningelgem]

@williamdes : I thought about reproducing this on the demo version available, but it's just not possible. As @Nitromethane stated, you need to "lock tables table write" in order to make it work. But the locking session immediately ends on the web interface (as the script stops & all locks are cleared at the end of the session).

So I tried his steps:

1. Log on to the MySQL server: "mysql coffee" (for example on the test server)
2. lock tables admin write
3. On the webinterface: log on & try to open the coffee database from the left node tree.

This is reproducible from my side on phpMyAdmin 4.8.5, MySQL server 5.6.43, MySQL Client version: mysqlnd 5.0.12-dev & PHP 7.2.16.

[williamdes]

Reproduced successfully when using LOCK TABLES testtable1 READ; in my mysql cli

[svaningelgem]

Thanks for the fix 👍