slow listing of tables compared to phpmyadmin 4.9.7

First of all I cannot say that this is actual bug but it's causing a lot of issues in our workflow.

We are using cPanel on our server where phpmyadmin is provisioned by the panel. We opened ticket to them but their conclusion was to turn directly to the developers of phpmyadmin.

We currently use phpmyadmin version 4.9.11, I also tested it with version 5.2.1 and the result is the same. When we try to list table from phpmyadmin it takes sometime to load the tables because of the queries executed towards information_schema. We do not see such behavior in version 4.9.7, tables there are listed almost immediately. The thing in our environment is that it is a bit complex which leads to this behavior. Our servers are using shared storage, also they are virtualized under hyper-v and the database server has 5k databases with 35k grants. Also we do experience this issue if mysql server is remote, there is no such issue if mysql is local . All this combined make queries towards information_schema to be very slow.

• Operating system: CloudLinux 7
• Database version: 10.3.31-MariaDB

Here are the queries in question.

In version 4.9.7 when you expand table queries are:

230421  9:27:27	 10147 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10147 Quit
		 10148 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10148 Query	SELECT @@version, @@version_comment
		 10148 Query	SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
		 10148 Query	SET lc_messages = 'en_US'
		 10149 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10148 Query	SELECT CURRENT_USER()
		 10148 Query	SHOW GRANTS
		 10148 Query	USE `test_asd`
		 10148 Query	SHOW DATABASES LIKE 'information_schema'
		 10148 Query	SHOW DATABASES LIKE 'performance_schema'
		 10148 Query	SHOW DATABASES LIKE 'mysql'
		 10148 Query	SHOW DATABASES LIKE 'sys'
		 10148 Query	SHOW DATABASES LIKE 'test\_asd'
		 10148 Query	SET collation_connection = 'utf8mb4_unicode_ci'
		 10148 Query	SHOW DATABASES LIKE 'information_schema'
		 10148 Query	SHOW DATABASES LIKE 'performance_schema'
		 10148 Query	SHOW DATABASES LIKE 'mysql'
		 10148 Query	SHOW DATABASES LIKE 'sys'
		 10148 Query	SHOW DATABASES LIKE 'test\_asd'
		 10148 Query	SHOW DATABASES LIKE 'test\_asd'
		 10148 Query	SHOW DATABASES LIKE 'information_schema'
		 10148 Query	SHOW DATABASES LIKE 'mysql'
		 10148 Query	SHOW DATABASES LIKE 'performance_schema'
		 10148 Query	SHOW DATABASES LIKE 'sys'
		 10148 Query	SHOW DATABASES LIKE 'test\_asd'
		 10148 Query	SHOW DATABASES LIKE 'information_schema'
		 10148 Query	SHOW DATABASES LIKE 'mysql'
		 10148 Query	SHOW DATABASES LIKE 'performance_schema'
		 10148 Query	SHOW DATABASES LIKE 'sys'
		 10148 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type`IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10148 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type`NOT IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10148 Query	SHOW FUNCTION STATUS WHERE `Db`='test_asd'
		 10148 Query	SHOW PROCEDURE STATUS WHERE `Db`='test_asd'
		 10148 Query	SHOW EVENTS FROM `test_asd`
		 10148 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type`IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10148 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type`IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10148 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type`IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10148 Query	SELECT 1 FROM mysql.user LIMIT 1
		 10148 Query	SHOW GRANTS FOR CURRENT_USER()
		 10148 Query	SHOW GRANTS FOR CURRENT_USER()
		 10148 Query	SHOW MASTER LOGS

In versions 4.9.11 and 5.2.1 when you expand table queries are:

230421  9:33:15	 10171 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10171 Quit
		 10172 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10172 Query	SELECT @@version, @@version_comment
		 10172 Query	SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
		 10172 Query	SET lc_messages = 'en_US'
		 10173 Connect	cpses_bik7nx8t1u@localhost as anonymous on
		 10173 Query	SHOW TABLES FROM `phpmyadmin`
		 10172 Query	SET collation_connection = 'utf8mb4_unicode_ci'
		 10173 Query	SHOW TABLES FROM `phpmyadmin`
		 10172 Query	SELECT CURRENT_USER()
		 10172 Query	SHOW DATABASES WHERE TRUE
		 10172 Query	SHOW DATABASES WHERE TRUE AND ( LOCATE('test_', CONCAT(`Database`, '_')) = 1 OR LOCATE('information_', CONCAT(`Database`, '_')) = 1 )
		 10172 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type` IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10172 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type` NOT IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10172 Query	SHOW FUNCTION STATUS WHERE `Db`='test_asd'
		 10172 Query	SHOW PROCEDURE STATUS WHERE `Db`='test_asd'
		 10172 Query	SHOW EVENTS FROM `test_asd`
		 10172 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type` IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10172 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type` IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10172 Query	SHOW FULL TABLES FROM `test_asd` WHERE `Table_type` IN('BASE TABLE', 'SYSTEM VERSIONED')
		 10172 Query	SELECT 1 FROM mysql.user LIMIT 1
		 10172 Query	SHOW GRANTS FOR CURRENT_USER()
		 10172 Query	SHOW GRANTS FOR CURRENT_USER()
		 10173 Quit
		 10172 Quit

In versions 4.9.11 and 5.2.1 there are 2 issues:

1. Not sure where this is coming from since there is no such database at all

10173 Query	SHOW TABLES FROM `phpmyadmin`

2. This 2 queries are taking very long time because of the things I mentioned about our setup.

10172 Query	SHOW DATABASES WHERE TRUE
10172 Query	SHOW DATABASES WHERE TRUE AND ( LOCATE('binarecloud_', CONCAT(`Database`, '_')) = 1 OR LOCATE('information_', CONCAT(`Database`, '_')) = 1 )

We have configured the following option to not search database in information_schema which helps only when database are listed but not when tables are:
$cfg['Servers'][$i]['DisableIS'] = true;

We just need phpmyadmin to not use information_schema when you expand tables like the old version does. Is there something that we can do to fix this?

Thanks!

The report names no repository file or test; start by tracing the table-expansion entry point and the query generation behind SHOW DATABASES and DisableIS, comparing versions 4.9.7, 4.9.11, and 5.2.1. Done means table listing avoids the slow information_schema-related queries in the described setup without breaking database and table discovery.