Too large query - what to do?

janise · June 3rd, 2004, 04:22 PM

I don't think there's anything wrong with the .sql file. I once imported an 8MB table by splitting it into smaller parts. I had to make 20 parts for that to be successful. I now have a DB of 170MB. I just thought there must be some easier way...

Janis

Daniel Walker · June 4th, 2004, 06:03 AM

Well, you have two separate instances of MySQL which both agree that thre's something happening to call the connection to close before the update is completed, as far as I can tell.

AS MySQL themselves say, about this sort of thing:
"You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server's uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash. Start by checking whether issuing the query again kills the server again."

Best of luck, and if it does turn out to be anything wierd, we'd all appreciate if you'd let us know what it was, so we can be ready for it ever happening to us :)
Dan

happygv · June 4th, 2004, 06:46 AM

Hi Janise,

I found a tool for you here.
http://www.myzips.com/download/MyISQL-(ISQL-for-MySQL).phtml

I downloaded this, installed and tried it in my PC here. This works fine. This is an alternate for isql for Mysql. (it is a 15 day trial, hope you can sort it out within 15 days;))

Syntax for command line mode is

Code:

MyISQL.exe -dADVERT -hlocalhost -p3306 -uroot -c -s"BACKUP_TABLES.sql" -l"run.log"

Should be helpful to you.
Cheers!

_________________________
-Vijay G

Strive for Perfection

janise · June 5th, 2004, 11:04 AM

Yes, Daniel, I read MySQL Manual about this error. And it says:

Quote:

quote:You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection.

I'm almost completely sure that in my case the query is too long and it isn't incorrect. In this one file there're several automatically generated phpMyAdmin queries that should renew a DB (if I have lost if, for example).
The problem query (or two) is the one who inserts board's post values - it is one big "insert into" query giving values for all board's posts (the same could be for members' private messeges, too).

MySQL Manual tells that the default query limit size is 1MB, although it may be increased (no more than 16MB if the MySQL is older than 4.0.8).

Still, I'm not sure if that will work and won't put too much load on the server. I hoped there is a way how you can decompose the process, make it somehow gradual with some progress bar preferably.

happygv, I tried that program and after a few times get it to work too. :) (I think, at least). Well, anyway while it was working I didn't see any progress. I didn't know if it's worth to wait or not, is it doing anything or not...
I could see only MyISQL window on the task bar (not restorable) and in the Task Manager I saw that MyISQL is taking ~95% of processor's work. So, I let it alone and went to watch TV. :D

Unfortunately it didn't succeeded. This is the log file:

Quote:

quote:2004.06.04. 16:29:30, Connect, OK
2004.06.04. 16:29:32, Execute, "sicu.sql.zip.sql" ERROR Stmt #2
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #5
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #8
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #11
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #14
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #16
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #19
2004.06.04. 16:29:34, Execute, "sicu.sql.zip.sql" ERROR Stmt #22
2004.06.04. 16:29:35, Execute, "sicu.sql.zip.sql" ERROR Stmt #25
2004.06.04. 16:29:35, Execute, "sicu.sql.zip.sql" ERROR Stmt #28
2004.06.04. 16:29:35, Execute, "sicu.sql.zip.sql" ERROR Stmt #31
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #34
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #37
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #40
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #43
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #46
2004.06.04. 16:29:36, Execute, "sicu.sql.zip.sql" ERROR Stmt #49
2004.06.04. 16:30:29, Execute, "sicu.sql.zip.sql" ERROR Stmt #53
2004.06.04. 17:18:08, Execute, "sicu.sql.zip.sql" ERROR Stmt #56
2004.06.04. 17:55:38, Disconnect, OK

The test file was 11.2MB long.

Janis

janise · June 14th, 2004, 11:50 AM

If I wanted to try to increase the maximum size, should this would be the correct way of doing it?

Quote:

quote:mysql -O max_allowed_packet=10M kurne < PZ.sql -u root -p

Thanks,
Janis