MySQL Bugs: #31691: information_schema: columns are longtext instead of varchar

Bug #31691	information_schema: columns are longtext instead of varchar
Submitted:	18 Oct 2007 12:39	Modified:	29 Nov 2007 14:07
Reporter:	Jan Schmidt	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	BLOB, information_schema, longtext, tempfiles

Description:
As has been described in http://bugs.mysql.com/bug.php?id=7215, 'longtext' is not an appropriate data type for information_schema columns. After that bug was closed, obviously some columns in information_schema were once again changed to type 'longtext'. This is especially annoying in combination with the fact that tempfiles are creates in the datadir (c.f. http://bugs.mysql.com/bug.php?id=30287), considering that any kind of BLOB type (such as LONGTEXT) makes mysql write tempfiles on disk. Thus, a simple "SELECT * FROM information_schema.columns;" must access the disk.

How to repeat:
select table_schema,table_name, column_name from information_schema.columns where data_type = 'longtext';

Suggested fix:
Use VARCHAR instead of LONGTEXT.

Thank you for the bug report.

Bug#30287 is fixed, the problem with TEXT field is described in the bug#29153 report.

duplicated with bug#29153