RE: LOAD DATA LOCAL INFILE
Hi Richard, Try looking at mysqlimport instead. I'm only taking a punt that it works with that version but the manual doesn't say anything about it being since a certain version. It works as at 3.23.58 so hopefully it may do for .55 Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Richard Whitney [mailto:[EMAIL PROTECTED] Sent: Thursday, 28 October 2004 10:37 AM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi! Can someone point me in the right direction? I have this that works in v.4x: $sql = LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE `members` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; When I try it using 3.23.55 I get the following error: #1148 - The used command is not allowed with this MySQL version Is there any way I can make this work using 3.23.55? TIA! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net [EMAIL PROTECTED] 310-943-6498 602-288-5340 The day this country abandons God is the day God will abandon this country Linux is like a wigwam, no windows, no gates, apache inside. - borrowed from Sharon Kimble off of the Fedora mailing list -- 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: LOAD DATA LOCAL INFILE
Richard Whitney wrote: Hi! Can someone point me in the right direction? I have this that works in v.4x: $sql = LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE `members` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; When I try it using 3.23.55 I get the following error: #1148 - The used command is not allowed with this MySQL version Is there any way I can make this work using 3.23.55? TIA! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net [EMAIL PROTECTED] 310-943-6498 602-288-5340 The day this country abandons God is the day God will abandon this country Linux is like a wigwam, no windows, no gates, apache inside. - borrowed from Sharon Kimble off of the Fedora mailing list Someone else had asked about the LOAD DATA syntax earlier this morning and getting the same error message that you're getting and one of the Ensita guys responded with this link: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html It looks like you might fall into the MySQL version discussed in the link. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE issue
Hi Michael, Yes, I rebuilt it to ensure it was switched on. The configure line was ./configure --enable-local-infile --without-server Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 3:19 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Well, as you say, that error message means it's been disabled in either the client or the server. You rebuilt the server from source with --enable-local-infile, and the server says local-infile is ON. You've tried turning it on in the client with --local-infile, but you haven't mentioned whether the client was built with --enable-local-infile. I'm not sure the command line switch does any good if local-infile was disabled at build time. So, did you build the client with --enable-local-infile? Michael Logan, David (SST - Adelaide) wrote: Hi Michael, Thanks. I rechecked things but porkribs /u2/lcscreative/sql_scripts $ mysql --local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version Still a most unhappy camper. I had to resort to placing the file on the server, owned and group mysql in the datadir before it would work. Even though I did chmod 777 on the file, it was still upset. I am running Solaris 8 on the client and 9 on the server. I don't know whether this would make a difference, I can't understand why it would. Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 2:37 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Perhaps the problem is that there is no such option as --enable-local-infile in the mysql client. I believe you want --local-infile. Client options are detailed in the manual http://dev.mysql.com/doc/mysql/en/mysql.html. Michael Logan, David (SST - Adelaide) wrote: Hi Folks, I am having a few issues with a LOAD DATA LOCAL INFILE command. As you can see by the command below, I am receiving an error 1148. The documentation states this is generally because I don't have --enable-local-infile on in both client and server. I have switched it on via command line below and when I do a show variables on the server, I see local-infile = ON. I have also tried loose-local-infile in the my.cnf on both client and server. I have just rebuilt the server ensuring --enable-local-infile was in the ./configure options. Both client and server are version 4.0.20 and are on different hosts. The sql file I am using is CREATE DATABASE IF NOT EXISTS weblog; use weblog; CREATE TABLE IF NOT EXISTS dept (department INT PRIMARY KEY, main_dept INT NOT NULL, description VARCHAR(40)); CREATE TABLE IF NOT EXISTS dns_cache (ip_address CHAR(15) NOT NULL PRIMARY KEY, hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS web_rec (recnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date DATE NOT NULL, time TIME NOT NULL, source_ip CHAR(15) NOT NULL, method CHAR(10) NOT NULL, department INT, source_port SMALLINT NOT NULL, username VARCHAR(255), c_ip CHAR(15), user_agent VARCHAR(255), referrer TEXT, last_status SMALLINT, last_substatus SMALLINT, win32_status SMALLINT, sent_bytes INT, recv_bytes INT); CREATE TABLE IF NOT EXISTS UserAgent (agentnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, browser VARCHAR(255), description VARCHAR(255)); CREATE TABLE IF NOT EXISTS Referrer (Refnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ref_uri VARCHAR(255), hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS status (status INT NOT NULL PRIMARY KEY, description VARCHAR(255)); LOAD DATA LOCAL INFILE '/u2/lcscreative/sql_scripts/categories.csv' --- Line 46, error 1148 occurs here INTO TABLE dept FIELDS TERMINATED BY ',' ENCLOSED BY '' (main_dept,department,description); porkribs /u2/lcscreative/sql_scripts $ mysql --enable-local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version porkribs /u2/lcscreative/sql_scripts $ Anybody have any ideas on what I've done wrong? Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax
Re: LOAD DATA LOCAL INFILE issue
Perhaps the problem is that there is no such option as --enable-local-infile in the mysql client. I believe you want --local-infile. Client options are detailed in the manual http://dev.mysql.com/doc/mysql/en/mysql.html. Michael Logan, David (SST - Adelaide) wrote: Hi Folks, I am having a few issues with a LOAD DATA LOCAL INFILE command. As you can see by the command below, I am receiving an error 1148. The documentation states this is generally because I don't have --enable-local-infile on in both client and server. I have switched it on via command line below and when I do a show variables on the server, I see local-infile = ON. I have also tried loose-local-infile in the my.cnf on both client and server. I have just rebuilt the server ensuring --enable-local-infile was in the ./configure options. Both client and server are version 4.0.20 and are on different hosts. The sql file I am using is CREATE DATABASE IF NOT EXISTS weblog; use weblog; CREATE TABLE IF NOT EXISTS dept (department INT PRIMARY KEY, main_dept INT NOT NULL, description VARCHAR(40)); CREATE TABLE IF NOT EXISTS dns_cache (ip_address CHAR(15) NOT NULL PRIMARY KEY, hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS web_rec (recnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date DATE NOT NULL, time TIME NOT NULL, source_ip CHAR(15) NOT NULL, method CHAR(10) NOT NULL, department INT, source_port SMALLINT NOT NULL, username VARCHAR(255), c_ip CHAR(15), user_agent VARCHAR(255), referrer TEXT, last_status SMALLINT, last_substatus SMALLINT, win32_status SMALLINT, sent_bytes INT, recv_bytes INT); CREATE TABLE IF NOT EXISTS UserAgent (agentnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, browser VARCHAR(255), description VARCHAR(255)); CREATE TABLE IF NOT EXISTS Referrer (Refnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ref_uri VARCHAR(255), hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS status (status INT NOT NULL PRIMARY KEY, description VARCHAR(255)); LOAD DATA LOCAL INFILE '/u2/lcscreative/sql_scripts/categories.csv' --- Line 46, error 1148 occurs here INTO TABLE dept FIELDS TERMINATED BY ',' ENCLOSED BY '' (main_dept,department,description); porkribs /u2/lcscreative/sql_scripts $ mysql --enable-local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version porkribs /u2/lcscreative/sql_scripts $ Anybody have any ideas on what I've done wrong? Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE issue
Hi Michael, Thanks. I rechecked things but porkribs /u2/lcscreative/sql_scripts $ mysql --local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version Still a most unhappy camper. I had to resort to placing the file on the server, owned and group mysql in the datadir before it would work. Even though I did chmod 777 on the file, it was still upset. I am running Solaris 8 on the client and 9 on the server. I don't know whether this would make a difference, I can't understand why it would. Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 2:37 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Perhaps the problem is that there is no such option as --enable-local-infile in the mysql client. I believe you want --local-infile. Client options are detailed in the manual http://dev.mysql.com/doc/mysql/en/mysql.html. Michael Logan, David (SST - Adelaide) wrote: Hi Folks, I am having a few issues with a LOAD DATA LOCAL INFILE command. As you can see by the command below, I am receiving an error 1148. The documentation states this is generally because I don't have --enable-local-infile on in both client and server. I have switched it on via command line below and when I do a show variables on the server, I see local-infile = ON. I have also tried loose-local-infile in the my.cnf on both client and server. I have just rebuilt the server ensuring --enable-local-infile was in the ./configure options. Both client and server are version 4.0.20 and are on different hosts. The sql file I am using is CREATE DATABASE IF NOT EXISTS weblog; use weblog; CREATE TABLE IF NOT EXISTS dept (department INT PRIMARY KEY, main_dept INT NOT NULL, description VARCHAR(40)); CREATE TABLE IF NOT EXISTS dns_cache (ip_address CHAR(15) NOT NULL PRIMARY KEY, hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS web_rec (recnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date DATE NOT NULL, time TIME NOT NULL, source_ip CHAR(15) NOT NULL, method CHAR(10) NOT NULL, department INT, source_port SMALLINT NOT NULL, username VARCHAR(255), c_ip CHAR(15), user_agent VARCHAR(255), referrer TEXT, last_status SMALLINT, last_substatus SMALLINT, win32_status SMALLINT, sent_bytes INT, recv_bytes INT); CREATE TABLE IF NOT EXISTS UserAgent (agentnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, browser VARCHAR(255), description VARCHAR(255)); CREATE TABLE IF NOT EXISTS Referrer (Refnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ref_uri VARCHAR(255), hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS status (status INT NOT NULL PRIMARY KEY, description VARCHAR(255)); LOAD DATA LOCAL INFILE '/u2/lcscreative/sql_scripts/categories.csv' --- Line 46, error 1148 occurs here INTO TABLE dept FIELDS TERMINATED BY ',' ENCLOSED BY '' (main_dept,department,description); porkribs /u2/lcscreative/sql_scripts $ mysql --enable-local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version porkribs /u2/lcscreative/sql_scripts $ Anybody have any ideas on what I've done wrong? Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA LOCAL INFILE issue
Well, as you say, that error message means it's been disabled in either the client or the server. You rebuilt the server from source with --enable-local-infile, and the server says local-infile is ON. You've tried turning it on in the client with --local-infile, but you haven't mentioned whether the client was built with --enable-local-infile. I'm not sure the command line switch does any good if local-infile was disabled at build time. So, did you build the client with --enable-local-infile? Michael Logan, David (SST - Adelaide) wrote: Hi Michael, Thanks. I rechecked things but porkribs /u2/lcscreative/sql_scripts $ mysql --local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version Still a most unhappy camper. I had to resort to placing the file on the server, owned and group mysql in the datadir before it would work. Even though I did chmod 777 on the file, it was still upset. I am running Solaris 8 on the client and 9 on the server. I don't know whether this would make a difference, I can't understand why it would. Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 2:37 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Perhaps the problem is that there is no such option as --enable-local-infile in the mysql client. I believe you want --local-infile. Client options are detailed in the manual http://dev.mysql.com/doc/mysql/en/mysql.html. Michael Logan, David (SST - Adelaide) wrote: Hi Folks, I am having a few issues with a LOAD DATA LOCAL INFILE command. As you can see by the command below, I am receiving an error 1148. The documentation states this is generally because I don't have --enable-local-infile on in both client and server. I have switched it on via command line below and when I do a show variables on the server, I see local-infile = ON. I have also tried loose-local-infile in the my.cnf on both client and server. I have just rebuilt the server ensuring --enable-local-infile was in the ./configure options. Both client and server are version 4.0.20 and are on different hosts. The sql file I am using is CREATE DATABASE IF NOT EXISTS weblog; use weblog; CREATE TABLE IF NOT EXISTS dept (department INT PRIMARY KEY, main_dept INT NOT NULL, description VARCHAR(40)); CREATE TABLE IF NOT EXISTS dns_cache (ip_address CHAR(15) NOT NULL PRIMARY KEY, hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS web_rec (recnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date DATE NOT NULL, time TIME NOT NULL, source_ip CHAR(15) NOT NULL, method CHAR(10) NOT NULL, department INT, source_port SMALLINT NOT NULL, username VARCHAR(255), c_ip CHAR(15), user_agent VARCHAR(255), referrer TEXT, last_status SMALLINT, last_substatus SMALLINT, win32_status SMALLINT, sent_bytes INT, recv_bytes INT); CREATE TABLE IF NOT EXISTS UserAgent (agentnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, browser VARCHAR(255), description VARCHAR(255)); CREATE TABLE IF NOT EXISTS Referrer (Refnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ref_uri VARCHAR(255), hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS status (status INT NOT NULL PRIMARY KEY, description VARCHAR(255)); LOAD DATA LOCAL INFILE '/u2/lcscreative/sql_scripts/categories.csv' --- Line 46, error 1148 occurs here INTO TABLE dept FIELDS TERMINATED BY ',' ENCLOSED BY '' (main_dept,department,description); porkribs /u2/lcscreative/sql_scripts $ mysql --enable-local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version porkribs /u2/lcscreative/sql_scripts $ Anybody have any ideas on what I've done wrong? Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA LOCAL INFILE doesn't show in binlog
Not sure about the 3.x series, but in 4.x, if you build your own mysql, you need to explicitly enable that feature via a configure option. I think binary builds all have it enabled though. Issac - Original Message - From: David Brännlund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 11:14 AM Subject: LOAD DATA LOCAL INFILE doesn't show in binlog Hi, I'm running MySQL 3.23.52 with binary logging enabled. All commands seems to be written to the log except for LOAD DATA LOCAL INFILE. Is there a setting that needs to be tweaked for this to work? Thanks for your help, David _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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: Load Data Local Infile problem
[EMAIL PROTECTED] wrote: I'm load a CSV file with five fields into mysql. It loads up fine. Problem comes in when I try to refresh the data with updates. I grab this file and convert it to CSV every 12 hours. The data shows past 24 hours only, so basically I'm trying to make a permanent archive. I tried creating a PRIMARY key of the first 4 fields to be unique to filter out duplicates. Problem is, when I have that key on, no new data gets written to the database, even clearly NEW records. Table is warlog, fields are time, attacker, coords, defender, status (obviously a game). A unique record would match the first four, or at the very least time and coords. Whenever I go to load data infile the second time around, no records get written if any indexes are present. If no indexes I get duplicates. If I put an index with the duplicates, I only get the first set of data with no updates. Is there something about primary keys I should know about? I've created this database with them and tried every combination, but I can't seem to get the update part to work. If you don't specify IGNORE or REPLACE keyword, LOAD DATA LOCAL works the same as IGNORE is specified. -- 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: Load Data Local Infile problem
This is from the manual. See the 3rd paragraph. The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values. If you specify REPLACE, input rows replace existing rows (in other words rows that has the same value for a primary or unique index as an existing row). See section 6.4.7 REPLACE Syntax. If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, the behavior depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE
From: Daniel Kiss [mailto:[EMAIL PROTECTED] Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 I believe it's... LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE tablename FIELDS TERMINATED BY ',' OPTONALLY ENCLOSED BY ''; -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE
If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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: LOAD DATA LOCAL INFILE
Thanks for the help. The problem was the line terminating character. My source files are in DOS format '\r\n' and not the default posix one '\n'. Thanks again. If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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: LOAD DATA LOCAL INFILE question...
Egor, Thanks for the info. However, like I said, I already have local-infile=1 and set-variable = local-infile=1 in my my.ini on the server. I also have already done 'show variable like '%infile%' ' and receive local_infile ON in the result set. I run the command line client with --local-infile=1 I still receive the same error regardless of the configuration. -- R - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 22, 2003 5:25 AM Subject: Re: LOAD DATA LOCAL INFILE question... Robert [EMAIL PROTECTED] wrote: Having problems using 'LOAD DATA LOCAL INFILE' from a UNIX client talking to a Win32 server. I've read the documentation several times now and have had zero success with this command. You should run client and server with --local-infile option: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -- 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: LOAD DATA LOCAL INFILE question...
Reverend Deuce [EMAIL PROTECTED] wrote: Egor, Thanks for the info. However, like I said, I already have local-infile=1 and set-variable = local-infile=1 in my my.ini on the server. I also have already done 'show variable like '%infile%' ' and receive local_infile ON in the result set. I run the command line client with --local-infile=1 I still receive the same error regardless of the configuration. Should work. What are the server and client versions? -- 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: LOAD DATA LOCAL INFILE question...
Versions: Server is Win32 MySQL max 4.0.16 Client is FreeBSD MySQL 4.0.13 -- R Should work. What are the server and client versions? -- 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: LOAD DATA LOCAL INFILE question...
Robert [EMAIL PROTECTED] wrote: Having problems using 'LOAD DATA LOCAL INFILE' from a UNIX client talking to a Win32 server. I've read the documentation several times now and have had zero success with this command. My server has the appropriate permissions configured in the MY.INI file (this was verified using 'show variables') as does my client on the UNIX system. I am attempting this using the root login to my server during testing. I receive the dreaded command not valid for this version of MySQL error. Server is Win32 MySQL max 4.0.16 Client is FreeBSD MySQL 4.0.13 You should run client and server with --local-infile option: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -- 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: LOAD DATA LOCAL INFILE
Dear Bjoern, it is better to send all mailing list requests in english. Best regards Nils Valentin Tokyo/Japan -Translation: Hi Bioern, es ist bessser alle anfragen an die mailing Liste grundsaetzlich in English zu formulieren. Besten Gruss Nils Valentin Tokyo/Japan 2003 7 7 16:37Bjrn Weichel : Hallo lieber Leser, Ich versuche seit lngerem mit dem u. g. Befehl eine .csv Tabelle in eine Datenbank einzupflegen. Auf dem Server luft MySQL 3.23.5* und der Import funktioniert nicht. Der andere Server hat die MySQL Version 3.23.44 und der Import funktioniert. Ich habe die Rechte alle angeschaltet, so dass es auch da keine Probleme geben sollte. Hier mein Befehl: LOAD DATA LOCAL INFILE 'csv/gversand.csv' REPLACE INTO TABLE versand FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\' Vielen Dank fr die Hilfe. Gru Bjrn +++ Media2be, Weichel Mller GbR Agentur fr Neue Medien, Marketing und Handel St. Johanner Str. 41-43 66111 Saarbrcken Fon: 0681 59 59 08 98 Fax: 0681 59 59 08 99 Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Net: http://www.media2be.de http://www.media2be.de +++ -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile
If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client Read the mysql manual from msyql.com about 'manual_MySQL_Database_Administration.html#LOAD_DATA_LOCAL' Regards, - Original Message - From: John May [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 AM Subject: Load Data Local Infile OK, tearing my hair out here... I am trying to get a LOAD DATA LOCAL INFILE command to work. It works when I start the MySQL client with --local-infile=1, but never in any other situation. Starting without this option, or submitting the request through PHP or Lasso results in the command isn't supported error. I have tried a million different my.cnf changes, but no luck. Is there any way to tell MySQL to default to allowing LOAD DATA LOCAL INFILE instead of having to specify it in the client login? I'm trying this with both version 3.23.51 and 4.0.12-max. The even odder thing is that I can get it to work fine on another machine running the pre-built 3.23.51 binary from entropy.ch. AND, if I move that build to the 3.23.51 hand-built machine, the error returns. Any ideas GREATLY appreciated. Thanks! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- 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: Load Data Local Infile
Can this only be done at build time? Or can I enable it after the fact? If so, how? I tried enable-local-infile as both a setting in my.cnf and when calling safe_mysqld both with no luck. In the meantime, I've found that PHP apparently calls MySQL with the local-infile option on - once I downgraded to 4.2.2 from 4.3.0 that is. This at least helps things a bit. - John If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client Read the mysql manual from msyql.com about 'manual_MySQL_Database_Administration.html#LOAD_DATA_LOCAL' Regards, - Original Message - From: John May [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 AM Subject: Load Data Local Infile OK, tearing my hair out here... I am trying to get a LOAD DATA LOCAL INFILE command to work. It works when I start the MySQL client with --local-infile=1, but never in any other situation. Starting without this option, or submitting the request through PHP or Lasso results in the command isn't supported error. I have tried a million different my.cnf changes, but no luck. Is there any way to tell MySQL to default to allowing LOAD DATA LOCAL INFILE instead of having to specify it in the client login? I'm trying this with both version 3.23.51 and 4.0.12-max. The even odder thing is that I can get it to work fine on another machine running the pre-built 3.23.51 binary from entropy.ch. AND, if I move that build to the 3.23.51 hand-built machine, the error returns. Any ideas GREATLY appreciated. Thanks! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile
The other odd thing I find is that the MySQL manual states: By default, all MySQL clients and libraries are compiled with --enable-local-infile , to be compatible with MySQL 3.23.48 and before. However, I'm using a 4.0.12-max binary (pre-compiled binary direct from MySQL's site) on one machine, and enable-local-infile is definitely NOT compiled into it... Have others seen this case contrary to the documentation? - John Can this only be done at build time? Or can I enable it after the fact? If so, how? I tried enable-local-infile as both a setting in my.cnf and when calling safe_mysqld both with no luck. In the meantime, I've found that PHP apparently calls MySQL with the local-infile option on - once I downgraded to 4.2.2 from 4.3.0 that is. This at least helps things a bit. - John If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client Read the mysql manual from msyql.com about 'manual_MySQL_Database_Administration.html#LOAD_DATA_LOCAL' Regards, - Original Message - From: John May [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 AM Subject: Load Data Local Infile OK, tearing my hair out here... I am trying to get a LOAD DATA LOCAL INFILE command to work. It works when I start the MySQL client with --local-infile=1, but never in any other situation. Starting without this option, or submitting the request through PHP or Lasso results in the command isn't supported error. I have tried a million different my.cnf changes, but no luck. Is there any way to tell MySQL to default to allowing LOAD DATA LOCAL INFILE instead of having to specify it in the client login? I'm trying this with both version 3.23.51 and 4.0.12-max. The even odder thing is that I can get it to work fine on another machine running the pre-built 3.23.51 binary from entropy.ch. AND, if I move that build to the 3.23.51 hand-built machine, the error returns. Any ideas GREATLY appreciated. Thanks! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John May wrote: The other odd thing I find is that the MySQL manual states: By default, all MySQL clients and libraries are compiled with --enable-local-infile , to be compatible with MySQL 3.23.48 and before. However, I'm using a 4.0.12-max binary (pre-compiled binary direct from MySQL's site) on one machine, and enable-local-infile is definitely NOT compiled into it... Have others seen this case contrary to the documentation? - John You don't say what OS you're on, but this was broken in Windows builds until MySQL 4.0.13 (which is the latest stable version, btw.) -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+46F7tvXNTca6JD8RAqwZAJwNVmxY0KhQXm5NXpcAyiSTFKRp6gCfVwnj UAMdENoo/r4wH8izEQwTYrU= =g6z4 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile
configuring is only be done on build time procedure. I supposed (not sure) that your problem is with your precompiled binary. I dont really know whether the default precompiled binary building with enable LOAD DATA or not. so , you can try install it with mysql source .(not precompile binary). Regards. ROny - Original Message - From: John May [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 3:31 AM Subject: Re: Load Data Local Infile The other odd thing I find is that the MySQL manual states: By default, all MySQL clients and libraries are compiled with --enable-local-infile , to be compatible with MySQL 3.23.48 and before. However, I'm using a 4.0.12-max binary (pre-compiled binary direct from MySQL's site) on one machine, and enable-local-infile is definitely NOT compiled into it... Have others seen this case contrary to the documentation? - John Can this only be done at build time? Or can I enable it after the fact? If so, how? I tried enable-local-infile as both a setting in my.cnf and when calling safe_mysqld both with no luck. In the meantime, I've found that PHP apparently calls MySQL with the local-infile option on - once I downgraded to 4.2.2 from 4.3.0 that is. This at least helps things a bit. - John If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client Read the mysql manual from msyql.com about 'manual_MySQL_Database_Administration.html#LOAD_DATA_LOCAL' Regards, - Original Message - From: John May [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 AM Subject: Load Data Local Infile OK, tearing my hair out here... I am trying to get a LOAD DATA LOCAL INFILE command to work. It works when I start the MySQL client with --local-infile=1, but never in any other situation. Starting without this option, or submitting the request through PHP or Lasso results in the command isn't supported error. I have tried a million different my.cnf changes, but no luck. Is there any way to tell MySQL to default to allowing LOAD DATA LOCAL INFILE instead of having to specify it in the client login? I'm trying this with both version 3.23.51 and 4.0.12-max. The even odder thing is that I can get it to work fine on another machine running the pre-built 3.23.51 binary from entropy.ch. AND, if I move that build to the 3.23.51 hand-built machine, the error returns. Any ideas GREATLY appreciated. Thanks! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- 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: Load Data Local Infile
OS X (10.1 Client and 10.2 Server). My 3.23.51 builds seem to have the same problem. Since PHP access it properly, and the MySQL client can with the proper variable setting, I guess I'll just leave it alone. - John -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John May wrote: The other odd thing I find is that the MySQL manual states: By default, all MySQL clients and libraries are compiled with --enable-local-infile , to be compatible with MySQL 3.23.48 and before. However, I'm using a 4.0.12-max binary (pre-compiled binary direct from MySQL's site) on one machine, and enable-local-infile is definitely NOT compiled into it... Have others seen this case contrary to the documentation? - John You don't say what OS you're on, but this was broken in Windows builds until MySQL 4.0.13 (which is the latest stable version, btw.) -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+46F7tvXNTca6JD8RAqwZAJwNVmxY0KhQXm5NXpcAyiSTFKRp6gCfVwnj UAMdENoo/r4wH8izEQwTYrU= =g6z4 -END PGP SIGNATURE- -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data local infile
Terence, Rich, I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version This is because of security issues, as the manual says: In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with possible security issues when it comes to LOAD DATA LOCAL. [snip] If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client. [snip] For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option -- local-infile[=1], or disabled with --local-infile=0. Full text: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html I use 4.0.7-max-nt on a Win2K box, and whatever I do, I cannot use LOAD DATA LOCAL (keep getting error 1148). I tried: - Starting the server with --local-infile=1, and once more with --local-infile. - Starting the mysql client with --local-infile[=1] - Combining server and client options The manual page on this is quite contradictory. I agree with the user comment on that manual page: I just wasted 3 hours of my time tracking down this annoying security improvement -- thanks but no thanks, MySQL developers. This was poorly thought out and poorly implemented. Any hints how to make LOAD DATA LOCAL work? Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: rich allen [EMAIL PROTECTED] To: Terence Ng [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 6:23 AM Subject: Re: load data local infile sound like you may want to add the following to your my.cnf file for MySQL [mysqld] local-infile=1 [mysql] local-infile=1 - hcir On Tuesday, December 31, 2002, at 06:23 PM, Terence Ng wrote: Hi, I have just upgraded to 3.23.54 How to input bulk data into table? I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version How come? What method can I use to input bulk data? - 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: load data local infile
sound like you may want to add the following to your my.cnf file for MySQL [mysqld] local-infile=1 [mysql] local-infile=1 - hcir On Tuesday, December 31, 2002, at 06:23 PM, Terence Ng wrote: Hi, I have just upgraded to 3.23.54 How to input bulk data into table? I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version How come? What method can I use to input bulk data? - 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: load data local infile
try adding these line to your my.cnf file (mysql config file) [mysqld] local-infile=1 [mysql] local-infile=1 - hcir On Thursday, December 12, 2002, at 09:52 AM, Yuyi Guo wrote: Hi: I am using mysql v4_0_5 both clinet and server. I cannot get load data local infile to work after many different tries. Does anyone make it work with this version? It worked for when I used v3.23.52. - 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: load data local infile
Thanks, it works after we recomplied it the enable-local-infile. By default, it is truned off. Cheers, yuyi Rich Allen wrote: try adding these line to your my.cnf file (mysql config file) [mysqld] local-infile=1 [mysql] local-infile=1 - hcir On Thursday, December 12, 2002, at 09:52 AM, Yuyi Guo wrote: Hi: I am using mysql v4_0_5 both clinet and server. I cannot get load data local infile to work after many different tries. Does anyone make it work with this version? It worked for when I used v3.23.52. - 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: LOAD DATA LOCAL INFILE
John, Thursday, November 28, 2002, 7:02:58 PM, you wrote: JC Thanks very much. JC Following on your suggestions, I then went ahead and put the text file JC into my directory: JC /usr/local/mysql JC so that the file was then /usr/local/mysql/absence.txt JC and, with that done, the JC LOAD DATA LOCAL INFILE absence.txt into table absence; JC command in the mysql client successfully did load it. JC (It didn't work when put into the /usr/local/mysql/scripts directory JC or in the /usr/local/mysql/data directory.) JC Is there anyway to change the place where the client looks when given JC the relative pathname of a file to be LOADed, or is one stuck with JC having to put all text files in that one immutable location? Nope. And storing text files in /usr/local/mysql is not a good idea .. If you execute LOAD DATA LOCAL from your own client application/script, store path to the dir in some configuration variable and so on. JC (On the Mac, we aren't allowed to see the contents of JC /usr/local/mysql/ in the GUI. We have to access it through the UNIX JC Terminal program, and then fiddle with permissions, which, I am not JC bashful to say, I am reluctant to start doing without comprehending the JC implications, which, I also am not bashful to say, I don't.) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: LOAD DATA LOCAL INFILE
The MySQL Manual states the following in the LOAD DATA INFILE section :- If a filename with no leading components is given, the server looks for the file in the database directory of the current database. -Original Message- From: John Connolly [mailto:[EMAIL PROTECTED]] Sent: 28 November 2002 14:12 To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hello all --- I posted this yesterday, perhaps before my subscription took effect, and so I am not sure that it got distributed. So, forgive the repetition in sending, if that's the case. Anyway . . . Here's the question, as succinctly as I can formulate it: The documentation seems to say that one can load a tab-delimited text file located in the current directory of the client host into a database by running the client mysql and executing the following command: mysql LOAD DATA LOCAL INFILE absence.txt into table absence; When absence.txt is located in /Users/jack/Desktop/SQL_stuff/samp_db/, then the command: mysql LOAD DATA LOCAL INFILE /Users/jack/Desktop/SQL_stuff/samp_db/ absence.txt into table absence; does work. But the command: mysql LOAD DATA LOCAL INFILE absence.txt into table absence; does not work. Rather, it produces the error: ERROR: File 'absence.txt' not found (Errcode: 2) That is, absolute pathnames work, but relative pathnames do not. I have been careful to be sure that, when invoking the mysql client, I am in the correct directory, and that when I invoke mysql, that I include the --local-infile option, as the following printout shows: [Jacks-G4:~/Desktop/SQL_stuff/samp_db] jack% mysql --local-infile samp_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 3.23.52-entropy.ch Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql LOAD DATA LOCAL INFILE absence.txt into table absence; ERROR: File 'absence.txt' not found (Errcode: 2) I have concluded that one cannot use the relative pathname of a file in the LOAD DATA LOCAL INFILE command. I hope I am wrong, as it would be nice to use the simpler, relative, pathname. With thanks for your replies, am I wrong or right? If wrong, what am I missing? Jack Connolly Hanover, MA [EMAIL PROTECTED] Running, on Mac OS 10.2, server and client on same machine: ++ | version() | ++ | 3.23.52-entropy.ch | ++ - 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 BBCi at http://www.bbc.co.uk/ This e-mail (and any attachments) is confidential and may contain personal views which are not the views of the BBC unless specifically stated. If you have received it in error, please delete it from your system, do not use, copy or disclose the information in any way nor act in reliance on it and notify the sender immediately. Please note that the BBC monitors e-mails sent or received. Further communication will signify your consent to this. - 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: LOAD DATA LOCAL INFILE
John, Thursday, November 28, 2002, 2:12:27 AM, you wrote: JC Here's the question, as succinctly as I can formulate it: JC The documentation seems to say that one can load a tab-delimited text JC file located in the current directory of the client host into a JC database by running the client mysql and executing the following JC command: JC mysql LOAD DATA LOCAL INFILE absence.txt into table absence; JC When absence.txt is located in JC /Users/jack/Desktop/SQL_stuff/samp_db/, then the command: JC mysql LOAD DATA LOCAL INFILE /Users/jack/Desktop/SQL_stuff/samp_db/ JC absence.txt into table absence; JC does work. JC But the command: JC mysql LOAD DATA LOCAL INFILE absence.txt into table absence; JC does not work. Rather, it produces the error: JC ERROR: JC File 'absence.txt' not found (Errcode: 2) JC That is, absolute pathnames work, but relative pathnames do not. JC I have been careful to be sure that, when invoking the mysql client, I JC am in the correct directory, and that when I invoke mysql, that I JC include the --local-infile option, as the following printout shows: JC [Jacks-G4:~/Desktop/SQL_stuff/samp_db] jack% mysql --local-infile JC samp_db JC Reading table information for completion of table and column names JC You can turn off this feature to get a quicker startup with -A JC Welcome to the MySQL monitor. Commands end with ; or \g. JC Your MySQL connection id is 14 to server version: 3.23.52-entropy.ch JC Type 'help;' or '\h' for help. Type '\c' to clear the buffer. JC mysql LOAD DATA LOCAL INFILE absence.txt into table absence; JC ERROR: JC File 'absence.txt' not found (Errcode: 2) JC I have concluded that one cannot use the relative pathname of a file JC in the LOAD DATA LOCAL INFILE command. I hope I am wrong, as it would JC be nice to use the simpler, relative, pathname. JC Am I wrong or right? If wrong, what am I missing? If you don't use absolute pathname, your file must be located in the client program/script directory (in your case mysql client program) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: load data local infile
Hello Pallavi, i m using redhat linux 7.2 with mysql 3.23.49a-max-log with Apache 1.3.24 . i have encounter following error for load data local infile when i tried to use an example on mysql site's documentation file : mysql use test; Database changed mysql show tables; ++ | Tables_in_test | ++ | dept | | emp| | person | | pet| | shirt | ++ 5 rows in set (0.00 sec) mysql desc pet; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | name| varchar(20) | YES | | NULL| | | owner | varchar(20) | YES | | NULL| | | species | varchar(20) | YES | | NULL| | | sex | char(1) | YES | | NULL| | | birth | date| YES | | NULL| | | death | date| YES | | NULL| | +-+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql LOAD DATA LOCAL INFILE pet.txt INTO TABLE pet; ERROR 1148: The used command is not allowed with this MySQL version mysql mysql select version(); +--+ | version()| +--+ | 3.23.49a-max-log | +--+ 1 row in set (0.00 sec) how do i go about it..if i want to load thousands of papers data data in text file? u'r response R most appreciated. Please review 4.2.4 Security issues with LOAD DATA LOCAL =dn - 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: load data local infile
Pallavi, Wednesday, November 20, 2002, 11:01:51 AM, you wrote: PAP i m using redhat linux 7.2 with mysql 3.23.49a-max-log with PAP Apache 1.3.24 . PAP i have encounter following error for load data local infile when i PAP tried to use an example on mysql site's documentation file : [skip] PAP mysql LOAD DATA LOCAL INFILE pet.txt INTO TABLE pet; PAP ERROR 1148: The used command is not allowed with this MySQL PAP version PAP how do i go about it..if i want to load thousands of PAP papers data data in text file? If you use LOAD DATA LOCAL since 3.23.49 this command should be enabled: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: load data local infile
Try leaving out the word local. This corrected the same problem on XP Prof. BobJ - Original Message - From: nellA hciR [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 2:32 PM Subject: load data local infile i have read several archives/docs on this but still can not get this to work from the mysql command line, load data local infile DOES work, am trying to get this to work from Perl $sth = $dbh-prepare(load data local infile '/users/hcir/desktop/t2/0001' into table s0001); $sth-execute() or warn : $DBI::errstr\n; returns The used command is not allowed with this MySQL version Mac OS X mysql 4.0.3 /etc/my.cnf contains [mysqld] local-infile=1 [mysql] local-infile=1 thanks - hcir - 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: load data local infile
I have had luck by removing the word LOCAL from the statement. -=Bryan=- -Original Message- From: nellA hciR [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 11:33 AM To: MySQL List Subject: load data local infile i have read several archives/docs on this but still can not get this to work from the mysql command line, load data local infile DOES work, am trying to get this to work from Perl $sth = $dbh-prepare(load data local infile '/users/hcir/desktop/t2/0001' into table s0001); $sth-execute() or warn : $DBI::errstr\n; returns The used command is not allowed with this MySQL version Mac OS X mysql 4.0.3 /etc/my.cnf contains [mysqld] local-infile=1 [mysql] local-infile=1 thanks - hcir - 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: load data local infile
THANKS!! that works! - hcir I have had luck by removing the word LOCAL from the statement. -=Bryan=- from the mysql command line, load data local infile DOES work, am trying to get this to work from Perl $sth = $dbh-prepare(load data local infile '/users/hcir/desktop/t2/0001' into table s0001); $sth-execute() or warn : $DBI::errstr\n; returns The used command is not allowed with this MySQL version - 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: Re: LOAD DATA LOCAL INFILE not allowed this version?
Uriel, Monday, September 09, 2002, 4:21:40 AM, you wrote: C:mysql -r -N fall02 \mysqluw\mysql.txt UW (i.e. using the mysql command from DOS, and running a batch file) UW And I didn't change any configuration defaults since installing mySQL on UW this machine. Put local-infile=1 in the [mysqld] and [mysql] section of my.cnf(my.ini) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Re: LOAD DATA LOCAL INFILE not allowed this version?
THANKS VERY MUCH! That worked! - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 9:15 PM Subject: re: Re: LOAD DATA LOCAL INFILE not allowed this version? [.] Put local-infile=1 in the [mysqld] and [mysql] section of my.cnf(my.ini) - 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: LOAD DATA LOCAL INFILE not allowed this version?
One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0. In the case that LOAD DATA LOCAL INFILE is disabled in the server or the client, you will get the error message (1148): The used command is not allowed with this MySQL version http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -Original Message- From: Uriel Wittenberg [mailto:[EMAIL PROTECTED]] Sent: 08 September 2002 16:24 To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE not allowed this version? The command LOAD DATA LOCAL INFILE C:\\MO10.txt INTO TABLE stud1 fields terminated by / lines terminated by '\r\n'; produces: ERROR 1148 at line 1: The used command is not allowed with this MySQL vers Could someone explain why?? I'm running version MYSQL Ver 11.18 Distrib 3.23.51, for Win95/Win98 (i32) under WinXP Home standalone (i.e., no network, just one machine). I used to do this routinely under Win Me. Thanks in advance, Uriel - 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: LOAD DATA LOCAL INFILE not allowed this version?
I'm not doing that. I'm doing C:mysql -r -N fall02 \mysqluw\mysql.txt (i.e. using the mysql command from DOS, and running a batch file) And I didn't change any configuration defaults since installing mySQL on this machine. - Original Message - From: Sqlcoders.com Programming Dept [EMAIL PROTECTED] To: Uriel Wittenberg [EMAIL PROTECTED]; Mysql [EMAIL PROTECTED] Sent: Monday, September 09, 2002 8:31 AM Subject: RE: LOAD DATA LOCAL INFILE not allowed this version? One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0. In the case that LOAD DATA LOCAL INFILE is disabled in the server or the client, you will get the error message (1148): The used command is not allowed with this MySQL version http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -Original Message- From: Uriel Wittenberg [mailto:[EMAIL PROTECTED]] Sent: 08 September 2002 16:24 To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE not allowed this version? The command LOAD DATA LOCAL INFILE C:\\MO10.txt INTO TABLE stud1 fields terminated by / lines terminated by '\r\n'; produces: ERROR 1148 at line 1: The used command is not allowed with this MySQL vers Could someone explain why?? I'm running version MYSQL Ver 11.18 Distrib 3.23.51, for Win95/Win98 (i32) under WinXP Home standalone (i.e., no network, just one machine). I used to do this routinely under Win Me. Thanks in advance, Uriel - 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 - 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49]
Hello, Below is a thread from March. Basically, I am having the same problem in v3.23.49 (release version, not Alpha). I also have Linux RedHat 7.2, same as Tom. I compiled MySQL from source using gcc v2.96 . Here are the details of my problem: The SQL command LOAD DATA LOCAL INFILE '/home/toma/spotplay/EVD0619.EG' REPLACE INTO TABLE `TSN` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' works fine from a phpMyAdmin web page; however, the exact same command inside a Perl script on the same machine localhost fails with the message DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at /home/toma/lib/perl5/Database/MySql.pm line 61. Can't execute SQL statement: The used command is not allowed with this MySQL version I assume this discrepancy means that phpMyAdmin is using the server, while the Perl script is a client. I have tried all of the following -- using local-infile in my.cnf as Sinisa suggests -- rerunning./configure with arg --enable-local-infile, then recompiling -- restarting safe_mysqld with --local-infile=1 I have also looked at the suggested fixes in Security issues with LOAD DATA LOCAL in the MySQL documentation at http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html which says among other things By default, all MySQL clients and libraries are compiled with --enable-local-infile, to be compatible with MySQL 3.23.48 and before. If that is true, and I didn't change the default the first time I compiled it, then using --local-infile=1 when starting safe_mysqld should enable LOAD DATA LOCAL INFILE, the way I read it. In any case after recompiling it should enable it. The web page also says If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client. See section 8.4.3.159 mysql_options(). That's a C function. Any way to call this from Perl? Michael Widenius [EMAIL PROTECTED] wrote: Did you give the above option to both 'mysqld' and 'mysql' ? How do I do it for mysql from inside a Perl script? Can you pass args through Perl DBI calls to this end, does anyone know? Or am I missing something more obvious? Tom Date: Mon, 04 Mar 2002 15:27:58 -0500 http://us.i1.yimg.com/us.yimg.com/i/space.gif From: Thomas Birchmire [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif Subject: Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49] http://us.i1.yimg.com/us.yimg.com/i/space.gif CC: [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire Michael Widenius [EMAIL PROTECTED] wrote: Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa [EMAIL PROTECTED] writes: Description: LOAD DATA LOCAL INFILE ... leads to 'The used command is not allowed with this MySQL version' This happens regardless of whether I have a local-infile = 1 in my /etc/my.cnf. In neither case does the local-infile variable show up with SHOW VARIABLES. We don't have a status variable that shows if this feature is enabled or not. The above is quite strange as I did test this properly before doing a release. Did you give the above option to both 'mysqld' and 'mysql' ? Which MySQL distribution are you using ? Sinisa Hi! Sinisa A new startup variable : Sinisa --local-infile Sinisa must be used to enable the above feature. That is what the user described that he did do... Regards, Monty - 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 Attachment: Forwarded Message From: Sinisa Milivojevic [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif Date: Tue, 5 Mar 2002 14:43:23 +0200 http://us.i1.yimg.com/us.yimg.com/i/space.gif To: [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] http://us.i1.yimg.com/us.yimg.com/i/space.gif Subject: Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49] http://us.i1.yimg.com/us.yimg.com/i/space.gif Thomas Birchmire writes: I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire loca-infile is not a variable. Therefore, you either start a server with --local-infile
RE: LOAD DATA LOCAL INFILE
[snip] Texas AM University,College Station, TX,1999,Design, install, test and run blah, blah.,Whatever Industry LOAD DATA INFILE 'd:\\text.csv' INTO TABLE projects FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\n'; [/snip] Have you tried this... LOAD DATA INFILE 'd:\\text.csv' INTO TABLE projects FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; HTH! Jay sql,query - 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: LOAD DATA LOCAL INFILE
[snip] Texas AM University,College Station, TX,1999,Design, install, test and run blah, blah.,Whatever Industry LOAD DATA INFILE 'd:\\text.csv' INTO TABLE projects FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\n'; Have you tried this... LOAD DATA INFILE 'd:\\text.csv' INTO TABLE projects FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; [/snip] Can you post a description of your table, DESCRIBE tablename? HTH! Jay sql,query - 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: [Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]]
Hi! Thomas == Thomas Birchmire [EMAIL PROTECTED] writes: Thomas I tried putting --local-infile in my server script and local-infile in the Thomas /etc/my.cnf file. I also placed a number or logging cammands in the startup Thomas script hoping to catch lower level messages. There appears to be little Thomas effect on anything. Thomas Regards, Tom Birchmire Thomas -- script and log stuff Thomas Startup Script using --local-infile Thomas #! /bin/sh Thomas cd /usr/local/mysql/ Thomas ./bin/safe_mysqld --user=mysql --local-infile --warnings --log-long-format Thomas MYSQL Session with error mysql select * from pet - ; Thomas +--++-+--++---+ Thomas | name | owner | species | sex | birth | death | Thomas +--++-+--++---+ Thomas | Puffball | Diane | hamster | f| 1999-03-30 | NULL | Thomas | Fluffy | Harold | cat | f| 1993-02-04 | NULL | Thomas +--++-+--++---+ Thomas 2 rows in set (0.03 sec) mysql load data local infile /home/tomb01/data/pet.txt into table pet; Thomas ERROR 1148: The used command is not allowed with this MySQL version mysql quit You also have to start 'mysql' with --local-infile for this to work. Regards, Monty - 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49]
Thomas Birchmire writes: I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire loca-infile is not a variable. Therefore, you either start a server with --local-infile or have in your /etc/my.cnf : [mysqld] local-infile -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: [Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]]
I tried putting --local-infile in my server script and local-infile in the /etc/my.cnf file. I also placed a number or logging cammands in the startup script hoping to catch lower level messages. There appears to be little effect on anything. Regards, Tom Birchmire -- script and log stuff Startup Script using --local-infile #! /bin/sh cd /usr/local/mysql/ ./bin/safe_mysqld --user=mysql --local-infile --warnings --log-long-format MYSQL Session with error mysql select * from pet - ; +--++-+--++---+ | name | owner | species | sex | birth | death | +--++-+--++---+ | Puffball | Diane | hamster | f| 1999-03-30 | NULL | | Fluffy | Harold | cat | f| 1993-02-04 | NULL | +--++-+--++---+ 2 rows in set (0.03 sec) mysql load data local infile /home/tomb01/data/pet.txt into table pet; ERROR 1148: The used command is not allowed with this MySQL version mysql quit Sinisa Milivojevic [EMAIL PROTECTED] wrote: Thomas Birchmire writes: I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire loca-infile is not a variable. Therefore, you either start a server with --local-infile or have in your /etc/my.cnf : [mysqld] local-infile -- - 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: [Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]]
Thomas Birchmire writes: I tried putting --local-infile in my server script and local-infile in the /etc/my.cnf file. I also placed a number or logging cammands in the startup script hoping to catch lower level messages. There appears to be little effect on anything. Regards, Tom Birchmire As I said, a fix will appear in 3.23.50, but if you build from source, this is a patch that fixes a problem : --- 1.160/configure.in Wed Feb 13 21:53:22 2002 +++ 1.161/configure.in Tue Mar 5 15:46:28 2002 @@ -591,6 +591,8 @@ [ ENABLED_LOCAL_INFILE=no ] ) +AC_SUBST(ENABLE_LOCAL_INFILE) + # Use Paul Eggerts macros from GNU tar to check for large file support. MYSQL_SYS_LARGEFILE --- 1.65/libmysql/libmysql.cWed Feb 13 19:09:26 2002 +++ 1.66/libmysql/libmysql.cTue Mar 5 15:46:28 2002 @@ -1022,7 +1022,7 @@ /* Only enable LOAD DATA INFILE by default if configured with - --with-enabled-local-inflile + --enable-local-infile */ #ifdef ENABLED_LOCAL_INFILE mysql-options.client_flag|= CLIENT_LOCAL_FILES; -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa [EMAIL PROTECTED] writes: Description: LOAD DATA LOCAL INFILE ... leads to 'The used command is not allowed with this MySQL version' This happens regardless of whether I have a local-infile = 1 in my /etc/my.cnf. In neither case does the local-infile variable show up with SHOW VARIABLES. We don't have a status variable that shows if this feature is enabled or not. The above is quite strange as I did test this properly before doing a release. Did you give the above option to both 'mysqld' and 'mysql' ? Which MySQL distribution are you using ? Sinisa Hi! Sinisa A new startup variable : Sinisa --local-infile Sinisa must be used to enable the above feature. That is what the user described that he did do... Regards, Monty - 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49]
I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire Michael Widenius [EMAIL PROTECTED] wrote: Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa [EMAIL PROTECTED] writes: Description: LOAD DATA LOCAL INFILE ... leads to 'The used command is not allowed with this MySQL version' This happens regardless of whether I have a local-infile = 1 in my /etc/my.cnf. In neither case does the local-infile variable show up with SHOW VARIABLES. We don't have a status variable that shows if this feature is enabled or not. The above is quite strange as I did test this properly before doing a release. Did you give the above option to both 'mysqld' and 'mysql' ? Which MySQL distribution are you using ? Sinisa Hi! Sinisa A new startup variable : Sinisa --local-infile Sinisa must be used to enable the above feature. That is what the user described that he did do... Regards, Monty - 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49
Rob Steele writes: Thanks. I don't think this feature is working as the documentation says it should. Section 4.2.4 of the manual says: By default all MySQL clients and libraries are compiled with | --enable-local-infile|, to be compatible with MySQL 3.23.48 and before. That seems to not be true, at least with the Aplha binary. Thanks, Rob thanks, we shall check it out !! -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: LOAD DATA LOCAL INFILE (Only loads 20,000 or 41,000 Records)
This worked for me: create table tblZips( id int unsigned not null auto_increment primary key, city char(24) not null default '', state char(2) not null default '', zip char(5) not null default '' ) Type=InnoDB; Saved CSV file as a UNIX (instad of DOS format file) LOAD DATA INFILE 'zips.csv' INTO TABLE tblZips FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n' (city,state,zip); Query OK, 41943 rows affected (3.86 sec) Records: 41943 Deleted: 0 Skipped: 0 Warnings: 0 mysql select min(id) AS first, max(id) AS last, count(id) as total FROM tblZips; +---+---+---+ | first | last | total | +---+---+---+ | 20973 | 62915 | 41943 | +---+---+---+ - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Sean O'Donnell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 5:00 PM To: MySQLMailingList Subject: LOAD DATA LOCAL INFILE (Only loads 20,000 or 41,000 Records) his is my first post here so please excuse any irrelevancies. I am trying to load a .csv file that contains 41943 Records of Data. I am only able to load 20972 Records. (it seems to skip almost every-other record). The file, located @: http://thisLinux.kicks-Ass.net/projects/databases/tbl_mapZipCodes.csv and contains 41943 lines that match the following comma-seperated structure: 'Pleasantville','NY','00401' 'Holtsville','NY','00501' 'Holtsville','NY','00544' Here is my query mysql LOAD DATA LOCAL INFILE '/usr/local/httpd/htdocs/projects/databases/tbl_ma - INTO TABLE RecruiterConsole.tbl_mapZipCodes - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '\'' - LINES TERMINATED BY '\n' - (mapCity,mapState,mapZip); Here are the Results Query OK, 20972 rows affected (1.56 sec) Records: 20972 Deleted: 0 Skipped: 0 Warnings: 41943 I have tried a few different trouble-shooting scenarios and none seem to work for me. Anybody have any suggestions or similar experiences? Thanks in advance. Sean O'Donnell Monrovia, CA. [EMAIL PROTECTED] http://dynamicwebdevelopers.com - 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: load data local infile
Yes the data is consistent firstname, lastname, address, city... The data is sent to me in eight different files every day. I want to import the data into a mysql database and set a field that is not included in the txt file. The files are sent to me already organized by region and would like to have load data local infile or the like, set a field for each region for each file as it is imported. - 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: load data local infile
Hi, Not quite sure what you mean, but you can use load data infile 'file' into table mytab (col1, col3, col2) to specify in to which columns the data should go. HTH Quentin -Original Message- From: Douglas Potter [mailto:[EMAIL PROTECTED]] Sent: Friday, 4 January 2002 12:27 p.m. To: [EMAIL PROTECTED] Subject: load data local infile I want to set a field for all of the records I import while importing using load data local infile. The field is not specified in the file I am importing. Is there a way to do it? Mysql database importing tab delimited file. - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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
