MySQL Bugs: #34867: Backup: crash if altered view

Bug #34867	Backup: crash if altered view
Submitted:	27 Feb 2008 0:08	Modified:	8 Sep 2008 18:00
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
I create a view v1.
I create a view v2 based on v1.
I alter view v1.
I say BACKUP DATABASE.
Crash.

How to repeat:
mysql> create database d;
Query OK, 1 row affected (0.00 sec)

mysql> use d
Database changed
mysql> create view v1 as select 5;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v2 as select * from v1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter view v1 as select 6;
Query OK, 0 rows affected (0.00 sec)

mysql> backup database d to '1';
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the bug report. Verified as described.

[Thread 1147967824 (LWP 23827) exited]
080227  8:37:07 [Note] Backup: Backup completed
mysqld: sql_class.cc:425: void Diagnostics_area::set_eof_status(THD*): Assertion `! is_set()' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1147701584 (LWP 23816)]
0x0000003bfca30ec5 in raise () from /lib64/libc.so.6
(gdb) 

create database d;
use d
create view v1 as select 5;
create view v2 as select * from v1;
alter view v1 as select 6;
backup database d to '1';

I predicted wrong.  This isn't related to my bug at all.

mysqld: handler.cc:1970: THD* handler::ha_thd() const: Assertion `!table || !table->in_use || table->in_use == current_thd' failed.

table->in_use->query  is  "backup database d to '1'"
table->in_use->proc_info  is  "Locking thread: holding table locks"

current_thd ->query  is  "backup progress locking thread"
current_thd ->proc_info  is  "checking permissions"

If you follow the attached script

create database d;
use d;
create view v1 as select 5;
create view v2 as select * from v1;
alter view v1 as select 6;

then v2 is invalid

mysql> select * from v2;
ERROR 1356 (HY000): View 'd.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

When trying to write the metadata for this view to the preamble, open_normal_and_derived_tables returns an error code. There is a bug in si_objects TableObj::do_serialize that returns FALSE (i.e., 'ok') when this error code is encountered. See si_objects.cc @ 1772-1773. 

#0  open_normal_and_derived_tables (thd=0x953f910, tables=0x9605970, flags=0)
    at sql_base.cc:4151
#1  0x084d907e in obs::TableObj::do_serialize (this=0x9592088, thd=0x953f910, 
    serialization=0x95ada88) at si_objects.cc:1772
#2  0x088c8543 in obs::Obj::serialize (this=0x9592088, thd=0x953f910, 
    serialization=0x95ada88) at ../si_objects.h:188
#3  0x088c5027 in bcat_get_item_create_query (catalogue=0x95ab904, 
    item=0x9590944, stmt=0xae608f84) at kernel.cc:1811
#4  0x088d801e in bstream_wr_item_def (s=0x9593624, cat=0x95ab904, 
    kind=PER_DB_ITEM, item=0x9590944) at stream_v1.c:1558
#5  0x088d7a70 in bstream_wr_meta_data (s=0x9593624, cat=0x95ab904)
    at stream_v1.c:1258
#6  0x088d61b4 in bstream_wr_preamble (s=0x9593624, hdr=0x95ab904)
    at stream_v1.c:164
#7  0x088cb996 in backup::write_preamble (info=@0x95ab900, s=@0x9593620)
    at stream.h:156
#8  0x088c7cf8 in Backup_restore_ctx::do_backup (this=0xae6090b0)
    at kernel.cc:872

Suggested fix:
Return TRUE instead of FALSE when open_normal_and_derived_tables fails in the code mentioned above.

The suggested fix is not sufficient if the database contains another view:

create database d;
use d;
create view view1 as select 1;
create view v1 as select 5;
create view v2 as select * from v1;
alter view v1 as select 6;

backup database d to 'bup_alterview.bak';

-> Lost connection to MySQL server during query

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/50998

2676 Jorgen Loland	2008-08-06
      Bug#34867 - Backup: crash if altered view
      
      Old behavior: Server crash if a view was altered before backing 
      up the database.
            
      New behavior: Backup command reports error.
            
      Fix: A bug in si_objects.cc#do_serialize incorrectly returned
      'false' (success) when it failed to open a view. It now
      cleans up and returns 'true' (error) in this case.

Patch approved. Well done!

Patch approved.

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/51270

2681 Jorgen Loland	2008-08-11
      Bug#34867 - Backup: crash if altered view
            
      Old behavior: Server crash if a view was altered before backing 
      up the database.
                  
      New behavior: Backup command reports error.
                  
      Fix: A bug in si_objects.cc#do_serialize incorrectly returned
      'false' (success) when it failed to open a view. It now
      cleans up and returns 'true' (error) in this case.

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/51271

2681 Jorgen Loland	2008-08-11
      Bug#34867 - Backup: crash if altered view
            
      Old behavior: Server crash if a view was altered before backing 
      up the database.
                  
      New behavior: Backup command reports error.
                  
      Fix: A bug in si_objects.cc#do_serialize incorrectly returned
      'false' (success) when it failed to open a view. It now
      cleans up and returns 'true' (error) in this case.

Noted in 6.0.7.

If a view was altered before backing up a database, BACKUP DATABASE
caused a server crash.

Pushed into 6.0.6-alpha  (revid:jorgen.loland@sun.com-20080811104141-wnb21ro350ezf0g0) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)