Re: INTO OUTFILE ERROR...
On 9/19/2014 10:59 AM, Don Wieland wrote: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Please note: cpanel can create linux users. You need to create and manage the MySQL users. There is no relationship between the linux users and the MySQL users. -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
Am 22.09.2014 um 12:44 schrieb william drescher: On 9/19/2014 10:59 AM, Don Wieland wrote: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Please note: cpanel can create linux users. You need to create and manage the MySQL users. There is no relationship between the linux users and the MySQL users irreleveant, he just don't realize that file permissions are *not* in the scope of adding them to the DB with ALL PRIVILEGES why in the world did i post the link to the answer last friday if people are too lazy to read GRANT FILE ON *.* TO 'asdfsdf'@'localhost'; is a global setting because you access *the hosts FILESYSTEM* with it http://lmgtfy.com/?q=mysql+permissions+into+outfile http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold signature.asc Description: OpenPGP digital signature
Re: INTO OUTFILE ERROR...
Check permission of user 'teal1dwd_teal' DK Sent from Phone On 19-Sep-2014, at 8:13 pm, Don Wieland d...@pointmade.net wrote: Hi gang, Trying to generate a CSV file using mySQL and I keep getting this error below. The target folder has full permissions to write (777). I am not certain why this is happening. Any answers? Thanks! Database_Class error: DATABASE_ERROR: Access denied for user 'teal1dwd_teal'@'localhost' (using password: YES) IN [SELECT u.user_id, u.first_name AS u_first_name, u.last_name AS u_last_name, c.client_id AS c_client_id, c.first_name AS c_first_name, c.middle_name AS c_middle_name, c.last_name AS c_last_name, c.address AS c_address, c.city AS c_city, c.state AS c_state, c.zip AS c_zip, c.dob AS dob_ymd, c.phone_home AS c_phone_home, c.phone_cell AS c_phone_cell, c.phone_work AS c_phone_work, c.email AS c_email, c.other_contacts AS c_other_contacts, count(*) as apt_qty FROM tl_appt apt JOIN tl_clients c on c.client_id = apt.client_id JOIN tl_rooms r on r.room_id = apt.room_id JOIN tl_users u on u.user_id = apt.user_id WHERE apt.time_start between '1388552400' and '1420088399' and r.location_id = '1' GROUP BY u.user_id, c.client_id having count(*) 1 ORDER BY u.first_name, u.last_name, c.last_name, c.first_name INTO OUTFILE '/tmp/1_2031305738.csv' FIELDS TERMINATED BY ',' ENCLOSED BY ''] Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
Am 19.09.2014 um 16:43 schrieb Don Wieland: Trying to generate a CSV file using mySQL and I keep getting this error below. The target folder has full permissions to write (777). I am not certain why this is happening. Any answers? Thanks! the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Database_Class error: DATABASE_ERROR: Access denied for user 'teal1dwd_teal'@'localhost' (using password: YES) IN [SELECT u.user_id, u.first_name AS u_first_name, u.last_name AS u_last_name, c.client_id AS c_client_id, c.first_name AS c_first_name, c.middle_name AS c_middle_name, c.last_name AS c_last_name, c.address AS c_address, c.city AS c_city, c.state AS c_state, c.zip AS c_zip, c.dob AS dob_ymd, c.phone_home AS c_phone_home, c.phone_cell AS c_phone_cell, c.phone_work AS c_phone_work, c.email AS c_email, c.other_contacts AS c_other_contacts, count(*) as apt_qty FROM tl_appt apt JOIN tl_clients c on c.client_id = apt.client_id JOIN tl_rooms r on r.room_id = apt.room_id JOIN tl_users u on u.user_id = apt.user_id WHERE apt.time_start between '1388552400' and '1420088399' and r.location_id = '1' GROUP BY u.user_id, c.client_id having count(*) 1 ORDER BY u.first_name, u.last_name, c.last_name, c.first_name INTO OUTFILE '/tmp/1_2031305738.csv' FIELDS TERMINATED BY ',' ENCLOSED BY ''] signature.asc Description: OpenPGP digital signature
Re: INTO OUTFILE ERROR...
On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band
Re: INTO OUTFILE ERROR...
Am 19.09.2014 um 16:59 schrieb Don Wieland: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. into outfile is *not* a db-specific permission http://lmgtfy.com/?q=mysql+permissions+into+outfile http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold signature.asc Description: OpenPGP digital signature
Re: INTO OUTFILE ERROR...
Thanks Reindl, Yes I stubbled upon this page last night. I try running that query: grant all privileges on teal1dwd_teal.* to 'teal1dwd_teal'@'localhost' identified by 'my_user_pw'; I get this error: #1044 - Access denied for user 'teal1dwd'@'localhost' to database 'teal1dwd_teal' Do I need to perform this query in a different place? Sigh… Don On Sep 19, 2014, at 8:03 AM, Reindl Harald wrote: into outfile is *not* a db-specific permission http://lmgtfy.com/?q=mysql+permissions+into+outfile http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band
Re: INTO OUTFILE ERROR...
Hi there, Le 19/09/2014 17:19, Don Wieland a écrit : grant all privileges on teal1dwd_teal.* to 'teal1dwd_teal'@'localhost' identified by 'my_user_pw'; I get this error: #1044 - Access denied for user 'teal1dwd'@'localhost' to database 'teal1dwd_teal' I think you are trying to access the DB with the 'teal1dwd' user, but your grant applies to 'teal1dwd_teal'. Hope this helps ;). Regards, Christophe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
Le 19/09/2014 18:00, Christophe a écrit : Hi there, Le 19/09/2014 17:19, Don Wieland a écrit : grant all privileges on teal1dwd_teal.* to 'teal1dwd_teal'@'localhost' identified by 'my_user_pw'; I get this error: #1044 - Access denied for user 'teal1dwd'@'localhost' to database 'teal1dwd_teal' I think you are trying to access the DB with the 'teal1dwd' user, but your grant applies to 'teal1dwd_teal'. I forgot to tell, that GRANT queries need to be performed by a user which has the GRANT privilege. Don't you have root access to MySQL instance ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
On Sep 19, 2014, at 9:11 AM, Christophe wrote: Don't you have root access to MySQL instance ? I guess not, I am using a managed server VPSLatch. I chatted with them and they are going to GRANT the permissions I need…at least that is what the tech said. I waiting with baited breath ;oP Thanks! Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band
Re: INTO OUTFILE ERROR...
Maybe you need to grant access from localhost to the user? On Fri, September 19, 2014 16:59, Don Wieland wrote: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 'Into outfile' doesn't include the column names. How can it be done?
On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp
Re: 'Into outfile' doesn't include the column names. How can it be done?
Jake Peavy [EMAIL PROTECTED] wrote on 03/14/2006 01:52:28 PM: On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp Nope, no suggestions. You are mixing purposes (not to mention data types) when you add column names to the output of INTO OUTFILE. Have you looked at using the CSV storage engine or mysqldump as alternatives? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 'Into outfile' doesn't include the column names. How can it be done?
On 3/14/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jake Peavy [EMAIL PROTECTED] wrote on 03/14/2006 01:52:28 PM: On 3/10/06, Jake Peavy [EMAIL PROTECTED] wrote: On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp Nobody has any ideas at all? (sheds solitary tear) -jp Nope, no suggestions. You are mixing purposes (not to mention data types) when you add column names to the output of INTO OUTFILE. Have you looked at using the CSV storage engine or mysqldump as alternatives? Yes, but based on the docs it would appear that it doesn't produce column names either (presumably because the table definition is stored in the .frm): mysql CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--++ | i| c | +--++ |1 | record one | |2 | record two | +--++ 2 rows in set (0.00 sec) If you examine the test.CSV file in the database directory after executing the preceding statements, its contents look like this: 1,record one 2,record two I didn't actually try this, however, because my version hasn't been built to include the CSV engine. I could, of course, rebuild, but it didn't look like it would do what I wanted anyway. I think this (INTO OUTFILE w/column names) would be used infrequently, but it would be handy to send a small subset of data to someone so they could pivot it anyway they like in Excel. Appreciate the info though, if anyone else has any ideas How would one transform a single column of rows into a single row of columns? Seems like part of a crosstab query... -jp
Re: 'Into outfile' doesn't include the column names. How can it be done?
On 3/7/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor So, to all the SQL wizards out there... How would one perform this same operation, but using the INFORMATION_SCHEMA virtual db to provide the column headings in the first row rather having to hand type them as shown above? Bonus marks for beginning the line with a # My attempt stalled as soon as I got one column of data in the result set with a row for each column of the target table. SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'testtable' Ideas? TIA, -jp
Re: 'Into outfile' doesn't include the column names. How can it be done?
Ariel Sánchez Mora [EMAIL PROTECTED] wrote on 03/06/2006 03:12:20 PM: When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel As far as I am aware, INTO OUTFILE does not have a setting where you can tag the first line with the column names. You may need to use something else like the CSV storage engine or mysqldump to get the results you want. Or, as a last resort, you may have to create your own table export routine. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 'Into outfile' doesn't include the column names. How can it be done?
At 14:12 -0600 3/6/06, Ariel Sánchez Mora wrote: When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. There is no option. INTO OUTFILE dumps only data, not metadata. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Into outfile' doesn't include the column names. How can it be done?
Maybe, if you make a file with the SELECT content and call them from the OS shell, like this: example of the content of the my_file SELECT table1.a, tabela1.b, table1.c FROM mydatabase.table; After, call this file from the OS shell: shellmysql -A mydatabase my_file This will result on the screen. You can direct the result to a file: shellmysql -A mydatabase my_file my_table.sql If you need the tabs, do with -t on the options: shellmysql -A -t mydatabase my_file my_table.sql -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Monday, March 06, 2006 5:12 PM To: mysql@lists.mysql.com Subject: 'Into outfile' doesn't include the column names. How can it be done? When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Into outfile' doesn't include the column names. How can it be done?
Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor - Original Message - From: Ariel Sánchez Mora [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 06, 2006 8:12 PM Subject: 'Into outfile' doesn't include the column names. How can it be done? When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: into outfile
Scott, At 11:58 AM 8/23/2004, you wrote: SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. select * into outfile 'p:\win2ktmp\mytest.txt' from mytable where order by Make sure the output directory exists and make sure the into Outfile filename appears before the from statement. BTW, you have .html as a file extension. MySQL only outputs in text (Into OutFile) or binary (Into DumpFile) files, not HTML. If you want HTML then you need to format the thml table it yourself in whatever language you prefer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: into outfile
If you wanted html output you could do this mysql -uuser -p -H -equery_to_run webFile.html ... The -H switch produces HTML output. -Original Message- From: mos To: [EMAIL PROTECTED] Sent: 8/23/04 12:11 PM Subject: Re: into outfile Scott, At 11:58 AM 8/23/2004, you wrote: SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. select * into outfile 'p:\win2ktmp\mytest.txt' from mytable where order by Make sure the output directory exists and make sure the into Outfile filename appears before the from statement. BTW, you have .html as a file extension. MySQL only outputs in text (Into OutFile) or binary (Into DumpFile) files, not HTML. If you want HTML then you need to format the thml table it yourself in whatever language you prefer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: into outfile
At 12:59 PM 8/23/2004, you wrote: If you wanted html output you could do this mysql -uuser -p -H -equery_to_run webFile.html ... The -H switch produces HTML output. It would be real nice if they added this feature to the Select statement. :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
There's got ot be something else wrong. I've got the exact same problem and I have done a chmod 666 on the directory I am trying to write to with no luck. I am running v4.0.15 /T on 6/1/04 7:06, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Would there be any obvious reasons for its inability to access the desktop? I have used that desktop directory many times before in UNIX. Check permissions on that dir. It must be writeable by the user you run mysqld as ('mysql' by default). on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
At 5:18 -0500 6/2/04, [EMAIL PROTECTED] wrote: There's got ot be something else wrong. I've got the exact same problem and I have done a chmod 666 on the directory I am trying to write to with no luck. I am running v4.0.15 Mode is not enough, you must consider ownership. Remember, *you* are not trying to write the file. The MySQL server (mysqld) is trying to write the file, and it might not be running under your user ID. It's probably running from the mysql account. /T on 6/1/04 7:06, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Would there be any obvious reasons for its inability to access the desktop? I have used that desktop directory many times before in UNIX. Check permissions on that dir. It must be writeable by the user you run mysqld as ('mysql' by default). on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
Paul DuBois wrote: At 5:18 -0500 6/2/04, [EMAIL PROTECTED] wrote: There's got to be something else wrong. I've got the exact same problem and I have done a chmod 666 on the directory I am trying to write to with no luck. I am running v4.0.15 Mode is not enough, you must consider ownership. Huh? I don't understand what you are saying here. Remember, *you* are not trying to write the file. The MySQL server (mysqld) is trying to write the file, and it might not be running under your user ID. It's probably running from the mysql account. Right. The server, running as user mysql, writes the file, so the mysql user must be able to write a file in the target directory. This does not require ownership, however, only the correct set of permissions. To do anything in the destination directory, the mysql user must have execute permission on every part of the path. In the case of the original poster, where we want to write a file in /Users/johnmistler/Desktop, mysql must have execute permission on /, /Users, /Users/johnmistler, and /Users/johnmistler/Desktop just to be able to cd (change directory) to /Users/johnmistler/Desktop. Once there, it must have write permission to create a new file. Usually, the path to one's home directory is executable by everyone by default, so giving write permission to the subdirectory is enough. If, however, the targetdir is more than one level below home, you'll probably have to add execute permission to the intervening directories as well. This is why `chmod 666 targetdir` does not work. It gives everyone permission to read and write targetdir, but it removes execute permission. Without execute, you cannot cd into targetdir, rendering write permission useless. Instead, you should do `chmod 733 targetdir`. This gives execute and write permission to everyone, but reserves read permission to targetdir's owner. Thus, mysql will be able to cd to targetdir and write a file (as will anyone else), but only targetdir's owner will be able to list the contents of targetdir (the point of read permission), which adds a teeny bit of security. As an example, if targetdir is the /path/to/home/mysqlstuff/proj1/outfiles directory, you will probably need to chmod 755 mysqlstuff chmod 755 mysqlstuff/proj1 chmod 733 mysqlstuff/proj1/outfiles If you have the power to set targetdir's group to mysql, (assuming mysql runs as user=mysql, group=mysql), you can drop the world permissions in favor of just the group permissions. sudo chgrp mysql targetdir chmod 730 targetdir That would be an improvement in terms of security, since it further limits access to targetdir. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
Would there be any obvious reasons for its inability to access the desktop? I have used that desktop directory many times before in UNIX. Thanks, John on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
John Mistler [EMAIL PROTECTED] wrote: Would there be any obvious reasons for its inability to access the desktop? I have used that desktop directory many times before in UNIX. Check permissions on that dir. It must be writeable by the user you run mysqld as ('mysql' by default). on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: into outfile
At 18:25 -0400 5/5/04, [EMAIL PROTECTED] wrote: hi, when i use the into outfile query in mysql to put the records in a text file, and when i open the file that the records are stored in, i dont see each record on one line. each record is seperated by the square symbol. Is there a way in mysql that i can specify that i need one record per line in the text file??? Seems to me you've already asked a variant on this question recently, a couple of days ago. The answers that have already been given to that question also answer this question ... don't they? If you don't understand those answers, then please just say so and we can try to rephrase them. Otherwise, there doesn't seem much point in repeating the same information? Thanks, Liz -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE
At 19:18 -0400 4/30/04, [EMAIL PROTECTED] wrote: hi, I am trying to put the output of certain queries into a file using SELECT * INTO OUTFILE FILENAME FROM TABLE NAME WHERE CONDITION; I am able to see the file and the records are there. But is there a way I can see one record per line??? If i do the above i see all the records continously when i save it as txt file.dont see it as one record in one line. ? The default _is_ to write one record per line. The line terminator is (as the manual indicates) newline (linefeed). Perhaps you are viewing the output with a program that doesn't understand how to display such files? If you believe that that output is being written otherwise, check it with a hexdump program to see what's really in the output file. Thanks, liz -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE
If you're using notepad to view it, it'll display it all as one line because notepad only recognises the complete CR/LF as a new line. A CR or an LF by themselves will just show up as a square, non-displayable character and won't break the line. Wordpad, on the other hand, understands that a CR or an LF by itself is often used interchangeably with a CRLF...and renders its output accordingly. hth Dan - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 30, 2004 4:41 PM Subject: Re: INTO OUTFILE At 19:18 -0400 4/30/04, [EMAIL PROTECTED] wrote: hi, I am trying to put the output of certain queries into a file using SELECT * INTO OUTFILE FILENAME FROM TABLE NAME WHERE CONDITION; I am able to see the file and the records are there. But is there a way I can see one record per line??? If i do the above i see all the records continously when i save it as txt file.dont see it as one record in one line. ? The default _is_ to write one record per line. The line terminator is (as the manual indicates) newline (linefeed). Perhaps you are viewing the output with a program that doesn't understand how to display such files? If you believe that that output is being written otherwise, check it with a hexdump program to see what's really in the output file. Thanks, liz -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: into outfile error
Avelino F writes: I managed to set the file permission and managed to send the output to the mysql database directory used. Do you know if there is a way to make mysql write to a remote directory? As in on any machine in the world? No. As in on the client machine? Not really, since there's no equivalent of LOAD LOCAL DATA INFILE. What you can do is either use the mysql command line client and to a file, or just loop over the result and save it in whatever format you wish in your favourite language. Is there a global or local configuration that can be specified where mysql will write to? It will write where you tell it to, if that location fulfils the criteria listed in the manual. I think mysql chdir()s to the data directory, but I wouldn't rely on it. Entering the full path isn't all that work anyway. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: into outfile error
At 1:08 -0400 10/24/01, Avelino F wrote: Hello, I managed to set the file permission and managed to send the output to the mysql database directory used. Do you know if there is a way to make mysql write to a remote directory? Is there a global or local configuration that can be specified where mysql will write to? The server's doing the writing of the file, and it can only write the file on the server host. If you want the output written to a file on a client host, you'll need to use a client program that issues the select and writes the result to a file itself. Regards, Avelino At 10:09 PM 10/23/2001 -0500, you wrote: At 10:43 PM -0400 10/23/01, Avelino F wrote: Hello, After logging into mysql and selecting a database, I type select * from song into outfile 'data123.txt'; but receive the following error message: ERROR 1045: Access denied for user: 'avelino@localhost' (Using password: YES) mysql I made sure the user has full permissions to the database using the grant command. Any suggestions on what can be wrong? Said user might have full permissions on the database, but to use SELECT INTO OUTFILE, you also need the FILE privilege, which is a global privilege, not a database-specific one. Most administrators won't grant the FILE privilege lightly. Thanks, Avelino -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: into outfile error
You may also want to make sure that you did: flush privileges; to make sure they are implemented. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 7:36 PM To: Avelino F Cc: [EMAIL PROTECTED] Subject: Re: into outfile error At 1:08 -0400 10/24/01, Avelino F wrote: Hello, I managed to set the file permission and managed to send the output to the mysql database directory used. Do you know if there is a way to make mysql write to a remote directory? Is there a global or local configuration that can be specified where mysql will write to? The server's doing the writing of the file, and it can only write the file on the server host. If you want the output written to a file on a client host, you'll need to use a client program that issues the select and writes the result to a file itself. Regards, Avelino At 10:09 PM 10/23/2001 -0500, you wrote: At 10:43 PM -0400 10/23/01, Avelino F wrote: Hello, After logging into mysql and selecting a database, I type select * from song into outfile 'data123.txt'; but receive the following error message: ERROR 1045: Access denied for user: 'avelino@localhost' (Using password: YES) mysql I made sure the user has full permissions to the database using the grant command. Any suggestions on what can be wrong? Said user might have full permissions on the database, but to use SELECT INTO OUTFILE, you also need the FILE privilege, which is a global privilege, not a database-specific one. Most administrators won't grant the FILE privilege lightly. Thanks, Avelino -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: into outfile error
At 10:43 PM -0400 10/23/01, Avelino F wrote: Hello, After logging into mysql and selecting a database, I type select * from song into outfile 'data123.txt'; but receive the following error message: ERROR 1045: Access denied for user: 'avelino@localhost' (Using password: YES) mysql I made sure the user has full permissions to the database using the grant command. Any suggestions on what can be wrong? Said user might have full permissions on the database, but to use SELECT INTO OUTFILE, you also need the FILE privilege, which is a global privilege, not a database-specific one. Most administrators won't grant the FILE privilege lightly. Thanks, Avelino -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: into outfile error
Hello, I managed to set the file permission and managed to send the output to the mysql database directory used. Do you know if there is a way to make mysql write to a remote directory? Is there a global or local configuration that can be specified where mysql will write to? Regards, Avelino At 10:09 PM 10/23/2001 -0500, you wrote: At 10:43 PM -0400 10/23/01, Avelino F wrote: Hello, After logging into mysql and selecting a database, I type select * from song into outfile 'data123.txt'; but receive the following error message: ERROR 1045: Access denied for user: 'avelino@localhost' (Using password: YES) mysql I made sure the user has full permissions to the database using the grant command. Any suggestions on what can be wrong? Said user might have full permissions on the database, but to use SELECT INTO OUTFILE, you also need the FILE privilege, which is a global privilege, not a database-specific one. Most administrators won't grant the FILE privilege lightly. Thanks, Avelino -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redirecting Outfile
On 14 Apr 2001 16:55:51 -0300, Brian Cassidy wrote: Redirecting file output (for user bpc) with a command such as select * into outfile 'home/bpc/test.txt' from table gives the error ERROR 1: Can't create/write to file "/home/bpc/test.txt" (Errcode: 13). The documentation recommends setting parameter tmpdir. I have RedHat 7 with MySQL 3.23 and so I added file /home/bpc/.my.cnf with the parameter tmpdir=/home/bpc but that did not fix the problem. [tonu@x3 tonu]$ perror 13 Error code 13: Permission denied [tonu@x3 tonu]$ Something is wrong with permissions, not directory names. Tnu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redirecting Outfile
since the outfile is being written as the user that owns the mysqld process you'll need to make sure that, that user has write access to the location specified. Brian Cassidy wrote: Redirecting file output (for user bpc) with a command such as select * into outfile 'home/bpc/test.txt' from table gives the error ERROR 1: Can't create/write to file "/home/bpc/test.txt" (Errcode: 13). The documentation recommends setting parameter tmpdir. I have RedHat 7 with MySQL 3.23 and so I added file /home/bpc/.my.cnf with the parameter tmpdir=/home/bpc but that did not fix the problem. --- Brian Cassidy [EMAIL PROTECTED] http://people.unb.ca/~bpc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php