Bug #31662 'null' is shown as type of fields for view with bad definer, breaks mysqldump
Submitted: 17 Oct 2007 13:09 Modified: 3 Dec 2007 17:23
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: bfsm_2007_10_25, mysqldump

[17 Oct 2007 13:09] Domas Mituzas
Description:
if a view is created with invalid definer (or a definer gets removed), DESCRIBE shows field types as 'null'.
As field types are reused later by  mysqldump, invalid stub table entries are placed to dumps afterwards:

/*!50001 CREATE TABLE `a` (
  `xx` null
) */;

This renders mysqldumps not executable. 

Related: Bug#26817

How to repeat:
CREATE ALGORITHM=UNDEFINED DEFINER=`a`@`b` SQL SECURITY DEFINER VIEW `test`.`testview` AS select `mysql`.`user`.`User` AS `xx` from `mysql`.`user`

DESCRIBE testview;

$ mysqldump test testview

Suggested fix:
Verified against: 
ChangeSet@1.2573, 2007-10-04 19:31:32+02:00, joerg@trift2. +1 -0
[17 Oct 2007 13:12] Domas Mituzas
Same is for 'SHOW FIELDS'
[26 Oct 2007 7:01] 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/36404

ChangeSet@1.2550, 2007-10-26 09:01:29+02:00, tnurnberg@mysql.com +2 -0
  Bug#31662: 'null' is shown as type of fields for view with bad definer, breaks mysqldump
  
  SHOW FIELDS FROM a view with no valid definer was possible (since fix
  for Bug#26817), but gave NULL as a field-type. This led to mysqldump-ing
  of such views being successful, but loading such a dump with the client
  failing. Patch allows SHOW FIELDS to give data-type of field in underlying
  table.
[27 Oct 2007 6:35] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 in opt
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[3 Dec 2007 17:23] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

SHOW COLUMNS and DESCRIBE displayed null as the column type for a
view with no valid definer. This caused mysqldump to produce a
non-reloadable dump file for the view.