Re: Issue with timestamp columns while working with MySQL load data in file
On 2015/04/12 08:52, Pothanaboyina Trimurthy wrote: The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Look up function SYSDATE: all other times timestamps are kept in step, beginning at the time when the transaction begins. Your described effect is intended. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Issue with timestamp columns while working with MySQL load data in file
Hi All, I am facing an issue with timestamp columns while working with MySQL load data in file, I am loading around a million records which is taking around 2 hours to complete the load data. Before get into more details about the problem, first let me share the table structure. CREATE TABLE `test_load_data` ( `id1` int(11) DEFAULT NULL, `col10` varchar(255) DEFAULT NULL, `DB_CREATED_DATETIME` datetime DEFAULT NULL, `DB_MODIFIED_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; LOAD DATA LOCAL INFILE '/x.dat' INTO TABLE test_load_data FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id1,col10,@DB_CREATED_DATETIME) SET DB_CREATED_DATETIME = NOW() ; mysql select DB_CREATED_DATETIME,DB_MODIFIED_DATETIME,count(1) from test_load_data group by DB_CREATED_DATETIME,DB_MODIFIED_DATETIME; +-+--+--+ | DB_CREATED_DATETIME | DB_MODIFIED_DATETIME | count(1) | +-+--+--+ | 2015-04-07 10:08:09 | 2015-04-07 10:08:09 | 100 | +-+--+--+ 1 row in set (2.14 sec) The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Thank you in advance. -- Thanks, Trimurthy P Mobile : +91 97397 64298 http://mysqlinternals.blogspot.in/ https://www.linkedin.com/pub/trimurthy-pothanaboyina/5a/9a9/96b
kill LOAD DATA INFILE
Hi all,
I've a question, i need to killing a load data in file. Normally used
show processlist and kill PID. But don't work.
any idea?
Thanks :D
{ name : Rafael Valenzuela,
open source: [Saiku Admin Console,Anaytical Sport],
location : Madrid Spain,
twitter : [@sowe https://twitter.com/sowe] ,
linkedin : profilehttp://es.linkedin.com/pub/rafael-valenzuela/19/694/61a/
}
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
LOAD DATA in replication
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
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
LOAD DATA INFILE with space after quote but before comma
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 | ++--+
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
LOAD DATA INFILE Problem
Hello, after switching from MySQL 5.0.67 to 5.1.59 we get the following problem: We want to import data from a textfile example of the textfile: t...@test.com$yes$432145$xyz The command is: LOAD DATA LOCAL INFILE 'textfile.txt' INTO TABLE TESTTABLE FIELDS TERMINATED BY '$'; Selecting the data from the TESTTABLE by select * from TESTTABLE; the data in the first field looks like |test.com, if I do a select * from TESTTABLE\G I can see t...@test.com in the first field. Are there any changes between MySQL 5.0 and 5.1 ? Best Regards Spiker -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error in mysql replication with LOAD DATA INFILE
The application is designed to work such a way that it will process the csv files daily as part of the aggregate process to calculate some metrics. it runs fine on the master, when it come to slave through replicaiton it fails with the error. i even tried upgrading the slave to latest version mysql 5.1.53 after i see some post on the internet saying we have some issues in the older version , but it keeps giving the same error. thanks Anand On Mon, Dec 20, 2010 at 7:42 PM, who.cat win@gmail.com wrote: i wanna know you have done LOAD DATA INFILE in master ,why are you tring to do it in the slave ?The master didn't replication the data to the master ? All you best What we are struggling for ? The life or the life ? On Mon, Dec 20, 2010 at 3:32 PM, Anand Kumar sanan...@gmail.com wrote: On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error '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 ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: 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 ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
Error in mysql replication with LOAD DATA INFILE
Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error '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 ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: 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 ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
Re: Error in mysql replication with LOAD DATA INFILE
Hi Anand, Just try 'load data local infile',it maybe work. Eric 2010/12/20 Anand anand@gmail.com: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error '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 ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: 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 ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error in mysql replication with LOAD DATA INFILE
On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error '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 ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: 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 ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
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=arch...@jab.org
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
load data in php
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
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: Any faster building primary/unique indexes after Load Data Infile?
Hi, On Sun, Feb 21, 2010 at 1:42 PM, mos mo...@fastmail.fm wrote: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Yes. It is going to create a new table, copy the rows into it, and then delete the old one. Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. Dirty hacks with .frm files and REPAIR TABLE have sometimes been known to help in cases like this. But it's not for the faint of heart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Any faster building primary/unique indexes after Load Data Infile?
Then before loading alter table table_name disable keys; load data alter table table enable keys; This will enable faster data load and faster index rebuild. regards anandkl On Fri, Feb 26, 2010 at 8:03 AM, Baron Schwartz ba...@xaprb.com wrote: Hi, On Sun, Feb 21, 2010 at 1:42 PM, mos mo...@fastmail.fm wrote: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Yes. It is going to create a new table, copy the rows into it, and then delete the old one. Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. Dirty hacks with .frm files and REPAIR TABLE have sometimes been known to help in cases like this. But it's not for the faint of heart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Any faster building primary/unique indexes after Load Data Infile?
I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. TIA Mike MySQL 5.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Any faster building primary/unique indexes after Load Data Infile?
Generally, you should find that removing and re-adding the indexes will speed up your operation. I do not believe that ALTER TABLE with just index additions will require a table rebuild, but even if it does, doing a table copy will be a fairly fast operation (much faster than loading from other sources). Don't forget to set the MyISAM sort buffer size high while you create the indexes. / Carsten mos skrev: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. TIA Mike MySQL 5.1 -- 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
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=arch...@jab.org
LOAD DATA INFILE Syntax error
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=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
load data into temporary table
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: mysqldump failing to load data
Hi, MySQL v4.1.22 on Linux 2.6.18-6-686 I have a dump file generate with mysqldump created by a version 4.1.10 server. I want to import the dump file into a different server. When I run mysqldump --database mydb --debug mydumpfile.sql I get the following: -- MySQL dump 10.9 -- -- Host: localhost Database: mydb -- -- -- Server version 4.1.22-debug-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40101 SET sql_mo...@old_sql_mode */; /*!40014 SET foreign_key_chec...@old_foreign_key_checks */; /*!40014 SET unique_chec...@old_unique_checks */; /*!40101 SET character_set_clie...@old_character_set_client */; /*!40101 SET character_set_resul...@old_character_set_results */; /*!40101 SET collation_connecti...@old_collation_connection */; /*!40111 SET sql_not...@old_sql_notes */; The database remains empty. Is there some incompatibility between the data I am trying to import and the installed server? There is the following from the trace log: | cli_read_query_result | mysql_select_db | | enter: db: 'spl2' | | net_clear | | | vio_blocking | | | | enter: set_blocking_mode: 0 old_mode: 1 | | | | exit: 0 | | | vio_blocking | | | vio_read | | | | enter: sd=4, buf=0xb7c97008, size=1047551 | | | | vio_error: Got error 11 during read | | | | exit: -1 | | | vio_read | | | vio_blocking | | | | enter: set_blocking_mode: 1 old_mode: 0 | | | | exit: 0 | | | vio_blocking | | net_clear User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 679, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 4, Involuntary context switches 3 Does anyone have any ideas what the probem might be? Thanx, Dp. hi, mysql error 11 indicates that the system cannot create new thread or resource is temporarily unavailable. two reasons: 1. server is out of memory 2. used up all file descriptors -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump failing to load data
Hi, On Thu, Apr 2, 2009 at 1:18 PM, Virgilio Quilario virgilio.quila...@gmail.com wrote: Hi, MySQL v4.1.22 on Linux 2.6.18-6-686 I have a dump file generate with mysqldump created by a version 4.1.10 server. I want to import the dump file into a different server. When I run mysqldump --database mydb --debug mydumpfile.sql If you are running that command to import then you are sure to have a problem. Use: mysql dumpfile http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html Or in your case mysql mydb mydumpfile.sql Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump failing to load data
Hi, MySQL v4.1.22 on Linux 2.6.18-6-686 I have a dump file generate with mysqldump created by a version 4.1.10 server. I want to import the dump file into a different server. When I run mysqldump --database mydb --debug mydumpfile.sql I get the following: -- MySQL dump 10.9 -- -- Host: localhostDatabase: mydb -- -- -- Server version 4.1.22-debug-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40101 SET sql_mo...@old_sql_mode */; /*!40014 SET foreign_key_chec...@old_foreign_key_checks */; /*!40014 SET unique_chec...@old_unique_checks */; /*!40101 SET character_set_clie...@old_character_set_client */; /*!40101 SET character_set_resul...@old_character_set_results */; /*!40101 SET collation_connecti...@old_collation_connection */; /*!40111 SET sql_not...@old_sql_notes */; The database remains empty. Is there some incompatibility between the data I am trying to import and the installed server? There is the following from the trace log: | cli_read_query_result | mysql_select_db | | enter: db: 'spl2' | | net_clear | | | vio_blocking | | | | enter: set_blocking_mode: 0 old_mode: 1 | | | | exit: 0 | | | vio_blocking | | | vio_read | | | | enter: sd=4, buf=0xb7c97008, size=1047551 | | | | vio_error: Got error 11 during read | | | | exit: -1 | | | vio_read | | | vio_blocking | | | | enter: set_blocking_mode: 1 old_mode: 0 | | | | exit: 0 | | | vio_blocking | | net_clear User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 679, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 4, Involuntary context switches 3 Does anyone have any ideas what the probem might be? Thanx, Dp. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqlimport/load data infile is using a temp file - why?
Hi, I've been trying to import a 10G dump file using mysqlimport and it is eventually failing because it runs out of tmpdir space -- I get Errcode: 28. I was surprised that it was using a temp file at all. I've looked in the documentation and other sources but have not been able to find anything about this file. This is on v5.0.51. The table is innodb and has 75 columns with 65% of them tinyint, 20% float, and the rest char,datetime,int. The primary key is composite on two columns (int,char). The data file that is being imported is on the database server. I'll try this again and monitor the status parameters to see what it is doing, but I was wondering if anyone already knows? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport/load data infile is using a temp file - why?
Mysql use tmpdir, when ever there is any index creation. regards anandkl On 8/21/08, jthorpe [EMAIL PROTECTED] wrote: Hi, I've been trying to import a 10G dump file using mysqlimport and it is eventually failing because it runs out of tmpdir space -- I get Errcode: 28. I was surprised that it was using a temp file at all. I've looked in the documentation and other sources but have not been able to find anything about this file. This is on v5.0.51. The table is innodb and has 75 columns with 65% of them tinyint, 20% float, and the rest char,datetime,int. The primary key is composite on two columns (int,char). The data file that is being imported is on the database server. I'll try this again and monitor the status parameters to see what it is doing, but I was wondering if anyone already knows? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport/load data infile is using a temp file - why?
You should increase parameter named max_bulk_insert_buffer_size and max_allowed_packet. On 8/21/08, Ananda Kumar [EMAIL PROTECTED] wrote: Mysql use tmpdir, when ever there is any index creation. regards anandkl On 8/21/08, jthorpe [EMAIL PROTECTED] wrote: Hi, I've been trying to import a 10G dump file using mysqlimport and it is eventually failing because it runs out of tmpdir space -- I get Errcode: 28. I was surprised that it was using a temp file at all. I've looked in the documentation and other sources but have not been able to find anything about this file. This is on v5.0.51. The table is innodb and has 75 columns with 65% of them tinyint, 20% float, and the rest char,datetime,int. The primary key is composite on two columns (int,char). The data file that is being imported is on the database server. I'll try this again and monitor the status parameters to see what it is doing, but I was wondering if anyone already knows? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysql - load data file question..
can u please show use the content of the test.csv file. Also is comapny
name a single column or two different columns
If its two different columns than try this
load data file '/foo/test.csv' into table abc.test fields terminated by ','
(company,name);
On 6/28/08, bruce [EMAIL PROTECTED] wrote:
Hi..
I've got an issue with doing a Load data file cmd..
my test text tbl has a column named company name i'm trying to figure out
how to use the load data file cmd, to be able to extract the company name
col...
when i do:
load data file '/foo/test.csv' into table abc.test (company name);
load data file '/foo/test.csv' into table abc.test ('company name');
i get errs for both of the above...
i can get this to work if i have a column with a single name...
can't find any pointers via google..
any pointers/thoughts/etc...
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql - load data file question..
Hi..
I've got an issue with doing a Load data file cmd..
my test text tbl has a column named company name i'm trying to figure out
how to use the load data file cmd, to be able to extract the company name
col...
when i do:
load data file '/foo/test.csv' into table abc.test (company name);
load data file '/foo/test.csv' into table abc.test ('company name');
i get errs for both of the above...
i can get this to work if i have a column with a single name...
can't find any pointers via google..
any pointers/thoughts/etc...
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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]
Problem attempting to use load data into
Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/ elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:29 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:33 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P I don't know you from Adam, you insignificant little cur! ;-P Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 4:37 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. Just to complete the archives, This did fix it. Make sure you don't try and put literal tab values \t and new line values \n into your data and it should work just fine! So thank you Dan for your help! And everyone else as well! -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport load data infile
i read about mysqlimport load data infile for mysql, but i can't find a way to import text file using length of column, instead of delimiter my text file contains fixed length column: -- i can use ms excel to convert all files to .csv format and import, but it would take a long time and i have to escape delimiter. so, is there a way to import text file with fixed column size into mysql??? thanks -- 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
Odd behavior of 'load data'
Hi, I'm developing a PHP/MySQL app, and I use load data infile to feed data to MySQL. At the develop server I haven't any error, the app works great, but when I upload to the production server, all the load data infile statements fails with an error like this: can't stat path/to/my_cvs_file The funny thing is that if I connect at production server to mysql as mysql's super user I have not problem, the app work fine, but if I use another user, then the app fails with the can't stat error. I know my user needs the File privilege so I told the mysql's admin that. First he used phpmyadmin to create my user and set privileges, but when I told you about the error, he use the mysql console with the command: grant all privileges on mydatabase.* to [EMAIL PROTECTED] identified by 'mypasswd' with grant option; But I got the same error. This is what I get if I query my mysql's user: *** 1. row *** Host: localhost User: filasPOS Password: 7b47ae4b0907b267 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 *** 2. row *** Host: % User: filasPOS Password: 7b47ae4b0907b267 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: Y Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: Y Lock_tables_priv: N Execute_priv: Y Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 any clue? thanks in advance. -- Mauricio Tellez
Re: Odd behavior of 'load data'
Mauricio Tellez wrote: Hi, I'm developing a PHP/MySQL app, and I use load data infile to feed data to MySQL. At the develop server I haven't any error, the app works great, but when I upload to the production server, all the load data infile statements fails with an error like this: can't stat path/to/my_cvs_file That's usually a file permission problem rather than a mysql one. If you have root access to the server, su to the mysql user and try: stat path/to/my_file does that work or give the same error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd behavior of 'load data'
You should paste the result of command show grants for 'filasPOS'@'localhost',not the message pasted here. On Jan 8, 2008 12:04 PM, Mauricio Tellez [EMAIL PROTECTED] wrote: Hi, I'm developing a PHP/MySQL app, and I use load data infile to feed data to MySQL. At the develop server I haven't any error, the app works great, but when I upload to the production server, all the load data infile statements fails with an error like this: can't stat path/to/my_cvs_file The funny thing is that if I connect at production server to mysql as mysql's super user I have not problem, the app work fine, but if I use another user, then the app fails with the can't stat error. I know my user needs the File privilege so I told the mysql's admin that. First he used phpmyadmin to create my user and set privileges, but when I told you about the error, he use the mysql console with the command: grant all privileges on mydatabase.* to [EMAIL PROTECTED] identified by 'mypasswd' with grant option; But I got the same error. This is what I get if I query my mysql's user: *** 1. row *** Host: localhost User: filasPOS Password: 7b47ae4b0907b267 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 *** 2. row *** Host: % User: filasPOS Password: 7b47ae4b0907b267 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: Y Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: Y Lock_tables_priv: N Execute_priv: Y Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 any clue? thanks in advance. -- Mauricio Tellez -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Odd behavior of 'load data'
That's the output of the stat command: File: `/var/www/apache2-default/filas/admin/upload/rankeo_pos.csv' Size: 2909Blocks: 8 IO Block: 4096 archivo regular Device: 802h/2050d Inode: 672206 Links: 1 Access: (0666/-rw-rw-rw-) Uid: ( 33/www-data) Gid: ( 33/www-data) Access: 2008-01-07 23:43:21.0 -0600 Modify: 2008-01-07 23:43:21.0 -0600 Change: 2008-01-07 23:43:21.0 -0600 as you can see the file is rw for everybody and all the path has access rights. Let me explain again, perhaps my english isn't good enough: I create a mysql root user with the following command: *mysqladmin* -u *root password* my_passwdand if I connect with this user like: $db = mysqli_connect(localhost, root, my_passwd); mysqli_select_db($db, 'filasPOS'); I can use the load data statement without errors. But when I tried to connect with the user filasPOS (you can see the grant statement that I use with this user at my first post, and Moon's Father by the way, the output of the grant statement was: Query Ok, 0 rows affected) like this: $db = mysqli_connect(localhost, filasPOS, mypasswd); mysqli_select_db($db, 'filasPOS'); and try to use the load data statement I got the error. I think this really is a mysql problem and no an OS file permision problem, because mysql always use the user mysqld is running to access the file specified by load data, and the user you use to connect to mysql has nothing to do in that, am I right? So, why this work properly with the root user but not with the filasPOS user? Thanx for the help 2008/1/7, Chris [EMAIL PROTECTED]: Mauricio Tellez wrote: Hi, I'm developing a PHP/MySQL app, and I use load data infile to feed data to MySQL. At the develop server I haven't any error, the app works great, but when I upload to the production server, all the load data infile statements fails with an error like this: can't stat path/to/my_cvs_file That's usually a file permission problem rather than a mysql one. If you have root access to the server, su to the mysql user and try: stat path/to/my_file does that work or give the same error? -- Mauricio Tellez
Re: Odd behavior of 'load data'
Thanks a lot Joe, the LOAD DATA LOCAL did the trick, but just to be courius, why work LOAD DATA with user root but not with filasPOS user, and LOAD DATA LOCAL work with both users? thanks in advance 2008/1/8, joe [EMAIL PROTECTED]: forgot to mention that I an using 5.1 I use mysql_local_infile=1 in the DB connect string also use LOAD DATA local INFILE in the load statement Script run on the DB host and does not run as root. Not special privs were need, the file just needed to be accessible from mysqld -Original Message- From: joe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 08, 2008 12:01 AM To: 'Mauricio Tellez' Subject: RE: Odd behavior of 'load data' You may need to use LOAD DATA LOCAL INFILE. -Original Message- From: Mauricio Tellez [mailto:[EMAIL PROTECTED] Sent: Monday, January 07, 2008 11:43 PM To: Chris Cc: mysql@lists.mysql.com Subject: Re: Odd behavior of 'load data' That's the output of the stat command: File: `/var/www/apache2-default/filas/admin/upload/rankeo_pos.csv' Size: 2909Blocks: 8 IO Block: 4096 archivo regular Device: 802h/2050d Inode: 672206 Links: 1 Access: (0666/-rw-rw-rw-) Uid: ( 33/www-data) Gid: ( 33/www-data) Access: 2008-01-07 23:43:21.0 -0600 Modify: 2008-01-07 23:43:21.0 -0600 Change: 2008-01-07 23:43:21.0 -0600 as you can see the file is rw for everybody and all the path has access rights. Let me explain again, perhaps my english isn't good enough: I create a mysql root user with the following command: *mysqladmin* -u *root password* my_passwdand if I connect with this user like: $db = mysqli_connect(localhost, root, my_passwd); mysqli_select_db($db, 'filasPOS'); I can use the load data statement without errors. But when I tried to connect with the user filasPOS (you can see the grant statement that I use with this user at my first post, and Moon's Father by the way, the output of the grant statement was: Query Ok, 0 rows affected) like this: $db = mysqli_connect(localhost, filasPOS, mypasswd); mysqli_select_db($db, 'filasPOS'); and try to use the load data statement I got the error. I think this really is a mysql problem and no an OS file permision problem, because mysql always use the user mysqld is running to access the file specified by load data, and the user you use to connect to mysql has nothing to do in that, am I right? So, why this work properly with the root user but not with the filasPOS user? Thanx for the help 2008/1/7, Chris [EMAIL PROTECTED]: Mauricio Tellez wrote: Hi, I'm developing a PHP/MySQL app, and I use load data infile to feed data to MySQL. At the develop server I haven't any error, the app works great, but when I upload to the production server, all the load data infile statements fails with an error like this: can't stat path/to/my_cvs_file That's usually a file permission problem rather than a mysql one. If you have root access to the server, su to the mysql user and try: stat path/to/my_file does that work or give the same error? -- Mauricio Tellez Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13 - Release Date: 12/31/2007 12:00 AM Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13 - Release Date: 12/31/2007 12:00 AM Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13 - Release Date: 12/31/2007 12:00 AM -- Mauricio Tellez
Re: Replication Falls Out Of Sync With LOAD DATA
On Nov 28, 2007 11:18 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Master and Slaves are 5.x. Hopefully I've figured out the issue. When the slave would fall out of sync, I would resync using rsync. In that rsync command I was using the --delete option which would delete the relay log from the slave machine. Since the relay log was being removed constantly, the replication was in a strange state. That problem is fixed so hopefully things should stay in sync. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Falls Out Of Sync With LOAD DATA
We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What do you mean by falls out of sync? LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by out of sync :) On Nov 28, 2007 4:32 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- 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: Replication Falls Out Of Sync With LOAD DATA
The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Baron Schwartz wrote: What do you mean by falls out of sync? LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by out of sync :) On Nov 28, 2007 4:32 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data
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).
thnx,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
load data infile - fails to load my db2 del (ascii) file
hi all, right now i'm trying to migrate from db2 running under linux to mysql v5.1. i manage to export out the db2 structure data into a del (ascii) file. but when i try to load the data from the del file to mysql table, it generate an error. below is the load data infile syntax i use = LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'; and below is the sample layout of del file (in the above case is TABLE01.del) = 1,0,2007-07-31 00:25:12 2,0,2007-07-31 14:09:00 3,0,2007-07-31 00:00:00 4,0,2007-07-31 00:00:00 5,0,2007-07-31 00:00:00 and below is the TABLE01 structure = FieldName, Type, Null, Primary -- MY_TABLEID, int(11), NO, PRIMARY KEY CNTS, int(11), NO, NOT PRIMARY KEY INSERT_DATE, datetime, NO, PRIMARY KEY the error i encounter is = ERROR 1292 (22007): Incorrect datetime value: '2007-07-31 00:25:12. i do some trick by trying to remove the qoute sign from 2007-07-31 00:25:12 so the del file become = 1,0,2007-07-31 00:25:12 2,0,2007-07-31 14:09:00 3,0,2007-07-31 00:00:00 4,0,2007-07-31 00:00:00 5,0,2007-07-31 00:00:00 And i do load data infile again with same command = LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'; and it was successful. to me it is very strange, since i can do a sql insert using 2007-07-31 00:25:12 for example: insert into TABLE01 values (11, 22, 2007-07-31 00:25:12) successfully without any error!!! in fact, when i use a MySQL Administrator tools, and i export out the data from TABLE01 into CSV, then i found that the structure is exactly the same with del file generated by db2. do i make any mistake?? or do i need to initialize something before i call LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' ...? Any help is appreciated. Thank you. Regards, Lanes -- View this message in context: http://www.nabble.com/load-data-infile---fails-to-load-my-db2-del-%28ascii%29-file-tf4762331.html#a13620281 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and 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]
load data infile and character set
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
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]
data position changes when 'Load Data infile'......
Hi Friend, Today I was testing the command 'Load data infile ...' command ( http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html ) in my system. That time I was surprised when I put select statement in that table. The scenario as follows : In a text file which is to be loaded, I am having data as follows: 3 v,4 a mysql desc mytable; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | foo | char(1) | YES | | NULL|| +---+-+--+-+-++ 2 rows in set (0.00 sec) mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| ++--+ 2 rows in set (0.01 sec) mysql load data infile '/home/mysql/egdata' into table mytable lines terminated by ','; Query OK, 2 rows affected, 0 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| | 3 | v| | 4 | a| ++--+ 4 rows in set (0.00 sec) mysql delete from mytable where id in (3,4); Query OK, 2 rows affected (0.00 sec) mysql load data infile '/home/mysql/egdata' into table mytable lines terminated by ','; Query OK, 2 rows affected, 0 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| | 4 | a| | 3 | v| ++--+ 4 rows in set (0.00 sec) The select query gives the different orders for value '3' and '4' which was loaded twice with same txt file. Why is this happening like this? Any reason or algorithm involve in this?
Re: [solved]LOAD DATA INTO doesn't work correctly with utf8
Hi Ananda, Ananda Kumar schrieb: So you set the collation_database=utf8_bin, what was your character_set_database values. character_set_database is utf8. The collation utf8_bin slows down queries, but is necessary in dealing with multilingual information. utf8_general_ci is faster, but can not distinguish in keys between symbols which are sorted at the same position in national character sets, like e.g. German a and ä, or French e and é. 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: [solved]LOAD DATA INTO doesn't work correctly with utf8
Okie, i will also try this, as we also load data from a flat file. regards anandkl On 8/31/07, Harald Vajkonny [EMAIL PROTECTED] wrote: Hi Ananda, Ananda Kumar schrieb: So you set the collation_database=utf8_bin, what was your character_set_database values. character_set_database is utf8. The collation utf8_bin slows down queries, but is necessary in dealing with multilingual information. utf8_general_ci is faster, but can not distinguish in keys between symbols which are sorted at the same position in national character sets, like e.g. German a and ä, or French e and é. 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?
LOAD DATA INTO doesn't work correctly with utf8
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
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: [solved]LOAD DATA INTO doesn't work correctly with utf8
Harald Vajkonny schrieb: 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. That was it. If I choose utf8_bin as collation everything works fine! :) 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: [solved]LOAD DATA INTO doesn't work correctly with utf8
Hi Harald, So you set the collation_database=utf8_bin, what was your character_set_database values. regards anandkl On 8/30/07, Harald Vajkonny [EMAIL PROTECTED] wrote: Harald Vajkonny schrieb: 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. That was it. If I choose utf8_bin as collation everything works fine! :) 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]
performance of extended insert vs. load data
all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks!
Re: performance of extended insert vs. load data
On 7/23/07, Sid Lane [EMAIL PROTECTED] wrote: is either one significantly faster than the other? Yes, LOAD DATA INFILE is much faster. are there additional (faster) approaches I have not thought of? Not that I've found. I think you'd have to write directly to the C API to beat LOAD DATA INFILE. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
