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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: BLOB, information_schema, longtext, tempfiles

[18 Oct 2007 12:39] Jan Schmidt
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.
[22 Oct 2007 17:57] MySQL Verification Team
Thank you for the bug report.
[29 Nov 2007 14:06] Sergei Glukhov
Bug#30287 is fixed, the problem with TEXT field is described in the bug#29153 report.
[29 Nov 2007 14:07] Sergei Glukhov
duplicated with bug#29153