MySQL server has gone away - Not updating to Recent

Well, if the code isn't trying to renew the connection, where is the fatal error coming from? That looks to be line 324 of stories.php.

$update = dbquery("UPDATE ".TABLEPREFIX."fanfiction_stories SET updated = NOW( ) WHERE sid = '$sid'");

I'm not that knowledgeable with how MySQL works, so I'm not sure what they mean by it's not "renewing the connections." It's a single query telling it to update something and it doesn't do it (unless they mean it's not sending the proper db info to do so with, in which case I would think you'd get a different message than 'server has gone away').

Like I said, I don't know a lot about MySQL but my opinion/intuition tells me it's their database not allowing enough connections for whatever reason. Perhaps eFic could be streamlined better for a large site like yours, but I'd say with what you're paying they should be trying to handle this.

Like I said, I don't know a lot about MySQL but my opinion/intuition tells me it's their database not allowing enough connections for whatever reason. Perhaps eFic could be streamlined better for a large site like yours, but I'd say with what you're paying they should be trying to handle this.

Thanks for the input. I had a feeling it was just a query overload, but I can't get a straight answer from the host.  🙄

We did actually try to have a 3rd-party programmer help us see if we could cut down on the server load/queries while we were still on Dreamhost (with only 10k members) but they didn't come up with much. We may consider calling in help again to go over everything obsessively to see if they can spot something to combine/work on.

I've been learning a ton about PHP lately, and hopefully will be learning more about MySQL soon than just building queries, and one of the things I want to do is review some aspects of eFic that could be made better for large archives. We've currently got about 15,000 members at my main site (though far fewer chapters and everything than you've got) and some stuff is a bit rough. I think authors.php has been changed so it doesn't take so long to load, and coauthors was changed it doesn't generate a list of all members each time the story form is accessed (both things that were problematic at my site). One thing I want to address is admin edits of stories - it's the same loading of all members in a dropdown for the username, like what used to be used for coauthors. I think I'd like to make this stay fixed at the same username, but with a button/link next to it to edit it, that way the list only needs to be loaded when necessary (or something else along those lines).

Anyway, this is getting off topic, but maybe sometime in the future we could collaborate on making things more friendly for large sites.

I've often thought a bunch of us with larger archives should get together and see what is/is not working, etc.

In the meantime, our host's brilliant suggestion is for us to use mysql_pconnect() for persistent connections. My initial thought is that wouldn't that make any problems with too many queries at once even worse by clogging the pipes so to speak, but I admittedly don't know much about mySQL. (They're also supposedly upping the amount of queries allowed, which I think will do far more good than persistent connections).

So yeah, is reworking eFiction to use persistent connections a) a good idea and if it is, b) is it feasible?

Here's something interesting: apparently mysql_connect() is deprecated in version 5.0, according to the MySQL docs. It says to use mysql_real_connect(): http://dev.mysql.com/doc/refman/5.0/en/mysql-connect.html

The PHP manual does talk about it here: http://us.php.net/manual/ro/function.mysql-pconnect.php

In any case, the message is being generated by PHP, not MySQL. I looked in the PHP manual and found a reference to the 2006 message in the comments to mysql_ping ( http://us.php.net/manual/ro/function.mysql-ping.php). The comment says, "If you get 'error 2006: MySQL server has gone away' messages when running (really) long scripts, mysql_ping will help detecting the loss of the db-connection. This can happen, when 'wait timeout' is reached (MySQL default is 8 hours)."

It sounds like what's happening is you don't have enough connections being allowed, and some queries aren't able to connect right away and timing out (or ending once their code is executed? That's what happens with mysql_connect() but I don't know how waiting works in MySQL). It sounds like the suggestion the hosting company made probably makes sense. It's also good they're adding more connections, because otherwise I think changing it to mysql_pconnect() might just be shifting the problem to other queries.

I did find someone talking about how mysql_pconnect() can be problematic when there's lots of queries, or something along those lines. So maybe it's best to see what happens since they've upped the connection limit.

As for feasibility, the only place I remember seeing the mysql_connect at all is in includes/mysql_functions.php. That doesn't mean it's not found other places, though.

It sounds like what's happening is you don't have enough connections being allowed, and some queries aren't able to connect right away and timing out (or ending once their code is executed?

That was my impression, but I don't know enough about PHP to really explain why I thought that.

I did find someone talking about how mysql_pconnect() can be problematic when there's lots of queries, or something along those lines. So maybe it's best to see what happens since they've upped the connection limit.

Yeah, if they don't have enough connections now, I don't think tying up the lines with persistent connections will help any. I didn't really have any intention of switching things over to persistent, but I felt obligated to ask since I told the host I'd think about it.

I think it might be okay to use mysql_pconnect() only with the queries that are giving you trouble, but I don't think eFic's built that way off the bat.

As for feasibility, the only place I remember seeing the mysql_connect at all is in includes/mysql_functions.php. That doesn't mean it's not found other places, though.

From what I've seen, all the mysql_ functions are rewritten with "db" as a prefix, and they're controlled in the includes/mysql(i)_functions.php files. So, if you want to do experimenting, you should use those files to test it out on and it'll affect it site-wide. I don't think mysql_connect will be anywhere else though; you only have it "once" per "session". :S

ETA

http://blog.taragana.com/index.php/archive/mysql-tip-mysql-server-has-gone-away-or-lost-connection-to-server-during-query-fix/ ? :S

One thing I want to address is admin edits of stories - it's the same loading of all members in a dropdown for the username, like what used to be used for coauthors. I think I'd like to make this stay fixed at the same username, but with a button/link next to it to edit it, that way the list only needs to be loaded when necessary (or something else along those lines).

In version 4 this will be an ajax enabled text box that will let the user type in the first couple letters in the username then select from a list of matches.  I won't be doing this for v3.5 as there's too much involved.  I mention it as something you can look into.

Now the host says that it's getting these errors in the error log and they must be the problem.  🙄

[01-Sep-2009 06:19:33] PHP Warning:  Invalid argument supplied for foreach() in /home/jonasb7/public_html/reviews.php on line 303
[01-Sep-2009 06:19:58] PHP Parse error:  syntax error, unexpected T_STRING in /home/jonasb7/public_html/user/profile.php(70) : eval()'d code on line 1
[01-Sep-2009 06:20:38] PHP Parse error:  syntax error, unexpected T_STRING in /home/jonasb7/public_html/user/profile.php(70) : eval()'d code on line 1

Not in updating the most recent.  Those are not even remotely connected.  It looks like you've got an error in a custom profile field somewhere for the last 2.  PHP should be able to continue on though. The first 1 is a warning and php should be able to ignore it without problems as well.  The first one is caused because you've got a series with no stories or sub-series.  Find and delete that one and that error will go away.

How does eFiction determine if it should send out notification e-mails? Someone randomly asked if it was the amount of people who favorited a story that could be causing the problem and I figured I'd ask.

Also WTF is this?

[01-Sep-2009 22:46:01] PHP Warning:  array_merge() [<a href="'function.array-merge'>function.array-merge</a>"]: Argument #2 is not an array in /home/jonasb7/public_html/browse.php on line 166
[01-Sep-2009 22:46:01] PHP Warning:  array_filter() [<a href="'function.array-filter'>function.array-filter</a>"]: The first argument should be an array in /home/jonasb7/public_html/browse.php on line 170

There's a couple bugs in the browse.php page.  I'm fixing them now.  They'll be in the SVN tonight.

Awesome, thanks! I've never seen those error message before.