Bug #86405 Stored procedure execution affected by query select in information_schema.tables
Submitted: 22 May 2017 13:09 Modified: 31 May 2017 14:09
Reporter: Atanaska Zoubeva Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.36-1 OS:Red Hat (el7, x64)
Assigned to: CPU Architecture:Any
Tags: PROCEDURE, query_cache_type

[22 May 2017 13:09] Atanaska Zoubeva
Description:
A stored procedure execution from a persistent connection is affected when the "select * from information_schema.tables" is executed from any other connection. The query fails with MySQL error code 1054: "Unknown column 'tt_data_mobile_packagesS" (actual message might differ from the one posted). The stored procedure remains non working until the query_cache_type is set to 0 or the query_cache_size is zeroed.

How to repeat:
MySQL config (my.cnf) should have the following set:

[mysqld]
query_cache_type = 1

table_open_cache=1000
open_files_limit=2000

More than 1000 tables should be created in all databases. The sample attached DB dump they are in "test" database.

Persistent connection should be opened and the stored procedure (getDataPackConfigs_json included in the attachments) should be called at least once before executing the following statement (i.e. from mysql itself):

select * from information_schema.tables;

The stored procedure next calls are failing until the connection is re-established.

The stored procedure execution is provided in the DBTest.php script.

The behavior is also reproduced on Fedora 25:
php-json-7.0.19-1.fc25.x86_64
php-mysqlnd-7.0.19-1.fc25.x86_64
MySQL-server-5.6.33-1.el7.x86_64
php-cli-7.0.19-1.fc25.x86_64
MySQL-client-5.6.33-1.el7.x86_64
php-pdo-7.0.19-1.fc25.x86_64
php-common-7.0.19-1.fc25.x86_64
Linux localhost.localdomain 4.10.15-200.fc25.x86_64 #1 SMP Mon May 8 18:46:06 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[22 May 2017 13:11] Atanaska Zoubeva
DB dump, stored procedure execution script, version information, my.cnf

Attachment: Report.tar.gz (application/gzip, text), 26.58 KiB.

[23 May 2017 6:11] Atanaska Zoubeva
pcap trace of a broken session

Attachment: test.pcap (application/vnd.tcpdump.pcap, text), 139.50 KiB.

[23 May 2017 6:14] Atanaska Zoubeva
Attached a pcap trace. Up until package 32 inclusive the requests are served without issues. Package 36 contains the select in the information schema which brakes the stored procedure and in package 58 the error code 1054 is returned with message "Unknown column 'testS' in 'field list'". Actually "testS" is the database name with capital S at the end and it is not used anywhere in the stored procedure.
[31 May 2017 14:09] MySQL Verification Team
Hi,

I can reproduce this problem but I doubt this will be fixed since we are retiring support for query cache. You can read more here:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

all best
Bogdan