MySQL Bugs: #44939: SQL dumps containing broad views fail when executing

Bug #44939	SQL dumps containing broad views fail when executing
Submitted:	18 May 2009 17:20	Modified:	18 Jul 2012 14:37
Reporter:	Andreas Streichardt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.1.28, 5.1.33, 5.0, 5.1, 6.0 bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, mysqldump, Views

Description:
We are using very broad tables and hit the MyISAM column limit. To work around this problem we are generating a few of these broad tables and combine them using a view.

When using mysqldump to create a dump of such a database this generates broken SQL as the view is first created as a table and at the end this table will be dropped and the original view will be recreated.

If the definition of the view contains more columns than the MyISAM column limit this will of course fail. I couldn't find anything in the documentation or in the mysqldump options on how to solve this problem (without loosing the view).

See:

www.3uu.de/viewtest.dump
www.3uu.de/broad_view.sql

How to repeat:
pipe the sql into a database and try to generate a valid dumpfile using mysqldump

viewtest.dump is the result of such a dump operation and this won't help recreating a database

Suggested fix:
Don't create an intermediate table in the dump but recreate the view directly.

Thank you for the report.

There was similar bug #31434 fixed in version 5.1.29. Please try this version in your environment and inform us if problem still exists.

at least also broken in 5.1.33 (i doubt it broke again in the meantime?)

if i understand the fixed bug correctly then the problem were inno tables and the fix was to create myisam tables for views because they can hold more columns. Note that my testcase is about MyISAM tables so the solution mentioned there won't help here. myisams limit is at about 2400 int columns.

Thank you for the report.

Verified as described using broad_view.sql provided.

We have a hen-egg problem here.

I would guess half of the world ppl read from left to right and from top to bottom.

Same computers doing when working on scripts. Usually scripts are executed from top to bottom.

SQL is a really old language (developing started in 197x) and just is executing from top to bottom.

Why are we creating a table from a view?

The reason is simple.

Thinking about validation checks.

Restore could fail when you have a table with a foreign key column for which the referenced table don't exist.

Inserts could fail when inserting a value into a foreign key column when there is not same value already in referenced column.

Creating a view will fail when you SELECT from another not already existing view.

And so on ...

There is none perfect dumping algorithm for it. Other RDBMS and also other MySQL dumping tools like the MySQL BACKUP (the SQL BACKUP that you will find in MySQL 6.0) or the backup in MySQL Administrator/Workbench might use other algorithm here but they all have some not solvable issues.

Our tool mysqldump solved the problem for creating views from not already existing views by creating first a table from every view and create the views at the end.

I am not able to see a solution for your problem without changing the whole design for mysqldump. But that is a decision I will let to the development.

In any case we will inform our Workbench and MySQL BACKUP developer teams about your problem so that they will be able to consider this problem before their backup/dumping code is too complex for considering.

There is a workaround for you:

Generally, I recommend to make two dumps:
$ mysqldump --no-data --triggers --routines > file1.dump
$ mysqldump --no-create-info > file2.dump

Now you have only all create statements (DDL) in file1.dump and only the data in file2.dump.

Open file1.dump in an editor.

It is plain SQL.

If you don't need the CREATE TABLE of your VIEW then just delete it from the dump file.

When you are using InnoDB or another transaction based storage engine it is also recommended to look into the data file and optimise the insert statements.

After editing the files first restore the "schema" and then restore the data:

$ mysql db < manual_edited_file1.dump
$ mysql db < file2.dump

Works for me[tm]

Attachment: simple_views.diff.txt (text/plain), 1.81 KiB.

ok i made a quick and dirty hack which works for me. i simply added a new option to mysqldump which skips the temporary table stuff. This solves my bug and should do the job for the other bug as well....this is of course a very special case but there were already two ppl hitting this bug so it might be worthwhile to include such an option

Bug #46779 marked as duplicate of this one.

Bug #46779 contains different test, but I believe cause of 2 failures are same

This is marked a 'client' issue.  In my report (marked as duplicate here) I marked as 'server' issue!  VIEWs are implemented server-side.  Server should provide funtionalities to hanle VIEWs properly in clients.

The only consistent solution woukd be to implement a VIEW_CHECKS 0|1 (Session) variable in the server.  It would be quite similar to FOREIGN_KEY_CHECKS in concept.

With such we could get rid of the ugly and stupid 'dummy' tables that do not work anyway as even the default engine (MyISAM) and the server itself has limitations on tables that VIEWS don't have.

Wouldn't it be most reasonable to have mysqldump dump views in topological order based on inter-view dependencies? This should avoid all those hacks about intemediate tables.

I notice that several comments state the fact that views are dumped in an arbitrary order, but I'm unsure whether there is a very good reason why it has to be this way.

I think it would be very difficult "to have mysqldump dump views in topological order based on inter-view dependencies". It will require parsing of SHOW CREATE VIEW, next decide if a referenced table is a BASE_TABLE or a VIEW - and if it is a VIEW then all over.

I still think the only reasonable solution is to be able to create the VIEW without checking for underlying TABLEs (or VIEWs). That is why I several times proposed a VIEW_CHECKS variable (working similar to FOREIGN_KEY_CHECKS). Also tables are dumped in "arbitrary" order irrespective of FK-dependencies/topology. If you use Foreign Keys you will SET FOREIGN_KEY_CHECKS = 0 on top of the dump.

But MySQL/Oracle interest in solving this does not impress. This report is now 2½ years old.  And there are more.

Noted in 5.5.27, 5.6.7, 5.7.0 changelogs.

mysqldump could dump views and the tables on which they depend in
such an order that errors occurred when when the dump file was
reloaded.