MySQL Bugs: #49437: Cannot do SHOW FIELDS for big view

Bug #49437	Cannot do SHOW FIELDS for big view
Submitted:	3 Dec 2009 23:26	Modified:	27 Jul 2011 16:41
Reporter:	Jan Steemann (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0++	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	frm, Views

Description:
The commands DESCRIBE, SHOW FIELDS, SHOW COLUMNS and SHOW CREATE TABLE cannot be run on views that are temporarily materialized to a MyISAM table that have a structure too big to be hold in an .frm file.

In sql/sql_select.cc::create_tmp_table(), there is a decision whether the intermediate table should be of type MyISAM or type HEAP.
If MySQL can use heap, everything works fine. If the intermediate table will contain one or more blob (or text) columns, mysql can't use the HEAP engine as it does not support these column types.
When the MyISAM table is created, it will burst if the table structure will not fit into the maximum size of 65535 bytes that the .frm structure can have.

This will only happen if the view has a lot of columns and the intermediate table will have a column type the HEAP engine does not support.

It is therefore possible to create views whose structure cannot be queried afterwards.

Issuing a show command (e.g. SHOW FIELDS) on the view will result in something like this:
ERROR 126 (HY000): Incorrect key file for table '/ramraid/#sql_671b_1'; try to repair it

I have also seen queries failing in storage/myisam/mi_create.c with error 1125 and error text: "MyISAM table '%s' has too many columns and/or ndexes and/or unique constraints.

This is still broken in more recent versions, e.g. MySQL 5.4.2.

The issue will also make mysqldump abort when dumping the structure of such views, meaning backing up the views is not possible anymore with mysqldump.

The view still works fine with SELECT, UPDATE, INSERT, DELETE, only the show commands will not work anymore.

How to repeat:
Will upload an sql file to create the tables and views.

Suggested fix:
In sql/sql_select.cc, in function create_tmp_table(), adding the following lines will fix the problem:

*reg_field= 0;
*blob_field= 0; // End marker
share->fields= field_count;

+ if (thd->lex->sql_command == SQLCOM_SHOW_FIELDS || thd->lex->sql_command == SQLCOM_SHOW_CREATE)
+ blob_count=0;

/* If result table is small; use a heap */
/* future: storage engine selection can be made dynamic? */

This will fix the problem described but of course it is a workaround only and not a proper fix. Effectively this will force all SHOW FIELDS commands to use the HEAP engine. HEAP does not support blob/text, so there might be issues for tables containing these types. However the intermediate table will not be populated with any records so even if the intermediate table will have a text or blob column, it will never contain any data.
I haven't ssen any issues when using the patch myself so far, however, it should be reviewed by someone who has a better picture of the codebase than I have.

Table and view definitions for test case

Attachment: table_structure.tar.gz (application/gzip, text), 63.97 KiB.

To reproduce the issue, unpack the attached file and run the SQL commands in it.
This will set up a few base tables plus a view "view_broken" that will select all columns from the base tables.

Then run 
SHOW FIELDS FROM view_broken;

This should fail with the error described.

The file attached also contains a view "view_ok" that has one column less than "view_broken" and that still has a lot of columns but will still work.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >create database d2;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >use d2
Database changed

mysql 5.1 >source c:/tmp/test.sql
Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected (0.34 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.30 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.30 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.44 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.18 sec)

Query OK, 0 rows affected (7.99 sec)

Query OK, 0 rows affected (7.64 sec)

mysql 5.1 >SHOW FIELDS FROM view_broken\G
ERROR 126 (HY000): Incorrect key file for table 'C:\Windows\TEMP\#sql770_1_1'; try to repair it
mysql 5.1 >

view_ok:

  Extra:
*************************** 9174. row ***************************
  Field: c9174
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 9175. row ***************************
  Field: c9175
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
9175 rows in set (7.39 sec)

mysql 5.1 >

Thank you for the bug report. Verified as described.

Any updates?

I just verified it and it is still broken in 5.5.6-rc.

mysql> desc view_broken;
ERROR 126 (HY000): Incorrect key file for table '/data/mysql/tmp/#sql_1592_1'; try to repair it
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.6-rc-log |
+--------------+
1 row in set (0.00 sec)

I'm not sure you hit the .frm file limit here, and not the limit for the maximum number of columns in a table.
How many columns does this view have?
We just need to produce an error message if the view has more columns than the limit we currently have for a table (4096).

Producing an error at view validation time is R1/E1 - low effort/low risk fix.
Extending the limits is a major undertaking, R5/E5.

Please split the bug into two (failure to detect an unsupported view at creation time, and a feature request to support such views), and associate the customer support issues accordingly. 

Thank you,
-- 
kostja

Perhaps the risk of adding a message is not so low as I initially evaluated. There may be users who already use these unsupported views and rely on them to work.

I don't think this is related to a column limit of 4096, because the view named "view_ok" in the attached test case has 9,175 columns and does still work, even when describing it or selecting from it.

Creating a view with one more column does also work (i.e. does not return an error code) but this view cannot be described anymore. In this case, the mentioned error occurs.

When materializing the result of the SHOW FIELDS command, MySQL will internally use create_tmp_table() in sql/sql_select.cc. At some point this will decide whether to use MyISAM or Heap for the table. 
The problem does only occur if MySQL chooses a MyISAM table. It works ok if MySQL chooses Heap.
Using the two lines of code I provided in the bug report does force the selection to a Heap table for SQLCOM_SHOW_FIELDS commands by hard coding the "blob_count" variable to 0, and this does fix the particular problem.

However, this is of course not a proper fix.

Jan,
the fact that a view with 9175 columns works for you, your application does not mean that it was intended this way or that we support it.
Obviously it's an omission in the view implementation that we don't set a limit on the maximum amount columns in a view.

What this maximum equals to is a different story.
Implementing your request is a serious undertaking, and as such can not be done in a GA version.

I hope you will appreciate that I am telling that right away without keeping this bug in "Verified" forever and thus setting false expectations.

Thanks,
-- 
kostja

I am not saying the maximum should be enforced or changed to whatever value.

All I can say is that the current version of MySQL (5.5.6-rc) and previous versions (at least 5.1.x) effectively allow the creation of much larger views, provided they have the "correct" column types (compatible with the Heap engine, that is no blobs).

For example, the following PHP test script creates a view with 103,500 columns:
<?php

$db=mysqli_connect("127.0.0.1","root","passwd","test");

$tables=array();
for ($i=1;$i<=45;$i++)
{
  $parts=array();
  $table="tmp".$i;
  $tables[]=$table;
  if ($i>1)
    $wheres[]="tmp1.id1=".$table.".id".$i;

  for ($j=1;$j<2300;$j++)
    $parts[]="c".(($i*2300)+$j)." tinyint";

  mysqli_query($db,"DROP TABLE IF EXISTS ".$table);
  mysqli_query($db,"CREATE TABLE ".$table." (id".$i." INT NOT NULL,".implode(",",$parts).",PRIMARY KEY(id".$i.")) ENGINE=MyISAM");
}

mysqli_query($db,"DROP VIEW IF EXISTS tmp");
mysqli_query($db,"CREATE VIEW tmp AS SELECT * FROM ".implode(",",$tables)." WHERE ".implode(" AND ",$wheres));

Issueing a "DESCRIBE tmp" will result in:
103500 rows in set (7 min 30.58 sec)

Selecting from this view also works.
I am not saying this is a good way to store data, but as it has worked in the past, people may have used it.

I am convinced that getting rid of the limits is a challenge and cannot be achieved easily.

In the meantime, I would prefer predictability of things.
It is currently somewhat challenging to tell how many columns a table can effictively have because this depends on column names (.frm overall limits, short column names better than longer ones), column types (.frm limits, MyISAM vs. Heap, VARCHAR vs. fixed size columns), total row length (InnoDB, too big row issue). 
If the effective limits for tables would be more predictable than now.

If a table can only have X columns max, so be it, but then this should be enforced consistently. From my point of view, it would be best to make this enforcement in the next major release of MySQL (5.5.6) so the GA version (5.1) does not change its behaviour.

Noted in 5.6.3 changelog.

For some statements such as DESCRIBE or SHOW, views with too many
columns produced errors.