blocks|key|594540|text|最初创建SQL视图或过程的用户已被删除。如果您重新创建了该用户，它应该会解决您的错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594541|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The user who originally created the SQL view or procedure has been deleted. If you recreate that user, it should address your error.

blocks|key|594672|text|如果用户存在，则：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594673|mysql>+flush+privileges;|code-block|syntax|javascript|594674|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If the user exists, then:

<pre><code>mysql&gt; flush privileges;
</code></pre>

blocks|key|378863|text|按如下方式创建已删除的用户：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|378864|mysql>+create+user+'web2vi';|code-block|syntax|javascript|378865|或|378866|mysql>+create+user+'web2vi'@'%25';|378867|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Create the deleted user like this : 

<pre><code>mysql&gt; create user 'web2vi';
</code></pre>

or

<pre><code>mysql&gt; create user 'web2vi'@'%';
</code></pre>

blocks|key|595316|text|通过运行以下注释修复了此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595317|grant+all+on+*.*+to+'web2vi'@'%25'+identified+by+'root'+with+grant+option;
FLUSH+PRIVILEGES;|code-block|syntax|javascript|595318|如果你得到的是some_other而不是web2vi，那么你必须相应地更改名称。|offset|length|style|CODE|595319|entityMap^0|0|0|7|A|K|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Fixed by running this following comments. 

<pre><code>grant all on *.* to 'web2vi'@'%' identified by 'root' with grant option;
FLUSH PRIVILEGES;
</code></pre>

if you are getting <code>some_other</code> instead of <code>web2vi</code> then you have to change the name accordingly.

blocks|key|594973|text|对于未来的googlers:我在尝试更新数据库中不包含视图的表时收到了类似的消息。经过一些挖掘，我发现我已经在该表上导入了触发器，而这些都是由不存在的用户定义的东西。去掉触发器就解决了问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594974|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For future googlers: I got a similar message trying to update a table in a database that contained no views. After some digging, it turned out I had imported triggers on that table, and those were the things defined by the non-existant user. Dropping the triggers solved the problem.

blocks|key|595384|text|grant+all+on+*.*+to+'username'@'%25'+identified+by+'password'+with+grant+option;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|595385|示例：|unstyled|595386|grant+all+on+*.*+to+'web2vi'@'%25'+identified+by+'password'+with+grant+option;|595387|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>grant all on *.* to 'username'@'%' identified by 'password' with grant option;
</code></pre>

example:

<pre><code>grant all on *.* to 'web2vi'@'%' identified by 'password' with grant option;
</code></pre>

blocks|key|379144|text|快速修复以解决问题并转储文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379145|mysqldump+--single-transaction+-u+root+-p+xyz_live_db+>+xyz_live_db_bkup110116.sql|code-block|syntax|javascript|379146|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

quick fix to work around and dump the file:

<pre><code>mysqldump --single-transaction -u root -p xyz_live_db &gt; xyz_live_db_bkup110116.sql
</code></pre>

blocks|key|594451|text|用户'web2vi‘在您的mysql服务器上不存在。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594452|请参阅http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user|offset|length|594453|如果该用户确实存在，请检查该用户可以从哪些服务器访问，尽管我可能会认为这是一个不同的错误(例如，您可能有web2vi@localhost，但您访问数据库的方式为web2vi@%25+(At+At))|594454|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user^0|0|3|2F|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

The user 'web2vi' does not exist on your mysql server. 

See <a href="http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user">http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user</a>

If that user does exist, check what servers it can access from, although I would have thought that would be a different error (EG you might have web2vi@localhost, but you are accessing the db as web2vi@% (At anything)

blocks|key|378819|text|我在root用户ans上也遇到了同样的问题，当我替换了|type|unstyled|depth|inlineStyleRanges|entityRanges|data|378820|root@%25|code-block|syntax|javascript|378821|通过|378822|root@localhost|378823|因此，如果允许用户'web2vi‘从'localhost’进行连接，您可以尝试：|378824|web2vi@localhost|378825|我远程连接到数据库了。|378826|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I had the same problem with root user ans it worked for me when I replaced 

<pre><code>root@%
</code></pre>

by

<pre><code>root@localhost
</code></pre>

So, if the user 'web2vi' is allowed to connect from 'localhost', you can try:

<pre><code>web2vi@localhost
</code></pre>

I'm connected remotely to the database.

blocks|key|378579|text|几分钟前我也遇到过同样的问题，在从mysql.user表中删除一个未使用的用户后，我遇到了这个问题，但是执行alter+view修复了这个问题，下面是一个非常简单的命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|378580|SELECT+CONCAT("ALTER+DEFINER=`youruser`@`host`+VIEW+",
table_name,"+AS+",+view_definition,";")+FROM+
information_schema.views+WHERE+table_schema='databasename'|code-block|syntax|javascript|378581|将其与mysql命令行混合使用(假设*nix，不熟悉windows)：|378582|>+echo+above_query+%7C+mysql+-uuser+-p+>+alterView.sql
>+mysql+-uuser+-ppass+databasename+<+alterView.sql|378583|注意:该命令会在文件上生成额外的SELECT+CONCAT，如果您不删除它，则会使mysql+-uuser+-ppass+databasename+<+alterView.sql失败。|offset|length|style|CODE|378584|来源：https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views|378585|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views^0|0|0|0|0|15|1C|0|3|21|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@$M|15|N|16|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|17|8|@]|9|@$M|18|N|19|1|1A]]|A|$]]|$1|S|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

I had your very same problem minutes ago, I ran into this issue after deleting an unused user from mysql.user table, but doing an alter view fixed it, here is a handy command that makes it very simple:

<pre><code>SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name," AS ", view_definition,";") FROM 
information_schema.views WHERE table_schema='databasename'
</code></pre>

Mix this with the mysql command line (assuming *nix, not familiar with windows):

<pre><code>&gt; echo above_query | mysql -uuser -p &gt; alterView.sql
&gt; mysql -uuser -ppass databasename &lt; alterView.sql
</code></pre>

Note: the command generates and extra SELECT CONCAT on the file, making <code>mysql -uuser -ppass databasename &lt; alterView.sql</code> fail if you don't remove it.

Source: <a href="https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views">https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views</a>

blocks|key|594792|text|我的5美分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594793|当我尝试从视图中进行选择时，也出现了相同的错误。|594794|但是，问题似乎是该视图是从从不同服务器备份中还原的另一个视图中选择的。|594795|事实上，是的，用户是无效的，但从第一眼看起来并不明显。|594796|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

My 5 cents.

I had same error while I tried to select from a view.

However problem appears to be that this view, selected from another view that was restored from backup from different server.

and in fact, YES, user was invalid, but was not obvious where to from the first look.

blocks|key|595055|text|尝试将您的过程设置为SECURITY+INVOKER|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|595056|Mysql默认将过程安全性设置为"DEFINER“(创建者)..您必须将安全性设置为“调用者”。|595057|entityMap^0|A|G|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Try to set your procedure as 
<code>SECURITY INVOKER</code> 

Mysql default sets procedures security as "DEFINER" (CREATOR OF).. you must set the security to the "invoker".

blocks|key|594731|text|您的视图"view_quotes“可能已从"web2vi”是有效用户的其他数据库复制到"web2vi“不是有效用户的数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|594732|将"web2vi“用户添加到数据库或更改视图(正常情况下，删除DEFINER='web2vi'@'%25‘部分并执行脚本即可完成此操作)|594733|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Your view, "view_quotes" may have been copied from a different database where "web2vi" is a valid user into a database where "web2vi" is not a valid user. 
Either add the "web2vi" user to the database or alter the view (normally removing the DEFINER='web2vi'@'%' part and executing the script will do the trick)

blocks|key|595008|text|在我的例子中，该表有一个触发器，其中有一个不存在的定义用户。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595009|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In my case, the table had a trigger with a DEFINER user that didn't exist.

blocks|key|595032|text|您可以尝试这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595033|$+mysql+-u+root+-p+
>+grant+all+privileges+on+*.*+to+`root`@`%25`+identified+by+'password';+
>+flush+privileges;|code-block|syntax|javascript|595034|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this:

<pre><code>$ mysql -u root -p 
&gt; grant all privileges on *.* to `root`@`%` identified by 'password'; 
&gt; flush privileges;
</code></pre>

blocks|key|378461|text|进入编辑例程部分，在底部，将Security+Type从Definer更改为Invoker。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|378462|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Go into the edit routine section and and at the bottom, change Security Type from Definer to Invoker.

blocks|key|378896|text|如果这是一个存储过程，则可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|378897|UPDATE+`mysql`.`proc`+SET+definer+=+'YournewDefiner'+WHERE+definer='OldDefinerShownBefore'|code-block|syntax|javascript|378898|但这样做并不可取。|378899|对我来说，更好的解决方案是创建定义器：|378900|create+user+'myuser'+identified+by+'mypass';
grant+all+on+`mytable`.*+to+'myuser'+identified+by+'mypass';|378901|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If this is a stored procedure, you can do:

<pre><code>UPDATE `mysql`.`proc` SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'
</code></pre>

But this is not advised.

For me, better solution is to create the definer:

<pre><code>create user 'myuser' identified by 'mypass';
grant all on `mytable`.* to 'myuser' identified by 'mypass';
</code></pre>

blocks|key|379121|text|当mysql.proc为空，但系统总是注意到table_name不存在时，您只需在mysql命令行中以根用户身份输入：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379122|CHECK+TABLE+`database`.`table_name`+QUICK+FAST+MEDIUM+CHANGED;
flush+privileges;|code-block|syntax|javascript|379123|完毕！|379124|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

when mysql.proc is empty, but system always notice "user@192.168.%" for table_name no exist,you just root in mysql command line and type:

<pre><code>CHECK TABLE `database`.`table_name` QUICK FAST MEDIUM CHANGED;
flush privileges;
</code></pre>

over!

blocks|key|595246|text|我来这里是为了同样的问题，我在我的代码中找不到某个特定用户正在执行该操作的任何地方。显然，它来自触发器，该触发器使用的用户已被长期删除(db是从旧版本恢复的)，因此，如果您像我一样感到困惑，请查看您的db事件/触发器/例程。希望这能帮助到一些人。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595247|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

i came here for the same problem, i couldn't find anywhere in my code where a certain user was making the action.
apparently it was from a trigger that was using a user which was long deleted (db was restored from an older version)
so in case you are puzzled as i were, take a look at your db events/triggers/routines.
hope this will help someone.

blocks|key|595626|text|在我的例子中，删除所有视图就解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595627|DROP+VIEW+view_name;|code-block|syntax|javascript|595628|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In my case, deleting all the views solved the issue.
<pre><code>DROP VIEW view_name;
</code></pre>

blocks|key|379706|text|我尝试过上述方法，但在创建视图时感觉像是重复操作。我在更新导入数据库的视图时遇到了同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379707|您只需在本地创建具有create权限的用户，就可以简单地克服这个问题。|379708|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I have tried the above methods, but feels like repetitive action when creating the view. I got the same issue while updating the views of the imported database.
You can simply overcome the issue in LOCAL by simply creating the User with the create privilege.

blocks|key|595748|text|您可以将特定数据库的定义者更改为现有用户：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595749|UPDATE+mysql.proc+SET+definer+=+'existing_user@localhost'+WHERE+db+=+'database_name';|code-block|syntax|javascript|595750|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can change the definer for a specific database to an existing user:
<pre><code>UPDATE mysql.proc SET definer = 'existing_user@localhost' WHERE db = 'database_name';
</code></pre>

blocks|key|379793|text|对我来说，从定义器中删除''确实起到了作用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|379794|DEFINER+=+user@localhost|379795|entityMap^0|C|2|0|0|O|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

For me, removing the <code>''</code> from the DEFINER did the trick.
 
<code>DEFINER = user@localhost</code>

blocks|key|379088|text|数据库用户似乎也是区分大小写的，所以当我有一个root'@'%25+user时，我没有ROOT'@'%25+user。我通过工作台将用户更改为大写，问题就解决了！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379089|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The database user also seems to be case-sensitive, so while I had a root'@'% user I didn't have a ROOT'@'% user. I changed the user to be uppercase via workbench and the problem was resolved!

blocks|key|379317|text|尝试以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379318|mysqldump+--routines+--single-transaction+-u+root+-proot+portalv3+>+c:\portal.sql|code-block|syntax|javascript|379319|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try the following:

<pre><code>mysqldump --routines --single-transaction -u root -proot portalv3 &gt; c:\portal.sql
</code></pre>

blocks|key|595463|text|此外，要更改触发器的定义器(ALTER不起作用)，您可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595464|为每个触发器生成DROP和CREATE命令：|595465|SELECT+CONCAT("DROP+TRIGGER+",+trigger_name,+";",+"+CREATE+TRIGGER+",+TRIGGER_NAME,+"+AFTER+",+EVENT_MANIPULATION,+"+ON+",+EVENT_OBJECT_SCHEMA,+".",+EVENT_OBJECT_TABLE,+"+FOR+EACH+ROW+",+ACTION_STATEMENT,+";")+AS+sqlCommand+FROM+information_schema.triggers+WHERE+EVENT_OBJECT_SCHEMA+=+"yourdatabase";|code-block|syntax|javascript|595466|在foreach中执行它。当我将生产数据库带到我的开发机器上，使用foreach遍历所有命令并自动重新创建触发器时，我会在我的应用程序中使用它。这为我提供了自动化的选项。|595467|PHP/Laravel中的示例：|595468|++++$this->info('DROP+and+CREATE+TRIGGERS');
++++$pdo+=+DB::connection()->getPdo();
++++$sql+=+'SELECT+CONCAT("DROP+TRIGGER+",+trigger_name,+";",+"+CREATE+TRIGGER+",+TRIGGER_NAME,+"+AFTER+",+EVENT_MANIPULATION,+"+ON+",+EVENT_OBJECT_SCHEMA,+".",+EVENT_OBJECT_TABLE,+"+FOR+EACH+ROW+",+ACTION_STATEMENT,+";")+AS+sqlCommand+FROM+information_schema.triggers+WHERE+EVENT_OBJECT_SCHEMA+=+"mydatabase";';
++++$stmt+=+$pdo->prepare($sql,+[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY+=>+true]);
++++$stmt->execute();
++++$result+=+$stmt->fetchAll(PDO::FETCH_ASSOC);
++++$stmt->closeCursor();

++++foreach($result+as+$rs){
++++++++$pdo+=+DB::unprepared($rs['sqlCommand']);
++++++++break;
++++}|595469|提示:由于mysql缓冲区查询的问题，我不得不用pdo来做，描述了here。|offset|length|595470|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries.^0|0|0|0|0|0|0|X|4|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|16|8|@]|9|@$Q|17|R|18|1|19]]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

As addition, to change definer for TRIGGERS (ALTER does not work), you can do it like this:

Generate a DROP and a CREATE command for every Trigger:

<pre><code>SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";
</code></pre>

Execute it in a foreach. I use this in my app when I take the production database to my development machine and go it with a foreach over all commands and recreate the triggers automatically. This gives me the option to automate it.

Example in PHP/Laravel:

<pre><code> $this-&gt;info('DROP and CREATE TRIGGERS');
 $pdo = DB::connection()-&gt;getPdo();
 $sql = 'SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "mydatabase";';
 $stmt = $pdo-&gt;prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =&gt; true]);
 $stmt-&gt;execute();
 $result = $stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
 $stmt-&gt;closeCursor();

 foreach($result as $rs){
 $pdo = DB::unprepared($rs['sqlCommand']);
 break;
 }
</code></pre>

Hint: I have to do it with pdo because of the mysql buffer query problem, described <a href="https://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries.">here</a>

blocks|key|379512|text|试试这个，这是个简单的解决方案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|379513|mysql+-u+root+-p
SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|379514|更多信息请访问https://stackoverflow.com/a/42183702/5407056|offset|length|379515|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/42183702/5407056^0|0|0|7|18|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Try this
This is simple solution

<pre><code>mysql -u root -p
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>

more at <a href="https://stackoverflow.com/a/42183702/5407056">https://stackoverflow.com/a/42183702/5407056</a>

blocks|key|379363|text|您可以创建名为web2vi的用户并授予所有权限|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|379364|entityMap^0|7|6|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

you can create user with name <code>web2vi</code> and grant all privilage

blocks|key|595570|text|//根据需要更新所有或特定程序，使用现有用户(my+case+-root)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|595571|1)+UPDATE+mysql.proc+p+SET+definer+=+'root@%25'+WHERE+1=1+LIMIT+1000;+(limit子句是由于各个mysql版本在更新时会报错，not+restricted+with+limit或no+where+condition使用)|offset|length|style|CODE|595572|2)+FLUSH+PRIVILEGES;+//+or+restart+the+server|595573|entityMap^0|0|3|1S|0|3|16|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

// update all or particular procedures to your wanted, use existing user (my case -root)

1) 
<code>UPDATE mysql.proc p SET definer = 'root@%' WHERE 1=1 LIMIT 1000;</code>
 (limit clause is for the reason that various mysql versions complains when is updated not restricted with limit or no where condition is used)

2) 
<code>FLUSH PRIVILEGES; // or restart the server</code>

When I run the following query I get an error:

<pre><code>SELECT
 `a`.`sl_id` AS `sl_id`,
 `a`.`quote_id` AS `quote_id`,
 `a`.`sl_date` AS `sl_date`,
 `a`.`sl_type` AS `sl_type`,
 `a`.`sl_status` AS `sl_status`,
 `b`.`client_id` AS `client_id`,
 `b`.`business` AS `business`,
 `b`.`affaire_type` AS `affaire_type`,
 `b`.`quotation_date` AS `quotation_date`,
 `b`.`total_sale_price_with_tax` AS `total_sale_price_with_tax`,
 `b`.`STATUS` AS `status`,
 `b`.`customer_name` AS `customer_name`
FROM `tbl_supplier_list` `a`
 LEFT JOIN `view_quotes` `b`
 ON (`b`.`quote_id` = `a`.`quote_id`)
LIMIT 0, 30
</code></pre>

The error message is:

<pre><code>#1449 - The user specified as a definer ('web2vi'@'%') does not exist
</code></pre>

Why am I getting that error? How do I fix it?

MySQL error 1449: The user specified as a definer does not exist

数据库

服务器

当我运行以下查询时，我得到一个错误：SELECT  `a`.`sl_id`                     AS `sl_id`,  `a`.`quote_id`                  AS `quote_id`,  `a`.`sl_date`                   AS `sl_date`,  `a`.`sl_type`                   AS

问MySQL错误1449:指定为定义者的用户不存在
EN

回答 29

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL错误1449:指定为定义者的用户不存在EN

回答 29

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL错误1449:指定为定义者的用户不存在
EN