MySQL Bugs: #14717: Unable to update view

Bug #14717	Unable to update view
Submitted:	7 Nov 2005 19:11	Modified:	26 Feb 2006 21:57
Reporter:	Mr Wakazula	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.18	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am unable to edit a view that has been created by the query browser, using the query browser.

DB: MySql 5.0.15
DB OS: Windows 2003 (SP1)
Query Browser: 1.1.15

How to repeat:
1) open query browser
2) log in using root account
3) right click `Edit View` on the view in question
4) Status bar says: `No arguement for format '%`'

[MAMMAL]
DROP TABLE IF EXISTS `test`.`mammal`;
CREATE TABLE  `test`.`mammal` (
  `mammal_id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(16) NOT NULL default '',
  `sex` varchar(8) NOT NULL default '',
  `is_pet` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`mammal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[CANINE]
DROP TABLE IF EXISTS `test`.`canine`;
CREATE TABLE  `test`.`canine` (
  `canine_id` bigint(20) unsigned NOT NULL auto_increment,
  `mammal_id` bigint(20) unsigned NOT NULL default '0',
  `breed` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`canine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[MAMMAL_CANINE::Genered using Query Browser]
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `mammal_canine` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS `name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS `canine_id`,`c`.`breed` AS `breed` from (`mammal` `m` join `canine` `c` on((`m`.`mammal_id` = `c`.`mammal_id`)))

Thank you for a problem report. Sorry, but I was not able to repeat it using the steps you described. I created both tables in the empty test database on a newly installed 5.0.15 on XP, with Query Browser 1.1.15. When I tried to create the VIEW:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW
`mammal_canine` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS
`name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS
`canine_id`,`c`.`breed` AS `breed` from (`mammal` `m` join `canine` `c`
on((`m`.`mammal_id` = `c`.`mammal_id`)))

I've got a message at the bottom of the window that the definer name is not fully qualified. So, I changed it to `root`@`localhost`, view was created successfully, AND I was able to get its text for further editing using the Edit View from the menu.

So, please, describe what else should I do to repeat the problem you encountered.

Creating the view isn't the problem.  If you read the description, the problem is editing the view once it is created.  I can create views, and then they appear in the tree view on the right.  When I right click the newly created view, the status bar says:

No argument for format '%`'

I am on Win XP sp 2, Query Browser 1.1.17, connecting to Debian 3.1, MySQL 5.0.15.

Email me at furmanc@empirekosher.com if you want more detail, screen prints, etc.

Valeriy,

Where you able to `Edit` the view using Query Browser: 1.1.15?

Thanks for the posting Corey.  It's good to hear that others are experiencing
the same problem.

Yes, I was able to Edit the views both in 1.1.15 and in 1.1.17 I've upgraded to yesterday. As soon as I set view definer as `root`@`localhost`, not @`%`, upon creation of the view. I can post you a screenshot.

If I try to create the view just as you described, I am getting the "View definer is not fully qualified", error #1446. The same error - in mysql command line client:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `mammal_canine3` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS `name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS `canine_id`,`c`.`br
eed` AS `breed` from (`mammal` `m` join `canine` `c` on((`m`.`mammal_id` = `c`.`mammal_id`)));
ERROR 1446 (HY000): View definer is not fully qualified
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mammal_canine3` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS `name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS `canine_id`
,`c`.`breed` AS `breed` from (`mammal` `m` join `canine` `c` on((`m`.`mammal_id` = `c`.`mammal_id`)));
Query OK, 0 rows affected (0.01 sec)

So, I do not understand how you was able to create a view without error messages with your SQL. As soon as view is successfully created, I can edit it without any problems.

What version of MySQL server do you use?

As stated in my my original posting:

DB: MySql 5.0.15
DB OS: Windows 2003 (SP1)
Query Browser: 1.1.15

OK. MySQL server 5.0.15... So, please, try to execute your create view statement from the mysql command line client (having tables already created). Then just copy and paste the results, as I did.

1) logged into server (5.0.15) from the command prompt
2) use test;
3) CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW
`mammal_canine3` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS
`name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS
`canine_id`,`c`.`br
eed` AS `breed` from (`mammal` `m` join `canine` `c` on((`m`.`mammal_id` =
`c`.`mammal_id`)));
4) ERROR 1446 (HY000): View definer is not fully qualified

That was exactly my question: how you get the view created with your code, if it gives this error. Please, reread my comment of Nov. 11:

"Yes, I was able to Edit the views both in 1.1.15 and in 1.1.17 I've upgraded to yesterday. As soon as I set view definer as `root`@`localhost`, not @`%`, upon creation of the view."

Please, create the view with DEFINER `root`@`localhost`, try to edit it in Query Browser and inform about the results.

"how you get the view created with your code"

There appears to be some confusion here.  The view was created via the Query Browser: 1.1.15.

1) Log into Query Browser using the root account
2) `Execute` the following query:
SELECT m.mammal_id AS mammal_id,m.name AS name,m.sex AS sex,m.is_pet AS is_pet,c.canine_id AS canine_id,c.breed AS breed 
FROM mammal m 
JOIN canine c ON m.mammal_id = c.mammal_id
3) Click: `Query` -> `Create View from Select`

------
Up to this point, all is well
------

4) `Execute` the following query:
SELECT * FROM test.mammal_canine

------
The appropriate data is returned.
------

5) Now, right click `Edit View` on mammal_canine

------
Query Browser throws an error: No argument for format '%`'
------

Does this help?

Still can't repeat according to your description in the last comment with QB 1.1.17. "Edit view" works and it gives me (sorry, I called it vvv1 this time, on a fresh test db, with only 2 tables you mentioned):

DROP VIEW IF EXISTS `test`.`vvv1`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vvv1` AS select `m`.`mammal_id` AS `mammal_id`,`m`.`name` AS `name`,`m`.`sex` AS `sex`,`m`.`is_pet` AS `is_pet`,`c`.`canine_id` AS `canine_id`,`c`.`breed` AS `breed` from (`mammal` `m` join `canine` `c` on((`m`.`mammal_id` = `c`.`mammal_id`)));

May be, it was 1.1.15-only problem So, please, try to repeat with QB 1.1.17 on a fresh, empty database, and inform about the results.

I'm experiencing the exact same problem described. Platform: Windows XP, Query Browser  1.1.17

1) Right-click the schema, select "Create new view", enter a name, then get the skeleton. Set the view as:

CREATE VIEW `supertel`.`v_temp` AS
  SELECT * FROM ipphones;

2) Click Execute, view shows up in the shema list. I can select from view, it's all there.

3) Right-click the view and select Edit. I get the "No argument for format '%'" error at the bottom.

Note: If I right-click the view and say Copy SQL to clipboard, clipboard gets the following definition:

DROP VIEW IF EXISTS `supertel`.`v_temp`;
CREATE ALGORITHM=UNDEFINED DEFINER=`emorgoch`@`%` SQL SECURITY DEFINER VIEW  `supertel`.`v_temp` AS select `ipphones`.`phoneID` AS `phoneID`,`ipphones`.`hardwareIP` AS `hardwareIP`,`ipphones`.`softwareIP` AS `softwareIP`,`ipphones`.`commPort` AS `commPort`,`ipphones`.`status` AS `status` from `ipphones`;

Dear all,
To repeat this bug you should create a view and try to edit it in the (QUERY BROWSER) if you are able to edit it change the 'localhost' or the IP you entered to '%' , execute the view and try to edit it again , in all cases you are gonna be able to edit it from the administrator .
please , try to fix this .
Amr El-Imam

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I just received a notice from the bug system that:

"No feedback was provided for this bug for over a month, so it is
being suspended automatically." (Dec 18th)

Oddly enough on Nov 28th and Dec 7th, other users have posted complaints.
I don't understand why this issue is being closed as it has not been addressed.

I guess I'll have to wait for the next version of MySql Query Browser.  Maybe with luck the bug will disappear.

Sorry, but I still was not able to repeat the problem with any set of steps proposed in QB 1.1.17. But (!) I am connecting locally, as root. So, all my new views has CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`..., and they are editable.

If I try to explicitely use ... DEFINER=`root`@`%`, I get an error message 1446 (View definer is not fully qualified) from MySQL 5.0.15. Please, explain me how can I create view with DEFINER=`root`@`%`? It is the reason for further error messages, but I can't get it created in such a way. Should I connect from remote host, perfrom some specific GRANT or what (do not include direct manipulations with tables in mysql database, please)?

- I am connecting remotely via the root account to the MySql server
- there are no special grant privileges with the root or any other account
- as for: "Please, explain me how can I create view with DEFINER=`root`@`%`"

I really don't know what else to say.  My query does not even contain a "DEFINER" reference.  It is being added by the query browser when the view is created.

I'm using the Query Browser 1.1.17 on Windows XP against mysql server 5.0.15 running on a linux host. I've granted a user access from any host in our network to a database. A view can be created and executed, but not edited. On the statusbar is mentions says [No argument for format '%`]. I guess that once the user has been granted access its the DEFINER seems to become 'user'@'%' instead of 'user'@'hostname'.

I was able to edit a view when connected to a remote server running on Linux
from a XP box. Could you please test the new version 1.1.18.

Thanks in advance.

My appologies about the delay, our office has been swamped.

As requested, I performed the test (see Nov 17 post) using MySql Query Browser v1.1.18.
Query Browser throws an error: No argument for format '%`'.

Interestingly enough, I can edit the view from MySql Administrator v1.1.6.
It shows:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test`.`mammal_canine` AS select `m`.`mammal_id` AS `mam

It's odd that I cannot see the sql statement in it's entirety.

DETAILS:
- client: Windows XP (SP2)
- server: Windows 2003 (SP1), MySql 5.5.15

Then according with your last post, this bug can be marked as duplicate
of http://bugs.mysql.com/bug.php?id=16785?

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".