MySQL Bugs: #31434: mysqldump dumps view as table

Bug #31434	mysqldump dumps view as table
Submitted:	7 Oct 2007 19:05	Modified:	20 Oct 2008 17:29
Reporter:	Ryan Liu
Status:	Closed
Category:	Client	Severity:	S2 (Serious)
Version:	5.0.45, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Tatjana A. Nuernberg	Target Version:	5.1.29,5.0+
Triage:	Triaged: D2 (Serious)

Description:
I have some views in a mysql database, and I use mysqldump to backup files daily. 

When I use mysql < dumpFile to restore it, it says "too many columns".

That is the reason I created a view -- I have thousands of columns that can not be in one
table. 

I looked at dump file, it try to create a table based on view schema, that is why it
fails. 

I tested mysql CE 5.0.19 and 5.0.45, on windows 2000 and windows Vista Ultimate.

Hope there will be a fix soon!
Ryan Liu
Fengpu Software 

How to repeat:
create 4 or more  big tables (more than 1000 columns each table)
build  view contains all columns in these tables
use mysqldump backup the data and shcema
drop database and recreate it
use mysql <dumpFile to resotre database

Suggested fix:
dump the sql for creating view, not as a big table

I think the problem is that a VIEW can be defined on another VIEW!  As there really is no
way to control in what order they are dumped, the SQL of the DUMP will contain a CREATE
TEABLE statement a view.  The table will later be dropped and a VIEW created.  This is
what every program does that is able to create SQL-dumps of VIEWS based on VIEWS 
(Importing the script would return an error if VIEWs were attempted created in the wrong
order)

This is a detail that also our users have often overlooked!  They see the CREATE TABLE
statement in the SQL dump and think they found a bug!  

Try search the DUMP file for the tablename to the end-of-file!  Isn't the TABLE DROPPED
and a VIEW created?

But if you are reaching a limit to the # of columns that can be created, then this
'temporay table' solution has a problem!

So I would like to hear the solutions to this as well!
 
Peter Laursen
Webyog (not MySQL AB)

Thank you for the report.

There are several different bug reports about similar problem.

Please provide output of SHOW CREATE VIEW and SHOW CREATE TABLE for each underlying
table. Also provide accurate command you use to invoke mysqldump.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

create database test;
use test;

/*==============================================================*/
/* Table: country                                               */
/*==============================================================*/
create table if not exists country
(
   id                             int                            not null
AUTO_INCREMENT,
   name                           varchar(50)                    not null,
   abbreviate                     varchar(10),
   code                           varchar(10),
   primary key (id)
);

/*==============================================================*/
/* Table: province                                              */
/*==============================================================*/
create table if not exists province
(
   id                             int                            not null
AUTO_INCREMENT,
   name                           varchar(50)                    not null,
   short_name                     varchar(50),
   country_id                     int                            not null,
   primary key (id),
   INDEX idx_Reference_40 (country_id), FOREIGN KEY (country_id)
    REFERENCES country (id)
);

/*==============================================================*/
/* Table: city                                                  */
/*==============================================================*/
create table if not exists city
(
   id                             int                            not null
AUTO_INCREMENT,
   name                           varchar(200)                   not null,
   short_name                     varchar(50),
   area_code                      varchar(10)                    not null,
   province_id                    int                            not null,
   primary key (id),
   INDEX idx_Reference_41 (province_id), FOREIGN KEY (province_id)
    REFERENCES province (id)
);

delete from country;
insert into country (id,name,code) values(1,"China", 'CN');
insert into country (id,name,code) values(2,"USA", 'US');

   
delete from province;
insert into province (id,name,country_id) values (201, "Califonia",  2);
insert into province (id,name,country_id) values (202, "Shanghai",1);

create view countryProvince as select country.id as c_id, country.name as c_name,
province.id as p_id, province.name as p_name from country, province where
province.country_id = country.id;

mysqldump -u root -p  --allow-keywords --single-transaction --quick --verbose
--result-file c:\mydump.txt test

BTW, if I use --skip-add-drop-table with mysqldump, the temporary table created for view
will not be dropped when it finally creates view.  

mycati -u root -p test < mydump.txt

Verified as described with MySQL 5.0-bk tree.

When you dump a database with views then there is a "create table" at the dump file
instead of only the view syntax.

dump file:
...
DROP TABLE IF EXISTS `countryProvince`;
/*!50001 DROP VIEW IF EXISTS `countryProvince`*/;
/*!50001 CREATE TABLE `countryProvince` (
  `c_id` int(11),
  `c_name` varchar(50),
  `p_id` int(11),
  `p_name` varchar(50)
) */;
...
/*!50001 DROP TABLE `countryProvince`*/;
/*!50001 DROP VIEW IF EXISTS `countryProvince`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`miracee`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `countryProvince` AS select `country`.`id` AS `c_id`,`country`.`name` AS
`c_name`,`province`.`id` AS `p_id`,`province`.`name` AS `p_name` from (`country` join
`province`) where (`province`.`country_id` = `country`.`id`) */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

this is 'not a bug'!!

Let us concentrate things a little

DROP TABLE IF EXISTS `countryProvince`;
/*!50001 DROP VIEW IF EXISTS `countryProvince`*/;
/*!50001 CREATE TABLE `countryProvince`
/*!50001 DROP TABLE `countryProvince`*/;
/*!50001 DROP VIEW IF EXISTS `countryProvince`*/;
/*!50001 CREATE ..  VIEW `countryProvince` AS .. */

I *has to be done like this* to ensure that VIEWs defined on VIEWS will always restore
(as there is no way to specify/control in which order VIEWS are dumped - and so the
'child' VIEW may be dumped before the 'parent' VIEW -- and for that reason a
'dummy'/'temporary' table needs to be created).

When we at Webyog get such complaint with our backup tools we explain this issue to our
user in 5 minutes.  Here it seems to take 5 months ...

@to the reporter: did not everything restore OK? What are you complaining about? Some
script that you did not even read *to the end*?

You are right about the SQL File created by mysqldump. But i think the problem Ryan Liu is
a different.

If you have a view, with more columns than the max columnsize in mysql, you'll get a
create table syntax which throws an error as Ryan Liu reported. Since the view maybe
valid, the temporary table isn't..

Stefan, thank you for the hint.

Verified as described using attached test case.

Problem can happen with following scenario:

1. One has MyISAM table with more than 767 columns. She probably get warning, but table
would be created successfully.
2. Server started with option --default-storage-engine=innodb, so we have more columns
than allowed limit.
3, Try to reload the dump.

test case

Attachment: bug31434.test (application/octet-stream, text), 61.13 KiB.

option file

Attachment: bug31434-master.opt (application/octet-stream, text), 41 bytes.

I guess I uploaded wrong script last time so you couldn't repeat the problem.

I see the problem with the view which join 8+ INNODB tables, each 1000+ more columns.

Maybe it is bad design. But anyway, that is where problem occures. 

Thanks,
Ryan Liu

This is indeed hotfixed by forcing the stand-in tables to be of MyISAM type.

To wit:

- ha_innobase::create() can throw HA_ERR_TO_BIG_ROW (sic) (if we have more columns than
REC_MAX_N_FIELDS (or rather an arbitrary constant, 1000 in the current server); MyISAM
does not throw it.

- the engine has no relevance otherwise, as the stand-in table will be DROPped and
replaced with the VIEW anyway, it's never really worked with.

- MyISAM's there anyway (for the time being) thanks to our admin tables (ie, it's marked
as mandatory in configure)

- this is not pretty. I'd really prefer sorted output in dump, and possibly VALID/ENABLED
flags for views in the server.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52994

2726 Tatiana A. Nurnberg	2008-09-01
      Bug#31434 mysqldump dumps view as table
      
      mysqldump creates stand-in tables before dumping the actual view.
      Those tables were of the default type; if the view had more columns
      than that (a pathological case, arguably), loading the dump would
      fail. We now make the temporary stand-ins MyISAM tables to prevent
      this.

q'd for 5.0.70, 5.1.30, 6.0.8

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54357

2689 Georgi Kodinov	2008-09-19
      fixed a problem with the push of bug #31434

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54362

2689 Georgi Kodinov	2008-09-19
      fixed a problem with the push of bug #31434

Noted in 5.1.29 changelog.

mysqldump could fail to dump views containing a large number of
columns.  

Leaving report status unchanged; this is early documentation of an upcoming push into
5.1.29.

Pushed into 5.1.30  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti) (version
source revid:kgeorge@mysql.com-20081001094725-vf4mqjkmajlm22qy) (pib:4)

Setting report to NDI pending push into 5.0.x, 6.0.x.

Pushed into 6.0.8-alpha  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti)
(version source revid:kgeorge@mysql.com-20081001100520-exs1tupnfanm1mij) (pib:5)

Noted in 6.0.8 changelog.

Setting report to NDI pending push into 5.0.x.

Noted in 5.0.70 changelog.

Pushed into 5.1.29-ndb-6.2.17  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti)
(version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti)
(version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti)
(version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

This bug probably should be fixed in different way: see bug #44939 for example.

If i understand the problem correctly then the problem is that mysqldump can't determine
if a view depends on another view. Without knowign mysqls internals: I guess parsing the
SQL again is too much work in mysqldump. Can't mysql hold some metadata somewhere? In my
imagination mysql should store a hint somewhere if a view depends on another view and
could store this information combined with which view(s) it is there. mysqldump could
then read that information and put it into the right order when dumping the database.

See also bug #46779 showing this bug was fixed not properly.

I'll admit that I'm not perfectly happy with the phrasing.
The issue was fixed within the given constraints; that this is not the "ideal solution"
(one that would imply a rewrite of mysqldump, that is, a fix that has no
time/effort/testing constraints) was already acknowledged further up.

To re-itered what I said above,

- I'd like for output to be sorted, tables, views, views depending on those, etc.,
eliminating forward-references where possible. This is arguably a soft requirement.

- I'd like to be able to restore broken views. That is, a view should arguably have a
"valid"-flag in the server. By that token, you could just restore all the views flagging
them invalid ("no checks"), and then all the way at the end (when all the data and the
referenced tables/views are restored already) try to enable them. This would not only
allow you to replay views in any order (without stand-in tables), it would also enable
you to dump and restore views that have lost some of their base-tables, which might be
regarded as a plus. (You'd still see an error on trying to enable them, so we wouldn't
drift into "silent failure" land.) This is unequal but very similar to what Peter Laursen
writes in Bug#44939 (though I had more of a tri-state (ENABLED, DISABLED, INCOMPLETE) in
mind, and a per-view switch (ALTER VIEW SET STATE=ENABLED, or some such. Or more
specifically, enabled:1, valid:1, only the server can change "valid" (meaning all
base-tables/-views are there), and enabled&&valid is required to use the view) but that's
arguably an implementation detail.

'fiddling' with clients/mysqldump does not help. A server-side solution is required. In
case both:

* there are 'views defined on views'
* a table materialising the view violates server or engine/MyISAM restrictions of some
kind

.. then the server has no appropriate functionality for clients to handle such views
during backup/restore.

It is quite ridiculous actually that VIEWS in MySQL are 'non_materialising' and still it
is necessary to 'materialize' during restore.

I have elsewhere proposed a VIEW_CHECKS (session) variable similar to FOREIGN_KEY_CHECKS.
That is consistent, will solve all such problems and we will get rid of the ugly 'dummy'
tables.