Bug #69383 PREPARE from a 800 KB string causes ER_PARSE_ERROR "memory exhausted"

• Submitted: 2 Jun 2013 20:57
• Modified: 3 Jun 2013 9:11
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5, 5.6, 5.7
• OS: Any
• CPU Architecture: Any

[2 Jun 2013 20:57] Elena Stepanova

Description:
Initially reported as https://bugs.launchpad.net/maria/+bug/821241 long time ago. Google doesn't find a match in here, so I'll refile it. 

The provided test case creates a valid SQL query about 800 KB long and attempts to use it to prepare statement. PREPARE fails with something like

query 'prepare stmt from @sql' failed: 1064: memory exhausted near ') UNION ALL SELECT COUNT(`c45`) FROM `db`.`t174` FORCE INDEX (`ind45`) UNION ALL'

Please give your verdict whether it's a bug or an expected limitation. 

How to repeat:
--disable_warnings
DROP DATABASE IF EXISTS db;
--enable_warnings
CREATE DATABASE db;
USE db;

--let $tables = 200
while ($tables) 
{
	--let $sql = CREATE TABLE t$tables (pk INT NOT NULL
	--let $indexes = PRIMARY KEY (pk)
	--let $columns = 63
	while ($columns) 
	{
		--let $sql = $sql, c$columns INT
		--let $indexes = $indexes, INDEX ind$columns (c$columns)
		--dec $columns
	}
	--let $sql = $sql, $indexes) ENGINE=MyISAM
	--eval $sql
	--dec $tables
}

SET SESSION group_concat_max_len=100*1024*1024;

SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1;

select length(@sql);

prepare stmt from @sql;

DROP DATABASE db;

[3 Jun 2013 9:11] MySQL Verification Team

Hello Elena,

Thank you for the report.
Verified as described.

Thanks,
Umesh

[3 Jun 2013 15:23] MySQL Verification Team

Simpler testcase:

-----------
drop table if exists t1;
create table t1(a int,key(a))engine=myisam;
set @sql=concat(repeat("select count(a) from t1 union all ",10000),"select count(a) from t1");
select length(@sql);
prepare s from @sql;
-----------

[28 Mar 2017 0:30] Yichen Jia

I have a similar bug that the string length is just 235K in mysql version 6.16