Incomplete UPDATE after bulk-loading with LOAD DATA INFILE

Similar Threads:

1.Bulk Load not loading all XML data

2.MySQL skipping one column with LOAD DATA INFILE

Hello all,

I am troubleshooting a maddening problem with MySQL 5.0.18-nt on my XP
Pro dev
box. When running a LOAD DATA INFILE... command, it works correctly
when
executed from the MySQL command line, but skips one column (yet loads
the rest
of the table normally) when the exact same command is run from within
PHP5.

Has anyone experienced this? Or better yet, resolved it? Anybody know
why it's
happening?

The table in question is:

CREATE TABLE clean_events (
    id              INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY
KEY,
    record_status   ENUM('INCOMPLETE','NEW','OK') NOT NULL DEFAULT
'INCOMPLETE',
    recycle_status  ENUM('ACTIVE','RECYCLED','DELETED') NOT NULL
DEFAULT
'ACTIVE',
    event_name      VARCHAR(255) DEFAULT NULL,
    artist_ids      VARCHAR(255) DEFAULT NULL,
    venue_id        INT(10) UNSIGNED NOT NULL DEFAULT '0',
    start_date      DATE NOT NULL DEFAULT '0000-00-00',
    end_date        DATE NOT NULL DEFAULT '0000-00-00',
    ticket_urls     TEXT,
    notes           TEXT
);

The 'artist_ids' column is the one being skipped.

Any ideas?

Regards,
-Ray

3.problem with LOAD DATA INFILE and TIMESTAMP default

	1) I'm running 5.1.26, with "sql strict" mode.
	2) I have an existing table 'p':

mysql> desc p;
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                                      | Null | 
Key | Default           | Extra                       |
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+
| id             | int(11)                                   | NO   | 
PRI | NULL              | auto_increment              |
| full       | varchar(32)                               | NO   | 
|                   |                             |
| use        | varchar(32)                               | NO   | 
|                   |                             |
| serial | int(11)                                   | YES  |     | 0 
                 |                             |
| status         | enum('active','retired','dead','unknown') | YES  | 
     | active            |                             |
| notes          | text                                      | YES  | 
     | NULL              |                             |
| last_updated   | timestamp                                 | NO   | 
     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+

	3) I would like to load data into this table using:

LOAD DATA INFILE '/home/huff/code/sql/data.new'
	INTO TABLE p
	FIELDS TERMINATED BY '|';

	4) When I use that LOAD DATA command with this data:

0|Suka|Suka|0|active||


	I get this:

ERROR 1292 (22007): Incorrect datetime value: '' for column 
'last_updated' at row 1

	5) If I use

LOAD DATA INFILE '/home/huff/code/sql/names.new'
	INTO TABLE p set last_updated=NOW()
	FIELDS TERMINATED BY '|';

	I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near 'FIELDS TERMINATED BY '|'' at line 1

	I've read the 5.1 docs (several times), Googled various parts of the 
error messages, and searched the mailing list and newsgroup archives ...
	... and still managed to miss the essential clue.

	Help, please?
	Respectfully,


     			Robert Huff



	

4.load data infile works on Mysql 4 and not in 5

Hi all, I've this query that works fine on mysql 4.1.22 and not in 5.0.45

LOAD DATA INFILE 'C:/myfile.txt' REPLACE INTO TABLE mytable FIELDS 
TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES 
TERMINATED BY '\n'

CREATE TABLE `mytable` (
  a char(2) NOT NULL default '',
  b tinyint(3) unsigned NOT NULL default '0',
  c mediumint(8) unsigned NOT NULL default '0',
  d char(13) NOT NULL default '',
  e mediumint(8) unsigned NOT NULL default '0',
  f date NOT NULL default '0000-00-00',
  g date default NULL,
  h char(3) default NULL,
  i float(8,2) unsigned default NULL,
  j float(8,2) unsigned default NULL,
  k float(3,2) unsigned default NULL,
  l float(8,2) unsigned default NULL,
  m float(8,2) unsigned default NULL,
  n tinyint(3) unsigned default NULL,
  o tinyint(3) unsigned default NULL,
  p tinyint(3) unsigned default NULL,
  q tinyint(3) unsigned default NULL,
  r tinyint(3) unsigned default NULL,
  s date default NULL,
  u tinyint(3) unsigned default NULL,
  v tinyint(3) unsigned default NULL,
  v tinyint(3) unsigned default NULL,
  x float unsigned default NULL,
  PRIMARY KEY  (a,b,c,d),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

the myfile.txt
CH 10 1073757 102161427 21150 19970401  CHF 0 0 0 0 0 0 0 0 0 0 20061221 0 0 
0 0
CH 10 1073765 102161427 27860 19970401  CHF 0 0 0 0 0 0 0 0 0 0 20061221 0 0 
0 0

Thanks for helping.

Bob

5.Parallel Importing text file in MYSQL using LOAD DATA INFILE

Hi,

I'm pretty new to the mysql world.  I'm trying to understand the
quickest way on how to import a very large tab or common delimited
file into a table programatically.  I have a few questions that
hopefully someone will be kind enough to answer.

1)  Given the following "serial" statement:
LOAD DATA INFILE '/test/bigfile.txt' INTO TABLE tblData;

What is the syntax to change that to a parallel statement?  Would I
split the file into smaller files and then append?

IE:
LOAD DATA INFILE '/test/bigfile1.txt' INTO TABLE tblData;
LOAD DATA INFILE '/test/bigfile2.txt' INTO TABLE tblData;
LOAD DATA INFILE '/test/bigfile3.txt' INTO TABLE tblData;

Or can I keep the file "unsplit"?  And then adjust something on the
actual "LOAD" statement to make it run parallel?

2)  I understand that importing tables into MYISAM tables are faster
than importing it into InnoDB tables.  Is it an appropriate strategy
for fast performance to first import data into a MYISAM table.  Then
to insert the same data into an InnoDB table  for other things such as
updates, data cleansing etc?

3)  If I understand correctly, the LOAD DATA INFILE can't be used in
stored procedures?  It must be created within a batch file then run?

Thanks

6. Load Data Infile question

7. LOAD DATA LOCAL INFILE .... problem

8. LOAD DATA INFILE problem