网站首页技术博客
Access denied; you need (at least one of) the SUPER privilege(s) for this operatio
摘要数据库报错[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operatio
数据库报错[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
仔细检查脚本语句使用DEFINER约束,直接删除DEFINER约束条件,重新运行就好了。
在阿里云数据库中,这样的约束过不去
改之前
DROP VIEW IF EXISTS `goods_in_out_view`; CREATE ALGORITHM=UNDEFINED DEFINER=`xmxy`@`localhost` SQL SECURITY DEFINER VIEW `goods_in_out_view` AS SELECT `xmxy_stock`.`goods_in`.`id` AS `id`, `xmxy_stock`.`goods_in`.`shop_id` AS `shop_id`, CONVERT('in' USING utf8mb4) AS `type`, `xmxy_stock`.`goods_in`.`goods_id` AS `goods_id`, `xmxy_stock`.`goods_in`.`in_price` AS `price`, `xmxy_stock`.`goods_in`.`number` AS `number`, `xmxy_stock`.`goods_in`.`supplier_id` AS `supplier_id`, 0 AS `customer_id`, `xmxy_stock`.`goods_in`.`user_name` AS `user_name`, `xmxy_stock`.`goods_in`.`create_time` AS `create_time`, `xmxy_stock`.`goods_in`.`update_time` AS `update_time`, `xmxy_stock`.`goods_in`.`delete_time` AS `delete_time` FROM `xmxy_stock`.`goods_in` WHERE (`xmxy_stock`.`goods_in`.`delete_time` = 0) UNION SELECT `xmxy_stock`.`goods_out`.`id` AS `id`, `xmxy_stock`.`goods_out`.`shop_id` AS `shop_id`, CONVERT('out' USING utf8mb4) AS `type`, 0 AS `goods_id`, `xmxy_stock`.`goods_out`.`price` AS `price`, `xmxy_stock`.`goods_out`.`number` AS `number`, 0 AS `supplier_id`, `xmxy_stock`.`goods_out`.`customer_id` AS `customer_id`, `xmxy_stock`.`goods_out`.`user_name` AS `user_name`, `xmxy_stock`.`goods_out`.`create_time` AS `create_time`, `xmxy_stock`.`goods_out`.`update_time` AS `update_time`, `xmxy_stock`.`goods_out`.`delete_time` AS `A` FROM `xmxy_stock`.`goods_out` WHERE ( `xmxy_stock`.`goods_out`.`delete_time` = 0 )
删除
DEFINER=`xmxy`@`localhost`
改之后
DROP VIEW IF EXISTS `goods_in_out_view`; CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `goods_in_out_view` AS SELECT `xmxy_stock`.`goods_in`.`id` AS `id`, `xmxy_stock`.`goods_in`.`shop_id` AS `shop_id`, CONVERT('in' USING utf8mb4) AS `type`, `xmxy_stock`.`goods_in`.`goods_id` AS `goods_id`, `xmxy_stock`.`goods_in`.`in_price` AS `price`, `xmxy_stock`.`goods_in`.`number` AS `number`, `xmxy_stock`.`goods_in`.`supplier_id` AS `supplier_id`, 0 AS `customer_id`, `xmxy_stock`.`goods_in`.`user_name` AS `user_name`, `xmxy_stock`.`goods_in`.`create_time` AS `create_time`, `xmxy_stock`.`goods_in`.`update_time` AS `update_time`, `xmxy_stock`.`goods_in`.`delete_time` AS `delete_time` FROM `xmxy_stock`.`goods_in` WHERE (`xmxy_stock`.`goods_in`.`delete_time` = 0) UNION SELECT `xmxy_stock`.`goods_out`.`id` AS `id`, `xmxy_stock`.`goods_out`.`shop_id` AS `shop_id`, CONVERT('out' USING utf8mb4) AS `type`, 0 AS `goods_id`, `xmxy_stock`.`goods_out`.`price` AS `price`, `xmxy_stock`.`goods_out`.`number` AS `number`, 0 AS `supplier_id`, `xmxy_stock`.`goods_out`.`customer_id` AS `customer_id`, `xmxy_stock`.`goods_out`.`user_name` AS `user_name`, `xmxy_stock`.`goods_out`.`create_time` AS `create_time`, `xmxy_stock`.`goods_out`.`update_time` AS `update_time`, `xmxy_stock`.`goods_out`.`delete_time` AS `A` FROM `xmxy_stock`.`goods_out` WHERE ( `xmxy_stock`.`goods_out`.`delete_time` = 0 )