List:General Discussion« Previous MessageNext Message »
From:Tanmay Pradhan Date:October 20 2010 2:14am
Subject:Re: mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
View as plain text  
Yes, root@localhost entry is still present in user table. Only
root@'%' is deleted. So it's not obvious to fail.

Hi yu.zou,
The root@localhost entry already had all privileges, except this entry
had empty password column.

user@localhost entry before GRANT
============================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host                       | User    | Password
            | Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost                  | root    |
            | Y           | Y           | Y           | Y           |
Y           | Y         | Y           | Y             | Y            |
Y         | Y          | Y               | Y          | Y          | Y
           | Y          | Y                     | Y                | Y
           | Y               | Y                | Y                | Y
             | Y                   | Y                  | Y
    | Y          | Y            |          |            |
|              |             0 |           0 |               0 |
             0 |


However, I still gave the following cmd.

mysql> GRANT select, lock tables ON *.* TO 'root'@'localhost'
IDENTIFIED BY 'password';
mysql> flush privileges;

user@localhost entry after GRANT
==========================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host                       | User    | Password
            | Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost                  | root    |
*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | Y           | Y
| Y           | Y           | Y           | Y         | Y           |
Y             | Y            | Y         | Y          | Y
 | Y          | Y          | Y            | Y          | Y
        | Y                | Y            | Y               | Y
        | Y                | Y              | Y                   | Y
                | Y                | Y          | Y            |
   |            |             |              |             0 |
  0 |               0 |                    0 |


Still mysqldump fails.....


Hi michael dykman,
I dropped all procedures, still mysqldump failed.

What else could be the issue?

Thanks for the help.

Regards,
Tanmay

On Wed, Oct 20, 2010 at 12:35 AM, Michael Dykman <mdykman@stripped> wrote:
> Of course you know you did not delete root@localhost, just root @ '%'
> which generally should not matter to mysqldump.
>
> What I suspect is the issue here is that the database you are trying
> to dump contains procedures/methods that were defined by a user while
> logged in as root@'%'.  If you recreate those procs as your database
> user, this should correct.
>
>  - michael dykman
>
>
> On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati
> <prajapatikc@stripped> wrote:
>> Hi Pradhan,
>>
>> Obviously, it should fail. Since you have deleted the root user which is
>> used by mysqldump for making connection to mysql server for taking backup
>>
>> Krishna
>> CGI.COM
>>
>>
>>
>> On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan <tanmay04@stripped>
> wrote:
>>
>>> Hi,
>>>
>>> I am using the following version of MySQL on my Mac OS X Server 10.5.8:
>>> *** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
>>> readline 5.1 ***
>>>
>>> In order to restrict root account login from localhost only, I did the
>>> following:
>>> mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
>>> mysql> FLUSH PRIVILEGES;
>>>
>>> After this,
>>> mysqldump failed with the following error:
>>> $ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE >
>>> abc.dump
>>> mysqldump: Got error: 1449: The user specified as a definer
>>> ('root'@'%') does not exist when using LOCK TABLES
>>>
>>> Even following cmd failed:
>>> $ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -pxxxx
>>> ABC_DATABASE > abc.dump
>>> mysqldump: Got error: 1045: Access denied for user 'user1'@'<IP
>>> Address>' (using password: YES) when using LOCK TABLES
>>>
>>> Can anybody advise as how to make mysqldump work while restricting
>>> root login access from localhost only?
>>>
>>> Thanks for any help.
>>>
>>> Regards,
>>> Tanmay
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>
>
>
> --
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.
>
Thread
mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan19 Oct
  • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESKrishna Chandra Prajapati19 Oct
    • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESMichael Dykman19 Oct
      • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan20 Oct
        • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan21 Oct
    • Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%')does not exist when using LOCK TABLESMySQL)20 Oct