Category Archives: mysql

Yii migration use change in addition to up and down

Finally I pushed some of my yii extensions to github.
https://github.com/radzserg/yii-Rz

Still need to work on README.md I have lack of time now. All in all I think I can refer to it now.

And as a bonus I will tell about my extension for CDbMigration. A lot of migration tools/lib already support change() method in addition to up() and down(). 

But the main problem here – it’s easy to implement it if we create table or add a column or index in this case we know that we need to do in down action. It’s easy we need to remove appropriate object. For example:

    public function up()
    {
        $this->addColumn('user', 'email', 'varchar(255) AFTER username');
        $this->addColumn('user', 'password', 'varchar(255) AFTER email');
    }

    public function down()
    {
        $this->dropColumn('user', 'email');
        $this->dropColumn('user', 'password');
    }

Easy enough. But what do we need to do in down action if we create table. In this case everything is quite complicated. We either need to keep DB snapshot for each migration. Or create it going through migration until some migration that you need. Both quite difficult to implement and I guess even if you can implement such approach you still have a lot of problem.

The good thing here that in most migrations we add or create something. And we don’t need to care about down function. And in that cases when we need to implement down functionality we will do that using closures.

Here are my code
https://github.com/radzserg/yii-Rz/blob/master/db/ChangeMigration.php

Here are examples of some of my migrations

<?php

class m130626_132012_add_not_confirmed_users extends Rz\db\ChangeMigration
{
    public function change()
    {
        $this->addColumn('sm_user', 'confirmed', 'TINYINT(1) NOT NULL DEFAULT 0 after create_time');
        $this->addColumn('user', 'confirmed', 'TINYINT(1) NOT NULL DEFAULT 0 after create_time');

        $this->alterColumn('sm_user', 'user_id', 'INT', function() {
           $this->alterColumn('sm_user', 'user_id', 'INT NOT NULL');
        });
    }

}

When you run yiic migrate down. Migration will save queue of back actions in this example

  • remove column sm_user.confirmed (autogenerated)
  • remove column user.confirmed (autogenerated)
  • alter sm_user change user_id to int not null (our manual down closure)

And finally execute all this action in reverse order that’s it. No need to care about down functionality and order.

I proposed that approach to yii core team https://github.com/yiisoft/yii2/issues/593#issuecomment-20397820 but they refuse it. Well maybe it’s correct when we talk about framework. Anyway I decided to implement and try it. And for now I like it 🙂

Try it too and let me know do you like it or no.

Prevent General error: 2006 MySQL server has gone away in Zend Framework

As introduction

I started to get General error: 2006 MySQL server has gone away error in one of my project. I’ve read http://dev.mysql.com/doc/refman/5.0/en/gone-away.html but still was not sure why does it happen. Until I stared use php Gearman workers. That was daemon processes they work in memory all the time. I had 5 workers and all of them have such error. I realized that workers sleep between tasks and do not send any command to mysql and mysql close connection. Such problem can happen with cron script that works for a long time but has delays between queries(do some other tasks).

Solution

One of the things for which I like Zend Framework is really easy extensibility. We can just override default Adapter and add some functionality. So the idea is simple if we get 2006 error during query we will try to reconnect. If it didnt’t help we will throw original exception.

<?php

/**
 * Extended Zend_Db_Adapter_Pdo_Mysql - try to reconnect if mysq gone away
 * User: radzserg
 * Date: 4/19/12
 */
class App_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql
{

    const MAX_RECONNECT_COUNT = 1;

    public function query($sql, $bind = array())
    {
        try {
            return parent::query($sql, $bind);
        } catch (Zend_Db_Statement_Exception $e) {
            $message = $e->getMessage();
            if ($message == 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away') {
                $this->_reconnect();
                return parent::query($sql, $bind);
            } else {
                throw $e;
            }
        }
    }

    private function _reconnect()
    {
        $this->_connection = null;
        for ($i = 1; $i <= self::MAX_RECONNECT_COUNT; $i++) {
            sleep(1);
            try {
                $this->_connect();
            } catch (Zend_Db_Adapter_Exception $e) {
                if ($i == self::MAX_RECONNECT_COUNT) {
                    throw $e;
                }
            }
            if ($this->_connection) {
                return ;
            }
        }
    }
}

All we have to do now is just add appropriate instruction to config
resources.multidb.default.adapterNamespace = “App_Db_Adapter”

That’s it feedback is welcome.

Zend_Test_PHPUnit_DatabaseTestCase with multidb

Today I’ll tell you how you can run tests for multiple databases with PHPUnit_DatabaseTestCase at the same time.There are 2 ways. First is very simple you can use in your fixtures something like this

It will work if only you have enough rights it’s good for development/local db. But as a rule on even dev servers it won’t work.

Second approach is to extend PHPUnitsetUp
I’ve changed some names you can use more clear names in your projects.

abstract class App_Test_PHPUnit_DatabaseTestCase extends Zend_Test_PHPUnit_DatabaseTestCase
{
 
    protected $_connectionMock;
    protected $_secondDbConnectionMock;
 
    protected $backupGlobalsBlacklist = array('application');  // btw this hack will speed up your tests
 
    /**
     * @return Zend_Test_PHPUnit_Db_Connection
     */
    protected function getConnection()
    {
        if ($this->_connectionMock == null) {
            $multiDb = Zend_Registry::get('multidb');
 
            $connection = $multiDb->getDb();
 
            $this->_connectionMock = $this->createZendDbConnection(
                $connection, ''
            );
 
            Zend_Db_Table_Abstract::setDefaultAdapter($connection);
        }
        return $this->_connectionMock;
    }
 
    protected function getSecondDbConnection()
    {
        if ($this->_dnsConnectionMock == null) {
            $multiDb = Zend_Registry::get('multidb');
 
            $connection = $multiDb->getDb('second_db');
 
            $this->_secondDbConnectionMock = $this->createZendDbConnection(
                $connection, ''
            );
        }
 
        return $this->_dnsConnectionMock;
    }
 
    protected function setUp()
    {
        parent::setUp();
 
        $this->databaseTester = NULL;
 
        $this->getDatabaseTester()->setSetUpOperation($this->getSetUpOperation());
        $this->getDatabaseTester()->setDataSet($this->getDataSet());
        $this->getDatabaseTester()->onSetUp();
 
        $secondDataSet = $this->getDataSetForSecondDb();
        if ($dnsDataSet) {
            // create data set for second db
            $secondDataTester = new PHPUnit_Extensions_Database_DefaultTester($this->getSecondDbConnection());
            $secondDataTester->setSetUpOperation($this->getSetUpOperation());
            $secondDataTester->setDataSet($secondDataSet);
            $secondDataTester->onSetUp();
        }
 
    }
 
    protected function getDataSetForSecondDb()
    {
        return null;
    }
 
}

as you see we just check does method getDataSetForSecondDb return data. You have to override it in child classes. If we get dataSet we will set up operations for second Db.

Everything is quite simple but in the fullness of time it made me to  look inside PHPUnit_* classes. So I hope I can save you time with that stuff.