{"id":14795,"date":"2011-04-27T10:01:06","date_gmt":"2011-04-27T08:01:06","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=14795"},"modified":"2011-04-27T10:01:06","modified_gmt":"2011-04-27T08:01:06","slug":"mysql-export-csv-into-outfile-triggers-access-denied-error","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2011\/04\/27\/mysql-export-csv-into-outfile-triggers-access-denied-error\/","title":{"rendered":"MySQL export CSV into OUTFILE triggers “access denied” error"},"content":{"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 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>\n
I 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>\n
I 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_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},"_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":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":12480,"url":"https:\/\/mamchenkov.net\/wordpress\/2010\/04\/21\/unit-tests-with-cakephp\/","url_meta":{"origin":14795,"position":0},"title":"Unit tests with CakePHP","author":"Leonid Mamchenkov","date":"April 21, 2010","format":false,"excerpt":"I've spent a large part of yesterday setting up the testing environment for a CakePHP project.\u00a0 As always, every time I do something that I have done before, I wanted to do it better, using all the experienced that was acquired previously.\u00a0 And this often leads to the discovery of\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":20769,"url":"https:\/\/mamchenkov.net\/wordpress\/2014\/02\/19\/the-worst-typo-in-mysql-query-ever\/","url_meta":{"origin":14795,"position":1},"title":"The worst typo in MySQL query ever","author":"Leonid Mamchenkov","date":"February 19, 2014","format":false,"excerpt":"Well, today I've learned something new. \u00a0Typing a query like this: [code lang=\"sql\" light=\"1\"] UPDATE `table` SET `field` = '1' WHERE `id` = '123' [\/code] I made a typo and entered the following: [code lang=\"sql\" light=\"1\"] UPDATE `table` SET `field` = '1' WHERE `id` - '123' [\/code] Yup. \u00a0A simple\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28414,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/02\/26\/top-10-mysql-8-0-features-for-dbas-ops\/","url_meta":{"origin":14795,"position":2},"title":"TOP 10 MySQL 8.0 features for DBAs & OPS","author":"Leonid Mamchenkov","date":"February 26, 2018","format":false,"excerpt":"Here's a list of the TOP 10 MySQL 8.0 features for DBAs and OPS, with some detailed explanations of what they are and links to more information.\u00a0 The features covered are: Temporary Tables Improvements Persistent global variables No more InnoDB System Tables Reclaim UNDO space from large transactions UTF8 performance\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":26926,"url":"https:\/\/mamchenkov.net\/wordpress\/2016\/11\/21\/monitoring-the-monitoring-keeping-zabbix-server-service-up\/","url_meta":{"origin":14795,"position":3},"title":"Monitoring the monitoring : keeping Zabbix server service up","author":"Leonid Mamchenkov","date":"November 21, 2016","format":false,"excerpt":"After our recent MySQL migrations, I started getting a weird issue - Zabbix server process was crashing periodically (several times a day). [code light=\"true\"] 8395:20161109:175408.023 [Z3005] query failed: [2013] Lost connection to MySQL server during query [begin;] 8395:20161109:175408.024 [Z3001] connection to database 'zabbix_database_name_here' failed: [2003] Can't connect to MySQL server\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":26925,"url":"https:\/\/mamchenkov.net\/wordpress\/2016\/11\/21\/fixing-innodb-error-log-file-ib_logfile0-is-of-different-size\/","url_meta":{"origin":14795,"position":4},"title":"Fixing “InnoDB: Error: log file .\/ib_logfile0 is of different size”","author":"Leonid Mamchenkov","date":"November 21, 2016","format":false,"excerpt":"For the last few days I've been moving MySQL databases around at work. \u00a0Being a bit in a rush and overconfident (I have backups!), \u00a0I was simply detaching the \/var\/lib\/mysql volume on one host (running Amazon AMI and MySQL) and attaching it to another host (running CentOS 7 and MariaDB).\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28562,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/05\/08\/database-flow-modern-self-hosted-web-interface-for-sql-and-graphql\/","url_meta":{"origin":14795,"position":5},"title":"Database Flow – modern, self-hosted web interface for SQL and GraphQL","author":"Leonid Mamchenkov","date":"May 8, 2018","format":false,"excerpt":"Database Flow is a modern, Open Source, self-hosted, web-based tool for working with SQL databases and GraphQL APIs.\u00a0 It supports a variety of the database engines: IBM DB2, Oracle, H2, PostgreSQL, MySQL, SQLite, Informix, and Microsoft SQL Server.\u00a0 It features an advanced SQL editor, query plan analyzer, GraphQL client, schema\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2018\/05\/database-flow-500x281.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/14795","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/comments?post=14795"}],"version-history":[{"count":0,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/14795\/revisions"}],"wp:attachment":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/media?parent=14795"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/categories?post=14795"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/tags?post=14795"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/keyring_services?post=14795"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}