Blog - comments

Thanks for sharing this information to us. If you are free, kindly visit this site for Oracle Develo...
anantha
Thank you very much..was struggling so hard !!! to run a simple sql statement!! u ve made my day!! ...
Sunny
Hi David, Your post is the best i have read so far on the subject. One remark : CASE WHEN "SUM(qp.ro...
Eyal Golombek
Hi Martin, No unfortunately I've no bug opened. SR were closed as resolved by customer. Increasing n...
Did you succeed in getting support to open a bug for this? Which agent versions are affected?Best re...
Martin Decker
Blog Gregory Steulet Mysqldump issue - SELECT command denied to user

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Mysqldump issue - SELECT command denied to user

During one of my last MySQL mission, I encountered a "security error" with mysqldump. The mission was about migrating a MySQL database from one box to another MySQL version and from Windows to Linux with different users. In order to do that, I used mysqldump. During the import of the data, I got the following warning:

 

ERROR 1449 (HY000) at line 1860: The user specified as a definer ('cdadmin'@'%') does not exist

 

Following the migration, I did the backup strategy. During the first mysql dump, I got the following error message:

 

mysql@mysql001: [mysqld2] mysqldump test
Running mysqldump for instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23, please wait...
MySQL dump of instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23 has failed.
mysqldump: Couldn't execute 'show table status like 'cdr\_app\_browser\_v'': SELECT command denied to user ''@'%' for column 'APPLICATION' in table 'cd_app_browser' (1143)

 

Msqldump exited without finishing to dump the mysql database. Obvisously, both errors had the same root issue.

My initial dump file contained a DEFINER clause and this was the reason of these error messages. As specified in the MySQL documentation, the DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINERcharacteristics. When you dump the data structure for views, triggers, and stored routines, you also dump the permissions related to such objects, with the DEFINER clause.

The MySQL Bug 50594 (http://bugs.mysql.com/bug.php?id=50594) is closely related to this issue. In order to remove the DEFINER clause from the dump file - which can be time consuming - some tools such as MySQL dump filter may help you. For more information regarding this tool, please have a look at this blog posting: http://datacharmer.blogspot.ch/2009/12/filtering-mysqldump-output.html

Rate this blog entry:
1

Grégory Steulet  is Chief Financial Officer (CFO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix/Linux, etc.). Grégory Steulet is "Oracle Certified Professional 10g", "MySQL Cluster 5.1 Certified", and "Avaloq Certified Professional 2.6". Prior to joining dbi services, Grégory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Grégory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services / Banking, Logistics, Pharma etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Wednesday, 30 July 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter