Froxlor

I've got a similar problem on 0.9.26. I have 2 clients with lots of traffic and since 0.9.25 traffic and disk usage are always 0.
Also backup it's not working also since 0.9.26 update !!!

Tried your patch but doesn't seem to work.

PHP Warning: mysql_query(): MySQL server has gone away in /var/www/froxlor/lib/classes/database/class.db.php on line 199
PHP Warning: mysql_query(): Error reading result set's header in /var/www/froxlor/lib/classes/database/class.db.php on line 199
We are sorry, but a MySQL - error occurred. The administrator may find more information in syslog with the ID 3b2f7d485ff1f1bd18957fc7387c4c57


All these problems about long cron operations could be solved by adding an additional check for a valid database connection before attempting a query against the server: /lib/classes/database/class.db.php, line 189, function query($query_str, $unbuffered = false, $suppress_error = false)

Anyone can suggest a fast solution ?

Regards,
/Sorin

frontline wrote:

I've got a similar problem on 0.9.26. I have 2 clients with lots of traffic and since 0.9.25 traffic and disk usage are always 0.

In my opinion your problem is not similar to mine. You have problems since 0.9.25, but this version worked perfectly for me. The reason for my problem was Bug #977, which was solved in 0.9.26 but caused the problem I described here. That is why, the patch doesn't solve your problem.

frontline wrote:

All these problems about long cron operations could be solved by adding an additional check for a valid database connection before attempting a query against the server: /lib/classes/database/class.db.php, line 189, function query($query_str, $unbuffered = false, $suppress_error = false)

Yes it would be a solution, but not my favorite solution (bad coding style in my opinion). And it wouldn't solve my problem, because of the error "Lost connection to MySQL server during query" (connection ok -> query starts -> connection gets killed -> query can't finish)

My knowledge about Froxlor is still limited, so please correct me if I'm mistaken.

Maybe using a persistent connection to MySQL would be a better solution ? (http://php.net/manual/en/function.mysql-pconnect.php)

Anyone from the dev team could confirm this ?

Regards,
/Sorin

Another solution could to use mysql_ping (http://dev.mysql.com/doc/refman/5.5/en/mysql-ping.html) before query is executed (/lib/classes/database/class.db.php).

This should check if connection is alive and if not, try to reconnect (http://dev.mysql.com/doc/refman/5.5/en/auto-reconnect.html).

According to documentation, this could create problems with InnoDB transactions, but Froxlor uses MyISAM (correct me if I'm wrong).

From documentation, it seems that auto-reconnect feature is disabled by default since MySQL 5.0.13, so it should set manually:
To enable it, call mysql_options() with the MYSQL_OPT_RECONNECT option.
Any hint how to do that in PHP ? mysql_options is not a function in PHP.

Regards,
/Sorin

I'm still working to get my traffic/disk reports and backups working.

Quick question: why (since 0.9.26), cron_traffic.php ends with die() ?

Quick question: why (since 0.9.26), cron_traffic.php ends with die() ?

Because it forks and we don't want to have 2 cronjobs running around =)

Quickfix for "has gone away":
In lib/classes/database/class.db.php after line 191 global $numbqueries; add the following:

if(!mysql_ping($this->link_id)) {
//echo "[Unexpected] Mysql Server has gone away... attempting reconnect!".PHP_EOL;
$this->link_id = mysql_connect($this->server,$this->user,$this->password);
if($this->database) mysql_select_db($this->database);
}


And it wouldn't solve my problem, because of the error "Lost connection to MySQL server during query"

ping is also a query. If mysql hasn't actively closed the connection yet however queries are unable to be processed for some reason, e.g. the connection is invalid due to the fork, ping will notice it and the above patch temporarely fix the issue.

<-- REAL FIX BELOW -->

Below is a fix for the traffic-cronjob but* I currently cannot test it, could someone please confirm if it works?*
Add to lib/classes/database/class.db.php the following function:
function renew() {
$this->db($this->server,$this->user,$this->password,$this->database);
}

Then replace scripts/jobs/cron_traffic.php line 51-53 with the following:
$db->renew();

If your Mysql-Timeout is lower than the cronjob's length (eg, your issue is already present since Froxlor <= 0.9.25:
Edit lib/classes/database/class.db.php function 'query' and add the following after global $numbqueries;

static $LastPing = null;
if(is_null($LastPing)) $LastPing = time();
if((time()-$LastPing) > 5) {
$LastPing = time();
if(!mysql_ping($this->link_id)) $this->renew();
}

This will test the Mysql connection if it has been untested for more than 5 seconds and should bypass mysql's idle timeout as well as connection timeout values.

<-- REAL FIX BELOW -->

The idea of your fix seems to work, but your code doesn't. $this->user and $this->password aren't available anymore (unset in lib/cron_init.php). So your fix needs to be improved to work.

And you made the same mistake I did: no (valid) git diff ;)

Danke, läuft gut.

$this->user and $this->password aren't available anymore (unset in lib/cron_init.php).

I couldn't find anywhere where $this->user is unset, could you please confirm that not only $this->password is unset?
However I see absolutely NO reason for unsetting this var if it's scope is modified from 'public' (what the heck?) to 'private'.

The following files need changes for the fix to work:

/lib/functions/froxlor/function.openRootDB.php line 61
//unset($db_root->password);

/lib/cron_init.php line 159
//unset($db->password);

/lib/init.php line 122
//unset($db->password);

/ lib/classes/database/class.db.php line 74
private $password = '';

And you made the same mistake I did: no (valid) git diff ;)

I forked Froxlor some time ago in an attempt to streamline the dozens of patches and features I added to my Froxlor installations over time across the machines, however these have to be painstakingly backported and generalized to the Froxlor Git HEAD.
Valid Git Diff's are not that easy to build by hand :D

=Update=

Additional changes required:
admin_customers.php line 214 + 224 + 1238 + 1249
//unset($db_root->password);

customer_mysql.php line 67 + 106 + 186 + 302
//unset($db_root->password);

The changes actually don't work.
The message "MySQL server has gone away;"... already is signaled almost a second after froxlor_master_cronjob.php is started.
While checking with "cron_traffic.php" I noticed the fork "causes" troubles. After removing the fork it seemed working, I will do more checks there.

It seems inheriting the mysql connection context (class db with mysql connection) is failing ...
as soon as froxlor_master_cronjob.php terminates the mysql connection context gets closed and the inherited one will fail (if still beeing used (. I am actually not sure if a mysql connection context is supposed to be shareable among processes; it seems sharing after fork should not be done.

I also think sharing of global resources among processes should be checked carefully
(I also encountered problems with the instances in $db_root).

http://php.net/manual/de/function.pcntl-fork.php

I have the same error. All the jobs running via cron: cron_apsinstaller.php, cron_autoresponder.php and cron_tasks.php fails with the same error - from syslog: MySQL - Error: Invalid SQL: show databases; ErrNo: 2006; Desc: MySQL server has gone away;

Any resolution? or should I downgrade ?

I think I found a solution ...
(Sorry, currently I cannot provide a git diff)
in scripts/jobs/cron_traffic.php I added unsets to two globals with the child part after fork.
(Im am curious why they seem to be needed since there is a "new db(..) and new db_root(...) (encapsulatied into a function) already, which actually should do the job, but it is not sufficient ...
the block (the former patches (the renew stuff) still included):
elseif($TrafficPid == 0) { //Child
unset($GLOBALS['db']);
unset($GLOBALS['db_root']);
global $sb;
global $db_root;
posix_setsid();
fclose($debugHandler);
$debugHandler = fopen("/tmp/froxlor_traffic.log","w");
require ($pathtophpfiles . '/lib/userdata.inc.php'); //There is no bloody reason not to have sql values in the backend ready!
if(isset($sql['root_user']) && isset($sql['root_password']) && (!isset($sql_root) || !is_array($sql_root))) {
$sql_root = array(0 => array('caption' => 'Default', 'host' => $sql['host'], 'user' => $sql['root_user'], 'password' => $sql['root_password']));
unset($sql['root_user']);
unset($sql['root_password']);
}
$db = new db($sql_root['host'], $sql['user'], $sql['password'], $sql['db']); //detabase handler renewal after fork()
}else{..

This seems to work ... I couldn't check with more busy database ...
But I actually donot understand, why the unsets are needed ... the child creates a new complete instance (of the database class and the mysql interface) with:
"$db = new db(...)"
which shouldn't be connected to anything of the parent process ...
but without "unset($GLOBALS['db']);" it seems "$db = new db(...)" is still reusing parts of the old instance of db...

Sorry, there is even with mine something wrong ...
changing $sql_root['host'] to $sql['host'] as it supposed to be, it breaks again :(

I further tracked the issue:

After successful fork, whichever process (child or parent) will finish first will close the MySQL context/handle, which again causes the still running process failing with the following mysql query ...

A quick solution is to reopen the context within parent and child (after successful fork).

For a clean solution a redesign of cronjob handling should be considered.

so a mysql connection is opened before forking - and that connection is then inherited by BOTH forks - and the first fork to finish, closes it - tripping the rest ?

That's definetely a bug. wouldn't the "easy fix" - just be to make each fork start their own connection - if one does not wish to use persistent connections ?
or alternately - loop after forking in the initial process - waiting for the forks to all finish (can you do that in PHP? you can in C.. but..)

and after all the forks have finished - it's ofcourse the initial process task - to close the mysql connection.

After patching i got the following error:

PHP Warning: Packets out of order. Expected 1 received 49. Packet size=133120 in /var/www/froxlor/lib/classes/database/class.db.php on line 199
PHP Warning: mysql_query(): MySQL server has gone away in /var/www/froxlor/lib/classes/database/class.db.php on line 199
PHP Warning: mysql_query(): Error reading result set's header in /var/www/froxlor/lib/classes/database/class.db.php on line 199
We are sorry, but a MySQL - error occurred. The administrator may find more information in syslog with the ID 168dc6a85d81ce1feb6068ee6ebe54c5PHP Warning: Packets out of order. Expected 8 received 0. Packet size=65581 in /var/www/froxlor/lib/classes/database/class.db.php on line 199
PHP Warning: mysql_query(): Unable to save result set in /var/www/froxlor/lib/classes/database/class.db.php on line 199
We are sorry, but a MySQL - error occurred. The administrator may find more information in syslog with the ID f0d65b725f181dbb9610e13db56c405f

can anyone else verify this result? I cannot replicate.

This are the two mysql errors from the syslog:

Feb 21 00:25:01 server Froxlor: MySQL - Error: Invalid SQL: SELECT `id`, `domain`, `customerid`, `parentdomainid`, `speciallogfile` FROM `panel_domains` ;; ErrNo: 2006; Desc: MySQL server has gone away; Script: ; Ref: ; 168dc6a85d81ce1feb6068ee6ebe54c5

Feb 21 00:25:01 server Froxlor: MySQL - Error: Invalid SQL: SELECT `a`.*,#011 (SELECT SUM(`t`.`http` + `t`.`ftp_up` + `t`.`ftp_down` +`t`.`mail`)#011 FROM `panel_traffic_admins` `t`#011 WHERE `t`.`adminid` = `a`.`adminid` AND `t`.`year` = '2012'#011 AND `t`.`month` = '02') as `traffic_used_total`#011 FROM `panel_admins` `a` WHERE `a`.`reportsent` = '0'; ErrNo: 0; Desc: ; Script: ; Ref: ; f0d65b725f181dbb9610e13db56c405f

Today no Error in syslog.

And Backups were created.

Don´t have to time at the moment to take deeper look inside, but I found an interesting corelation:
In the beginning I could fix the bug with the first patch attached here (adding just 3 lines).

But I never had APS enabled. After enabling APS a couple days ago traffic calculation stopped working. None of the proposed patches here seemed to help (MySQL server has gone away, but no information about the script causing the error). Disabling the APS-Cron jobs did not help, but after disabling APS completly everything is working again!!

Just a hint:
There is already a revision for 'cron_traffic.php':
http://redmine.froxlor.org/projects/froxlor/repository/revisions/5d60b25900a4d0e978affb64501f90805e24660e
which is related to this resolved bug:
http://redmine.froxlor.org/issues/1028

I made a "next try" to fix the problem, looking promising, see patch here: https://github.com/Froxlor/Froxlor/pull/6
(btw: this bug is the reason why the release candidate is delayed -.-)

@EleRas
I tried your patch but still I get this in syslog:

Froxlor: MySQL - Error: Invalid SQL: show databases; ErrNo: 2006; Desc: MySQL server has gone away; Script: ; Ref: ; eb634828c1524d97fa2270b61aff6884

tilman19 wrote:

Don´t have to time at the moment to take deeper look inside, but I found an interesting corelation:
In the beginning I could fix the bug with the first patch attached here (adding just 3 lines).

But I never had APS enabled. After enabling APS a couple days ago traffic calculation stopped working. None of the proposed patches here seemed to help (MySQL server has gone away, but no information about the script causing the error). Disabling the APS-Cron jobs did not help, but after disabling APS completly everything is working again!!

I can confirm that:
The MySQl error appears if cron_apsinstaller and cron_traffic are started at same time via masterjob.
If cron_apsinstaller is not scheduled and cron_traffic starts alone there is no error.
I don't have to disable APS completely. It's just a question if both jobs are running at the same time or not.

Same here: cron_traffic.php aborts with mysql errors since last version(s). Deactivating APS cron task makes cron_traffic.php working. All without patches at version 0.9.26-1.

I was lucky too early ;-) Today at a time only the cron_traffic.php runs a new error in syslog as described before:

Mar 10 22:05:01 ns1 Froxlor: MySQL - Error: Invalid SQL: SELECT MAX as ` fguid` FROM `panel_customers`; ErrNo: 2006; Desc: MySQL server has gone away; Sc ript: ; Ref: ; 7d53ada11c0ef20c216c89fc0aaa1d9c

I dont need APS at this time, so I leave it deactivated. I saw also that traffic is calculated this time in face of mentioned error.

I am not shure what to do, expect of waiting for 0.9.27?