zma_m2[]: ERR [Can't insert event: MySQL server has gone awa

[BlankMan]

Periodically getting these, well pretty much daily, causing loss of events.

Have other mySQL DB's and not seeing any issues with them.

My question is, does ZM open a connection to the DB on startup and then keep that connection open or does it connect and open only as needed?

The reason for the above question is if it opens the connection on startup and then keeps it open, does ZM access the DB periodically or only when storing events? (Baring any user option changes and/or such.)

The reason for that question is if it opens on startup and only access the DB when storing an event, (baring any user option changes and/or such) this error might be caused by mySQL closing connections that are idle for 8 hours. It's my understanding that mySQL 5 has a default timeout of 8 hours and will close connections that have been idle for that long.

I can up that time but thought I'd see if that 8 hour timeout might be the issue here.

[coke]

I get that error whenever I run out of disk space, personally, might want to check that your purgewhenfull filter's running (and zmaudit).

[BlankMan]

Thanks, but don't think space is the issue. That mount point dedicated to ZM has 907G free and hasn't gone over 2% used yet.

zmaudit is now working properly, had some issues in the beginning, so it doesn't appear to be it either but I'll take a closer look at it.

[mastertheknife]

ZM uses persistent mysql connections, but many times i had mysql run out of connections, and mysql felt slow in general.
If the database is on the same system as ZM, there is no reason to use persistent connections because the overhead of a new connection is minimal.
Also, if you are using PHP 5.3, its known not to work well with persistent connections.

Here is how to change the web interface to use standard connections and not persistent ones, it will probably fix your problem and it will make ZM interface much more responsive:
In file includes/database.php, edit line 32, change it from

Code: Select all

$dbConn = mysql_pconnect( ZM_DB_HOST, ZM_DB_USER, ZM_DB_PASS ) or die( "Could not connect to database: ".mysql_error() );

To:

Code: Select all

$dbConn = mysql_connect( ZM_DB_HOST, ZM_DB_USER, ZM_DB_PASS ) or die( "Could not connect to database: ".mysql_error() );

[BlankMan]

Yep, looks to be PHP 5.3.

Thanks. I'll give that a try later then keep an eye on it and see if it still occurs.

[mastertheknife]

PHP 5.3 giving that error on persistent connections is a known bug, you can try my suggestion (best option in my opinion, no reason to use persistent connections in ZM) or change PHP version.

EDIT: After reading the title again, it seems my fix won't do because we're talking about zma and not the web interface. I guess changing PHP version is the only way if you don't want to bother with recompiling.

[BlankMan]

Recompiling is fine, can't tell you how many times I've done that already trying to get everything working and still am.

Replacing 5.3 is not such a good option for me. I use SuSE, 11.2 at the moment, and to replace it could be problematic with any/all the hooks and dependencies that might be involved. I like to rely on them for all the patches and updates and security stuff so I don't have to spend the time doing it.

They do tend to backport fixes too so they might address this but I'll try your suggestion and recompile.

[BlankMan]

In the includes/database.php I changed mysql_pconnect to mysql_connect and recompiled and still got the error so that didn't help.

I'm going to put it back to the original and maybe try increasing mySQL's idle timeout and see if that helps unless anyones got any other ideas. Short of getting off of PHP 5.3, just don't want to attempt that at this point.

[BlankMan]

Um, never mind, my last post, Due to a typo on my part I really didn't have the mysql_pconnect changed to mysql_connect, but I will now... Really...

[BlankMan]

Ok, the jury is in, for real this time, changing mysql_pconnect to mysql_connect did not solve this problem, I put it back to mysql_pconnect.

But, as I suspected, it appears to have been due to the mySQL default idle timeout of 8 hours. I increased that timeout to 7 days and am no longer having this problem. The max value is 31,536,000 seconds or 8,760 hours so setting it to 168 hours seems reasonable.

My morning event, when the sun comes up and the Dusk-to-Dawn light on my garage turns off that always failed to be captured due to this problem is now being captured.

[kingofkya]

Ok this is causes by a badly configured server and/or slow drive IO.

try copying one of the premade configs that mysql comes with see your disto documentation.

run sudo find / -name .cnf

I use medium on my server

my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf

[BlankMan]

Ok this is causes by a badly configured server and/or slow drive IO.

try copying one of the premade configs that mysql comes with see your disto documentation.

run sudo find / -name .cnf

I use medium on my server

my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf

Huh? It's caused by a badly configured server eh? How can you make that assumption? Why do you assume that one of those cnf's is not in use already? (my-medium.cnf is in use, SuSE does that by default.) Why do you assume that my.cnf has not been properly configured? (I have Oracle certification, so my,cnf was addressed long ago.) Do you assume mySQL is only being used for ZM? (It's not and was in use for other DB's long before ZM was installed.) Why to I need to use sudo if I'm logged in as root?

Have you read the mySQL manual? Here is an excerpt:

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent).

Error Code Description
CR_SERVER_GONE_ERROR The client couldn't send a question to the server.
CR_SERVER_LOST The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld. See Section 5.1.3, “Server System Variablesâ€

[jameswilson]

Blankman.
I have no real suggestions but am pretty sure that any assistance that kingogkya was likly to offer will now be less likly.

Just as he has no idea of your ability on setup etc, you also have the same restriction in judging him.

However i do concur that it seems like an IO issue. As your 100% its not a config issue then that may help your search

[BlankMan]

I know, that's why I apologized right away.

It seems any time a mySQL issue pops up everybody right away says it's a bad config or an I/O issue. Same thing happened when I asked about bogus events being created, performance.

I know it's not a config or I/O issue, if it where my other databases some of which are bigger and complex would have had the problem long ago.

I don't usually cry wolf, yes the server is plugged in and turned on. I've been doing this for 30+ years and I rule out the obvious before asking for help. When I do ask for help it's usually something I haven't seen before so I'm hoping someone has so I can get a quick fix.

I'm pretty convinced it's mySQL's wait_timeout that was causing it. Since I increased it the problem has not occurred and I check for it every day. And the fact it's mentioned in the mySQL documentation lends credence to it. Never ran into this before because my other databases don't sit idle that long nor keep a connection open that long. Open, get in do what you have to do, close. Even though they're accessed ever minute 24x7.

Our Help Desk at work used to do something similar. When a problem cropped up and was resolved and you told them what the cause was they would then tell the clients calling with any problem that that same cause was responsible for their new problem when it wasn't even close. I know, Level 1 support. Just felt like that was happening here, no matter what it's a bad config or I/O problem, performance, etc.

I overreacted, I apologize for that. It seems a lot of the issues I've had so far I've had to figure out myself, I guess I was expecting more, I just have a hard time believing that no one else has run into some of these. I do appreciate the help I've got, don't get me wrong.

Still can't get mpeg streaming to work and I've isolated it to the socket missing but no solutions forthcoming.

[jameswilson]

blankman

i hear what your saying re cant believe no-one else has got this etc. Ive had issues with software and then seems im trying to do something unique lol

re your sql timeout issue, it should only apply if mysql is not responding before the timeout (as you know) but that shouldnt happen.
Im wondering if your situation is slightly different, ie most of us (well i do) dedicate zm to a machine. If your sharing it then that may explain your issue.

re your mpeg issue. It needs ffmpeg and zm needs to be compiled with the correct ffmpeg version. Not all versions of ffmpeg work as expected with zm.

If you have done the above how are you setting it up to stream?