Bug #31434 mysqldump dumps view as table
Submitted: 7 Oct 2007 17:05 Modified: 20 Oct 2008 15:29
Reporter: Ryan Liu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.0.45, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[7 Oct 2007 17:05] Ryan Liu
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
[7 Oct 2007 19:23] Peter Laursen
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)
[10 Oct 2007 22:40] Sveta Smirnova
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.
[11 Nov 2007 0:00] Bugs System
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".
[11 May 2008 6:54] Ryan Liu
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
[15 May 2008 13:19] Susanne Ebrecht
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.
[15 May 2008 13:21] Susanne Ebrecht
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 */;
[29 May 2008 20:53] Peter Laursen
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*?
[19 Aug 2008 15:58] Stefan Haubold
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..
[19 Aug 2008 19:43] Sveta Smirnova
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.
[19 Aug 2008 19:44] Sveta Smirnova
test case

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

[19 Aug 2008 19:44] Sveta Smirnova
option file

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

[19 Aug 2008 19:54] Ryan Liu
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
[1 Sep 2008 7:21] Tatiana Azundris Nuernberg
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.
[1 Sep 2008 12:19] Bugs System
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.
[18 Sep 2008 15:20] Tatiana Azundris Nuernberg
q'd for 5.0.70, 5.1.30, 6.0.8
[19 Sep 2008 13:26] Bugs System
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
[19 Sep 2008 13:33] Bugs System
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
[7 Oct 2008 19:19] Paul DuBois
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.
[9 Oct 2008 18:00] Bugs System
Pushed into 5.1.30  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti) (version source revid:kgeorge@mysql.com-20081001094725-vf4mqjkmajlm22qy) (pib:4)
[9 Oct 2008 18:38] Paul DuBois
Setting report to NDI pending push into 5.0.x, 6.0.x.
[17 Oct 2008 16:43] Bugs System
Pushed into 6.0.8-alpha  (revid:kgeorge@mysql.com-20080919132432-n7qg7e110vhq8cti) (version source revid:kgeorge@mysql.com-20081001100520-exs1tupnfanm1mij) (pib:5)
[17 Oct 2008 17:15] Paul DuBois
Noted in 6.0.8 changelog.

Setting report to NDI pending push into 5.0.x.
[20 Oct 2008 15:29] Paul DuBois
Noted in 5.0.70 changelog.
[28 Oct 2008 21:03] Bugs System
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)
[28 Oct 2008 22:22] Bugs System
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)
[1 Nov 2008 9:47] Bugs System
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)
[19 May 2009 7:40] Sveta Smirnova
This bug probably should be fixed in different way: see bug #44939 for example.
[19 May 2009 7:51] Andreas Streichardt
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.
[18 Aug 2009 13:07] Sveta Smirnova
See also bug #46779 showing this bug was fixed not properly.
[19 Aug 2009 11:55] Tatiana Azundris Nuernberg
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.
[19 Aug 2009 12:05] Peter Laursen
'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.
[16 Aug 2012 10:21] wei liu
yes, I think 'use the given constraint' to check number of column was not even a solution, it was just a work around. If the temporary table of view has some column with 'too large row size', there will be another 'constraint check'.
The 'temporary table' method to solve 'view order problem' should be abandoned.
[16 Aug 2012 10:38] wei liu
But it seems use '--force' can ignore the errors. version:5.5.16
[16 Aug 2012 11:55] Georgi Kodinov
I don't dispute the fact that we should be doing a proper dependency graph and dumping according to it.
But for this to happen it has to be supported by the server first. 
Until this time I hope the current partial solution will take away a bit of the strain.