CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 13616

Troubleshoot to create MYSQL view

I am new to mysql so maybe to some of you my question will seem naive.

I have this query to create a table in database(here is fiddle):

CREATE TABLE `hydrants log` (
  `TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ID` int(8) NOT NULL,
  `TRIG` int(1) NOT NULL,
  `VALUE` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

also I have this query to create view: 

CREATE ALGORITHM=UNDEFINED DEFINER=`geo`@`%` SQL SECURITY DEFINER VIEW `hydrants_log_maxtime` 
AS select `hydrants log`.`ID` AS `id`,`hydrants log`.`TRIG` AS `trig`,`hydrants log`.`VALUE` AS `value`,max(`hydrants log`.`TIME`) AS `time` from `hydrants log` group by `hydrants log`.`ID`;

When I try to create it in sql fiddle. I get this error:

Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

I guess the problem with this row:

UNDEFINED DEFINER=`geo`@`%` SQL SECURITY DEFINER VIEW `hydrants_log_maxtime`

So my question is how can I change the view query to create the view above?

Upvotes: 1

Views: 42

Answers (1)

Andrew
Andrew

Reputation: 1866

I think, problem is in DEFINER=`geo`@`%` parameter of view. There is no such user in MySQL database of sqlfiddle and you cannot assign him as view definer. All other parameters must work.
Also restriction like SQL SECURITY DEFINER will not work in sqlfiddle since you have only one user and cannot add new or edit existing. Query with this parameter will work, but it will be unused.

Upvotes: 1

Related Questions