Using MySQL NDB with ruby on rails

Standard

Using NDB with ruby on rails is a big problem. Ruby generates table by its own script, but in default it generates the tables in InnoDB engine. Unfortunately, in the NDB cluster, if you generate tables in InnoDB, it is stored in the mysqld node, not in the data node, hence it will give you problems when you query one node and the tables are there and when you query the other node and the tables are NOT there.

mysql ndb cluster

so back to basics, we all know that mysql have many engines, MyISAM and InnoDB. There is the third one called NDB Cluster. A NDB cluster as shown, has 3 components: sqld (for querying), data node (for storing) and management. Hence if you have the MySQL setup as such, please create your tables as below. Take note of the engine type:

CREATE TABLE `announcements` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` text NOT NULL,
`platform` int(11) NOT NULL DEFAULT ‘0’,
`start_at` datetime NOT NULL,
`end_at` datetime NOT NULL,
`priority` int(11) NOT NULL DEFAULT ‘0’,
`category` int(11) NOT NULL DEFAULT ‘0’,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Notice the engine is a ndbcluster. That will tell MySQL to save it in the data node, so that the table can be accessed by all the sqld nodes. So now the issue is if RoR generates tables in InnoDB, how do we tell them to generate in NDB

The answer is to declare in the options of using NDB cluster when doing migration. Below is one example:

class CreateBundles < ActiveRecord::Migration

  def change

    create_table :bundles, options: “ENGINE=NDBCLUSTER” do |t|

      t.references :category, null: false

      t.column :price_type, “TINYINT UNSIGNED”, null: false, default: 0

      t.column :price, “MEDIUMINT UNSIGNED”, default: 0

      t.column :buy_limit, “MEDIUMINT UNSIGNED”, default: 0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s