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

Bug #44939	SQL dumps containing broad views fail when executing
Submitted:	18 May 19:20	Modified:	2 Jun 17:56
Reporter:	Andreas Streichardt
Status:	Verified
Category:	Client	Severity:	S3 (Non-critical)
Version:	5.1.28, 5.1.33, 5.0, 5.1, 6.0 bzr	OS:	Linux
Assigned to:		Target Version:
Tags:	Contribution, Views, mysqldump
Triage:	Triaged: D3 (Medium)

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.