Tag Archives: mysql

22 Jun
2011

Gearman Priorities And Persistent Storage

I have been writing a bit about Gearman lately, including installing it for PHP and Ubuntu, actually using it from PHP and also how I use persistent storage with Gearman. I’m moving on to look at adding jobs of different priorities.

I use Gearman entirely as a point to introduce asynchronous-ness in my application. There is a complicated and image-heavy PDF to generate and this happens on an automated schedule. To do this, I use the GearmanClient::doBackground method. This inserts a priority 1 job into my queue.

Using the doHighBackground() and the doLowBackground() methods insert jobs into the queue and checking out my persistent storage I see that the priorities work like this:

priority method
0 doHighBackground()
1 doBackground()
2 doLowBackground()

Gearman works out which task is the next highest priority and will hand it to the next available worker – which means that I can set my automated reporting lower priority than the reports requested by real live people wanting them now, and everyone is happy!

17 Mar
2011

Using Persistent Storage with Gearman

I’m using gearman for the first time in a new project, and two things in particular were bothering me. Firstly, there doesn’t seem to be a built-in way to see what’s in the queue. Secondly, if the gearman server dies (which seemed quite likely when I was first getting to grips with this stuff and writing really buggy code!) you lose your queue. Therefore I decided that I would switch gearman over to running with persistent storage. Continue reading →
01 Mar
2011

Dealing with MySQL Gone Away in Zend Framework

I wrote recently about having gearman in my application, however I have been seeing problems with the long-running PHP worker scripts. My logs had entries like this:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

The worker is a Zend Framework application, run from the CLI, and it seemed like the Zend_Db_Adapter had no way of knowing when MySQL had let go of its end of the connection. I tried a few different things, including Zend_Db_Adapter::getConnection(), but without success – until I dug through the source code (with some help from a friend) and realised that ZF was not reconnecting at all if it thought it already had a connection. So instead, I expressly disconnected and reconnected the database handler. At bootstrap time, I place my database handle into the registry, so I simply added this at the start of the actual function that the gearman worker calls:

$db = Zend_Registry::get('db');
$db->getConnection();

At the end of my script, before it returns to the loop waiting for another gearman job, I just disconnect my database:

$db->closeConnection();

Now Zend_Db_Adapter knows that when I ask it to connect, it needs to go off and make a new connection, and everything works really well! I was seeing the errors because I’m still only testing the system so it can go days between getting any new jobs, and the timeout on MySQL is shorter than that.

20 Oct
2010

Is Enum Evil?

When I work on database designs, either on my own projects or as advisor to others, I often find people very reluctant to use an enum type for any columns. Now, I’m not about to advocate the gratuitous use of an enum column, they definitely have some pitfalls, but I think it is important to understand these rather than just shouting “enum evil!” if anyone should mention them.

There are cases where an enum is the correct choice for a particular type of data, so let’s look at what an enum type actually is and does.

Continue reading →

07 Jul
2008

Mysql Error

I was running an import script today taken from a mysqldump from another user, when I saw an error that looked like this:

ERROR 1005 (HY000) at line 123: Cant create table  (errno: 150)

This is caused by a mysqldump or export process exporting tables in alphabetical order and not in the order in which they rely on one another. My tables had foreign keys which fail on import if the other table doesn’t exist when you create the table with the key. In this case I was only importing six or eight tables so I simply opened the script in a text editor and re-ordered the import blocks. On a bigger scale a more technical solution might be required!