Re: mysqldump: Got error: 1449: The user specified as a definer ('root''%') does not exist when using LOCK TABLES

From: Tanmay Pradhan (tanmay04gmail.com)
Date: Wed Oct 20 2010 - 19:43:09 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Appreciate any help on this.
Any alternative of mysqldump is also fine with me to take backup while
restricting root login access from localhost only.

Thanks in advance.

Regards,
Tanmay

On Wed, Oct 20, 2010 at 10:14 AM, Tanmay Pradhan <tanmay04gmail.com> wrote:
> Yes, rootlocalhost entry is still present in user table. Only
> root'%' is deleted. So it's not obvious to fail.
>
> Hi yu.zou,
> The rootlocalhost entry already had all privileges, except this entry
> had empty password column.
>
> userlocalhost 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;
>
> userlocalhost 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 <mdykmangmail.com> wrote:
>> Of course you know you did not delete rootlocalhost, 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
>> <prajapatikcgmail.com> 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 <tanmay04gmail.com> 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=prajapatikcgmail.com
>>>>
>>>>
>>>
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdykmangmail.com
>>
>>  May the Source be with you.
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]