How to reconnect to DB, or check for active connection.

9 Posts

whnunlife Reply #1, 13 years, 7 months ago

I'm working on a twitter crawler that stores the results returned from twitter into modx_site_content. I'm running into an issue, where after 5 or so minutes, if the script looses the DB connection, the script continues to run, but the connection is not re-opened.

What is the best way to make sure that after I iniate modx, if it looses the DB connection, to reconnect.

Here is just a few snippets from my script:

// Initialize modX
$modx= new modX();
$modx->initialize('mgr');

Now, in the error log, I see the following, whenever the script looses the connection to the DB:

Array
(
    [0] => HY000
    [1] => 2006
    [2] => MySQL server has gone away
)

[2013-02-05 11:53:50] (ERROR) Error HY000 executing statement:
INSERT INTO `twitter_dataresults` (`results`, `createdon`, `query_id`) VALUES (10, 1360086830, 73307)
Array
(
    [0] => HY000
    [1] => 2006
    [2] => MySQL server has gone away
)

I assume that before anytime in my script that I make a query, I should check to see if the connection is open, correct?

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #2, 13 years, 7 months ago

You could keep an eye on the return value of $tweetobject->save(); - when it fails to create the object because of mysql going away, it should return false, and you could kill of the process or try to connect again.

Beyond that I'm not sure if there might be a MySQL limit that kills connections after X minutes or if there's another reason it is breaking up..

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

9 Posts

whnunlife Reply #3, 13 years, 7 months ago

Quote from: markh at Feb 05, 2013, 04:16 PM

You could keep an eye on the return value of $tweetobject->save(); - when it fails to create the object because of mysql going away, it should return false, and you could kill of the process or try to connect again.

Beyond that I'm not sure if there might be a MySQL limit that kills connections after X minutes or if there's another reason it is breaking up..

Thanks, going to implement this part.

I'm guessing next, if this is the case, I would just have to call the following then?

// Initialize modX
$modx= new modX();
$modx->initialize('mgr');

9 Posts

whnunlife Reply #4, 13 years, 7 months ago

Update:

I'm successfully able to re-connect to the db, however, not sure if this is a bug or not. But after I re-connect, I'm unable to run $modx->addPackage

These are my findings.

First, the package I'm adding adds support for a custom table, all of which I followed from here: http://bobsguides.com/custom-db-tables.html

Now, this runs just fine, until the DB connection is lost. What I'm finding is that after I re-initialize modx, I can add the package, but a few details are missing. I'm just stumped here.

Here's just a short snipped from the package:

This is when the package has been added, and is functioning correctly.

fieldNames] => 
    [_class] => Dataresults
    [_package] => twitter
    [_alias] => Dataresults
    [_pk] => id
    [_pktype] => 
    [_table] => `twitter_dataresults`
    [_tableMeta] =>

This is when I re-add the package after loosing the db.

[fieldNames] => 
    [_class] => Dataresults
    [_package] => 
    [_alias] => Dataresults
    [_pk] => id
    [_pktype] => 
    [_table] => 
    [_tableMeta] =>

Now, if I keep comparing the printed array, [_fieldMeta] is empty as well.

Basically, here is the code I'm using for when the DB connection is lost, and I attempt to re-connect:

public static function reconnect() {
		self::$modx = new modX();
		self::$modx->initialize('mgr');

		// Add the Twitter package
		self::$twitterStatsObject = self::$modx->addPackage('twitter',self::$twitter_package . 'model/','twitter_');

		if (!self::$twitterStatsObject) {
	  		echo 'Failed to add Twitter Stats Package' . "\n";
	  		exit();
		} 

	}

The above code works well, except for the package. Am I missing something here? Also, the add package code is exactly the same in the connect() method. [ed. note: whnunlife last edited this post 13 years, 7 months ago.]