Re: LOAD DATA in replication
Hello Neubyr, On 1/29/2014 7:16 PM, neubyr wrote: I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'. According to manual - https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'. I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Appreciate any help on this. Master is using MySQL 5.0 and slave is using MySQL 5.6. -thanks, N The slave is not receiving the file from your local disk. When that file arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in the binary log and copied (via replication) to the slave where the slave performs a server-side LOAD DATA... . This is how STATEMENT-based replication operates. Does that make better sense? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LOAD DATA in replication
Thanks for the details Shawn. So row based replication would avoid server side LOAD DATA on slave. Unfortunately, the Master is using MySQL ver 5.0, so I don't think it can use row based replication. - thanks, N On Thu, Jan 30, 2014 at 7:48 AM, shawn l.green shawn.l.gr...@oracle.comwrote: Hello Neubyr, On 1/29/2014 7:16 PM, neubyr wrote: I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'. According to manual - https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'. I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Appreciate any help on this. Master is using MySQL 5.0 and slave is using MySQL 5.6. -thanks, N The slave is not receiving the file from your local disk. When that file arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in the binary log and copied (via replication) to the slave where the slave performs a server-side LOAD DATA... . This is how STATEMENT-based replication operates. Does that make better sense? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LOAD DATA in replication
If I don't mistake, there are some parameters to make that you are saying. Check statement-based-replication and row-based-replication. I think that this could help you. Regards, Antonio.
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 in php
If you are using v4 of php this will never work On Tue, Jun 1, 2010 at 10:24 AM, memo garcia mgar...@cistrans.cl wrote: Hi all, I have the following script: Load data Local infile ‘myData.csv’ Into table myTable Fields terminated by ‘,’ Enclosed by ‘’ Lines terminated by ‘\r\n’ (field1, field2, …) When this is sourced directly from mysql it works fine, but when invoked from php, I get the error The used command is no allowed with this MySQL version Any help on this? Thanks, Memo García Sir CIS Asociados Consultores en Transporte S.A. Austria 2042 Providencia Santiago de Chile F: 56-2- 2051033 Fax: 56-2-2051029 www.cisconsultores.cl
RE: load data in php
Nike, Im using PHP 5.2.3 with Suhosin-Patch 0.9.7 (cli) (built: May 7 2010 08:41:40) Thanks, Memo García Sir CIS Asociados Consultores en Transporte S.A. Austria 2042 Providencia Santiago de Chile F: 56-2- 2051033 Fax: 56-2-2051029 www.cisconsultores.cl _ De: Mike [mailto:hiji...@gmail.com] Enviado el: Tuesday, June 01, 2010 10:34 AM Para: mgar...@cistrans.cl CC: mysql@lists.mysql.com Asunto: Re: load data in php If you are using v4 of php this will never work On Tue, Jun 1, 2010 at 10:24 AM, memo garcia mgar...@cistrans.cl wrote: Hi all, I have the following script: Load data Local infile myData.csv Into table myTable Fields terminated by , Enclosed by Lines terminated by \r\n (field1, field2, ) When this is sourced directly from mysql it works fine, but when invoked from php, I get the error The used command is no allowed with this MySQL version Any help on this? Thanks, Memo García Sir CIS Asociados Consultores en Transporte S.A. Austria 2042 Providencia Santiago de Chile F: 56-2- 2051033 Fax: 56-2-2051029 www.cisconsultores.cl
Re: load data in php
This sounds like a matter to take up with whoever supports the particular PHP/MySQL interface you are using. Are you using the mysql, mysqli or dbo? The restriction very likely originates from that layer. - michael dykman On Tue, Jun 1, 2010 at 11:11 AM, memo garcia mgar...@cistrans.cl wrote: Nike, I’m using PHP 5.2.3 with Suhosin-Patch 0.9.7 (cli) (built: May 7 2010 08:41:40) Thanks, Memo García Sir CIS Asociados Consultores en Transporte S.A. Austria 2042 Providencia Santiago de Chile F: 56-2- 2051033 Fax: 56-2-2051029 www.cisconsultores.cl _ De: Mike [mailto:hiji...@gmail.com] Enviado el: Tuesday, June 01, 2010 10:34 AM Para: mgar...@cistrans.cl CC: mysql@lists.mysql.com Asunto: Re: load data in php If you are using v4 of php this will never work On Tue, Jun 1, 2010 at 10:24 AM, memo garcia mgar...@cistrans.cl wrote: Hi all, I have the following script: Load data Local infile ‘myData.csv’ Into table myTable Fields terminated by ‘,’ Enclosed by ‘’ Lines terminated by ‘\r\n’ (field1, field2, …) When this is sourced directly from mysql it works fine, but when invoked from php, I get the error The used command is no allowed with this MySQL version Any help on this? Thanks, Memo García Sir CIS Asociados Consultores en Transporte S.A. Austria 2042 Providencia Santiago de Chile F: 56-2- 2051033 Fax: 56-2-2051029 www.cisconsultores.cl -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- 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 into temporary table
Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i| +--+ |1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- 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 into temporary table
Thank you but the real problem occurs when you don't know the schema of the table in advance. If data.txt has two columns columns how can I still load it in a temporary table? I'm asking this question because I'd like to add an import csv feature to a web application. I know that you can load data infile into table without specifying the schema of this table but it does not look like you can do load data infile into a temporary table. Thank you, Alex 2009/5/19 Janek Bogucki janek.bogu...@studylink.com: Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i | +--+ | 1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=alex.ksi...@gmail.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 into temporary table
Hi Alex, It is true that use LOAD DATA INFILE you do need to know the schema of the table. I'm not sure how useful it would be to import arbitrary data if you don't have some expectations about what that data is. There are a couple options for you: 1. Make sure your users upload a CSV is a specific format, reject non-conforming input. 2. Let your script transform the user uploaded CSV file into the format the database is expecting. 3. Have your script simply parse the user uploaded CSV and generate insert statements as needed. Just because you want to accept CSV from your app, does not mean you must use LOAD DATA INFILE to get the data into MySQL. Regards, Gavin Towey -Original Message- From: alex.ksi...@gmail.com [mailto:alex.ksi...@gmail.com] On Behalf Of Alex K Sent: Tuesday, May 19, 2009 5:43 AM To: MySQL General List Subject: Re: load data into temporary table Thank you but the real problem occurs when you don't know the schema of the table in advance. If data.txt has two columns columns how can I still load it in a temporary table? I'm asking this question because I'd like to add an import csv feature to a web application. I know that you can load data infile into table without specifying the schema of this table but it does not look like you can do load data infile into a temporary table. Thank you, Alex 2009/5/19 Janek Bogucki janek.bogu...@studylink.com: Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i| +--+ |1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=alex.ksi...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- 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 FROM MASTER stops unfinished with Query OK
The test box doesn't have incoming data when he's taking the snapshot. Lock the production database while taking snapshot and setting up replication or you will have this problem. I've tried all the methods (snapshot, dump, hotcopy etc) and the issue is always the same. You can't bootstrap replication with a live server yet. You need to lock the entire server during the copy process and make sure you note the correct log position. One incoming row will break it because the keys get out of sync. -Neil - If it worked on your test box, but not in production, what is different about those 2 boxes? Common sense says If it worked on one box but not another, it's not the software, but a difference between the 2 boxes. And you can always file a bug report with MySQL or get a consultant (through MySQL AB or not) to help you. This list isn't the entirety of knowledge out there on MySQL, although it's pretty good. -Sheeri Same issue, the server chokes on duped data
Re: load data
On 11/13/07, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi there,
i have a text file that i prepare:
insert into `sa2007` (`id`,`amount`,`state`) values
('','1.00','oh'),
('','2.00','il'),
('','4.00','ks')
how do i import this file to sa2007 table from the command line? i tried
via phymyadmin, but it doesn't work (300 seconds timeout).
Try: mysql -uusername -ppassword database filename.sql
--
Check out the Dallas Music Wiki http://www.digitaldarkness.com
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 INTO doesn't work correctly with utf8
Before you import at the mysql prompt set below variables and then try again to load set session max_error_count=50; set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; regards anandkl On 8/30/07, Harald Vajkonny [EMAIL PROTECTED] wrote: Hello, I would like to import data from a utf8-coded comma seperated file. I created my database with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci and I started my mysql-client with the --default-character-set=utf8 option. Nevertheless, when I input primary key fields, which differ only in one umlaut character (e.g. achten and ächten) I get the following error message: ERROR 1062 (23000): Duplicate entry 'ächten' for key 1 (Same thing happens when I try to manually INSERT the row.) When I display my variable settings with SHOW variables LIKE 'c%'; I receive the following result: +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci| | collation_database | utf8_general_ci| | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert| 1 | | connect_timeout | 5 | +--++ 14 rows in set (0.02 sec) From this I conclude it is the server setting, which causes the trouble here. When I manipulate the settings manually from the client (with SET character_set_server=utf8; SET collation_server=utf8_general_ci;) the values do change, but not the behaviour. But this can be expected, since the server is already up and running with the wrong settings. Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Best regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Ananda Kumar schrieb: Before you import at the mysql prompt set below variables and then try again to load set session max_error_count=50; set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; This is not what I need, because I use utf8 as well as in the database as in the input file and I do not have any latin1 at all. When I choose latin1 here, indeed, I don't receive an error message, but my data becomes corrupted. When, however, I choose utf8 and utf8_general_ci as session character_set_database and session collation_database, I still get the same error message. :( Best regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INTO doesn't work correctly with utf8
I would like to import data from a utf8-coded comma seperated file. I created my database with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci and I started my mysql-client with the --default-character-set=utf8 option. Nevertheless, when I input primary key fields, which differ only in one umlaut character (e.g. achten and ächten) I get the following error message: ERROR 1062 (23000): Duplicate entry 'ächten' for key 1 (Same thing happens when I try to manually INSERT the row.) When I display my variable settings with SHOW variables LIKE 'c%'; I receive the following result: +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci| | collation_database | utf8_general_ci| | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert| 1 | | connect_timeout | 5 | +--++ 14 rows in set (0.02 sec) From this I conclude it is the server setting, which causes the trouble here. When I manipulate the settings manually from the client (with SET character_set_server=utf8; SET collation_server=utf8_general_ci;) the values do change, but not the behaviour. But this can be expected, since the server is already up and running with the wrong settings. Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Best regards, H. Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client is sending data in UTF-8. I believe that as your server is latin1, it will assume this is the character set used by the command line client. [1] http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Edward Kay schrieb: Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client is sending data in UTF-8. I believe that as your server is latin1, it will assume this is the character set used by the command line client. [1] http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Edward I tried, but the behaviour remains the same. I guess my first option should be to force the server to use utf8 as default. I would have no problems in doing so, because I hardly use any latin1 anymore. But how would I do so? The mysqld has some options for this, but I didn't manage yet to simply restart it with new options, because it is started somewhere in the init.d-procedure of my Linux system and even as root I cannot simply invoke it from the command line. Best regards, Harald -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Edward Kay napsal(a): I would like to import data from a utf8-coded comma seperated file. I created my database with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci and I started my mysql-client with the --default-character-set=utf8 option. Nevertheless, when I input primary key fields, which differ only in one umlaut character (e.g. achten and ächten) I get the following error message: ERROR 1062 (23000): Duplicate entry 'ächten' for key 1 (Same thing happens when I try to manually INSERT the row.) When I display my variable settings with SHOW variables LIKE 'c%'; I receive the following result: +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci| | collation_database | utf8_general_ci| | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert| 1 | | connect_timeout | 5 | +--++ 14 rows in set (0.02 sec) From this I conclude it is the server setting, which causes the trouble here. When I manipulate the settings manually from the client (with SET character_set_server=utf8; SET collation_server=utf8_general_ci;) the values do change, but not the behaviour. But this can be expected, since the server is already up and running with the wrong settings. Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Best regards, H. Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client is sending data in UTF-8. I believe that as your server is latin1, it will assume this is the character set used by the command line client. [1] http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Edward From my experience SET NAMES doesn't work, but character set of the database must be same as file's character set and this condition is OK. For sure I used script: USE database_with_correct_charset; LOAD DATA ...; And this worked fine for files with cp1250 and also with keybcs2 (I had two databases, of course) HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
I used the latin collation and latin db character set, to load data similar to you, and we got this done correctly. If your inserting multi byte data, then u need to set the above parameters. This was one of the solutions give by mysql, i am not able to get the url. I will search my notes and get back to you all. regards anandkl On 8/30/07, Dušan Pavlica [EMAIL PROTECTED] wrote: Edward Kay napsal(a): I would like to import data from a utf8-coded comma seperated file. I created my database with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci and I started my mysql-client with the --default-character-set=utf8 option. Nevertheless, when I input primary key fields, which differ only in one umlaut character (e.g. achten and ächten) I get the following error message: ERROR 1062 (23000): Duplicate entry 'ächten' for key 1 (Same thing happens when I try to manually INSERT the row.) When I display my variable settings with SHOW variables LIKE 'c%'; I receive the following result: +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci| | collation_database | utf8_general_ci| | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert| 1 | | connect_timeout | 5 | +--++ 14 rows in set (0.02 sec) From this I conclude it is the server setting, which causes the trouble here. When I manipulate the settings manually from the client (with SET character_set_server=utf8; SET collation_server=utf8_general_ci;) the values do change, but not the behaviour. But this can be expected, since the server is already up and running with the wrong settings. Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Best regards, H. Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client is sending data in UTF-8. I believe that as your server is latin1, it will assume this is the character set used by the command line client. [1] http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Edward From my experience SET NAMES doesn't work, but character set of the database must be same as file's character set and this condition is OK. For sure I used script: USE database_with_correct_charset; LOAD DATA ...; And this worked fine for files with cp1250 and also with keybcs2 (I had two databases, of course) HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Ananda Kumar schrieb: I used the latin collation and latin db character set, to load data similar to you, and we got this done correctly. If your inserting multi byte data, then u need to set the above parameters. This was one of the solutions give by mysql, i am not able to get the url. I will search my notes and get back to you all. When I load my multibyte data using latin character set and collation, I get the data loaded correctly into the database without error msg, but when I want to display it with SELECT * from table I don't get it displayed correctly, even after I change the settings back to utf8 after the import. Best regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Meanwhile I managed to change the server settings by adding the following lines in the [mysqld] section of my /etc/my.cnf: collation_server=utf8_unicode_ci character_set_server=utf8 skip-character-set-client-handshake Now the server also runs with utf8, but when loading the file, I still get the same error message... Regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
strange. did u exit and reconnect and did the select? On 8/30/07, Harald Vajkonny [EMAIL PROTECTED] wrote: Ananda Kumar schrieb: I used the latin collation and latin db character set, to load data similar to you, and we got this done correctly. If your inserting multi byte data, then u need to set the above parameters. This was one of the solutions give by mysql, i am not able to get the url. I will search my notes and get back to you all. When I load my multibyte data using latin character set and collation, I get the data loaded correctly into the database without error msg, but when I want to display it with SELECT * from table I don't get it displayed correctly, even after I change the settings back to utf8 after the import. Best regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips?
Re: LOAD DATA INTO doesn't work correctly with utf8
Ananda Kumar schrieb: strange. did u exit and reconnect and did the select? Yes, I tried it once more. I have to put the USE command before I change session settings to latin to make it work without error (otherwise I still get the duplicate message). But even after exiting I get the national characters displayed as two (or more) bytes. Regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Harald Vajkonny napsal(a): Ananda Kumar schrieb: strange. did u exit and reconnect and did the select? Yes, I tried it once more. I have to put the USE command before I change session settings to latin to make it work without error (otherwise I still get the duplicate message). But even after exiting I get the national characters displayed as two (or more) bytes. Try to convert file to latin1, if it's possible, create database with latin1 charset, create table with required structure (you can set utf8 charset to string fields ) and then load data. What client do you use to check whether data are OK? I can recommend MySQL Query Browser where I don't have to care about setting charset and collation parameters. HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Ananda Kumar schrieb: strange. did u exit and reconnect and did the select? Yes, I tried it once more. I have to put the USE command before I change session settings to latin to make it work without error (otherwise I still get the duplicate message). But even after exiting I get the national characters displayed as two (or more) bytes. Regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Dušan Pavlica schrieb: Try to convert file to latin1, if it's possible, create database with latin1 charset, create table with required structure (you can set utf8 charset to string fields ) and then load data. I can not convert the file into latin1, because it is multilingual (i.e. European, Japanese, Korean etc.). What client do you use to check whether data are OK? I can recommend MySQL Query Browser where I don't have to care about setting charset and collation parameters. I checked the file in MySQL Query Browser too, but even there I get it wrong, when I imported it with a latin session. I get it displayed right, when I import it with utf8, but then I have the message with duplicate keys and get only half of the data. Regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Harald Vajkonny napsal(a): Dušan Pavlica schrieb: Try to convert file to latin1, if it's possible, create database with latin1 charset, create table with required structure (you can set utf8 charset to string fields ) and then load data. I can not convert the file into latin1, because it is multilingual (i.e. European, Japanese, Korean etc.). What client do you use to check whether data are OK? I can recommend MySQL Query Browser where I don't have to care about setting charset and collation parameters. I checked the file in MySQL Query Browser too, but even there I get it wrong, when I imported it with a latin session. I get it displayed right, when I import it with utf8, but then I have the message with duplicate keys and get only half of the data. What's the charset and collation of your primary field in the table? Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Dušan Pavlica schrieb: What's the charset and collation of your primary field in the table? With which command do I get the charset and collation information of a single field in a table? SHOW CREATE TABLE returns: ... ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | But I believe it is utf8, because when I enter Japanese or Russian text in this field by INSERT it is displayed right. It just can't distinguish between a, ä or á, but treats it as if they were a. In doing this I got another idea: Does anybody know the difference between the collations utf8_general_ci, utf8_unicode_ci and utf8_bin? I'll try these first and then get back to you about the results. Best regards, H. -- Wouldn't the sentence 'I want to put a hyphen between the words Fish and And and And and Chips in my Fish-And-Chips sign' have been clearer if quotation marks had been placed before Fish, and between Fish and and, and and and And, and And and and, and and and And, and And and and, and and and Chips, as well as after Chips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data
Can you not change your proceedure and format your dates first using DAT_FORMAT()? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function _date-format You could put a trigger on the table that would format the dates before insert (although I'd go for the above) -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 06:30 To: MySQL General Subject: Fwd: load data Hi All, We have an application where we load data on a daily basis and then do some analysis and the move this data into different tables. Data is comming in files. The date format in the file is dd-mon- hh24:mi:ss', but as you all know, in mysql , the default date format is -mm-dd hh24:mi:ss. How can i specifiy this format in the load data infile script. I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (doj timestamp dd-mon- hh24:mi:ss); but data is not getting inserted. Please help me. regards anandkl This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data
Hi Campbell, I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (date_format(doj,'%d-%M-%Y %H:%i:%S'); 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 'date_format(doj,'%d-%M-%Y %H:%i:%S')' at line 1 and also LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (str_to_date(doj,'%d-%b-%Y %H:%i:%S'); 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 'str_to_date(doj,'%d-%b-%Y %H:%i:%S')' at line 1 Can u please tell me where i going wrong. regards anandkl On 7/11/07, Rhys Campbell [EMAIL PROTECTED] wrote: Can you not change your proceedure and format your dates first using DAT_FORMAT()? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function _date-format You could put a trigger on the table that would format the dates before insert (although I'd go for the above) -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 06:30 To: MySQL General Subject: Fwd: load data Hi All, We have an application where we load data on a daily basis and then do some analysis and the move this data into different tables. Data is comming in files. The date format in the file is dd-mon- hh24:mi:ss', but as you all know, in mysql , the default date format is -mm-dd hh24:mi:ss. How can i specifiy this format in the load data infile script. I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (doj timestamp dd-mon- hh24:mi:ss); but data is not getting inserted. Please help me. regards anandkl This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300
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 FROM MASTER stops unfinished with Query OK
If it worked on your test box, but not in production, what is different about those 2 boxes? Common sense says If it worked on one box but not another, it's not the software, but a difference between the 2 boxes. And you can always file a bug report with MySQL or get a consultant (through MySQL AB or not) to help you. This list isn't the entirety of knowledge out there on MySQL, although it's pretty good. -Sheeri On 5/29/06, Bgs [EMAIL PROTECTED] wrote: It seems nobody has a clue here :( I've given up with MySQL replication... Hope it will work in 5.1 ... BTW: Any official info or estimate about the production release? Bgs wrote: Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Bgs wrote: No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- 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 FROM MASTER stops unfinished with Query OK
It seems nobody has a clue here :( I've given up with MySQL replication... Hope it will work in 5.1 ... BTW: Any official info or estimate about the production release? Bgs wrote: Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Bgs wrote: No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- 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 FROM MASTER stops unfinished with Query OK
Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Bgs wrote: No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- 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 FROM MASTER stops unfinished with Query OK
sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Bgs wrote: No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant Hi - Yeah, that's why I asked. It's documented in the handbook. It really put a damper on my day when I read that ;) Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA FROM MASTER stops unfinished with Query OK
No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA FROM MASTER stops unfinished with Query OK
Bgs wrote: No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA giving BIG mysql-bin files ...
I think this is normal as the binary log will contain a record of all changes made to the data, therefore if you are loading large files regularly- the bin logs will be quite large. If you do not want the binary logging, edit the my.cnf file, comment out the line log-bin (#log-bin) and restart the server. Ade C.R.Vegelin wrote: Hi List, I would appreciate your help on the following. When using LOAD DATA INFILE 'inputfile.txt' into a MyISAM table, it creates mysql-bin.nn files under my database directory with the size of 'inputfile.txt' (about 200 MB). Since I have to load 12 inputfiles, I get about 2.5 GB of mysql-bin files. Question: is this normal ? If not, how can I avoid these mysql-bin files ? When using MySQL Administrator to look at a mysql-bin file, it shows only: 060330 8:29:00 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.15-nt-log' socket: '' port: 3306 Official MySQL binary I use the following script: DELETE FROM myTable; LOAD DATA INFILE 'infile.txt' INTO TABLE myTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (..., ..., ...) SET ...; The LOAD DATA ... gives: warnings = 0 MySQL version is: MySQL 5.0.15-nt-log on a Windows XP machine. TIA and Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA giving BIG mysql-bin files ...
Thanks Adrian, Dilipkumar, Dhandapani, I changed my.ini file, restarted the server and now it's okay. Regards, Cor - Original Message - From: Adrian Bruce [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, March 30, 2006 9:48 AM Subject: Re: LOAD DATA giving BIG mysql-bin files ... I think this is normal as the binary log will contain a record of all changes made to the data, therefore if you are loading large files regularly- the bin logs will be quite large. If you do not want the binary logging, edit the my.cnf file, comment out the line log-bin (#log-bin) and restart the server. Ade C.R.Vegelin wrote: Hi List, I would appreciate your help on the following. When using LOAD DATA INFILE 'inputfile.txt' into a MyISAM table, it creates mysql-bin.nn files under my database directory with the size of 'inputfile.txt' (about 200 MB). Since I have to load 12 inputfiles, I get about 2.5 GB of mysql-bin files. Question: is this normal ? If not, how can I avoid these mysql-bin files ? When using MySQL Administrator to look at a mysql-bin file, it shows only: 060330 8:29:00 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.15-nt-log' socket: '' port: 3306 Official MySQL binary I use the following script: DELETE FROM myTable; LOAD DATA INFILE 'infile.txt' INTO TABLE myTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (..., ..., ...) SET ...; The LOAD DATA ... gives: warnings = 0 MySQL version is: MySQL 5.0.15-nt-log on a Windows XP machine. TIA and Regards, Cor -- 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, Ignore in SET?
At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- 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, Ignore in SET?
From: Paul DuBois [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? _ Nyhet! MSN Messenger i Mobiltelefonen! http://mobile.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 0:07 + 1/29/06, Jessica Svensson wrote: From: Paul DuBois [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? LOAD DATA attempts to load every line. The only way it won't happen will be that some error occurs or you're using IGNORE and a duplicate-key error occurs. If you want to selectively ignore lines based on some other criterion, LOAD DATA is probably the wrong approach. At least if you're loading the data directly into the target table. You might consider another approach: Load the data into a temporary table, delete from it those records that have no product_id match (use the multiple-table DELETE syntax that enables you to delete records based on join conditions), and then load the remaining records into your target table (INSERT ... SELECT). -- 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 (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 IF?
on 11/3/05 12:11 AM, John thegimper at [EMAIL PROTECTED] wrote: I dont want it to INSERT that row. Sample.txt name;category;price samsung;dvd;60 siemens;mobile;40 none;none;0 Say i dont want to insert rows where category is mobile or price = 0. So in this case only the 'samsung;dvd;60' row would be inserted. Probably pretty simple to just delete them after youa re done. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- 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 IF?
At 1:16 +0100 11/3/05, John thegimper wrote: Is it possible to specify and IF statement or IGNORE statement when useing LOAD DATA? Example SET price = IF PRICE( 20) THEN IGNORE SET price = IGNORE IF 20 What is the semantics of IGNORE? What do you expect to happen? -- 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 IF?
I dont want it to INSERT that row. Sample.txt name;category;price samsung;dvd;60 siemens;mobile;40 none;none;0 Say i dont want to insert rows where category is mobile or price = 0. So in this case only the 'samsung;dvd;60' row would be inserted. Is this possible? Quoting Paul DuBois [EMAIL PROTECTED]: At 1:16 +0100 11/3/05, John thegimper wrote: Is it possible to specify and IF statement or IGNORE statement when useing LOAD DATA? Example SET price = IF PRICE( 20) THEN IGNORE SET price = IGNORE IF 20 What is the semantics of IGNORE? What do you expect to happen? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com - 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
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 and skip columns in text file...
In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA and skip columns in text file...
The folowing is out of the current MySQL manual. It looks like you could create an intermediate table with the fields you are interested in the front and garbage fields on the end. Then build a specific LOAD DATA INFILE with correct mapping for each file type [assuming you can tell this in your automated process]. Then after loading all 200 of your files run 1 INSERT...SELECT... with just the pertinent FIELDS from the intermediate table to your final table and TRUNCATE the intermediate table. It probably makes sense for the intermediate table to have minimal indexing so the initial loads will run faster. _ mysql LOAD DATA INFILE 'persondata.txt' - INTO TABLE persondata (col1,col2,...); You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns. If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented. If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in section 13.2.6 CREATE TABLE Syntax. -Original Message- From: Jessica Svensson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 12:28 PM To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- 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 and skip columns in text file...
Hi, On May 25, 2005, at 1:28 PM, Jessica Svensson wrote: That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. The ability to do this was just implemented recently in MySQL 5.0, along with some other enhancements to LOAD DATA INFILE in 5.0. I don't think the documentation has been updated yet to include the new information though. Hopefully it will be soon. You see the changeset and a preliminary description at http://lists.mysql.com/internals/23068 That should be able to do what you want (and more cool stuff!) Prior to that version, you would have to do as he recommended below with a separate staging table. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. 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 Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 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 and skip columns in text file...
Wow!! Thanks! I will upgrade right away... but i can't find any information in the url you supplied about how to specify what fields from the external file to read... will be quite hard to figure out on my own i guess? Thanks again! From: Harrison Fisk [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: Wed, 25 May 2005 19:03:20 -0400 Hi, On May 25, 2005, at 1:28 PM, Jessica Svensson wrote: That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. The ability to do this was just implemented recently in MySQL 5.0, along with some other enhancements to LOAD DATA INFILE in 5.0. I don't think the documentation has been updated yet to include the new information though. Hopefully it will be soon. You see the changeset and a preliminary description at http://lists.mysql.com/internals/23068 That should be able to do what you want (and more cool stuff!) Prior to that version, you would have to do as he recommended below with a separate staging table. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. 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 Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html _ Chat: Ha en fest på Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer Checka in här! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
