MySQL Bugs: #31662: 'null' is shown as type of fields for view with bad definer, breaks mysqldump

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

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

Same is for 'SHOW FIELDS'

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.

pushed to 5.0.52, 5.1.23 in opt

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

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.