Error Code 1449 There Is No
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the
Mysqldump Without Definer
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions mysql change definer Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million
Mysql Change Trigger Definer
programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error 1449 in MySQL - Alternative Solutions? up vote 2 down vote favorite 1 Recently, I deleted a user account how to check definer in mysql in MySQL assigned to my former boss. Then, some database functions like deleting records from tables he made weren't working, giving the following error: #1449 - There is no '*username*'@'localhost' registered Now, I added a new user with the same name (and diff. password) and it works fine with no errors. But, is there way to resolve this without an placeholder user account? mysql share|improve this question asked Jul 19 '12 at 17:47 mysql definer Robert Dundon 9028 How did you delete his user? From what I remember, there are multiple ways to delete users in MySQL, and not all of them are equally effective. –Jason Swett Jul 19 '12 at 17:50 Straight from the "users" table in the mysql table via phpMyAdmin. Is there a better way? –Robert Dundon Jul 19 '12 at 18:23 1 If I remember correctly, I would usually use DROP USER. dev.mysql.com/doc/refman/5.0/en/drop-user.html –Jason Swett Jul 19 '12 at 19:05 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote Try replacing the DEFINER of the function First login to mysql as root@localhost Then, substitute root@localhost as the DEFINER UPDATE mysql.proc SET definer='root@localhost' WHERE definer = '*username*@localhost'; In fact, you can look at all DEFINERs like this: SELECT COUNT(1) DefinerCount,definer,type FROM mysql.proc GROUP BY definer,type; This will show you how many functions and procedures each user owns. If any other the reported DEFINERs no longer exist or are invalid, you can make root@localhost inherit them. Give it a Try !!! share|improve this answer answered Jul 19 '12 at 18:31 RolandoMySQLDBA 29.7k105495 The queries work, but no rows are affected. I was logged in as root (and obviously replaced username with the real username :) ). Any ideas? –Rob
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
Mysql View Definer
of this site About Us Learn more about Stack Overflow the company trigger command denied to user Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask
Grant All On *.* To 'root'@'%' Identified By 'password' With Grant Option;
Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; http://stackoverflow.com/questions/11566481/error-1449-in-mysql-alternative-solutions it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How do I change the DEFINER of a VIEW in Mysql? up vote 26 down vote favorite 12 When I run mysqldump, I get an error: mysqldump: Got error: 1449: The user specified http://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql as a definer ('root'@'foobar') does not exist when using LOCK TABLES This makes sense because foobar is a legacy machine that no longer exists. How do I change the definer of all my tables to 'root'@'localhost'? mysql mysqldump permissions share|improve this question edited Oct 25 '12 at 4:00 RolandoMySQLDBA 108k15138274 asked Dec 16 '11 at 2:56 kfmfe04 3291511 4 Do you have Views? Tables don't have definers I'm sure... Also see dba.stackexchange.com/q/4129/630 –gbn Dec 16 '11 at 6:18 1 @gbn +1 you were right - they were views - thx for the link, but I couldn't get it working quite right. However, I was able to modify the views in SQLyog so I could do the dump. –kfmfe04 Dec 16 '11 at 12:58 It work for me. {{ grant all on . to 'root'@'%' identified by 'password' with grant option; }} –Muhammad Azeem Sep 26 at 11:24 add a comment| 4 Answers 4 active oldest votes up vote 22 down vote accepted What I think is that the database you are trying to dump contains procedures/methods that were defined by a u
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss http://dba.stackexchange.com/questions/143225/error-1449-hy000-the-user-specified-as-a-definer-root-does-not-exist the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions http://www.pontikis.net/tip/?id=13 Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from mysql change others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist up vote -1 down vote favorite I am using cmd. error code 1449 CREATE OR REPLACE VIEW trips_flights_airports (trip_id,booking_reference,flight,flight2,dep_date,weight_restriction,size_restriction, type_restriction,travellr_id,cost,status,sender_id,flight_id1,flight_number1,dep_from1, arr_to1,dep_time1,arr_time1,operator1,mode1,flight_id2,flight_number2,dep_from2,arr_to2, dep_time2,arr_time2,operator2,mode2,airport_id1,city1,airport_code1,airport_name1,country1, airport_type1,airport_id2,city2,airport_code2,airport_name2,country2,airport_type2, airport_id3,city3,airport_code3,airport_name3,country3,airport_type3) AS SELECT * FROM trips_flights tf JOIN airports af ON tf.dep_from = af.airport_id JOIN airports at ON tf.arr_to = at.airport_id JOIN airports at2 ON tf.arr_to2 = at2.airport_id; When I am using above query cmd gives me following error: ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist But why and what will be the solution of this? mysql xp-cmdshell share|improve this question edited Jul 7 at 11:40 Marco 2,675619 asked Jul 7 at 11:13 user3164151 1 Is trips_flights also a view? –Michael - sqlbot Jul 8 at 0:05 yes trips_flights also a view. –user3164151 Jul 8 at 14:43 SHOW CREATE VIEW trips_flights; ... if you can. It seems likely that you will find the DEFINER of that view was root@% and someone has deleted that user. –Michael - sqlbot Jul 9 at 4:26 Perhaps root@localhost? root@% is leaving you vulnerable to the whole world; you should never use it! –Rick James Jul 12 at
1449 August 24th, 2013 14,023 Share: Tweet Trying to dump mysql databases (especially after mysql 5.1), it is possible to get the error mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES (another user may appear instead of 'root' and another address or IP instead of '%' ) This error is produced, because MySQL (latest 5.* versions) exports DEFINER with views or procedures, which might be not the same in production (usually a common mysql user) and developer machines (usually user root). These lines in mysqldump files start with 50013 like: /*!50013 DEFINER=`some_user`@`some_host` SQL SECURITY DEFINER */ As far as I know, there is not yet a flag for mysqldump to avoid DEFINER export. So, a solution is to replace DEFINER=`some_user`@`some_host` with DEFINER=CURRENT_USER The dump file can be imported afterwards to the developer machine. You may use sed, or an editor suitable to manage large files. I prefer the cross platform Tea editor or VIM or Cream. See also. Hope to find it helpful. Your comments are welcomed! Sign-up for our free email newsletter. Get updates when new tutorials and tips are published. You can unsubscribe anytime with a click. About the author pontikis.net Check out my backup solution bash-cloud-backup v2 10 years in production servers Free Open Source Your comments are welcomed! This site actively encourages commenting on any post. Comments are not pre-moderated, but this community does not tolerate direct or indirect attacks, name-calling or insults. Please, read terms of use and Comment Policy at privacy policy. More tips Terms Privacy Contact us Home Blog Tips Labs About