SQL file fails to properly import data

sneakyimp

someone sent me an SQL file. it is supposed to create a table and import a lot of large text fields into it.

running the sql file seems fine...

mysql database < file.sql

no errors come up or anything...should they?

at any rate, when i try to view the table in phpMyAdmin, it says 'page cannot be displayed'...like it can't find the page or something.

i think the problem has something to do with either line returns or quotes in the text fields in the data. all data is single quoted (') and any single quote that occurs within a data field is backslashed (\').

I can't seem to get to the bottom of this problem. If anyone has any advice about importing complex text data, i would greatly appreciate it.

cheese

Hi Sneakyimp,

Going to ask the obvious here, can you see the database on the left side of the screen, in the dropdown list (with the number of tables in brackets)?

Cheers,

Neil.

sneakyimp

yes. the database shows up. all the tables are listed (including the newly created one). i can easily view the structure of the table, but when i try to browse it, i get either 'Action canceled' or 'the page cannot be displayed' errors in my browser.

nemonoman

Those error messages suggest a problem such that the server (apache??) is running out of gas before the page is complete.

sneakyimp

i think you're right, nemonoman, but the error comes pretty much instantly. there's no long timeout or anything. maybe a memory overflow or something?

i had a similar problem with another database...turned out it was importing improperly...not sure exactly how. i ended up replacing all the 'curly quotes' with their html equivalents and it seemed fine.

i think the problem with this sql file has something to do with the fact that the fields are enclosed in single quotes (') and any single quotes in the material are backslashed (\'). or maybe not...i don't know. i suspect I'll have to try a bunch of different stuff.

nemonoman

The bad quotes scenario you describe is exactly what might cause an apache hiccup.

sneakyimp

how so?? i can't browse the table, but i can select certain combinations of fields from it just fine. certain other field combinations cause the error. is there any methodical way to locate the problem so i can fix it?

i can't seem to locate which record or field is actually causing the problem. for instance, if i select

field1
field2
field3

it chokes. but if i select any one of those individually, it seems fine.

cheese

Hi again,

How about backing up the database and deleting all the data. Then put in ONE set of known good data and see what happens.

If that works, you could put a few of the 'imported' data back and see if the errors come back. That way you will prove that your data is good or bad.

Just a thought.

Neil.

sneakyimp

it's really easy to create a single record that works. the problem is finding which record in 177 has some kind of weird problem and what exactly that weird problem is....

what might cause apache to choke? seems to me that if there's weird data, it would result in an error when importing. i'm importing using mysql command line. it also seems like i would get an error about 'invalid sql syntax' or whatever at the import stage.

however, all 177 records get imported...i haven't seen problems with the data yet except for the phpMyAdmin 'page not found' errors.

cheese

OK, see your point.

When I was in the electronics trade and we got a problem circuit, we used what they called the 'half split method'.

Lets say you have 256 records.

Try loading 128 of them. If they load OK, you know your error is in the scond half and you just half split the top half again.

If they dont, you then load 64.... etc. That way (assuming not too many rubbish records) to find one in 256, isn't quite so bad.

Cheers,

Neil.

sneakyimp

rats! i was afraid you'd say something like that.

so i'll import the database as usual, then select(*) for first half, second half, etc. until i locate a problematic record, eh? i will then need to compare database record to the SQL file to see if it was imported properly, etc. i can do that.

i was thinking, though, that the SQL file might have errors at the import stage and that i need to add an additional flag to the mysql command line when i import to make sure i can see the errors. I'd like to run the import one more time and make darn sure there are no errors...this all seems a bit fishy to me.

thorpe

run the sql command in phpmyadmin... it will definately spit out an error if something isn't right.

sneakyimp

the sql file is 996KB. I just pasted the whole thing into phpMyAdmin and imported - says it ran successfully and doesn't report any errors.

weird.

will try to find bad record and pass on any findings.