Forums

Hi, I have some problem while populating a table.

Using web2py and I'm moving from sqlite development to mysql production, I've been used to populate a table while initializing a fresh database directly from web2py looping over each record.

There are about 50.000 records with five fields of type varchar and int.

Moving to mysql I've found that my procedure failed due to timeout so I tried to use LOAD DATA LOCAL INFILE and i was successful with a short testing set, so I've generated a full set then loaded ok; the file size was 1.9 MB.

But for some reasons I need an additional field which is made by concatenating two other fields, in my original code I've set up things so that web2py computed the former. But now I'm injecting data directly and to avoid having to compute after the insert I've generated another full set with all fields so the file size grows to about 3.3MB.

And now I'm getting this error inmediately after starting the query... I've tried various things but the only way to perform a successful insert is to reduce the file size, I've been trying tweaking the connection like this:

mysql -hmysql.server -udido 'dido$db_here' -p --max_allowed_packet=16M --connect_timeout=20  --local-infile=1

...without luck, and now I'm out of options.

My question is, there is some configuration setting I can try to let this run or should I split the query in half? I don't know but I think that 50.0000 records and 3.3MB is not an huge size?

Thnaks, Diego

When you say "inmediately after starting the query" do you mean "immediately" in terms of time -- that is, less than a second after you hit the return key on that LOAD DATA command -- or do you mean it in terms of "without getting other errors first"?

If, when you hit enter, the command does nothing for about five minutes and then gives that error, I think I know what the cause might be. But if it's something else, we'll have to dig a bit deeper.

Yes in terms of time, there is no wait, the error returns almost istantly after pressed enter.

That's very odd. I agree with your original diagnosis that it looks like something to do with the max allowed packet size. But the server you're talking to is configured to support 16MB packets, and I see you're also specifying it on the command line. Maybe the CSV (you're using a CSV, right?) is getting expanded into something much larger in terms of SQL queries by the command....?

Anyway, I think splitting the file into two is probably the best way forward.

It's a CSV yes but it's not a dump, I generate it from some sources:

This is a sample data.

18,"Ali","01010",1,"Ali 01010"
19,"Ehari","01010",1,"Ehari 01010"
20,"Abetxuko","01013",1,"Abetxuko 01013"
21,"Arriaga","01013",1,"Arriaga 01013"
22,"Betoño","01013",1,"Betoño 01013"
23,"Gamarra Mayor","01013",1,"Gamarra Mayor 01013"
24,"Gamarra Nagusia","01013",1,"Gamarra Nagusia 01013"
25,"Campezo","01110",1,"Campezo 01110"
26,"Kanpezu","01110",1,"Kanpezu 01110"
27,"Santa Cruz De Campezo-Santikurutze Kanpezu","01110",1,"Santa Cruz De Campezo-Santikurutze Kanpezu 01110"

and the command:

LOAD DATA LOCAL INFILE '/home/dido/cities.txt' INTO TABLE city FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'

quite plain...

Right. That definitely should work. All I can imagine is that the LOAD DATA command is expanding your CSV out into longer INSERT INTO yourtable (field1, field2, field3, field4, field5, field4) VALUES (18,"Ali","01010",1,"Ali 01010") statements, and these make the total size of the import > 16GB.

Anyway, if you split your file into two halves, and then load them separately, I think it will work.

Being something to be used in automated develop deploy cycles I'm not very keen to going that way. But then if there is no other choice...

Thanks for the quick help :)

Found! As you may guess those are towns name's, and I'd never expected to find double quotes right there.

16676,"Nosa Señora Da "O"","15126",15,"Nosa Señora Da "O" 15126",

This single town crashed the process. Was difficult to track because the first field does not crash, only after concatenating the problem appears; please don't ask me about that last ugly field, I need it that way. :)

Sorry for wasting your precious time giles, many thanks!