Dynamically pick a database in cakephp.

While doing some research into sharding and cakephp I was looking for a way to change a model's database connection.

The docs specify in your models you can use:
var $useDbConfig = 'default';
which will find a matching variable in DATABASE_CONFIG and use the connection settings.

in database.php

class DATABASE_CONFIG {
 var $default = array(
  'driver' => 'mysql',
  'persistent' => false,
  'host' => 'localhost',
  'port' => '',
  'login' => 'myuser',
  'password' => 'yeahright',
  'database' => 'mycake',
  'schema' => '',
  'prefix' => '',
  'encoding' => ''
 );
...
}

Note: Difference between Sharding and Partitioning is shards resides on different servers. However both separate data depending on some field or attribute.

So to make this dynamic you start in bootstrap.php and create a hashing alg like this to separate your data:

$shardId = $id % 10;
$shardHostArray = array(5 => '192.168.0.25');

Configure::write('shard.host', $shardHostArray[5]);
or a
define('SHARDHOST', $shardHostArray[5]);

The next change you make is in database.php.

Add a constructor to class DATABASE_CONFIG

 public function __construct()
 {
  $this->shard = $this->default;
  $this->shard['host'] = SHARDHOST;
 }

Now when you have a model in cakephp such as GroupModel that you want to shard. You specify:

var $useDbConfig = 'shard'; // this is the name of a class member in
                                 // DATABASE_CONFIG. I created this var in 
                                 // the above constructor.

And the correct server address will be used. This allows you to stay within the cakephp conventions and not break the schema caching.

Database Sharding with Mysql and PHP

Database Sharding at Netlog, with MySQL and PHP

One of the better articles about scaling web sites.

rake db:migrate error when using differing libmysql.dll and mysql servers

I'm using a fedora mysql server, while the ruby server runs off of windows. the libmysql.dll form my previous post came from a 5.1 windows mysql server.

The version mysql server on fedora is 5.0. This caused the following error:

D:\workspace\rubytest>rake db:migrate
(in D:/workspace/rubytest)
rake aborted!
Mysql::Error: Commands out of sync; you can't run this command now: SHOW TABLES

(See full trace by running task with --trace)

Solution:
get the mysql-noinstall 5.0 server and copy the libmysql.sql into /ruby/bin

ERROR: While generating documentation for mysql

Error while running the command
gem install mysql
in windows xp

Produced this error:

C:\>gem install mysql
Successfully installed mysql-2.7.3-x86-mswin32
1 gem installed
Installing ri documentation for mysql-2.7.3-x86-mswin32...
Installing RDoc documentation for mysql-2.7.3-x86-mswin32...
ERROR: While generating documentation for mysql-2.7.3-x86-mswin32
... MESSAGE: Unhandled special: Special: type=17, text=""
... RDOC args: --op d:/Ruby/lib/ruby/gems/1.8/doc/mysql-2.7.3-x86-mswin32/rdoc -
-exclude ext --main README --quiet ext README docs/README.html
(continuing with the rest of the installation)

Solution:
set path=%path%;c:\mysql-5.1.33-win32\bin\

I then copied the libmysql.dll from the above bin into the ruby bin (I'm not sure this is necessary, but it was one of the steps). Note copying the libmysql.dll from the mysql tools did not work, it has to be from the mysql server files. An easy way without installing mysql server is to use the mysql noinstall zip.

I then ran the command
gem install mysql --no-rdoc

I still get an error if I run gem install mysql, but with the no-rdoc the ruby server is able to run.

You can now delete mysql-noinstall and your site should work.