MySQL Bugs: #46779: restore fails as 'dummy' table representing VIEW is invalid

Bug #46779	restore fails as 'dummy' table representing VIEW is invalid
Submitted:	18 Aug 2009 9:36	Modified:	18 Aug 2009 13:06
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There was a similar report here: 
http://bugs.mysql.com/bug.php?id=31434

Here is another test case that is not fixed:

How to repeat:
`col1` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col2` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col3` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col4` varchar(10000) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and 

CREATE TABLE `t2` (
`col1` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col2` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col3` varchar(10000) CHARACTER SET latin1 DEFAULT NULL,
`col4` varchar(10000) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- note that both tables are valid according to the limitation "The maximum row size for the used table type, not counting BLOBs, is 65535"

CREATE VIEW `vtst`.`v1_2` AS
SELECT t1.col1 AS c11, t1.col2 AS c12, t1.col3 AS c13, t1.col4 AS c14, t2.col1 AS c21, t2.col2 AS c22, t2.col3 AS c23, t2.col4 AS c24 FROM t1,t2;
-- note: a table 'materializing' this VIEW is too large as per the 65535 byte restriction.

-- now dump (using: "mysqldump -uroot -proot --databases dbname" or any other backup tool that is able to handle 'views defined on views') and import. Import fails with 
Error No.: 1118
Row size too large. The maximum row size for the used table type, not counting BLOBS, is 65525. you have to change some columns to TEXT or BLOBS.

A 'dummy' table is created to handle the 'view defined on view' case.

Suggested fix:
Not sure.  Ideally there should be an option to turn off such restrictions for empty/dummy tables or turn off the check for validity of the SELECT-part of a VIEW-definition. It is not satisfactory that user only is told that dump is not usable when importing.  

I am marking as a server bug - not a bug with mysqldump. The problem is that the implementation of 'VIEWS defined on VIEWS' is misssing a vital feature.

Thank you for the report.

See also bug #44939 which contains patch for mysqldump.

Have to decide if this is duplicate of bug #44939 though.

In my opinion the fix is required in the server and not client(s).

removing the generation of 'dummy' tables will workaround this in some cases (ie: when there are not 'views defined on views').

But if both
* there are 'views defined on views'
* a materialized table representing a view is invalid due to storage engine or server restrictions 

.. there is no client-side solution.

Thank you for the feedback.

I still think this is the same cause as with bug #44939. So they are duplicate.