Uploading Larger Files: 2006 MySQL Error & JSON.parse error, Problem & Solution, possible bug?

1

Xibo 1.7.7, WebhostingBuzz shared business server account
Apache Version 2.4.18
PHP Version 5.6.20
MySQL Version 5.6.30
OS Linux

I’ve searched the forums quite extensively and didn’t see anything for this particular issue, so I’m posting with the hope that this may help other users save time in trying to solve this problem. I’ve spent hours trying to resolve this as a relative newb!

I had a Xibo installation that was working just fine, then one day stopped working. I could not upload files larger than about 20MB. I went through the PHP settings and verified max post, max file size, memory, max_vars, max_packet_allowed, etc. Everything was on the order of 128-256MB and 4000 characters. I still kept getting errors when trying to upload files larger than about 20MB.

In the media uploader popup window, I had the following error:
SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data

When I went to the xibo log, I had the two errors below:
Error writing session: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
http://piwik.org/faq/troubleshooting/faq_183/

I submitted a webhost trouble ticket, which they blamed the Xibo. Upon further research, I honed in on the issue being related to mysql timeout settings. It seems my server recently changed the settings which created this problem. I’m still not sure exactly what mysql timeout variable is the source of the problem, but it appears to be connect_timeout and/or wait_timeout. According to the mysql wiki, all have a default time of 28800 seconds. I noticed mine were set very low, connect_timeout=10 and wait_timeout=20.

I then copied this installation to another server installation where connect_timeout=60, and wait_timeout=60. Sure enough, it worked just fine on this server. I contacted the webhost who said the mysql settings are server wide and they could not change them any higher. They also mentioned something about a security vulnerability as a reason to keep them low.

So I now know the source of my problems, but now have to find another webhost who has higher timeout values – unless this would be considered a bug that could be fixed in the next version. If having a large timeout is a security issue, is there a way that xibo can keep the connection active with a lower timeout value?

I hope this helps others, and I look forward to hearing if anyone else has encountered this issue and how they corrected it, short of moving to a new server.

Thanks!!

2

connect_timeout default is 10 seconds in all recent versions of MySQL as far as I can see so it’s not related to that.

wait_timeout will be the problem. They’re forcing the connection to close while xibo is still using it. MySQL’s default is 28800 seconds.

Basically, when you start your upload, Xibo connects to the database and reads/writes session information. Then the upload happens, and when it does, the same connection is used to write information about the upload back. In the interim, your host has closed the connection forcibly even though we haven’t asked for it to be closed. It’s not a configuration I’ve seen and I can’t think of any security issue with allowing an authenticated connection to remain open so that it can be used again. I certainly don’t see it as a bug on Xibo’s part.

What I suspect your hosting provider is trying to do by lowering that connection is cram more database connections on to their servers without hitting “Too many open connections” issues with MySQL by deliberately closing connections that are in use. Many modern web apps such as Xibo pool connections to avoid the overhead (for both webserver and MySQL server) of creating a new connection for each query.

We’ve never recommended using Xibo on shared hosting. There are companies out there who host Xibo commercially - the project sponsor included. If you don’t want the overhead of administering the server yourself then that would be the most sensible way to go.