I came across a weird problem today. \u00a0Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time. \u00a0I needed to export the results of some query into a CSV file directly from MySQL. \u00a0I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:<\/p>\n
\r\nSELECT id, field1, field2, field3\r\nINTO OUTFILE '\/tmp\/data.csv'\r\nFIELDS TERMINATED BY ','\r\nOPTIONALLY ENCLOSED BY '"'\r\nESCAPED BY '\\\\'\r\nLINES TERMINATED BY '\\n'\r\nFROM data_table\r\nWHERE field1 = 0;\r\n<\/pre>\nI was quite surprised to find myself staring at:<\/p>\n
ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)<\/p><\/blockquote>\n
My database user definitely had full access to the database. \u00a0I definitely could see the results of the query before the redirect to the file. \u00a0And I definitely had enough permissions to create files in \/tmp<\/em> directory. \u00a0And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems. \u00a0 What’s wrong this time?<\/p>\n
A quick search around got me to this Stack Overflow question<\/a>. \u00a0Apparently, database user has to be given a FILE\u00a0privilege, which is global (not per-database). \u00a0 Here is what I did to solve the problem (you’ll need to use MySQL root user of course):<\/p>\n
\r\nUSE mysql;\r\nUPDATE user SET File_priv = 'Y' WHERE User = 'db_user';\r\nFLUSH PRIVILEGES;\r\n<\/pre>\nI think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n
I came across a weird problem today. \u00a0Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time. \u00a0I needed to export the results of some query into a CSV file directly from MySQL. \u00a0I prepared my query, made sure that I can … Continue reading MySQL export CSV into OUTFILE triggers “access denied” error<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_links_to":"","_links_to_target":""},"categories":[1,18,133,62],"tags":[1559,3066,200],"keyring_services":[],"class_list":["post-14795","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","category-sysadmin","category-technology","tag-databases","tag-mysql","tag-security"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t