Issues Creating Views

[paulbrown79]

Hello

I created a view based on a SELECT statement but when I go and alter the view I see all this “extra stuff” added to the top as shown below.

Why does this stuff get added?

Also, if I dump the whole db to .SQL file on import on another host I get an error “Error Code: 1449 – The user specified as a definer ('web110-gtc'@'%') does not exist”

Thanks Paul

Code:

DELIMITER $$

USE `web110-gtc`$$

DROP VIEW IF EXISTS `vw_FinanceDashboard`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`web110-gtc`@`%` SQL SECURITY DEFINER VIEW `web110-gtc`.`vw_FinanceDashboard` AS (
SELECT…

[peterlaursen]

It is the MySQL server that adds this. It is not a SQLyog issue.  

Same would happen when you execute same CREATE VIEW statement from any client. 

Refer: 

http://dev.mysql.com/doc/refman/5.1/en/create-view.html

[peterlaursen]

 You get the error because DEFINER on source does not exist on target.. But we have this request already:

http://code.google.c…s/detail?id=324 (what would then create DEFINER as 'SQLyog user')

SQL_SECURITY for a VIEW (as well as for a STORED PROCEDURE etc.) is either INVOKER or DEFINER.  The server cannot  handle privileges for a VIEW without a DEFINER.  In particular not if SQL_SECURITY is DEFINER.

[paulbrown79]

Hey Guys

Thanks for your comments.

What is best method for moving views between servers?

Cheers

Paul

[peterlaursen]

I think there is no general answer to that. If the servers have the same users, SQLyog Schema Sync will handle.  If not the case you will have to edit the sync script (replacing DEFINER).

[Author]

Posts