John Kopanas wrote:
Argh... I am autogenerating a load file after parsing and massaging a
text file I get from an AS/400 system. My first field is an id that
is on auto_number so I put a tab at the begging of the line for each
one of my records... it throws a warning for each line... if instead
of putting an autonumber I put a random number it works perfectly.
How can I get it to work?
Sample line in my load file that works:
111111111111111111111 3066001210010000001467 SMART ALARMS INC 201
MILLWAY AVE UNIT #8 \N \N VAUGHAN ON L4K 5K8 0
1999-9-1
00:00:00 1999-9-1 00:00:00
Sample line that does not work:
3066001210010000001467 SMART ALARMS INC 201 MILLWAY AVE UNIT
#8 \N \N VAUGHAN ON L4K 5K8 0 1999-9-1 00:00:00
1999-9-1 00:00:00
My code that generates the sample line that does not work:
tempfile.puts
"\t#{record[0,22].strip}\t#{record[22,32].strip}\t#{record[54,32].strip}\t#{record[86,32].strip}\t#{record[118,32].strip}\t#{record[150,30].strip}\t#{record[180,2].strip}\t#{record[182,7].strip}\t0\t2000-01-01
00:00:00\t2000-01-01 00:00:00"
How can I get mysql to put the auto_numbers in for me? Why is it even
accepting my numbers?
Thanks :-)
Does anyone also know how I can see the warnings created by loadfile?
All I just see is the summary with skipped and warnings.
Thanks :-)
Try with 0 at autoinc position. If 0 or NULL doesn't do the trick...
well perhaps you should check your --sql-mode=
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns.
Normally, you generate the next sequence number for the column by
inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses
this behavior for 0 so that only NULL generates the next sequence number.
This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT
column. (Storing 0 is not a recommended practice, by the way.) For
example, if you dump the table with mysqldump and then reload it, MySQL
normally generates new sequence numbers when it encounters the 0 values,
resulting in a table with contents different from the one that was
dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file
solves this problem. mysqldump now automatically includes in its output
a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]