Loading lots of data into mysql table

[greywire]

So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

That makes it useless to me.

What options do I have?

[Bill Karwin]

gr******@gmail.com wrote:

Only one problem. It truncates fields to 256 characters, even on a
text field.

I do not observe this behavior. I just tested using LOAD DATA INFILE to
load a 300-character string into a VARCHAR column and a TEXT column.
I'm using MySQL Server 5.0.21. What version of MySQL are you using?

Regards,
Bill K.

[Thomas Bartkus]

<gr******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

That makes it useless to me.

What options do I have?

The LOAD DATA INFILE is not truncating your strings!

One would strongly suspect that your field definition is what limits you to
255 characters.
Both CHAR and VARCHAR require that the maximum length be declared and both
have a maximum length of 255. I would venture a beer bet that says you are
truncating to 255 chars (not 256). VARCHAR(255) or CHAR(255) are common
declarations for fields containing text strings. Larger strings for these
field types are illegal. And whatever the declared length - MySQL will
simply truncate the extra characters without complaining.

You would need to move to a BLOB type for larger strings - which you
certainly can do if you need to store longer strings.

My bet is that it's your field declaration that limits you to 255 chars.

Show us a SHOW CREATE TABLE for the one you are trying to stuff with those
long strings.
Thomas Bartkus

[greywire]

MySQL 5.0.18

The field in question (bulk_body) is a text field, so it should not
have a limit anywhere near as low as 255.

CREATE TABLE `lead_bulk` (
`bulk_id` int(10) unsigned NOT NULL,
`bulk_batch_id` int(10) unsigned NOT NULL,
`bulk_source` int(10) unsigned NOT NULL default '0',
`bulk_body` text NOT NULL,
`bulk_cdate` datetime NOT NULL default '0000-00-00 00:00:00',
`bulk_import_date` datetime default NULL,
`bulk_lead_id` int(10) unsigned default NULL,
`bulk_header` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`bulk_id`),
FULLTEXT KEY `bulk_body` (`bulk_body`)
) ENGINE=MyISAM;

According to the documentation for LOAD DATA it does not support Text
or BLOB fields:

" Some cases are not supported by LOAD DATA INFILE:

Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both
empty) and BLOB or TEXT columns. "
Thomas Bartkus wrote:

<gr******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

That makes it useless to me.

What options do I have?

The LOAD DATA INFILE is not truncating your strings!

One would strongly suspect that your field definition is what limits you to
255 characters.
Both CHAR and VARCHAR require that the maximum length be declared and both
have a maximum length of 255. I would venture a beer bet that says you are
truncating to 255 chars (not 256). VARCHAR(255) or CHAR(255) are common
declarations for fields containing text strings. Larger strings for these
field types are illegal. And whatever the declared length - MySQL will
simply truncate the extra characters without complaining.

You would need to move to a BLOB type for larger strings - which you
certainly can do if you need to store longer strings.

My bet is that it's your field declaration that limits you to 255 chars.

Show us a SHOW CREATE TABLE for the one you are trying to stuff with those
long strings.
Thomas Bartkus

[greywire]

And yes, it IS truncating at 256, not 255.

Yes, I understand that 255 is the max for an 8 bit number, and this
would make more sense. But its cutting it off at 256 regardless.

gr******@gmail.com wrote:

MySQL 5.0.18

The field in question (bulk_body) is a text field, so it should not
have a limit anywhere near as low as 255.

CREATE TABLE `lead_bulk` (
`bulk_id` int(10) unsigned NOT NULL,
`bulk_batch_id` int(10) unsigned NOT NULL,
`bulk_source` int(10) unsigned NOT NULL default '0',
`bulk_body` text NOT NULL,
`bulk_cdate` datetime NOT NULL default '0000-00-00 00:00:00',
`bulk_import_date` datetime default NULL,
`bulk_lead_id` int(10) unsigned default NULL,
`bulk_header` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`bulk_id`),
FULLTEXT KEY `bulk_body` (`bulk_body`)
) ENGINE=MyISAM;

According to the documentation for LOAD DATA it does not support Text
or BLOB fields:

" Some cases are not supported by LOAD DATA INFILE:

Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both
empty) and BLOB or TEXT columns. "
Thomas Bartkus wrote:

<gr******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

So I need to load lots of data into my database.
>
So I discover LOAD DATA INFILE.
>
Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.
>
Only one problem. It truncates fields to 256 characters, even on a
text field.
>
That makes it useless to me.
>
What options do I have?
>

The LOAD DATA INFILE is not truncating your strings!

One would strongly suspect that your field definition is what limits you to
255 characters.
Both CHAR and VARCHAR require that the maximum length be declared and both
have a maximum length of 255. I would venture a beer bet that says you are
truncating to 255 chars (not 256). VARCHAR(255) or CHAR(255) are common
declarations for fields containing text strings. Larger strings for these
field types are illegal. And whatever the declared length - MySQL will
simply truncate the extra characters without complaining.

You would need to move to a BLOB type for larger strings - which you
certainly can do if you need to store longer strings.

My bet is that it's your field declaration that limits you to 255 chars.

Show us a SHOW CREATE TABLE for the one you are trying to stuff with those
long strings.
Thomas Bartkus

[Norman Peelman]

<gr******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

That makes it useless to me.

What options do I have?

I was just reading about using TABLE LOCKS to increase the speed of
INSERTs on myisam table types...

mysql_query("LOCK TABLES table WRITE");
....insert code...
mysql_query("UNLOCK TABLES");

Look: http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

---
Norm

[strawberry]

gr******@gmail.com wrote:

And yes, it IS truncating at 256, not 255.

Yes, I understand that 255 is the max for an 8 bit number, and this
would make more sense. But its cutting it off at 256 regardless.

gr******@gmail.com wrote:

MySQL 5.0.18

The field in question (bulk_body) is a text field, so it should not
have a limit anywhere near as low as 255.

CREATE TABLE `lead_bulk` (
`bulk_id` int(10) unsigned NOT NULL,
`bulk_batch_id` int(10) unsigned NOT NULL,
`bulk_source` int(10) unsigned NOT NULL default '0',
`bulk_body` text NOT NULL,
`bulk_cdate` datetime NOT NULL default '0000-00-00 00:00:00',
`bulk_import_date` datetime default NULL,
`bulk_lead_id` int(10) unsigned default NULL,
`bulk_header` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`bulk_id`),
FULLTEXT KEY `bulk_body` (`bulk_body`)
) ENGINE=MyISAM;

According to the documentation for LOAD DATA it does not support Text
or BLOB fields:

" Some cases are not supported by LOAD DATA INFILE:

Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both
empty) and BLOB or TEXT columns. "
Thomas Bartkus wrote:

<gr******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

That makes it useless to me.

What options do I have?

>
The LOAD DATA INFILE is not truncating your strings!
>
One would strongly suspect that your field definition is what limits you to
255 characters.
Both CHAR and VARCHAR require that the maximum length be declared and both
have a maximum length of 255. I would venture a beer bet that says you are
truncating to 255 chars (not 256). VARCHAR(255) or CHAR(255) are common
declarations for fields containing text strings. Larger strings for these
field types are illegal. And whatever the declared length - MySQL will
simply truncate the extra characters without complaining.
>
You would need to move to a BLOB type for larger strings - which you
certainly can do if you need to store longer strings.
>
My bet is that it's your field declaration that limits you to 255 chars.
>
Show us a SHOW CREATE TABLE for the one you are trying to stuff with those
long strings.
Thomas Bartkus

Don't know if any of this helps - but if it does someone owes someone a
beer!

'make sure your max_allowed_packet parameter in MySQL is set
appropriately high'

also

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the
length of the column value, depending on the maximum possible length of
the type. See Section 11.4.3, "The BLOB and TEXT Types".

TEXT and BLOB columns are implemented differently in the NDB Cluster
storage engine, wherein each row in a TEXT column is made up of two
separate parts. One of these is of fixed size (256 bytes), and is
actually stored in the original table. The other consists of any data
in excess of 256 bytes, which stored in a hidden table. The rows in
this second table are always 2,000 bytes long. This means that the size
of a TEXT column is 256 if size <= 256 (where size represents the size
of the row); otherwise, the size is 256 + size + (2000 - (size -
256) % 2000).

[Thomas Bartkus]

<gr******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

And yes, it IS truncating at 256, not 255.

Yes, I understand that 255 is the max for an 8 bit number, and this
would make more sense. But its cutting it off at 256 regardless.

gr******@gmail.com wrote:

MySQL 5.0.18

The field in question (bulk_body) is a text field, so it should not
have a limit anywhere near as low as 255.

CREATE TABLE `lead_bulk` (
`bulk_id` int(10) unsigned NOT NULL,
`bulk_batch_id` int(10) unsigned NOT NULL,
`bulk_source` int(10) unsigned NOT NULL default '0',
`bulk_body` text NOT NULL,
`bulk_cdate` datetime NOT NULL default '0000-00-00 00:00:00',
`bulk_import_date` datetime default NULL,
`bulk_lead_id` int(10) unsigned default NULL,
`bulk_header` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`bulk_id`),
FULLTEXT KEY `bulk_body` (`bulk_body`)
) ENGINE=MyISAM;

According to the documentation for LOAD DATA it does not support Text
or BLOB fields:

" Some cases are not supported by LOAD DATA INFILE:

Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both
empty) and BLOB or TEXT columns. "

Okay. I wasn't mindful of the Text type. That should get you 64K string
lengths.
Your field definition [bulk_body] is not restricting your string length. My
suspicions were unjustified ;-)

But

I recreated this table on my version 4.1.13 and tried doing LOAD DATA INFILE
to stuff a record with long (300 char + ) strings into that [bulk_body]
field and it seems to work a charm. Long (300 char + ) strings get stuffed
without truncation.

According to the documentation for LOAD DATA it does not support

Text

or BLOB fields:

????
I don't know where that comes from but I just verifed that it does work with
Text fields.
I also have a production item actively stuffing a tinyblob with short
strings using LOAD DATA. So - if it's not allowed, my version doesn't seem
to know about it ;-)

Are you using an older version (than 4.1 ?) ?
What OS are you using ?

Perhaps there are chars embedded in the string that MySQL sees as End
Of Field markers? Embedded quotes? Commas? Linefeeds? Carriage Returns?
Or, if running MS OS, the dreaded Ctrl-Z (char(26)) char?

Still fishing.
Thomas Bartkus