数据库报错[Err] 1227 – Access denied; you need (at least one of) the SUPER privilege(s) for this operatio
数据库报错you need (at least one of) the SUPER privilege(s) for this operation
问题描述:
navicat执行脚本,数据库报错
[Err] 1227 – Access denied; you need (at least one of) the SUPER privilege(s) for this operation
由于导出的SQL脚本语句使用DEFINER约束,直接删除DEFINER约束条件,重新运行就好了。
在阿里云数据库中,这样的约束过不去
改之前
DROP VIEW IF EXISTS `sline_search`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sline_search` AS select '游记' AS `channelname`,0 AS `webid`,`sline_notes`.`id` AS `aid`,101 AS `typeid`,`sline_notes`.`title` AS `title`,`sline_notes`.`description` AS `description`,`sline_notes`.`litpic` AS `litpic`,`sline_notes`.`shownum` AS `shownum`,`sline_notes`.`kindlist` AS `kindlist`,'' AS `attrid`,0 AS `headimgid`,`sline_notes`.`id` AS `tid`,0 AS `ishidden` from `sline_notes` where (`sline_notes`.`status` = 1) union select '通用' AS `通用`,`sline_model_archive`.`webid` AS `webid`,`sline_model_archive`.`aid` AS `aid`,`sline_model_archive`.`typeid` AS `typeid`,`sline_model_archive`.`title` AS `title`,`sline_model_archive`.`content` AS `content`,`sline_model_archive`.`litpic` AS `litpic`,`sline_model_archive`.`shownum` AS `shownum`,`sline_model_archive`.`kindlist` AS `kindlist`,`sline_model_archive`.`attrid` AS `attrid`,0 AS `0`,`sline_model_archive`.`id` AS `id`,`sline_model_archive`.`ishidden` AS `ishidden` from `sline_model_archive` ;
删除
DEFINER=`root`@`localhost`
改之后
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `sline_search` AS select '游记' AS `channelname`,0 AS `webid`,`sline_notes`.`id` AS `aid`,101 AS `typeid`,`sline_notes`.`title` AS `title`,`sline_notes`.`description` AS `description`,`sline_notes`.`litpic` AS `litpic`,`sline_notes`.`shownum` AS `shownum`,`sline_notes`.`kindlist` AS `kindlist`,'' AS `attrid`,0 AS `headimgid`,`sline_notes`.`id` AS `tid`,0 AS `ishidden` from `sline_notes` where (`sline_notes`.`status` = 1) union select '通用' AS `通用`,`sline_model_archive`.`webid` AS `webid`,`sline_model_archive`.`aid` AS `aid`,`sline_model_archive`.`typeid` AS `typeid`,`sline_model_archive`.`title` AS `title`,`sline_model_archive`.`content` AS `content`,`sline_model_archive`.`litpic` AS `litpic`,`sline_model_archive`.`shownum` AS `shownum`,`sline_model_archive`.`kindlist` AS `kindlist`,`sline_model_archive`.`attrid` AS `attrid`,0 AS `0`,`sline_model_archive`.`id` AS `id`,`sline_model_archive`.`ishidden` AS `ishidden` from `sline_model_archive`