MySQL Bugs: #14256: definer in view definition is not fully qualified

Bug #14256	definer in view definition is not fully qualified
Submitted:	24 Oct 2005 13:30	Modified:	9 Nov 2005 2:57
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Linux (Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
When creating a view on a remote machine (user has access from host='%'), view definition doesn't contain fully qualified definer.

This affects also backups made with mysqldump (you can't restore views).

How to repeat:
1) Define a user account with wildcard
[15:19] root@(none)>grant all on test.* to 'georg'@'%' identified by 'foo';
Query OK, 0 rows affected (0.00 sec)

2) Connect to the remote server
mysql -ugeorg -p -hremote_server_name

CREATE VIEW V1 AS SELECT 1;
SHOW CREATE VIEW V1 \G

Output:
CREATE ALGORITHM=UNDEFINED DEFINER="georg"@"%" SQL SECURITY DEFINER VIEW "V1" AS select 1 AS "1"

3) Drop the view and recreate it with the output from previous SHOW CREATE VIEW
DROP VIEW V1;
CREATE ALGORITHM=UNDEFINED DEFINER="georg"@"%" SQL SECURITY DEFINER VIEW "V1" AS select 1 AS "1";

Error: ERROR 1446 (HY000): View definer is not fully qualified

Effecting another customer.

I have this problem and I want to make certain you understand my circumstance and that it will be addressed by this fix.  I use a simple create view statement on my master server like the following:

CREATE VIEW order_detail AS SELECT orders.order_id AS order_id, order_items.line_number AS line_number,order_items.quantity AS quantity
FROM orders, order_items
WHERE orders.order_id = order_items.order_id.

Once the slave tries to run it it looks like

CREATE ALGORITHM=UNDEFINED DEFINER=`jumpadmin`@`%` SQL SECURITY DEFINER VIEW `order_detail` AS select `orders`.`order_id` AS `order_id`,`order_items`.`line_number` AS `line_number`,`order_items`.`quantity` AS `quantity` from (`orders` join `order_items`) where (`orders`.`order_id` = `order_items`.`order_id`)

It is important that you do not have to fully qualify the user name because a fully qualified user, by definition, does not reside on my slave.

Thanks.

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/internals/31607

Pushed to bk 5.0.16.

Noted in 5.0.16 changelog.

I have installed version 5.0.18 on RH linux 9, but I ve the same problem.
Any suggestion?

1) be sure that you do not use --skip-grant tables (if you are then use DEFINER clause)
2) if above is not your case, then submit please new bugreport with repeatable sequance of SQL statements begining from table/view creation (because it looks like you have a bit different case)