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

[18 Aug 2009 9:36] Peter Laursen
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.
[18 Aug 2009 10:02] Sveta Smirnova
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.
[18 Aug 2009 11:16] Peter Laursen
In my opinion the fix is required in the server and not client(s).
[18 Aug 2009 11:24] Peter Laursen
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.
[18 Aug 2009 13:06] Sveta Smirnova
Thank you for the feedback.

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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.