473,888 Members | 1,462 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Loading lots of data into mysql table

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?

Aug 7 '06 #1
7 10171
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.
Aug 7 '06 #2

<gr******@gmail .comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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

Aug 7 '06 #3
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_da te` 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.goo glegroups.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
Aug 8 '06 #4
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_da te` 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.goo glegroups.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
Aug 8 '06 #5
<gr******@gmail .comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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("LO CK TABLES table WRITE");
....insert code...
mysql_query("UN LOCK TABLES");

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

---
Norm
Aug 8 '06 #6

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_da te` 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.goo glegroups.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_pac ket 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).

Aug 8 '06 #7
<gr******@gmail .comwrote in message
news:11******** *************@m 73g2000cwd.goog legroups.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_da te` 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

Aug 8 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1574
by: Beau E. Cox | last post by:
Hi - I have an application which is essentially 'read-only' - once the database is loaded it is only read from. So, I am trying to implement it with various indices and a table containg some large content in blob columns. I observe the max_allowed_packet setting and 'split' blobs too large among multiple rows. All is fine during testing. However, when I try a production load (which should load about 1.5G to the various
0
1286
by: Matt W | last post by:
Hi all, I'm planning to use MySQL's full-text search for my forum system (possibly 5+ million posts). I've been playing with it a lot lately to see the performance and functionality and have some suggestions/questions. First, since a few of you may be wanting to know, here is a thread where I was doing some speed/optimization tests and stuff with 3 million posts: http://www.sitepointforums.com/showthread.php?threadid=69555
4
8033
by: Shaun Campbell | last post by:
I know you're going to say search the mail archives, but I have and I can't find an answer to my simple question of is there an SQL statement to load an image in a .jpg file into a database table? Also, I have read in lots of places that it's probably not a good idea to store images in mysql but just store a pointer to the file. Is this the accepted way of doing things with mysql? Thanks
2
4220
by: Pindz | last post by:
Hello, I have a excel spreadsheet from which I would like to load data into a table in mysql. Can you please give me an example of the above. Thanks in advance Nagsy
12
7602
by: VMI | last post by:
For some reason, the process of retrieving data (about 20 records) from an Access table that has 400K records to a dataTable is taking over 3 mins. to complete. Below is my code to connect to the DB and query the table. The table "audit" primary key is "Line". Another weird thing (but I guess that's another post) is that, while it's doing the dataset Fill, my PC is slowed done substantially. But I don't know why that would happen since...
1
1596
by: Richard | last post by:
I have a table that is about 100 rows long and within that table I have tons of links. The table takes about 2 seconds to load. I think javascript might help somehow. The problem is that in FireFox and Netscape, as this document is written the page is being formated while this table is loading, and because the table is centered horizontally and I have some right-aligned divs above that, the table will appear to the right side of the...
4
2282
by: spwpreston | last post by:
Hello, I am new to MySQL. I am trying to load from a text file to a table, and am having problems with the fields in the text file being translated to the correct fields in the TABLE. Right now, I have a tab between each field in the text file, something like: Chicago USA 2,500,000 New York USA 5,000,000
7
5387
by: GaryDean | last post by:
I'm trying to load a GridView up with data manually, in code. I'm not using any datasource. Using this code.... ArrayList myRowArrayList; GridViewRow myGVR = new GridViewRow(0,0,DataControlRowType.DataRow, DataControlRowState.Normal); TableCell myCell = new TableCell(); TextBox myTextbox = new TextBox(); myTextbox.Text = "hello world"; myCell.Controls.Add(myTextbox);
10
13432
by: eholz1 | last post by:
Hello Members, I am setting up a photo website. I have decided to use PHP and MySQL. I can load jpeg files into the table (medium blob, or even longtext) and get the image(s) to display without a problem. I am using chunk_split(data) and the base64_encode and base64_decode on the files. I do a select from the database, and then echo the image (with header(Content Type: image/jpeg) and the decoded image displays fine. Yes, I have...
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11181
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10886
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5819
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.