Server issue Importing attachments: MySQL server has gone away

[AlexT]

I just encountered a "MySQL server has gone away" error while importing ~ 40GB of attachments from a vBulletin installation.

Granted, the server isn't the fastest (it's a KVM VM I set up for xF testing), but it's the first time I've encountered this issue with xF, which was OK even when importing millions of posts and private messages.

I suppose that the VM's IO is kinda slow, causing all the attachment copy/temp create/unlink operations to slow things down further (in particular with larger attachments), so that xF lost its active MySQL connection. Any chance to ping the DB during the process to maintain its connection like described over here?

 

[AlexT]

Update: According to the server logs, it's most likely that the VM ran out of memory (1.5GB) with PHP taking most of it. Not sure why the attachment import step would have caused this issue in PHP that looks like a leak. I am using imagick to generate the thumbnails.

 

[Walter]

I ran into this issue one time, in my case it was a corrupted image with a gigantic resolution using up too much memory. I removed the attachment and the issue went way.

 

[EasyTarget]

Update: According to the server logs, it's most likely that the VM ran out of memory (1.5GB) with PHP taking most of it. Not sure why the attachment import step would have caused this issue in PHP that looks like a leak. I am using imagick to generate the thumbnails.

There's a known issue when you copy an image in .php to do the conversion to .jpeg you have to implicitly destroy the image object in the code. Most likely the image objects are being left in memory.

 

[Jake Bunce]

Mysqli prepare error: MySQL server has gone away

That error is caused by one of these MySQL settings being exceeded:

wait_timeout
max_allowed_packet

Your host or server person needs to increase these settings in MySQL. There is more info here:

http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

 

[AlexT]

Jake, I am using default Mysql settings here, and there is no reason why they ought to be raised for this particular step in the import process. In fact, everything looks like the output error shown wasn't caused by a normal timeout, but by PHP being oom-killed due to using up all available memory during the import (naturally when a new PHP process is created afterwards it wouldn't have the handle to the old mysql connection anymore).

 

[Mike]

If it ran out of memory, it may be more likely that MySQL simply killed the connection (or MySQL itself appeared to die). What's the full back trace? If that's the case, then pinging won't do anything. This step does hit the DB on every pass, so a ping wouldn't really be possible (or at least, wouldn't provide any advantage).

 

[SneakyDave]

I don't know if this is the same thing, but I thought Mike was mentioning that there were pings being made to the database to keep this from happening, the MySQL time out limit.

EDIT: Just saw Mike's post.

 

[AlexT]

Mike, right, I mentioned the pinging when I thought first that it was indeed only a normal timeout to the db and that it would require a ping (or an adjustment to the parameters as Jake suggested).

But then I saw that PHP process was killed by the kernel for using up all memory during the attachment import process. Since it's a fair amount of attachments with around 40GB and since the error occured rather late in the process (around 70% or so), I am more tempted to say that there is a leakage problem and hence probably shouldn't be a considered xF bug (unless there are file handles or objects left opened perhaps).

Well, the only really annoying thing is, I believe unlike most of the other import steps, the attachment import is not transaction safe. So a crash during this stage is not something you'd like to see happen...

 

[Mike]

The worst thing you'd get is a file (or a few) written out that weren't necessarily associated with anything - no data loss or anything - so it's not really a huge deal.

It's unlikely that there's an explicit leak in terms of the XF code itself. You mentioned in another thread about a crash involving imagick - this is likely the underlying issue/cause. May be worth checking if the same error occurs with GD. We do limit out thumbing based on the (reported) dimensions to limit memory usage.

 

[AlexT]

Indeed the crash related to imagick occurred during the same import process, however it is unrelated to this one. With imagick, the crash occurred instantaneously when the imagick constructor was called with that particular png image I posted; this crash here occurred when the test machine ran of memory with processes being killed subsequently.

After this crash I was able to continue the import process (with a broken attachment file perhaps). After the imagick crash the import refused to continue due to duplicate key errors.

Anyhow, my fault, I should have investigated some more since indeed neither of the crashes appear to have been caused by xF.