Problem in LOAD DATA INFILE
Hi, I am facing problem while inserting more than 20,000 records using LOAD DATA INFILE statement through comma seperated file. Here is my statement: LOAD DATA LOCAL INFILE '/home/mysql/isa_logs/a.txt' INTO TABLE FirewallLog_1 FIELDS TERMINATED BY ','; I have persistantly observed that when in try to run this statement with a file having 20,000 records. The problems starts when records in the files are more then 20,262 ERROR 1030: Got error 28 from table handler This is the error I get. MySQL Version: 4.0.13 Host: Linux - Red Hat 7,0 Is this related to my sql global variables or hardware resources ? Thanks in advance. Regards, Alpesh Kothari Oracle DBA Net4Nuts Limited [EMAIL PROTECTED]
Re: Problem in LOAD DATA INFILE
Alpesh Kothari wrote: Hi, I am facing problem while inserting more than 20,000 records using LOAD DATA INFILE statement through comma seperated file. Here is my statement: LOAD DATA LOCAL INFILE '/home/mysql/isa_logs/a.txt' INTO TABLE FirewallLog_1 FIELDS TERMINATED BY ','; I have persistantly observed that when in try to run this statement with a file having 20,000 records. The problems starts when records in the files are more then 20,262 ERROR 1030: Got error 28 from table handler This is the error I get. MySQL Version: 4.0.13 Host: Linux - Red Hat 7,0 Is this related to my sql global variables or hardware resources ? Thanks in advance. Regards, Alpesh Kothari Oracle DBA Net4Nuts Limited [EMAIL PROTECTED] bash$ perror 28 Error code 28: No space left on device Free up some disk space, get a bigger disk, or move your data or temp directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with load data infile and user's privileges
OS Linux Red Hat 7.2 MySql 3.23.41 Hi All! That is my steps. With root privileges I doing: GRANT ALL ON mydb.* TO client@localhost identified by pwd; I got: OK With client privileges !!! I doing: mysql -u client -p passwd CREATE DATABASE mydb; I got: OK CREATE TABLE mytable ( col01 CHAR(2), col02 CHAR(2)); I got: OK Then I doing: LOAD DATA INFILE /usr/data/myfile INTO TABLE tab01; I got: ERROR 1045 Access denied fot user: 'client@localhost' (Using password: YES) Also I changed manualy for user(client) in database mysql in tables user db from N to Y Doesn't help. What is wrong I am doing? Why whith my client's privileges I can create databases, tables but can't load any data in to the tables? Thank you. Sergey. _ Cool Quiz - Websites That Keep You Guessing! http://www.coolquiz.com _ Promote your group and strengthen ties to your members with [EMAIL PROTECTED] by Everyone.net http://www.everyone.net/?btn=tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with load data infile and user's privileges
On Sat, 15 Jun 2002, Sergey Bondar wrote: OS Linux Red Hat 7.2 MySql 3.23.41 Hi All! That is my steps. With root privileges I doing: GRANT ALL ON mydb.* TO client@localhost identified by pwd; I got: OK The user needs the file privilege. Granting all on mydb.* does not give this privilege because the file privilege is a global privilege. To do this: GRANT FILE ON *.* TO... This will give the user access to files on the server, so you may want to think twice about giving them this privilege. http://www.mysql.com/doc/G/R/GRANT.html Regards, Jason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with load data infile and user's privileges
OS Linux Red Hat 7.2 MySql 3.23.41 Hi All! That is my steps. With root privileges I doing: GRANT ALL ON mydb.* TO client@localhost identified by pwd; I got: OK With client privileges !!! I doing: mysql -u client -p passwd CREATE DATABASE mydb; I got: OK CREATE TABLE mytable ( col01 CHAR(2), col02 CHAR(2)); I got: OK Then I doing: LOAD DATA INFILE /usr/data/myfile INTO TABLE tab01; I got: ERROR 1045 Access denied fot user: 'client@localhost' (Using password: YES) Also I changed manualy for user(client) in database mysql in tables user db from N to Y Doesn't help. What is wrong I am doing? Why whith my client's privileges I can create databases, tables but can't load any data in to the tables? Thank you. Sergey. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with load data infile and user's privileges
On Fri, 14 Jun 2002, Sergey wrote: snip With root privileges I doing: GRANT ALL ON mydb.* TO client@localhost identified by pwd; I got: OK snip Then I doing: LOAD DATA INFILE /usr/data/myfile INTO TABLE tab01; I got: ERROR 1045 Access denied fot user: 'client@localhost' (Using password: YES) Also I changed manualy for user(client) in database mysql in tables user db from N to Y Doesn't help. What is wrong I am doing? Why whith my client's privileges I can create databases, tables but can't load any data in to the tables? The user client@localhost also needs the global privilege File_priv in MySQL.user. Section 4.2.5 How the Privilege System Works has the details. I hope this fixes your problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with load data infile and user's privileges
At 20:23 -0400 6/14/02, Sergey wrote: OS Linux Red Hat 7.2 MySql 3.23.41 Hi All! That is my steps. With root privileges I doing: GRANT ALL ON mydb.* TO client@localhost identified by pwd; I got: OK With client privileges !!! I doing: mysql -u client -p passwd CREATE DATABASE mydb; I got: OK CREATE TABLE mytable ( col01 CHAR(2), col02 CHAR(2)); I got: OK Then I doing: LOAD DATA INFILE /usr/data/myfile INTO TABLE tab01; I got: ERROR 1045 Access denied fot user: 'client@localhost' (Using password: YES) Also I changed manualy for user(client) in database mysql in tables user db from N to Y Doesn't help. What is wrong I am doing? Why whith my client's privileges I can create databases, tables but can't load any data in to the tables? You're asking the server to load a file on the server host, which requires the FILE privilege. This is a global privilege, not a database-specific privilege. If the file is on your client machine, use LOAD DATA LOCAL INFILE instead. Thank you. Sergey. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with Load Data InFile
Dear all, I had a problem with load data infile syntax LOAD DATA INFILE '$file' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' that the original data has already carriage returns was considered as new records in the database, I solved it by using the following syntax: LOAD DATA INFILE '$file' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '^' well it worked BUT it generates a strange blank charachter at the beginning of the first field of each record is there any soloution that brings me peace of mind for such problems, concerning that I previously suffered from developing in local environment before publishing which forced me to use two different syntaxes of LOAD DATA... the first one with ADDSLASHES() function to the $file argument and the second without this funcion Please advice if you a solution - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with LOAD DATA INFILE
HI ALL: Happy New Year ;) I have a text file generated from MSAccess database, one of the fields (Long Text) have Carriage returns within the text, So the data of some records falls in more than one line in the text file, when I'm using the SQL statement LOAD DATA INFILE... I have only the first line of the data in this field, You know the data in the file looks like : 8|RO|The rates are applicabe for prepaid shipments only. Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n 9|KL|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp, Security Charge 0.13Usd per Kg based on actual weight of the shipment.|\r\n 10|JU|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n Please Help Best Regards Hayan Get your own 800 number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with LOAD DATA INFILE
Are you using mysqlimport utility? If not that may be you answer? If so what flags are you setting? david HI ALL: Happy New Year ;) I have a text file generated from MSAccess database, one of the fields (Long Text) have Carriage returns within the text, So the data of some records falls in more than one line in the text file, when I'm using the SQL statement LOAD DATA INFILE... I have only the first line of the data in this field, You know the data in the file looks like : 8|RO|The rates are applicabe for prepaid shipments only. Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n 9|KL|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp, Security Charge 0.13Usd per Kg based on actual weight of the shipment.|\r\n 10|JU|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n Please Help Best Regards Hayan Get your own 800 number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with LOAD DATA INFILE
I suggest writing a PERL script/filter which accepts the text file and sends a comma-delimited file to standard-out. This file could then be sucked in by mysql -Original Message- From: Michael Benbow [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 30, 2001 7:48 PM To: [EMAIL PROTECTED] Subject: Problem with LOAD DATA INFILE Hello all, I have a problem. I am able to get a copy of a text file which resides on another server for a task I am completing. What I need to do is import this text into a database table on a nightly basis, and I am trying to set this up via PHP so it is all automated. I have one problem though, and that is the format of the text file. The text file is not comma delimited or tab delimited, but it is a fixed format with each column spaced out evenly. I created a table with the same lengths as each field from the text file then I used the following line: LOAD DATA INFILE '/path/to/file.txt' INTO TABLE table_name IGNORE 1 LINES; This inserted the first field correctly but all other fields are NULL. If the file is tab delimited or comma delimited I'd have no problems, but since it isn't I am completely stuck... PLEASE HELP ME!!! Thank you, Michael. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with LOAD DATA INFILE
Hello all, I have a problem. I am able to get a copy of a text file which resides on another server for a task I am completing. What I need to do is import this text into a database table on a nightly basis, and I am trying to set this up via PHP so it is all automated. I have one problem though, and that is the format of the text file. The text file is not comma delimited or tab delimited, but it is a fixed format with each column spaced out evenly. I created a table with the same lengths as each field from the text file then I used the following line: LOAD DATA INFILE '/path/to/file.txt' INTO TABLE table_name IGNORE 1 LINES; This inserted the first field correctly but all other fields are NULL. If the file is tab delimited or comma delimited I'd have no problems, but since it isn't I am completely stuck... PLEASE HELP ME!!! Thank you, Michael. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
