Re: LOAD DATA INFILE with space after quote but before comma
2013/12/18 11:07 -0500, Anthony Ball I ran across a curious issue, I'd call it a bug but I'm sure others would call it a feature. I have a csv file with space between the and , and it causes MySQL to eat that field and the field after it as a single field. Is there a setting I can use to remedy this or do I just have to make sure no whitespace intrudes? Well, strictly speaking, it is a bug, in your file. If you can keep that from happening that is best, because in a CSV file the quotemark may appear only first, last, or next to a separator, unless it quotes another quote-mark. Otherwise, if it is consistent as in Dhaval Jaiswal s (2), only do as he suggests. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LOAD DATA INFILE with space after quote but before comma
(1)
yes it is an issue even i faced. for the remedy i search the {( ,) (,)}
values of , space between and , replaced by , in .csv itself.
(2)
The other way is, if all the values are like space between , then you can
use space and , in fields terminated by
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE testa FIELDS TERMINATED
BY ' ,' OPTIONALLY
ENCLOSED BY '';
(3) convert the .csv in insert statement and you can use mysqlimport.
On Wed, Dec 18, 2013 at 9:37 PM, Anthony Ball a...@suave.net wrote:
I ran across a curious issue, I'd call it a bug but I'm sure others would
call it a feature.
I have a csv file with space between the and , and it causes MySQL to eat
that field and the field after it as a single field. Is there a setting I
can use to remedy this or do I just have to make sure no whitespace
intrudes?
Here is an example:
testa ,testb
create temporary table testa (a char(15), b char(5)); LOAD DATA LOCAL
INFILE '/tmp/test.csv' INTO TABLE testa FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '';
Data in table is
mysql select * from testa;
++--+
| a | b|
++--+
| testa ,testb | NULL |
++--+
--
Regards
Dhaval
RE: Load Data Infile Errors
The answer is 3 =) With myisam tables, you can have partially complete statements. That is if you get an error, all rows handled before the error are still in the table. With innodb, an error generates a rollback and your table is returned to its state before the statement was run. To find the actual number of rows processed when using REPLACE or IGNORE, see the ROW_COUNT() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count Regards, Gavin Towey -Original Message- From: James W. McKelvey [mailto:james.w.mckel...@jpl.nasa.gov] Sent: Monday, October 25, 2010 12:16 PM To: mysql@lists.mysql.com Subject: Load Data Infile Errors Hello, I have a question about the execution cycle of LOAD DATA INFILE. If I issue a large file via LDI LOCAL, I know that the file is copied to the MySQL server and executed there. But at what point does the statement finish from the sender's point of view? 1) When the file is successfully copied? 2) When the file is copied and parsed? 3) When the file is completely processed? I'm guessing 2). The reason for asking is to determine what errors may be returned and how I can deal with them. Is it possible for the file to be partially processed, say, inserting the first half of the rows? If 2) or 3), I would say no (barring some serious server error). Since LOCAL implies IGNORE, is there any way to get the number of ignored rows? What about replace? Ultimately I want to know under what conditions I should reissue the file, and whether or not that could introduce duplicate entries for tables with non-unique keys. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Load Data Infile quirk
At 05:40 AM 10/18/2009, John wrote: Mike, What behaviour you experience depends to some extent on what storage engine you are using and on what other non-unique indexes you have on the tables. With LOAD DATA INFILE on empty MyISAM tables all non-unique indexes are created in a separate batch which makes it much faster if you have a lot of indexes. Ok, I thought that ALL indexes would be rebuilt later, including my primary index, and one unique index I have on the table. I must have misread that in the manual. Thanks. From memory you can create the indexes faster by turning them off with 'ALTER TABLE tablename DISABLE KEYS' before the 'LOAD DATA INFILE' command and then using 'ALTER TABLE tablename ENABLE KEYS' to re-create the indexes after the LOAD DATA INFILE completes. But Disable Keys has no affect on primary or unique indexes. So the only way for me to speed this up on loading data into empty tables is to remove all indexes and build them after the data has been loaded. That should save me 30% on the load times. Mike Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: 17 October 2009 22:49 To: mysql@lists.mysql.com Subject: Load Data Infile quirk I'm trying to speed up Load Data Infile and after some experimenting have noticed this qwirk. BTW, all of the tables used below are empty and have identical table structures. The value being loaded into the primary key column is 'NULL'. Test1: 246 seconds to run Load Data Infile into a table (Table1) with 1 primary autoinc column, and 2 compound keys. Test2: 69 seconds to run Load Data Infile into similar table (Table2) with no keys 111 seconds to rebuild the missing keys in Table2 69+111=180 seconds for Table2 compared to 246 seconds for Table1. Now I thought when using Load Data Infile on an empty table it would rebuild *all* of the keys AFTER the data has been loaded. This may not be the case. I suspect the extra time for Test1 is caused by the Load Data building the primary key as the data is being loaded. Can someone confirm this? If so, then when loading data into an empty table, it is always going to be faster to remove the keys then load the data, then add the keys. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.422 / Virus Database: 270.14.20/2441 - Release Date: 10/16/09 18:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Load Data Infile quirk
Mike, What behaviour you experience depends to some extent on what storage engine you are using and on what other non-unique indexes you have on the tables. With LOAD DATA INFILE on empty MyISAM tables all non-unique indexes are created in a separate batch which makes it much faster if you have a lot of indexes. From memory you can create the indexes faster by turning them off with 'ALTER TABLE tablename DISABLE KEYS' before the 'LOAD DATA INFILE' command and then using 'ALTER TABLE tablename ENABLE KEYS' to re-create the indexes after the LOAD DATA INFILE completes. Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: 17 October 2009 22:49 To: mysql@lists.mysql.com Subject: Load Data Infile quirk I'm trying to speed up Load Data Infile and after some experimenting have noticed this qwirk. BTW, all of the tables used below are empty and have identical table structures. The value being loaded into the primary key column is 'NULL'. Test1: 246 seconds to run Load Data Infile into a table (Table1) with 1 primary autoinc column, and 2 compound keys. Test2: 69 seconds to run Load Data Infile into similar table (Table2) with no keys 111 seconds to rebuild the missing keys in Table2 69+111=180 seconds for Table2 compared to 246 seconds for Table1. Now I thought when using Load Data Infile on an empty table it would rebuild *all* of the keys AFTER the data has been loaded. This may not be the case. I suspect the extra time for Test1 is caused by the Load Data building the primary key as the data is being loaded. Can someone confirm this? If so, then when loading data into an empty table, it is always going to be faster to remove the keys then load the data, then add the keys. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.422 / Virus Database: 270.14.20/2441 - Release Date: 10/16/09 18:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LOAD DATA INFILE Syntax error
Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); On Mon, Jun 29, 2009 at 7:07 AM, Ralph Kutschera news2...@ecuapac.dyndns.org wrote: Hallo List! I have a CVS file which i would like to import to MySQL. The file header and an example: | Page,Device,Group,ItemID,Item,Value | Overview,General,Computer,513,OS,Linux The table has: | Create Table: CREATE TABLE `table` ( | `ID` int(11) NOT NULL auto_increment, | `Page` varchar(128) default NULL, | `Device` varchar(128) default NULL, | `Group` varchar(128) default NULL, | `ItemID` varchar(128) default NULL, | `Item` varchar(128) default NULL, | `Value` varchar(128) default NULL, | PRIMARY KEY (`ID`) | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So I would like to import the first file field to the second table field, the second file field to the third table,... Just to have an index. I'm using: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, GROUP , ItemID, Item, Value); which gives me: | #1064 - You have an error in your SQL syntax; check the manual that | corresponds to your MySQL server version for the right syntax to use | near 'Group, ItemID, Item, Value)' at line 2 I cannot find the error. Please help me! MySQL version = 5.0.32-Debian_7etch8-log TIA, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LOAD DATA INFILE Syntax error
Johnny Withers schrieb: Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); Ooookay. Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Load data infile
LOAD DATA LOCAL INFILE http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://www.mysql.com/news-and-events/newsletter/2002-05/a12.html -Original Message- From: Velen [mailto:[EMAIL PROTECTED] Sent: Thursday, May 22, 2008 2:24 PM To: mysql@lists.mysql.com Subject: Load data infile Hi, I would like to know if I can use the Load data infile to update a table on the server from a workstation? I tried it but was unsuccessful. Is there any other way to do this from a workstation? Thanks. Regards, Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Hi, Try this. set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; regards anandkl On 10/29/07, Dušan Pavlica [EMAIL PROTECTED] wrote: Are you sure your file is coded in utf8? Character set of your file must be same as charset of your database. Dusan Caleb Racey napsal(a): Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Are you sure your file is coded in utf8? Character set of your file must be same as charset of your database. Dusan Caleb Racey napsal(a): Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Ananda Kumar wrote: Hi, Try this. set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; Rather: set session collation_database=utf8_general_ci; set session character_set_database=utf8; Also, make sure you have these in my.cnf: [client] default-character-set=utf8 [mysqld] default-character-set=utf8 init-connect='SET NAMES utf8' And you might want to add these also: collation_server=utf8_unicode_ci character_set_server=utf8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust If you haven't already, issue this first: SET CHARACTER SET utf8; SET NAMES utf8; LOAD DATA INFILE ... b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: Caleb Racey wrote: It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron Thanks for the suggestion I'm afraid i get the same behaviour when i try this approach accented values still truncate at the accent when i use load data infile. OK, the next suggestion is to use the 'binary' character set. By the way, I accidentally omitted quotes above; I should have typed SET character_set_database='utf8'; You should verify your connection's character sets with SHOW VARIABLES LIKE '%character%'; Try it with 'binary' and see if that works. Baron thanks again I'm afraid 'binary' achieved the same result as did using utf8 with quotes. I'm out of ideas, then. I wish I could help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
At 01:08 PM 6/27/2007, you wrote: Hi, I have a question on LOAD DATA INFILE command. I have a table with 10 columns. Is it possible to update only few columns of this table using LOAD DATA INFILE? No. As you found out Load Data loads the specified columns and sets the other columns to NULL. The only way around this is to write the current column values that you want to save back out to the text file. Or load the data into a second temporary table with just the columns you need, and then do an Update to the original table (assuming of course all of the data in the text file are updates and not new rows otherwise you'll then have to join the original table with the temp table to find the new rows then add those to the original table.) Whew! Mike (If programming was easy, everyone would be doing it.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
Or, Load the data into a temp , and create necessary index on the required columns, Write a stored proc to update columns in the original table with values from the temp table based on key columns joins between both tables regards anandkl On 6/28/07, mos [EMAIL PROTECTED] wrote: At 01:08 PM 6/27/2007, you wrote: Hi, I have a question on LOAD DATA INFILE command. I have a table with 10 columns. Is it possible to update only few columns of this table using LOAD DATA INFILE? No. As you found out Load Data loads the specified columns and sets the other columns to NULL. The only way around this is to write the current column values that you want to save back out to the text file. Or load the data into a second temporary table with just the columns you need, and then do an Update to the original table (assuming of course all of the data in the text file are updates and not new rows otherwise you'll then have to join the original table with the temp table to find the new rows then add those to the original table.) Whew! Mike (If programming was easy, everyone would be doing it.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile and newlines
Mark Nienberg wrote: I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; Any help appreciated. Mark You need to quote the actual linefeed character, not a backslash n. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile and newlines
Gerald L. Clark wrote: Mark Nienberg wrote: I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; You need to quote the actual linefeed character, not a backslash n. OK, I wrote a little perl script to replace \n with a real newline character and now it works, even without adding a backslash in front of it. I don't know why it used to work without this. Maybe the older version of phpmyadmin I was using did some kind of automatic conversion before inserting. Thanks for your help. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE and BIT columns
Hi Julie,
If you notice after your import, you have 3 warnings. This intrigued
me, so I created a test case (also running 5.0.18 standard):
create table bit_test (b bit(8));
cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
02
mysql load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 5
Hrm. I got 5 warnings; you'd only gotten 2. Weird!
mysql show warnings;
+-+--+-+
| Level | Code | Message |
+-+--+-+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+-+--+-+
5 rows in set (0.02 sec)
What this says to me is that the values were too big, for all but row 2.
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
+--+
6 rows in set (0.05 sec)
so the values make sense -- the values were larger than the
largest value, so it truncated it to the largest value. But why, when
I insert a 2, does it use 11010 instead of 10?
Let's test:
mysql insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 10 |
+--+
7 rows in set (0.00 sec)
That makes sense! the last value is 10, which makes sense for a
binary value of 2. On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:
mysql insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 10 |
| 110010 |
+--+
8 rows in set (0.01 sec)
Aha! the culprit -- it was thinking that the 2 in the file was a
string, not an int.
Hope this helped,
-Sheeri
On 2/24/06, Julie Kelner [EMAIL PROTECTED] wrote:
Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into
tables that have BIT(8) columns. No matter
what format I use, the result is not what I expect (see example below.)
Anyone know how to properly format the data for loading into a BIT column?
Thanks!
$ cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
02
mysql create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)
mysql load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 3 warnings (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
+--+
6 rows in set (0.00 sec)
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE and BIT columns
Sheeri,
Wow. That was my first forum email and I thought it would go unnoticed. I
sure was wrong.
You are exactly right, because apparently with LOAD DATA INFILE, everything
in the file is treated as a string. I'm using PHP to create the text file,
so I tried PHP's pack() function to write '2' as binary data. And...it
worked!
Thanks so much for your input.
~ Julie
- Original Message -
From: sheeri kritzer [EMAIL PROTECTED]
To: Julie Kelner [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, February 27, 2006 12:50 PM
Subject: Re: LOAD DATA INFILE and BIT columns
Hi Julie,
If you notice after your import, you have 3 warnings. This intrigued
me, so I created a test case (also running 5.0.18 standard):
create table bit_test (b bit(8));
cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
02
mysql load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 5
Hrm. I got 5 warnings; you'd only gotten 2. Weird!
mysql show warnings;
+-+--+-+
| Level | Code | Message |
+-+--+-+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+-+--+-+
5 rows in set (0.02 sec)
What this says to me is that the values were too big, for all but row 2.
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
+--+
6 rows in set (0.05 sec)
so the values make sense -- the values were larger than the
largest value, so it truncated it to the largest value. But why, when
I insert a 2, does it use 11010 instead of 10?
Let's test:
mysql insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 10 |
+--+
7 rows in set (0.00 sec)
That makes sense! the last value is 10, which makes sense for a
binary value of 2. On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:
mysql insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)
mysql select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 10 |
| 110010 |
+--+
8 rows in set (0.01 sec)
Aha! the culprit -- it was thinking that the 2 in the file was a
string, not an int.
Hope this helped,
-Sheeri
On 2/24/06, Julie Kelner [EMAIL PROTECTED] wrote:
Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into
tables that have BIT(8) columns. No matter
what format I use, the result is not what I expect (see example below.)
Anyone know how to properly format the data for loading into a BIT column?
Thanks!
$ cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
02
mysql create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)
mysql load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 3 warnings (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
mysql select bin(b+0) from bit_test
RE: LOAD DATA INFILE (SOLVED!)
For any of you that run into this problem I found this: After receiving some help from a friend it turns out that one of the directories within the path to the target destination was not flagged 755, and because of that one ( which happened to be 3 levels back) it didn't allow mysql to properly execute what it needed. So if you run into this simply check each directory within the path and make sure of it's settings -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Wed 1/11/2006 11:17 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13)
Re: LOAD DATA INFILE
Do chmod -R 755 on datapath Thanks Praj - Original Message - From: Jay Paulson (CE CEN) [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 11, 2006 10:47 PM Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE Syntax
Hi Elliot,
It is in the docs, just a little bit further down the page 8-)
LOCAL works only if your server and your client both have been enabled
to allow it. For example, if mysqld was started with --local-infile=0,
then LOCAL does not work. See Section 5.6.4, Security Issues with LOAD
DATA LOCAL.
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: Elliot Kleiman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 13 December 2005 8:12 AM
To: mysql@lists.mysql.com
Subject: LOAD DATA INFILE Syntax
Hi mysql-list,
I just installed,
++
| version() |
++
| 5.0.16-log |
++
Here is what I am testing out: (simple table and data)
% echo 'a b c d' testfile
mysql CREATE TABLE `test` (
- `fe` VARCHAR( 2 ),
- `fi` VARCHAR( 2 ),
- `fo` VARCHAR( 2 ),
- `fum` VARCHAR( 2 )
- );
Query OK, 0 rows affected (0.00 sec)
mysql load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version
Q: What am I doing wrong(missing)?
{ I did not see it in the documentation online }
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Thanks, Elliot
P.S.
The same command works fine in previous versions of MySQL.
__
WizardsWorks Cluster
http://www.wizardsworks.org/
--
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 INFILE (url)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Arno Coetzee wrote: Test USER wrote: Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu it is possible . you have to write your own script which fetches the file (using wget) and placing the file in a directory where it can be accessed. then you can load that file. i currently have something like this running... i make a dump file on the remote server runing a script with crontab. i gzip that file just to make the file transfer faster . the local server then fetches the file (wget) and gunzips it and then i load the file. works pretty good. The JDBC driver, and the mysqli interface for PHP support this for LOAD DATA LOCAL INFILE, check the documentation for each if this is an option for you. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDlGOEtvXNTca6JD8RAlGyAJwNUa73auCDBjIyib7ikIBrUb1vsQCfUPV5 eoaYiyOOX4T3YVEuOrjj+9A= =/wQd -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 INFILE (url)
I have never seen this. Mysql would have to do a wget of the file then dump it. Last I knew it wasn't a web browser. There may be a way to do the wget inline though, or at least write something in shell or perl to do it. Is this cron'd or something, or a one time thing? - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Test USER [EMAIL PROTECTED] Sent: 12/01/2005 04:55 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE (url) Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- 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 INFILE (url)
Hi thanks for your answer! I would like this to be done via cron 3-4 times a day. Quoting Peter J Milanese [EMAIL PROTECTED]: I have never seen this. Mysql would have to do a wget of the file then dump it. Last I knew it wasn't a web browser. There may be a way to do the wget inline though, or at least write something in shell or perl to do it. Is this cron'd or something, or a one time thing? - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Test USER [EMAIL PROTECTED] Sent: 12/01/2005 04:55 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE (url) Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE (url)
Test USER wrote: Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu it is possible . you have to write your own script which fetches the file (using wget) and placing the file in a directory where it can be accessed. then you can load that file. i currently have something like this running... i make a dump file on the remote server runing a script with crontab. i gzip that file just to make the file transfer faster . the local server then fetches the file (wget) and gunzips it and then i load the file. works pretty good. -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile fails to put entire PDF into one record
Gleb Paharenko wrote:
Hello.
Are you sure that you want to load PDF with LOAD DATA INFILE?
Well, I had been sure, but not so much anymore. s
Maybe LOAD_FILE is that you want:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Yup, this was the ticket. I'd seen several references to PDFs and LOAD
DATA INFILE. But this works great:
I'm using FoxPro to convert DBF data to MySQL, and have PDFs in FoxPro
memo fields. Here's (for the benefit of the archives) what I did (this
is xbase code):
* process each record in a DBF
scan
* job_no is a unique value for a record
m.lcPDF = load_file(' + curdir() + alltrim(job_no) + .pdf + ')
* convert single backslashes to doubles - else you get a
* Can't get stat of 'test.txt' (Errcode: 2) error
m.lcPDF = strtran(m.lcPDF, \, \\)
* create a string that consists of the INSERT command
m.lcStrInsert = [insert into COMPLETE (job_no, pdffile) values ] ;
+ [(']+m.lcJob_no + [',] + m.lcPDF + [)]
* use sql passthrough to execute the command and return a code
* indicating success or failure
m.liSuccess = sqlexec(m.liHandle, m.lcStrInsert)
* (more code here, but removed for clarity)
endscan
Thanks,
Whil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile fails to put entire PDF into one record
Hello. Are you sure that you want to load PDF with LOAD DATA INFILE? Maybe LOAD_FILE is that you want: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Whil Hentzen wrote: Hi folks, I want to load a set of PDFs into a MySQL 5 ISAM table. I'm using the following command LOAD DATA INFILE '1037021.pdf' INTO TABLE complete FIELDS TERMINATED BY '%%EOF' (d_c) in the Query Browser 1.1.17, MySQL 5.0.15, running on W2K. Field d_c is defined as LONGTEXT. The following bad things happen: 1. The PDF gets loaded into 553 separate rows, instead of just one record. 2. I get an error in the Query Browser (bottom pane, under the Result Set pane) after completion: Row 42 was truncated; it contained more data than there were input columns (error 1262). 3. If I change column d_c to be TEXT or BLOB, I get the same errant results. 4. If I remove the FIELDS TERMINATED BY clause, I get the following error repeated 64 times between rows 48 and 176, where nn runs from 48 to 176: Row nn was truncated; it contained more data than there were input columns (error 1262). I've done a search on the list archive through the beginning of the year and only found one set of three messages relating to LOAD DATA INFILE failing for loading PDFs (and Word and XML), but the author later solved it himself without telling us what he was doing wrong :(. Suggestions? Have I a big concept problem or just a little syntax one? Whil -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 INFILE and SET REPLACE
At 23:46 +0100 11/2/05, John thegimper wrote: Why cant i get this to work? The name dont get replaced... :P LOAD DATA LOCAL INFILE '/tmp/myfile.txt' INTO TABLE cache FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES (name) SET name = REPLACE(name, 'Coke', 'Pepsi'); Please file a bug report with a repeatable test case at http://bugs.mysql.com -- thanks. -- 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: load data infile
Hello. my mysql version is 4.2.2 Are you sure that you have this version? I strongly recommend you to use the latest release (4.1.15 now). The used command is not allowed with this MySQL version Have a look here: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html 'Yemi Obembe wrote: i'm having a problem with the load data infile command...i'm actually running the script from a php script that goes this way: ?php mysql_connect(localhost, $user, $pass); mysql_select_db(ng); $sql = load data local infile 'movies.txt' into table dir fields terminated by '|'; if($res = mysql_query($sql)) echo loaded; else echo mysql_error(); ? the script and the txt file movies.txt reside on the same directory. runnign the page, i get the error: The used command is not allowed with this MySQL version my mysql version is 4.2.2 what could be wrong? by the way, i'v tried changing the movies file path to a full path but still doesnt change things. - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Yahoo! Music Unlimited - Access over 1 million songs. Try it free. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 infile
Yemi, For a first debugging step, have php print out the $sql variable to a screen. Then copy and paste that to a mysql client, and make sure you get the same error. That's good to debug any script -- make sure the query that the script is doing can actually run. Sometimes it's in how php parses scripts and such. -Sheeri On 10/20/05, 'Yemi Obembe [EMAIL PROTECTED] wrote: i'm having a problem with the load data infile command...i'm actually running the script from a php script that goes this way: ?php mysql_connect(localhost, $user, $pass); mysql_select_db(ng); $sql = load data local infile 'movies.txt' into table dir fields terminated by '|'; if($res = mysql_query($sql)) echo loaded; else echo mysql_error(); ? the script and the txt file movies.txt reside on the same directory. runnign the page, i get the error: The used command is not allowed with this MySQL version my mysql version is 4.2.2 what could be wrong? by the way, i'v tried changing the movies file path to a full path but still doesnt change things. - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Yahoo! Music Unlimited - Access over 1 million songs. Try it free. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
I've been over that page before posting, with no luck. It might be an obvious error in syntax, but I can't figure it out. Jason On 9/26/05, Robert L Cochran [EMAIL PROTECTED] wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason
Re: LOAD DATA INFILE Syntax Error
Okay, now I get it. I was using the 4.1 series. Looks like an upgrade is in order. Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data infile
Hi, I have a file where the data looks like this: 1,23,345,45; 34,4,444,1er; I am then trying to load that data using the load data local infile and this statement: Load data local infile '/httpd/htdocs/sql/loader.sql' into table vehicles fields terminated by ',' enclosed by ' lines terminated by ';' I guess that should be: Load data local infile '/httpd/htdocs/sql/loader.sql' into table vehicles fields terminated by ',' enclosed by '' lines terminated by 'X'; Where 'X' is '\n' when you're on a UNIX/Linux system, '\r' when on Windows, or '\r\n' when on MacOS. HTH. -- Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile
Hallo, Op 30 Jun 05 schreef blackwater dev aan mysql@lists.mysql.com: bd 1,23,345,45; bd 34,4,444,1er; bd Load data local infile '/httpd/htdocs/sql/loader.sql' into table bd vehicles fields terminated by ',' enclosed by ' lines terminated by bd ';' I think it should be: enclosed by '' Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
Wrong path, you are referring to an uri, not a path. Way off topic to starting explaining basic file system stuff here. You should be the same path you used when your uploaded the file. Something like: /home/chris/datafile.txt Frank At 10:06 PM 6/7/05, Chris wrote: Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- 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 INFILE - what is the path to file?
Chris wrote: Thank you for your detailed response. You're welcome. It seems my problem is trying to define the path to my data file and this is where I seem to be missing something. No, the problem is permissions. As I explained previously, a relative path (one without a leading /) means a location under mysql's data directory, not a location relative to your script. That's why you get a No such file or directory error when you tried a relative path. Hence, to load a file not under mysql's data directory, you must use an absolute path (one starting with /), as you did originally. Note that with the absolute path, you got a Permission denied error, not a No such file or directory error. Mysql could not read the path you gave it. Permissions on all directories in the path are by default set to 755 except for the director at the top of the directories in my hosting account public_html which is set to 750. As I said, *every* directory in the path must be readable by mysql. Your top directory has 750 permissions, so, unless it is owned by user mysql or in the mysql group, mysql cannot access it. Either set it to 751, or change it to group mysql. If I look at: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which works from my tmp directory. The tmp directory is at the same level as public_html and has permissions 700. I'm sorry, but I just don't believe that. Permissions are user, group, world, in that order, so 700 means only the owner can access /tmp. If mysql can access /tmp, either /tmp is owned by mysql, which would be strange, or it doesn't have 700 permissions. Do this cd / ls -aFl and include the lines for . and tmp and public_html in your next post. So I guess I don't know why I can't specify the location of my data file from '/public_html/path_to_my_file/datafile.txt' You can, if you can fix the permissions. Thanks, Chris P.S. It might be a good idea `man chmod` to review file permissions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it. i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
hi, yes you did it and i failed to do so. thats why i am here. i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves only innodb as i mentioned in the very first post, i have read docs and it says that there are 3 ways i can start a transaction and i tried all three i.e. begin work statement, start transaction and SET AUTOCOMMIT=0; regards - Original Message - From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 11:08 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it. i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
this is strange, when i do this..
===
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql insert into tbltemp (tmp_crdd_no,tmp_serial_no,tmp_date)
values('100','100',NOW());
Query OK, 1 row affected (0.00 sec)
mysql select * from tbltemp;
++--+---+--+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++--+---+--+
| 1 | 100| 100| 2005-06-08
03:16:58 |
++--+---+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.01 sec)
mysql select * from tbltemp;
Empty set (0.01 sec)
=
all is ok but when i load the file it autocommits.. example below
=
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql LOAD DATA INFILE '/usr/local/apache2/htdocs/test.csv' INTO TABLE
tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(tmp_crdd_no,tmp_serial_no,tmp_date);
Query OK, 47 rows affected (0.01 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
i also checked server status variables before starting and after ending
transaction. everything looks fine but cant seem to figure out what is
heppeing here.i even upgraded to latest version, any help will be
appriciated thanks in advance
Haseeb Iqbal
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 1:21 PM
Subject: Re: LOAD DATA INFILE with INNODB
hi,
yes you did it and i failed to do so. thats why i am here.
i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves
only
innodb as i mentioned in the very first post,
i have read docs and it says that there are 3 ways i can start a
transaction
and i tried all three i.e. begin work statement, start transaction and SET
AUTOCOMMIT=0;
regards
- Original Message -
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 11:08 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
i did it. If you have myisam tables tables rather than innodb say it.
if you have specific os, say it.
i think you should elaborate, or read carrefully dev.mysql.com/doc
Mathias
Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]:
i used start transaction before using SET AUTOCOMMIT=0; also i dont
see
any
difference between the two. if there is please elaborate.
- Original Message - .
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 3:17 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
you transaction is implicit, so there has been an autocommit.
Look at this example !
mysql start transaction;
^^
mysql load data infile 'd:\\ldfile.txt' into table ldfile
Re: LOAD DATA INFILE - still confused
yes he is right i am doing the same. and giving full path. i have other problems with load data infile :( - Original Message - From: Frank Bax [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 08, 2005 1:39 AM Subject: Re: LOAD DATA INFILE - still confused At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
as it turns out it was a MYSQL BUG
http://bugs.mysql.com/bug.php?id=11151error=no
thanks anyway
regards Haseeb
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 08, 2005 3:32 AM
Subject: Re: LOAD DATA INFILE with INNODB
this is strange, when i do this..
===
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql insert into tbltemp (tmp_crdd_no,tmp_serial_no,tmp_date)
values('100','100',NOW());
Query OK, 1 row affected (0.00 sec)
mysql select * from tbltemp;
++--+---+--+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++--+---+--+
| 1 | 100| 100| 2005-06-08
03:16:58 |
++--+---+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.01 sec)
mysql select * from tbltemp;
Empty set (0.01 sec)
=
all is ok but when i load the file it autocommits.. example below
=
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql LOAD DATA INFILE '/usr/local/apache2/htdocs/test.csv' INTO TABLE
tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(tmp_crdd_no,tmp_serial_no,tmp_date);
Query OK, 47 rows affected (0.01 sec)
Records: 47 Deleted: 0 Skipped: 0 Warnings: 0
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
mysql select * from tbltemp limit 10;
++-+---+-+
| tmp_id | tmp_crdd_no | tmp_serial_no | tmp_date|
++-+---+-+
| 2 | 415162501 | 7198108197| 9/10/2003 12:00 |
| 3 | 415162502 | 4403223482| 8/31/2003 12:17 |
| 4 | 415162503 | 4438355415| 8/31/2003 20:11 |
| 5 | 415162504 | 5023027348| 8/31/2003 14:22 |
| 6 | 415162505 | 8090096387| 9/2/2003 22:38 |
| 7 | 415162506 | 8192661837| 9/1/2003 16:42 |
| 8 | 415162507 | 2746612555| 8/27/2003 22:06 |
| 9 | 415162508 | 8789620243| 9/1/2003 22:42 |
| 10 | 415162509 | 5422205728| 8/27/2003 20:05 |
| 11 | 415162510 | 8760612473| 8/24/2003 14:40 |
++-+---+-+
10 rows in set (0.01 sec)
i also checked server status variables before starting and after ending
transaction. everything looks fine but cant seem to figure out what is
heppeing here.i even upgraded to latest version, any help will be
appriciated thanks in advance
Haseeb Iqbal
- Original Message -
From: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 1:21 PM
Subject: Re: LOAD DATA INFILE with INNODB
hi,
yes you did it and i failed to do so. thats why i am here.
i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves
only
innodb as i mentioned in the very first post,
i have read docs and it says that there are 3 ways i can start a
transaction
and i tried all three i.e. begin work statement, start transaction and
SET
AUTOCOMMIT=0;
regards
- Original Message -
From: [EMAIL PROTECTED]
To: °l||l° Jinxed °l||l° [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, June 07, 2005 11:08 AM
Subject: Re: LOAD DATA INFILE with INNODB
Hi,
i did it. If you have myisam tables tables rather than innodb say it.
if you have specific os, say it.
i think you should elaborate, or read carrefully dev.mysql.com/doc
Mathias
Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]:
i used start transaction before using SET AUTOCOMMIT=0; also i dont
see
any
difference between the two. if there is please elaborate.
- Original Message
Re: LOAD DATA INFILE - still confused
Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - what is the path to file?
Thank you for your detailed response. It seems my problem is trying to define the path to my data file and this is where I seem to be missing something. Permissions on all directories in the path are by default set to 755 except for the director at the top of the directories in my hosting account public_html which is set to 750. If I look at: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which works from my tmp directory. The tmp directory is at the same level as public_html and has permissions 700. So I guess I don't know why I can't specify the location of my data file from '/public_html/path_to_my_file/datafile.txt' Thanks, Chris Michael Stassen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Chris wrote: I have been using LOAD DATA INFILE to load an ASCII data file into my database. The datafile is uploaded to the server temp area and the name of the file is passed to LOAD DATA INFILE query like: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I now want to load data using LOAD DATA INFILE from a data file located within my http_public directory. I can create a path to the file from my DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this error: (NOTE: I set the file permissions to 777) Don't do that, it's horribly insecure. Anybody could modify this file before mysql loads it. You should probably never make a file world-writable. Mysql only needs to read the file, so set the permissions to 744. Better yet, make it owned by the mysql group, and set permissions to 740. Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) ~: perror 13 OS error code 13: Permission denied In order to read the file, the mysql user must have read permission on the file (you've done that), and must have execute permission on every directory in the path to the file. So, for mysql to read /home/path/to/file/datafile.txt, you will need to set permissions of 711 on /home, /home/path, /home/path/to, and /home/path/to/file, in addition to the 744 permissions on datafile.txt. Now if I just create a query like: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I get this error File './my_database_name/datafile.txt' not found (Errcode: 2) ~: perror 2 OS error code 2: No such file or directory Which seems to tell me that LOAD DATA INFILE is looking for my data file in a location that is outside my hosting account. I just have an account with a shared hosting service provider. Without a leading /, the path is treated as a relative path -- relative to the server's data directory. Your file isn't there, hence the error. So how would I specify a path to a file that is outside the directory where my database is located? With a full path, as you did originally. You just have to make sure mysql has all the permissions neede to access it. OBSERVATION: It appears the tmp directory must be in the database path because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE. No, /tmp works because it (usually) has 1777 permissions, so mysql has the necessary execute permission to access /tmp's contents. Thanks for replies, Chris Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - what is the path to file?
Hi, load data infile tries to load data from specified directory on the server. if your file is on the client, try LOAD DATA LOCAL and verify the parameter --enable-local-infile more details are in http://dev.mysql.com/doc/mysql/en/load-data-local.html about data on the web server. Nota bene : *** D:\perror 13 OS error code 13: Permission denied You have an os permission eroor. Mathias Selon Chris [EMAIL PROTECTED]: I have been using LOAD DATA INFILE to load an ASCII data file into my database. The datafile is uploaded to the server temp area and the name of the file is passed to LOAD DATA INFILE query like: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I now want to load data using LOAD DATA INFILE from a data file located within my http_public directory. I can create a path to the file from my DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this error: (NOTE: I set the file permissions to 777) Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) Now if I just create a query like: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I get this error File './my_database_name/datafile.txt' not found (Errcode: 2) Which seems to tell me that LOAD DATA INFILE is looking for my data file in a location that is outside my hosting account. I just have an account with a shared hosting service provider. So how would I specify a path to a file that is outside the directory where my database is located? OBSERVATION: It appears the tmp directory must be in the database path because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE. Thanks for replies, Chris -- 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 INFILE - what is the path to file?
Chris wrote: I have been using LOAD DATA INFILE to load an ASCII data file into my database. The datafile is uploaded to the server temp area and the name of the file is passed to LOAD DATA INFILE query like: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I now want to load data using LOAD DATA INFILE from a data file located within my http_public directory. I can create a path to the file from my DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this error: (NOTE: I set the file permissions to 777) Don't do that, it's horribly insecure. Anybody could modify this file before mysql loads it. You should probably never make a file world-writable. Mysql only needs to read the file, so set the permissions to 744. Better yet, make it owned by the mysql group, and set permissions to 740. Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) ~: perror 13 OS error code 13: Permission denied In order to read the file, the mysql user must have read permission on the file (you've done that), and must have execute permission on every directory in the path to the file. So, for mysql to read /home/path/to/file/datafile.txt, you will need to set permissions of 711 on /home, /home/path, /home/path/to, and /home/path/to/file, in addition to the 744 permissions on datafile.txt. Now if I just create a query like: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I get this error File './my_database_name/datafile.txt' not found (Errcode: 2) ~: perror 2 OS error code 2: No such file or directory Which seems to tell me that LOAD DATA INFILE is looking for my data file in a location that is outside my hosting account. I just have an account with a shared hosting service provider. Without a leading /, the path is treated as a relative path -- relative to the server's data directory. Your file isn't there, hence the error. So how would I specify a path to a file that is outside the directory where my database is located? With a full path, as you did originally. You just have to make sure mysql has all the permissions neede to access it. OBSERVATION: It appears the tmp directory must be in the database path because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE. No, /tmp works because it (usually) has 1777 permissions, so mysql has the necessary execute permission to access /tmp's contents. Thanks for replies, Chris Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile. Empty input field - integer. How make NULL ?
Hi, If you let a tabulation, you will have 0 for numbers. supposing this is the file tab.txt : a 1 c a c mysql load data infile c:/tab.txt into table tab fields terminated by \t enclosed by \ lines terminated by \r\n; Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 1 mysql select * from tab; +--+--+--+ | a| b| c| +--+--+--+ | a|1 | c| | a|0 | c| +--+--+--+ 2 rows in set (0.00 sec) IF you want to handle NULL values, you must change empty values to \N : *** new tab.txt a 1 c a \N c mysql load data infile c:/tab.txt into table tab fields terminated by \t enclosed by \ lines terminated by \r\n; Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from tab; +--+--+--+ | a| b| c| +--+--+--+ | a|1 | c| | a| NULL | c| +--+--+--+ 2 rows in set (0.00 sec) Mathias Selon Pete Lancashire [EMAIL PROTECTED]: I've been surfing the list / google but can't find what to do I have a tab sep file, where there are empty fields i.e. tabtab the fields are associated with a mysql table integer type. how do I get the empty fields to become NULL instead of 0 (zero) with a warning ? Thanks, -pete -- 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 INFILE and STR_TO_DATE
thanks, that's what I feared already.. although ugly, will do it that way so far as I don't want to do any other pre-processing on the fixed CSV's I receive. moreover I just found out STR_TO_DATE isn't available in mysql 4.0 anyway.. Michael -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] I'm afraid that the syntax states that you can only use a list of column names. I think the best you can do is make an extra varchar column to hold the raw 'date' value from the csv and use an extra query to convert it into a format suitable for the gmtDate column. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE and STR_TO_DATE
using mysql 4.0.24 I'm stuck with a problem importing a CSV-file converting the date in the CSV from MM/DD/ to a mysql table.. I'm trying: LOAD DATA INFILE '/tmp/mydata.csv' INTO TABLE mytable FIELDSTERMINATED BY ','OPTIONALLY ENCLOSED BY '' (recordType, serviceType,STR_TO_DATE(gmtDate,'%m/%d/%Y'); The date in the CSV-file field #3 is in format MM/DD/ and therefore not importet correctly; Obvisouly I'm missing something on how to use functions for columns with LOAD DATA INFILE ? I'm afraid that the syntax states that you can only use a list of column names. I think the best you can do is make an extra varchar column to hold the raw 'date' value from the csv and use an extra query to convert it into a format suitable for the gmtDate column. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
John, thanks for your help, I've solved my problem splitting the field into
three fields on a temporary table. Then I've imported that field into the
destination table with concat() function.
Stefano
-- Messaggio originale --
From: John Doe [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: Load data infile and text fields
Date: Sun, 10 Apr 2005 02:32:28 +0200
Cc: [EMAIL PROTECTED]
Am Samstag, 2. April 2005 13.51 schrieb [EMAIL PROTECTED]:
First of all I hope you can be patient for my english
I'm working with data import into mysql from a txt file. I'm using LOAD
DATA INFILE
command but I cannot correctly import a text column of 595 characters.
I receive this (very large) file from an external organization and this
file is made
without separators bitween fields. I know only the exact lenght of each
field. All is fine for fields of 256 char, but I cannot import this
text
field of 595 characters. It's imported truncated at 255th character.
Help me please!
Stefano (osso)
I think this is a case where the splitting into the fields is better done
outside of mySQL.
You could run a simple script which takes your original file with nondelimited
records and produces a delemited file, and then import this delimited file.
Following a simple, non-generic perl script you can adapt to your field
lengths. The version below splits long records in fields of 13, 54, and
3
chars length, taking input from STDIN and output to STDOUT, so you could
use
it like
$ ./split.pl undelimited_file delimited_file
=== split.pl ===
#!/usr/bin/perl
use strict;
use warnings;
my $delimiter=;; # or \t or whatever
while (my $line=STDIN) { # process each line/record
my @fields=$line=~/^(.{13})(.{54})(.{3})/; # split into field by fix lengths
print join $delimiter, @fields; # output fields delimited
}
=== END split.pl ===
greetings joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Am Samstag, 2. April 2005 13.51 schrieb [EMAIL PROTECTED]:
First of all I hope you can be patient for my english
I'm working with data import into mysql from a txt file. I'm using LOAD
DATA INFILE
command but I cannot correctly import a text column of 595 characters.
I receive this (very large) file from an external organization and this
file is made
without separators bitween fields. I know only the exact lenght of each
field. All is fine for fields of 256 char, but I cannot import this text
field of 595 characters. It's imported truncated at 255th character.
Help me please!
Stefano (osso)
I think this is a case where the splitting into the fields is better done
outside of mySQL.
You could run a simple script which takes your original file with nondelimited
records and produces a delemited file, and then import this delimited file.
Following a simple, non-generic perl script you can adapt to your field
lengths. The version below splits long records in fields of 13, 54, and 3
chars length, taking input from STDIN and output to STDOUT, so you could use
it like
$ ./split.pl undelimited_file delimited_file
=== split.pl ===
#!/usr/bin/perl
use strict;
use warnings;
my $delimiter=;; # or \t or whatever
while (my $line=STDIN) { # process each line/record
my @fields=$line=~/^(.{13})(.{54})(.{3})/; # split into field by fix lengths
print join $delimiter, @fields; # output fields delimited
}
=== END split.pl ===
greetings joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Rhino, I've answered only to you and not to the list too, because I've already posted the same words answering to another mailing list user :-) My first problem is that the txt files I need to import have a fix structure without separators. After the field of 595 characters, there is another field of three character. I've tryied with txt data type but in that way I've lost the last field during import operations. Then, I cannot understood why, even with field seto to longtext, the string were cat at 255th character. But I've not investigated too much in this last aspect. Major problem, however, for me, were the lost of the last filed of three chars. Apropos the last problem, I think that the next priority for me will be to study php! And that surely will solve the problem! Thanks a lot for your help! Stefano -- Messaggio originale -- From: Rhino [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: Re: Load data infile and text fields Date: Mon, 4 Apr 2005 15:52:06 -0400 Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. With regards to your second problem, I could certainly do what you describe in a Java program - provided the MS Word documents are small enough to fit inside a BLOB! - but I'm not so sure how to do it without using a programming language. I just had a look at LOAD DATA INFILE to see if it can handle BLOBs but it says explicitly that it *CANNOT* handle BLOBs. Since the mysqlimport utility is just a front-end to LOAD DATA INFILE, I assume it has the same limitation. However, this page http://dev.mysql.com/doc/mysql/en/blob.html has some discussion of techniques to load BLOBs into tables. See the user comments in the bottom part of the page. All of them seem to use programming languages though. The user comments also raise an important issue: sometimes, it is better to store only a URL pointing to the document in the database and keep the actual document in the file system rather than the database. They suggest some guidelines to help you decide which approach is best for you. If you haven't considered these guidelines, you should. It might turn out that it is better for you to leave the documents outside of MySQL. Sorry I couldn't be more help. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Hello. Do you use a VARCHAR type for that column? It's maximum length is limited to 255 characters. I think, switching to TEXT type could solve the problem. [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this tex= t field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 infile and text fields
Michael, my problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my problem creating a temp table with my field splitted in three char fields: 250+250+95, then I've rebuild each line with the function concat()! Now I've another problem: after I've imported all txt files with a fully automated query, I need to import into a table with a blob field, a lot of little msword documents. Each record should have a single word file. I'd like to write a query (I hope without using api as php or other languages) that imports automatically all .doc files stored ina dir. Have any idea? Thanks Stefano -- Messaggio originale -- Subject: Re: Load data infile and text fields From: Michael Dykman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Date: Sat, 02 Apr 2005 09:18:56 -0500 What is the structure of the table you are importing to? you might have merely hit the natural limit of the column type. - michael dykman On Sat, 2005-04-02 at 06:51, [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- - michael dykman - [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 infile and text fields
Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. With regards to your second problem, I could certainly do what you describe in a Java program - provided the MS Word documents are small enough to fit inside a BLOB! - but I'm not so sure how to do it without using a programming language. I just had a look at LOAD DATA INFILE to see if it can handle BLOBs but it says explicitly that it *CANNOT* handle BLOBs. Since the mysqlimport utility is just a front-end to LOAD DATA INFILE, I assume it has the same limitation. However, this page http://dev.mysql.com/doc/mysql/en/blob.html has some discussion of techniques to load BLOBs into tables. See the user comments in the bottom part of the page. All of them seem to use programming languages though. The user comments also raise an important issue: sometimes, it is better to store only a URL pointing to the document in the database and keep the actual document in the file system rather than the database. They suggest some guidelines to help you decide which approach is best for you. If you haven't considered these guidelines, you should. It might turn out that it is better for you to leave the documents outside of MySQL. Sorry I couldn't be more help. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Monday, April 04, 2005 12:24 PM Subject: Re: Load data infile and text fields Rhino, many thanks for your answer! My problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my problem creating a temp table with my field splitted in three char fields: 250+250+95, then I've rebuild each line with the function concat()! Now I've another problem: after I've imported all txt files with a fully automated query, I need to import into a table with a blob field, a lot of little msword documents. Each record should get a single word file. I'd like to write a query (I hope without using api as php or other languages) that imports automatically all .doc files stored in a fixed directory. Have any idea? Thanks a lot Stefano -- Messaggio originale -- From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Load data infile and text fields Date: Sat, 2 Apr 2005 09:44:07 -0500 Stefano, The behaviour you are describing is normal, assuming that the column in your MySQL table is defined as CHAR(255) or VARCHAR(255). You didn't say which version of MySQL you are using. However, unless you are using MySQL 5.0.3 or later, 255 is the largest size available for a CHAR or VARCHAR column. (Starting with version 5.0.3, the maximum size of a VARCHAR is 65,536.) Assuming you are not on 5.0.3 or later, you should change your table definition to use one of the BLOB or TEXT datatypes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB or TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. You should look at the definitions of these column types in the manual - http://dev.mysql.com/doc/mysql/en/storage-requirements.html - to see which one best suits your requirements; only you know the largest value that you want to store in the column. Basically: - TINYBLOB and TINYTEXT are for values less than 256 characters long (which is no better than CHAR or VARCHAR in your case) - BLOB and TEXT are for values less than 65536 characters long - MEDIUMBLOB and MEDIUMTEXT are for values less than 16777216 characters long - LONGBLOB and LONGTEXT are for values less than 4294967296 characters long Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 02, 2005 6:51 AM Subject: Load data infile and text fields First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine
Re: Load data infile and text fields
On Apr 4, 2005, at 3:52 PM, Rhino wrote: Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. I think you missed this in the original post: I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. Importing from a fixed-width file requires a table with precisely matching column widths. I believe his (clever) solution was to import into 3 columns with the correct total width as a first pass, then CONCAT those 3 columns into one TEXT column. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
What is the structure of the table you are importing to? you might have merely hit the natural limit of the column type. - michael dykman On Sat, 2005-04-02 at 06:51, [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- - michael dykman - [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 infile and text fields
Stefano, The behaviour you are describing is normal, assuming that the column in your MySQL table is defined as CHAR(255) or VARCHAR(255). You didn't say which version of MySQL you are using. However, unless you are using MySQL 5.0.3 or later, 255 is the largest size available for a CHAR or VARCHAR column. (Starting with version 5.0.3, the maximum size of a VARCHAR is 65,536.) Assuming you are not on 5.0.3 or later, you should change your table definition to use one of the BLOB or TEXT datatypes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB or TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. You should look at the definitions of these column types in the manual - http://dev.mysql.com/doc/mysql/en/storage-requirements.html - to see which one best suits your requirements; only you know the largest value that you want to store in the column. Basically: - TINYBLOB and TINYTEXT are for values less than 256 characters long (which is no better than CHAR or VARCHAR in your case) - BLOB and TEXT are for values less than 65536 characters long - MEDIUMBLOB and MEDIUMTEXT are for values less than 16777216 characters long - LONGBLOB and LONGTEXT are for values less than 4294967296 characters long Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 02, 2005 6:51 AM Subject: Load data infile and text fields First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
At 05:18 PM 3/6/2005, Harrison Fisk wrote: Hi, On Mar 6, 2005, at 12:51 PM, mos wrote: At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. I gave that a try but I had to cancel Alter Table ... Enable Keys after 49 hours. I find it amazing that it takes only 6.25 hours to load 450 million rows into the table using Load Data Infile with the keys disabled, but over 49 hours (maybe a lot more) to rebuild 3 indexes. Why do you find that amazing? The import is pretty much a raw file copy by MySQL. That 6 hours is just copying data from one place to another. When you create indexes MySQL has to go through and sort the data. While doing so it has to create a special structure and maintain it and write it to disk. Creating an index is *much* more effort than a simple file copy. Even with 100g of disk space dedicated to the sort file. It started using Repair with Filesort for the first 7 hours, then it switched to Repair with KeyCache. It should only use one of the Repair methods. If it switched then something was wrong with Repair by filesort (maybe out of disk space?) Look in your error log and see if there is any message there about it. There was a [Warning] Warning: Enabling keys got errno 136, retrying and it kept running for almost 2 more days. (Yes, I guess I should have checked earlier.) So apparently it ran out of index space because I didn't build the table with Max_Rows=10 (1 billion?). I'm using NTFS so there is no 4gb limit. The data file is about 84g. There should have been plenty of disk space available for the index file. I think what bothers me the most about all this is I'm kept in the dark about the progress of rebuilding the index (or table). Wouldn't it be nice if the Show Process List could say 10% complete or 99% complete? If it sat at 5% complete for a few hours (days?), then I would know something was wrong. But if the progress steadily increased every few hours and when it got to 99% complete I would know it is almost complete. I could let my client know it is 99% complete rather than saying I don't know every few hours. I would like to see this % complete as a feature so people aren't kept in the dark. At least then they would know whether the process is hung or not. Just my 2 cents. Mike What did you have your myisam_* variables set to? I don't know anyone who can afford to take their database down for a few days (weeks?) while building an index. How often do you need to load in 450 million rows? Generally this only occurs once, or in a batch process, such as once per month. I don't know what your expectations are, but this is never going to be a very fast process under any DBMS. Importing 450M rows will take some decent amount of time. Mike mos [EMAIL PROTECTED] wrote: I have a 50g CSV file that I am trying to import into an empty MyISAM table. It appears to go fine except after 10 hours it hasn't completed. A Show Process List shows Info=load data infile ... and State=Repair with keycache. The table has a few hundred million rows of data. I assume it is using Repair with keycache to rebuild the indexes after the data has been loaded. From what I've read Repair with keycache is going to take a huge amount of time to complete. How do I disable Repair with keycache and use Repair with sort instead (which should be faster, right?)? I'm using MySQL 4.1.10 on Win XP and 1gb ram. TIA Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. I gave that a try but I had to cancel Alter Table ... Enable Keys after 49 hours. I find it amazing that it takes only 6.25 hours to load 450 million rows into the table using Load Data Infile with the keys disabled, but over 49 hours (maybe a lot more) to rebuild 3 indexes. Even with 100g of disk space dedicated to the sort file. It started using Repair with Filesort for the first 7 hours, then it switched to Repair with KeyCache. I don't know anyone who can afford to take their database down for a few days (weeks?) while building an index. Mike mos [EMAIL PROTECTED] wrote: I have a 50g CSV file that I am trying to import into an empty MyISAM table. It appears to go fine except after 10 hours it hasn't completed. A Show Process List shows Info=load data infile ... and State=Repair with keycache. The table has a few hundred million rows of data. I assume it is using Repair with keycache to rebuild the indexes after the data has been loaded. From what I've read Repair with keycache is going to take a huge amount of time to complete. How do I disable Repair with keycache and use Repair with sort instead (which should be faster, right?)? I'm using MySQL 4.1.10 on Win XP and 1gb ram. TIA Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
Hi, On Mar 6, 2005, at 12:51 PM, mos wrote: At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. I gave that a try but I had to cancel Alter Table ... Enable Keys after 49 hours. I find it amazing that it takes only 6.25 hours to load 450 million rows into the table using Load Data Infile with the keys disabled, but over 49 hours (maybe a lot more) to rebuild 3 indexes. Why do you find that amazing? The import is pretty much a raw file copy by MySQL. That 6 hours is just copying data from one place to another. When you create indexes MySQL has to go through and sort the data. While doing so it has to create a special structure and maintain it and write it to disk. Creating an index is *much* more effort than a simple file copy. Even with 100g of disk space dedicated to the sort file. It started using Repair with Filesort for the first 7 hours, then it switched to Repair with KeyCache. It should only use one of the Repair methods. If it switched then something was wrong with Repair by filesort (maybe out of disk space?) Look in your error log and see if there is any message there about it. What did you have your myisam_* variables set to? I don't know anyone who can afford to take their database down for a few days (weeks?) while building an index. How often do you need to load in 450 million rows? Generally this only occurs once, or in a batch process, such as once per month. I don't know what your expectations are, but this is never going to be a very fast process under any DBMS. Importing 450M rows will take some decent amount of time. Mike mos [EMAIL PROTECTED] wrote: I have a 50g CSV file that I am trying to import into an empty MyISAM table. It appears to go fine except after 10 hours it hasn't completed. A Show Process List shows Info=load data infile ... and State=Repair with keycache. The table has a few hundred million rows of data. I assume it is using Repair with keycache to rebuild the indexes after the data has been loaded. From what I've read Repair with keycache is going to take a huge amount of time to complete. How do I disable Repair with keycache and use Repair with sort instead (which should be faster, right?)? I'm using MySQL 4.1.10 on Win XP and 1gb ram. TIA Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. mos [EMAIL PROTECTED] wrote: I have a 50g CSV file that I am trying to import into an empty MyISAM table. It appears to go fine except after 10 hours it hasn't completed. A Show Process List shows Info=load data infile ... and State=Repair with keycache. The table has a few hundred million rows of data. I assume it is using Repair with keycache to rebuild the indexes after the data has been loaded. From what I've read Repair with keycache is going to take a huge amount of time to complete. How do I disable Repair with keycache and use Repair with sort instead (which should be faster, right?)? I'm using MySQL 4.1.10 on Win XP and 1gb ram. TIA Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 Infile ... Repair with KeyCache --WAY SLOW!
At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. That may work provided I can get the keys rebuilt later using FileSort and not KeyCache. You see the problem isn't in loading the data into the table which occurs reasonably fast that because running Load Data Infile on an empty table will disable the keys until all the data is loaded, so explicitly disabling before hand them won't help. After the data is loaded, Load Data Infile will then rebuild the keys and will choose to use Repair with keycache whereas Repair with filesort would be 100x-1000x faster. There doesn't seem to be any way to get it to use Repair with filesort. So I could use disable keys as you had suggested and then rebuild them manually with myisamchk repair with sort provided it doesn't reload all the data into a temporary table (which Alter Table usually does). If it does create a temporary table it will physically reloads the 500 million rows a second time and I will need another 100g of free disk space. So is there a solution to this paradox? I seem to be left with two options here: a) Do I shoot myself in the left foot right away, b) or Do I wait and shoot myself in the right foot? bg Is there a 3rd option? TIA Mike mos [EMAIL PROTECTED] wrote: I have a 50g CSV file that I am trying to import into an empty MyISAM table. It appears to go fine except after 10 hours it hasn't completed. A Show Process List shows Info=load data infile ... and State=Repair with keycache. The table has a few hundred million rows of data. I assume it is using Repair with keycache to rebuild the indexes after the data has been loaded. From what I've read Repair with keycache is going to take a huge amount of time to complete. How do I disable Repair with keycache and use Repair with sort instead (which should be faster, right?)? I'm using MySQL 4.1.10 on Win XP and 1gb ram. TIA Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
Hi, On Mar 3, 2005, at 11:32 AM, mos wrote: At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. That may work provided I can get the keys rebuilt later using FileSort and not KeyCache. You see the problem isn't in loading the data into the table which occurs reasonably fast that because running Load Data Infile on an empty table will disable the keys until all the data is loaded, so explicitly disabling before hand them won't help. After the data is loaded, Load Data Infile will then rebuild the keys and will choose to use Repair with keycache whereas Repair with filesort would be 100x-1000x faster. There doesn't seem to be any way to get it to use Repair with filesort. So I could use disable keys as you had suggested and then rebuild them manually with myisamchk repair with sort provided it doesn't reload all the data into a temporary table (which Alter Table usually does). If it does create a temporary table it will physically reloads the 500 million rows a second time and I will need another 100g of free disk space. So is there a solution to this paradox? It chooses which method to use based on your myisam_sort_buffer_size, myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Have you increased the size of these? Keep in mind these are SESSION variables, so they can be set on the connection right before you LOAD DATA INFILE. You always have to tell MySQL how much extra memory/disk it can use, it can't assume that you want it to use it. That is why it will use Repair by keycache, since it doesn't use any extra resources. If you increase the amount of diskspace or memory, then it should use a Repair by sort instead. Also keep in mind that Repair by sort doesn't work for UNIQUE or PRIMARY KEYs. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
At 12:39 PM 3/3/2005, Harrison Fisk wrote: Hi, On Mar 3, 2005, at 11:32 AM, mos wrote: At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. That may work provided I can get the keys rebuilt later using FileSort and not KeyCache. You see the problem isn't in loading the data into the table which occurs reasonably fast that because running Load Data Infile on an empty table will disable the keys until all the data is loaded, so explicitly disabling before hand them won't help. After the data is loaded, Load Data Infile will then rebuild the keys and will choose to use Repair with keycache whereas Repair with filesort would be 100x-1000x faster. There doesn't seem to be any way to get it to use Repair with filesort. So I could use disable keys as you had suggested and then rebuild them manually with myisamchk repair with sort provided it doesn't reload all the data into a temporary table (which Alter Table usually does). If it does create a temporary table it will physically reloads the 500 million rows a second time and I will need another 100g of free disk space. So is there a solution to this paradox? It chooses which method to use based on your myisam_sort_buffer_size, myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Have you increased the size of these? Keep in mind these are SESSION variables, so they can be set on the connection right before you LOAD DATA INFILE. You always have to tell MySQL how much extra memory/disk it can use, it can't assume that you want it to use it. That is why it will use Repair by keycache, since it doesn't use any extra resources. If you increase the amount of diskspace or memory, then it should use a Repair by sort instead. That's what I thought. I have myisam_max_sort_file_size set to 15.7GB and myisam_max_extra_sort_file_size set to 2GB. I confirmed this with Show Variables. I thought this would be enough to sort the indexes. I have confirmed there is enough free space on the hard drive to handle this both in the database directory and the mysql temp directory. Also keep in mind that Repair by sort doesn't work for UNIQUE or PRIMARY KEYs. So the solution is to keep using Load Data Infile with even larger max_sort variables and more disk space? There are only 4 indexes on the table and the largest index is around 50 bytes. This is running on an AMD3500+ with 1gb RAM. My problem is it takes about 6 hours to load in the rows using Load Data Infile to the empty table (which is fine) then by doing a Show ProcessList I'll know if it is using FileSort or KeyCache. That is a lot of time to waste if it ends up using KeyCache because then I know it could be a few days to index the table. So how do people force Load Data Infile to use FileSort and not KeyCache? In other words how do they know ahead of time what to set myisam_max_extra_sort_file_size set and myisam_max_sort_file_size to without guessing? To me it seems a lot like hit and miss guesswork. I would rather see a parameter BySort or ByKeyCache added to Load File command that forces it to use either FileSort or KeyCache rather than having the developer cross his fingers and hope the system variables are set correctly. Mike Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile ... Repair with KeyCache --WAY SLOW!
Hi, On Mar 3, 2005, at 3:13 PM, mos wrote: At 12:39 PM 3/3/2005, Harrison Fisk wrote: Hi, On Mar 3, 2005, at 11:32 AM, mos wrote: At 10:07 PM 3/2/2005, you wrote: Hello. You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process on the MyISAM table. That may work provided I can get the keys rebuilt later using FileSort and not KeyCache. You see the problem isn't in loading the data into the table which occurs reasonably fast that because running Load Data Infile on an empty table will disable the keys until all the data is loaded, so explicitly disabling before hand them won't help. After the data is loaded, Load Data Infile will then rebuild the keys and will choose to use Repair with keycache whereas Repair with filesort would be 100x-1000x faster. There doesn't seem to be any way to get it to use Repair with filesort. So I could use disable keys as you had suggested and then rebuild them manually with myisamchk repair with sort provided it doesn't reload all the data into a temporary table (which Alter Table usually does). If it does create a temporary table it will physically reloads the 500 million rows a second time and I will need another 100g of free disk space. So is there a solution to this paradox? It chooses which method to use based on your myisam_sort_buffer_size, myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Have you increased the size of these? Keep in mind these are SESSION variables, so they can be set on the connection right before you LOAD DATA INFILE. You always have to tell MySQL how much extra memory/disk it can use, it can't assume that you want it to use it. That is why it will use Repair by keycache, since it doesn't use any extra resources. If you increase the amount of diskspace or memory, then it should use a Repair by sort instead. That's what I thought. I have myisam_max_sort_file_size set to 15.7GB and myisam_max_extra_sort_file_size set to 2GB. I confirmed this with Show Variables. I thought this would be enough to sort the indexes. I have confirmed there is enough free space on the hard drive to handle this both in the database directory and the mysql temp directory. Also keep in mind that Repair by sort doesn't work for UNIQUE or PRIMARY KEYs. So the solution is to keep using Load Data Infile with even larger max_sort variables and more disk space? There are only 4 indexes on the table and the largest index is around 50 bytes. This is running on an AMD3500+ with 1gb RAM. My problem is it takes about 6 hours to load in the rows using Load Data Infile to the empty table (which is fine) then by doing a Show ProcessList I'll know if it is using FileSort or KeyCache. That is a lot of time to waste if it ends up using KeyCache because then I know it could be a few days to index the table. If you want to test it out to see how much is required (though it shouldn't matter, just set them as large as you possibly could (see below)), then take your table and load it once with indexes disabled. Then enable them. It will immediately do one or the other. If you are unhappy, then you can kill it, change variables and repeat. So how do people force Load Data Infile to use FileSort and not KeyCache? In other words how do they know ahead of time what to set myisam_max_extra_sort_file_size set and myisam_max_sort_file_size to without guessing? To me it seems a lot like hit and miss guesswork. I would rather see a parameter BySort or ByKeyCache added to Load File command that forces it to use either FileSort or KeyCache rather than having the developer cross his fingers and hope the system variables are set correctly. You don't play around with the variables. Set them to the largest possible amount that you would let MySQL use (ie. how much ram and how much disk space). Then it will have the information to make the decision properly. It only uses what it needs, up to your limit. If it sees that your limits are too small, then it won't be able to do a By Sort, even if you want it to. Why aren't you setting them as large as you can in the first go? Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Opposite
Hello. Use 'SELECT INTO OUTFILE ...' See: http://dev.mysql.com/doc/mysql/en/select.html shaun thornburgh [EMAIL PROTECTED] wrote: Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 INFILE Opposite
From: shaun thornburgh [mailto:[EMAIL PROTECTED] Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help SELECT ... INTO OUTFILE is what you want. It's sort of covered in the SELECT syntax documentation: http://dev.mysql.com/doc/mysql/en/select.html Pretty handy at times. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
shaun thornburgh [EMAIL PROTECTED] wrote on 02/15/2005 04:53:54 PM: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. What you want to do is called data scrubbing. Exactly how always depends on resources and the data itself however the general procedure works something like this: *create an import table that matches your source data *Import your data (without changes or omissions if at all possible) to this intermediate table. *Validate your imported data to make sure you have everything you wanted from the LOAD DATA INFILE command. *Eliminate any duplicate rows from your imported data table (many ways to do this. comparing the hash values for each row is one idea) *use your import table as the source for your final update/insert *drop your intermediate table. I would normally get into more details but it's time to head home. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: LOAD DATA INFILE using 4.0.17
Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
So what you meant was every field in each row must be unique from all other instances in all other rows? Not just each row must be unique? Bob - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 15, 2005 2:40 PM Subject: Re: LOAD DATA INFILE using 4.0.17 Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- 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 INFILE using 4.0.17
Sorry for the private answer hitted the wrong replay button.
It's possible for you unload data with an SQL like this ?
SELECT list, of, fields,
MD5 ( CONCAT ( list, of, fields ) )
INTO OUTFILE 'file_name'
FROM tab
if not (probably, you have csv files), you must use a shell script like
this (maybe slow)
#! /bin/sh
export SEP=;
while read myline ; do
echo ${myline}${SEP}$(echo ${myline} | md5sum | cut
--characters=-32)
done /etc/fstab
in M$ windows you must find an alternative.
BIG WARNINGs!
- This solution implies that forever you will be sticked to the same
method / program
- the md5 produced from the shell script will be different from the one
produced from the database
shaun thornburgh ha scritto:
Hi,
Thanks for your reply, but the problem I am facing is that there may
be duplicate values in the uploaded file and I dont want these to appear
in my table...
From: Bastian Balthazar Bux [EMAIL PROTECTED]
To: shaun thornburgh [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE using 4.0.17
Date: Tue, 15 Feb 2005 23:32:56 +0100
shaun thornburgh ha scritto:
Hi,
I have a table with 26 fields, each row in this table must be
unique. I can't define all of the fields to be primary keys as the limit
is 16. Therefore before I insert data I have to check that each row is
unique. Data is to be inserted into this table from files uploaded to
the server - CSV files. Is it possible to use the LOAD DATA INFILE
statement to check for unique rows, or does anyone have a better
solution to my problem!
Thanks for any advice offered.
make a table of 27 fields ;) , use the MD5() function
http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add
a unique index on the column that holds the md5 sum.
Regards, Francesco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
No just every row needs to be unique. Sorry for the confusion... From: Robert Dunlop [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 15:06:19 -0800 So what you meant was every field in each row must be unique from all other instances in all other rows? Not just each row must be unique? Bob - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 15, 2005 2:40 PM Subject: Re: LOAD DATA INFILE using 4.0.17 Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load data infile...
Richard Whitney mailto:[EMAIL PROTECTED] wrote on Tuesday, January 04, 2005 6:16 PM: I think I'm bringing this up again but with different errors. This: $sql = LOAD DATA INFILE '$file' REPLACE INTO TABLE `jobs` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; is getting the error Access denied for user: '[EMAIL PROTECTED]' (Using password: YES I have no idea where the % sign is coming from. shouldn't it say [EMAIL PROTECTED] like it's setup in the db connection script? MySQL treats connections to localhost differently to connections to the real hostname or IP address - the problem is likely in the mysql_connect or pconnect lines (if you're using php mysql module). Try adding a new user to the grant tables with duplicate permissions, but with host set to % (or localhost if the existing user has host set to % already). cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile...
Hello. Similar problems are described at: http://dev.mysql.com/doc/mysql/en/Connection_access.html Richard Whitney [EMAIL PROTECTED] wrote: I think I'm bringing this up again but with different errors. This: $sql = LOAD DATA INFILE '$file' REPLACE INTO TABLE `jobs` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; is getting the error Access denied for user: '[EMAIL PROTECTED]' (Using password: YES I have no idea where the % sign is coming from. shouldn't it say [EMAIL PROTECTED] like it's setup in the db connection script? Any clues? Thanks! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 INFILE
Goutham Thanks for your help. The problem in this case was line endings. I use an Apple g4 for web work on system 10.2. By default BBEdit uses macintosh line endings. MySQL does not recognize them. As soon as I changed the textfile format to unix line endings, it imported the data without any trouble at all. I take your point about mysql import and the nature of this command. The only reason I am using is that at the present time I do not have the time to reconfigure my server to make use of other ways of importing text. Thanks for your comments RB On 21/12/04 1:31 AM, Goutham S Mohan [EMAIL PROTECTED] wrote: Hi Rob, LOAD DATA INFILE is not a very verbose command. It doesn't spill out the exact root cause of the error. Forgive me, if I seem to be wrong for anybody who had a different opinion. mysqlimport is a wrapper around LOAD DATA INFILE with a lot of command line options. You might try your luck using the mysqlimport command which does the same for you. But I am not sure if it really helps you with more verbose output. The problem might be due to any of the following problems: - The data in the file needs to be in a format that mysql understands. With mysqlimport you can specify the column and record delimiters as required. - Escape characters need to be defined when you do a mysqlimport so that the data is not mistaken as a delimiter by mysql. - Also make sure that there are no foreign key violations in your target database. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard --- rob byrne [EMAIL PROTECTED] wrote: I am trying to load data from a text file into a table using the Load data infile statement. No matter how I change data types I seem only able to load in the first row of data into the MySQL table and no more. I have not been able to trach the source of this error or the cause of it? Does anyone know the cause so I can fix it? RB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
Hi Rob, LOAD DATA INFILE is not a very verbose command. It doesn't spill out the exact root cause of the error. Forgive me, if I seem to be wrong for anybody who had a different opinion. mysqlimport is a wrapper around LOAD DATA INFILE with a lot of command line options. You might try your luck using the mysqlimport command which does the same for you. But I am not sure if it really helps you with more verbose output. The problem might be due to any of the following problems: - The data in the file needs to be in a format that mysql understands. With mysqlimport you can specify the column and record delimiters as required. - Escape characters need to be defined when you do a mysqlimport so that the data is not mistaken as a delimiter by mysql. - Also make sure that there are no foreign key violations in your target database. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard --- rob byrne [EMAIL PROTECTED] wrote: I am trying to load data from a text file into a table using the Load data infile statement. No matter how I change data types I seem only able to load in the first row of data into the MySQL table and no more. I have not been able to trach the source of this error or the cause of it? Does anyone know the cause so I can fix it? RB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
Software Engineer, Hewlett Packard --- rob byrne [EMAIL PROTECTED] wrote: I am trying to load data from a text file into a table using the Load data infile statement. No matter how I change data types I seem only able to load in the first row of data into the MySQL table and no more. I have not been able to trach the source of this error or the cause of it? Does anyone know the cause so I can fix it? RB check your carrage returns, mysql seems to be unix freindly by default try adding lines terminated by '\n' or lines terminated by |'\r\n'| one should fix your problem, it defaults to '\n' but windows uses |'\r\n'| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile in Stored procedure
Hello. Looks like LOAD DATA INFILE supports only string constants in its syntax. I think it has sense, because security risk grows, when we can substitute variables in the file name. Scott Klarenbach [EMAIL PROTECTED] wrote: I can't seem to make the Load Data statement work inside of a stored procedure. ie LOAD DATA LOCAL INFILE file.txt INTO my_table this works fine in PHP, but when I use it in a procedure, and pass in the file name as a parameter, it won't compile. LOAD DATA LOCAL INFILE fileParameter INTO my_table I think it's because the statement requires the file to be in quotes, but if I concatenate the parameter into quotes, then it looks for the EXACT variable string, not the file. ie LOAD DATA LOCAL INFILE ' +fileParam+ ' INTO my_table --this returns an error saying it can't find the file fileParam :-). I also tried creating a prepared statement and then executing it USING my file parameters, but no luck either. Thanks. Any help is appreciated. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 INFILE question...
Somewhere about Sat, 20-Nov-2004 at 06:27PM +0100 (give or take), Ferhat BINGOL
wrote:
| Hi,
|
| I have a 72 fields data txt file and I was inserting all data
| previously but now I need only some of them to dump into the table.
| I would like to select only 4 fields which are the 1st, 5th,28th
| and 71st fields.
| Is there a statement to do that.
|
I think it would be simpler to pre-process the file using cut with the
appropriate delmiter if it's not tab-delimited already. Then import
the reduced file.
HTH
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: LOAD DATA INFILE question...
Hi Patrick,
I was doing that it was taking 20 seconds to chop the fields from the file.
That was the problem.
Meanwhile about my problem I have seen that the lines in data files end with
\r\n not \n so it solved the problem.
Now it takes 3 seconds to dump the file and chop the necassary fields.
thanks to MySQL.
:)
thank yo again for answer...
- Original Message -
From: Patrick Connolly [EMAIL PROTECTED]
To: Ferhat BINGOL [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Sunday, November 21, 2004 10:00 AM
Subject: Re: LOAD DATA INFILE question...
Somewhere about Sat, 20-Nov-2004 at 06:27PM +0100 (give or take), Ferhat
BINGOL wrote:
| Hi,
|
| I have a 72 fields data txt file and I was inserting all data
| previously but now I need only some of them to dump into the table.
| I would like to select only 4 fields which are the 1st, 5th,28th
| and 71st fields.
| Is there a statement to do that.
|
I think it would be simpler to pre-process the file using cut with the
appropriate delmiter if it's not tab-delimited already. Then import
the reduced file.
HTH
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE question...
You can load the file to an intermediate table and then complete your process using INSERT INTO targetTable SELECT col_1, col_5, col_28, col_71 FROM intermediateTABLE Ferhat BINGOL wrote: Hi, I have a 72 fields data txt file and I was inserting all data previously but now I need only some of them to dump into the table. I would like to select only 4 fields which are the 1st, 5th,28th and 71st fields. Is there a statement to do that. I have read http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html but could not find it... Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE question...
what about the file size. My file size is 10 mb. When I dump them all with LOAD DATA INFILE I only get first 9800 raws which is size of 2.9 mb. Is there a limit for file size? Where can I increase it? Regards.. - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: Ferhat BINGOL [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Saturday, November 20, 2004 10:41 PM Subject: Re: LOAD DATA INFILE question... You can load the file to an intermediate table and then complete your process using INSERT INTO targetTable SELECT col_1, col_5, col_28, col_71 FROM intermediateTABLE Ferhat BINGOL wrote: Hi, I have a 72 fields data txt file and I was inserting all data previously but now I need only some of them to dump into the table. I would like to select only 4 fields which are the 1st, 5th,28th and 71st fields. Is there a statement to do that. I have read http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html but could not find it... Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data Infile update?
Hi. If your table has a unique index on field 'name', then use load data infile 'file' replace into table 'table'; Lewick, Taylor [EMAIL PROTECTED] wrote: Can I perform an update on a table using load data infile..? If I have the following table... Name Score Rank John NULL NULL Susan NULL NULL And I have a data file that has... John,95,1 Susan,89,2 Any idea if this can be done? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 infile 'abcd.txt' into table b.chicago;
mysql load data infile 'abcd.txt' into table b.chicago; ' for key 1 Duplicate entry '[EMAIL PROTECTED] I think you have 2 e-mails that are equal in the file and the e-mail field is declared as a primary key (which implies UNIQUE). -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data infile
What is the current value for you id field? Approximately how many records are you inserting? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 8/20/04 3:16 AM Subject: load data infile Dear freinds, I am still getting errors.Load infile script. Guidance , please. Asif Qureshi -- --- mysql describe chicago - ; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | email | varchar(150) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.02 sec) mysql LOAD DATA INFILE - 'CHICAGO.TXT' into table - B.CHICAGO; ERROR 1062: Duplicate entry '0-' for key 1 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
Dear friend, I tried the IGNORE option so that data is loaded from the file to table, still getting error pasted below. Any advice. mysql use b Database changed mysql describe chicago - ; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | PRI | 0 | | | email | varchar(150) | | PRI | | | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql LOAD DATA INFILE IGNORE id - 'chicago.txt' INTO TABLE - b.chicago; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server ver 'chicago.txt' INTO TABLE b.chicago' at line 1 mysql - This is how I had done it before - mysql use b Database changed mysql LOAD DATA INFILE - 'chicago.txt' INTO TABLE - b.chicago; ERROR 1062: Duplicate entry '' for key 2 mysql -- --- +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | email | varchar(150) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.02 sec)
RE: load data infile question
For this type of custom loading you may want to explore a programming language such as Java or C/C++ or Perl. Depending on your platform you could even explore some third party tools. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/4/04 3:27 PM Subject: load data infile question I haven't used load data infile much, mainly because of issues like this question. I want to load a bunch of data for our data warehouse into about 10 different tables. But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. Is this how it has to be, or am i missing something? thanks much sean peters [EMAIL PROTECTED] -- 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 infile question
sean c peters wrote: But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. If no one else is going to be adding rows to the tables while you're doing the loading, then you can make your own values for the auto_increment column and include them in the text file rather than letting MySQL generate them. Just find the max current value and start counting from there, and using the same values in the child tables. I do something similar for one of my databases, and it works because there's no other process for inserting rows into those tables. That may not apply to your situation, though. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
Somewhere about Sun, 01-Aug-2004 at 11:31AM -0400 (give or take), Michael Stassen
wrote:
|
| Patrick Connolly wrote:
[...]
| Looks to me the mysql user should have no trouble with it:
|
| -rw-rw-r--1 pat pat 332 Jun 28 20:42 Orders.txt
|
| Every piece of the path to this file must be executable by mysql, as well.
I think that's the main problem I have. Since it's in a directory
beginning with /home/ and that directory is rwx--, one would have
to change that far back. Since this machine is not used by anyone
else, perhaps it would not be a problem changing that. Is that what
people normally do? Anything else I can think of seems incredibly
complicated.
|
| | : Also, to use LOAD DATA INFILE on server files, you must have
| | : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL.
|
| Think we can count that one out as the problem since LOCAL which would
| have the same requirement does work.
|
| The FILE privilege is not required with LOCAL. It is required without
| LOCAL, and for SELECT INTO OUTFILE. My bet would be that you don't have the
| FILE privilege. You can check with
Good guess. That was part of the problem (though I'd not have guessed
from the error message). I forgot that GRANT ALL does not include
FILE.
[...]
| How LOAD DATA LOCAL works was changed in 3.23.49 and 4.0.2. This
| is documented here
| http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html.
I had read that, but I'd not made the connexion with the error message.
Thanks again.
best
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
Somewhere about Sat, 31-Jul-2004 at 11:17AM -0400 (give or take), Michael Stassen
wrote:
| With LOCAL, the *client* reads the file on the client's machine.
| Without LOCAL, the *server* reeads the file on the server's
| machine. Even though the client and server machines are the same
| in your case, those are still different operations. There are
| restrictions on having the server do the work, for good reason.
| This is documented in the manual
| http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html:
I'm pretty sure I understand the reasons.
| : For security reasons, when reading text files located on the server,
| : the files must either reside in the database directory or be readable
| : by all.
Looks to me the mysql user should have no trouble with it:
-rw-rw-r--1 pat pat 332 Jun 28 20:42 Orders.txt
| : Also, to use LOAD DATA INFILE on server files, you must have
| : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL.
Think we can count that one out as the problem since LOCAL which would
have the same requirement does work.
I can't be absolutely sure but I seem to remember I did not have this
problem when I used 3.23.47 before I 'rpm -U'ed to 4.0.18. With the
Redhat distro version, I could *not* use LOAD DATA LOCAL unless I
started the client with --local-infile[=1] which seems to fit my
understanding of the docs. With 4.0.18, it's unnecessary which was
another surprise to me. Is there something I'm missing here?
|
| Michael
Thanks Michael.
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
Patrick Connolly wrote: Somewhere about Sat, 31-Jul-2004 at 11:17AM -0400 (give or take), Michael Stassen wrote: | With LOCAL, the *client* reads the file on the client's machine. | Without LOCAL, the *server* reeads the file on the server's | machine. Even though the client and server machines are the same | in your case, those are still different operations. There are | restrictions on having the server do the work, for good reason. | This is documented in the manual | http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html: I'm pretty sure I understand the reasons. | : For security reasons, when reading text files located on the server, | : the files must either reside in the database directory or be readable | : by all. Looks to me the mysql user should have no trouble with it: -rw-rw-r--1 pat pat 332 Jun 28 20:42 Orders.txt Every piece of the path to this file must be executable by mysql, as well. | : Also, to use LOAD DATA INFILE on server files, you must have | : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL. Think we can count that one out as the problem since LOCAL which would have the same requirement does work. The FILE privilege is not required with LOCAL. It is required without LOCAL, and for SELECT INTO OUTFILE. My bet would be that you don't have the FILE privilege. You can check with SHOW GRANTS FOR [EMAIL PROTECTED] or by inspecting the File_priv column in the mysql.user table. I can't be absolutely sure but I seem to remember I did not have this problem when I used 3.23.47 before I 'rpm -U'ed to 4.0.18. With the Redhat distro version, I could *not* use LOAD DATA LOCAL unless I started the client with --local-infile[=1] which seems to fit my understanding of the docs. With 4.0.18, it's unnecessary which was another surprise to me. Is there something I'm missing here? How LOAD DATA LOCAL works was changed in 3.23.49 and 4.0.2. This is documented here http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html. | | Michael Thanks Michael. You're welcome. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
With LOCAL, the *client* reads the file on the client's machine. Without LOCAL, the *server* reeads the file on the server's machine. Even though the client and server machines are the same in your case, those are still different operations. There are restrictions on having the server do the work, for good reason. This is documented in the manual http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html: : For security reasons, when reading text files located on the server, the : files must either reside in the database directory or be readable by all. : Also, to use LOAD DATA INFILE on server files, you must have the FILE : privilege. See section 5.5.3 Privileges Provided by MySQL. Michael Patrick Connolly wrote: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.18-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql LOAD DATA INFILE Orders.txt INTO TABLE Orders3 fields terminated by '\t'; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) Even thought the server is on the same machine as the host, I always get this error message if I try . If I add the word LOCAL, it is quite happy even though I know it shouldn't be necessary. That hasn't bothered me very much, but now I'm working on Chapter 9 of the Certification Study Guide which covers this topic, and the related SELECT INTO OUTFILE, I don't have a work around. I'm using Redhat 7.3 with the mysql RPMs from the MySQL site, not the ones that came with the distro. So far, everything else seems to work properly, but I'm mystified why this should happen. Somehow, I doubt that anyone will be able to replicate this problem, so that makes it unlikely anyone will have an answer, but one can't be sure. At one stage I thought it might be an obscure hardware difficulty with this aged machine (over 5 years) because of another obscure problem I had using fetchmail from a POP server. However, I noticed that once I switched off the ISP's virus checking, that problem vanished, so I'm less inclined to believe it's hardware. Any wild guesses welcome. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE
Resolved, used this syntax... LOAD DATA INFILE '/path/from/root/to/file.csv' INTO TABLE ma0133 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' - Phil. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.725 / Virus Database: 480 - Release Date: 19/07/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
