Archive for June 28, 2013

MySQL Cluster Manager 1.2.4 is released

MySQL Cluster Manager logoMySQL Cluster Manager 1.2.4 is now available to download from My Oracle Support or from the Oracle Software Delivery Cloud.

The biggest new feature in this release is the support for MySQL Cluster 7.3. Details on the changes can be found in the MySQL Cluster Manager documentation .

Documentation is available here.





MySQL Cluster 7.3 is now Generally Available – an overview

MySQL Cluster 7.3 logoMySQL Cluster 7.3 has now been declared GA! This means that you can deploy it in your live systems and get support from Oracle.

This post briefly describes the main new features in the release; for a deeper dive, refer to the What’s new in MySQL Cluster 7.3 white paper and the more specialised blog posts that you’ll find links to from this post.

I’ll also be giving more details in the MySQL Cluster 7.3 Webinar which is scheduled for 09:00 Pacific / Noon Eastern / 17:00 UK / 18:00 CET this Thursday (20th June). This is a great opportunity to get your questions answered in real-time by the experts. As usual, the webinar is free but you should n the register here ahead of time. Even if you can’t attend it’s worth registering as you’ll then be sent a link to the replay.

MySQL Cluster Auto-Installer

MySQL Cluster Auto-Installer

MySQL Cluster Auto-Installer

The MySQL Cluster Auto-Installer is a browser-based GUI that will provision a well configured, distributed Cluster in minutes, ready for test, development or production environments.

A major priority for this release is to make it much easier and faster to provision a cluster that is well tuned for your application and environment; we want you to focus on exploiting the benefits of MySQL Cluster in your application rather than on figuring out how to install, configure and start the database. The MySQL Cluster Auto-Installer provides a browser-based GUI which steps you through creating a Cluster tailored to your requirements. For a really good view of how the tool works, a tutorial video and detailed worked example is available from the blog post: MySQL Cluster 7.3 MySQL Cluster Auto-Installer.

Foreign Keys

Foreign Key constraints between tables

Tables with Foreign Key constraint

Foreign Keys (FKs) are a way of implementing relationships/constraints between columns in different tables. For example if we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

We believe that this is going to enable a whole new set of applications exploit the advantages of MySQL Cluster where:

  • Developers want to simplify their application by pushing referential checks down into the database
  • The application is built upon 3rd party middleware that is dependent on FKs
  • The application is already so dependent on FKs that it would be too complex to remove them

Note that the FK constraints will be applied regardless of how data is subsequently written (i.e. through SQL or any of the available NoSQL APIs that bypass the MySQL Server) – this ensures that the intended data integrity is always maintained.

More details and a worked example of using Foreign Keys with MySQL Cluster can be found in this post: Foreign Keys in MySQL Cluster.

JavaScript Driver for Node.js

MySQL Cluster driver for JavaScript/Node.jsNode.js is a platform that allows fast, scalable network applications (typically web applications) to be developed using JavaScript. Node.js is designed for a single thread to serve millions of client connections in real-time – this is achieved by an asynchronous, event-driven architecture – just like MySQL Cluster, making them a great match.

The MySQL Cluster NoSQL Driver for Node.js is implemented as a module for the V8 engine, providing Node.js with a native, asynchronous JavaScript interface that can be used to both query and receive results sets directly from MySQL Cluster, without transformations to SQL. As an added benefit, you can direct the driver to use SQL so that the same API can be used with InnoDB tables.

With the MySQL Cluster JavaScript Driver for Node.js, architects can re-use JavaScript from the client to the server, all the way through to a distributed, fault-tolerant, transactional database supporting real-time, high-scale services.

Developing an application to use this API is very straightforward and an example application with full setup instructions is provided in the post Using JavaScript and Node.js with MySQL Cluster – First steps.

Connection Thread scalability

MySQL Cluster thrives when it is offered as many operations in parallel as possible. To achieve this, parallelism should be configured at each layer. There should be multiple application threads sending work to the MySQL Server (or other API), there should be multiple MySQL Servers and finally multiple connections between the MySQL Server (or other API node) and the data nodes. This is explained in more detail in the MySQL Cluster Performance white paper.

Increased throughput with Connection Thread Scalability

Increased throughput with Connection Thread Scalability

Each of the connections to the data nodes consumes one of the 256 available node-ids and so in some scenarios they could cap the scalability of the Cluster. MySQL Cluster 7.3 greatly increases the throughput of each of these connections meaning that less connections (and therefore node-ids) are needed to tackle the same workload; this in turn means that more API nodes and data nodes can be added to the Cluster to scale the capacity and performance even further. Benchmarks have shown up to a 8x increase in throughput per connection. The graph illustrates how less NDB API connection threads in MySQL Cluster 7.3 can deliver increased throughput compared with MySQL Cluster 7.2; the benchmark was based on DBT2 using a single data node; a single MySQL Server and 128 client connections.

MySQL 5.6 Server

MySQL Cluster 7.3 also rebases onto MySQL 5.6. What this means is that the MySQL Server that comes with MySQL Cluster 7.3 is based on MySQL 5.6. This in turn means that when you mix and match MySQL Cluster (NDB) and InnoDB tables, you’ll be getting the benefits of the latest and greatest version of each storage engine.

Try it out!

Please go ahead and download and start experimenting with MySQL Cluster 7.3. We look forward to hearing how you get on!





Foreign Keys in MySQL Cluster

Foreign Key constraints between tables

Foreign Key constraints between tables

The newly announced GA of MySQL Cluster 7.3 (7.3.2) builds upon second DMR (7.3.1 m2) released earlier in the year which added Foreign Keys to MySQL Cluster. Foreign Keys is a feature requested by many, many people and has often been cited as the reason for not being able to replace InnoDB with MySQL Cluster when they needed the extra availability or scalability.

Note that this post is an up-version of the original – and was first published with the 7.3 labs release in June 2012.

What’s a Foreign Key

The majority of readers who are already familiar with Foreign Keys can skip to the next section.

Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, in the above figure, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

We refer to the towns table as the child and the counties table as the parent.

There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

  • Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.
  • Restrict: A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.
  • No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.
  • Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.
  • Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.

How to use Foreign Keys with MySQL Cluster

For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.

First of all, create the tables and add some data:

mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
  ENGINE=ndb;
mysql> INSERT INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
  ('Oxfordshire','England'),('Buckinghamshire','England');
mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
  INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county) 
  REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
  ('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | England |
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

Note that the FK is actually defined as part of creating the towns table with CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT. county_town is the label assigned to the constraint (if you don’t specify one then one will be automatically created – you can see it by executing SHOW CREATE TABLE towns). The constraint is set up from the county column in the towns (child) table to the county column in the counties (parent) table.

As a first check, we can confirm that values that are not part of the constraint can be modified in the parent table – in this case we’ll move the England/Wales border:

mysql> UPDATE counties SET country='WALES' WHERE county='Shropshire';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Next, to illustrate the kind of user error that can be prevented, we attempt to add a town in a county that has not yet been defined – we want this to fail as it would break the constraint we created:

mysql> INSERT towns VALUES ('Cambridge','Cambridgeshire');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  (Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                               |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 255 'Foreign key constaint violated: No parent row found' from NDB          |
| Error   | 1452 | Cannot add or update a child row: a foreign key constraint fails (Unknown error code) |
+---------+------+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

We can also confirm that the constraint checking works in the opposite direction, by attempting to delete a county that has towns defined for it:

mysql> DELETE FROM counties WHERE county='Berkshire';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 256 'Foreign key constaint violated: Referenced row exists' from NDB            |
| Error   | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) |
+---------+------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

It is also possible to delete FK constraints and add new ones, we do that next and at the same time we’ll switch to a CASCADE restriction. Note that adding and deleting FKs are on-line operations. In case you have forgotten the name of the constraint (or we had not explicitly named it) we can use the SHOW CREATE TABLE command to remind us:

mysql> SHOW CREATE TABLE towns;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| towns | CREATE TABLE `towns` (
  `town` varchar(30) NOT NULL,
  `county` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`town`),
  KEY `county_county_index` (`county`),
CONSTRAINT `county_town` FOREIGN KEY(`county`) REFERENCES `counties` (`county`)
  ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE towns DROP FOREIGN KEY county_town;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE towns ADD CONSTRAINT county_town FOREIGN KEY (county)
  REFERENCES counties(county) ON DELETE CASCADE ON UPDATE CASCADE;

The data in the tables will not have changed as part of dropping and adding constraints and so you can always DROP and the re-add constraints if you need to turn FKs off for a while:

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
| Shropshire      | WALES   |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

Now if we delete the county of Berkshire then the CASCADE option will cause all of the associated towns to be deleted too:

mysql> DELETE FROM counties WHERE county='Berkshire';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Oxfordshire     | England |
+-----------------+---------+
3 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Shrewsbury | Shropshire  |
| Oxford     | Oxfordshire |
+------------+-------------+
2 rows in set (0.00 sec)

Note that MySQL Cluster implements FKs in the data nodes and the constraints will be enforced regardless of how the data is written (through SQL, javascript, Memcached, ClusterJ, REST or directly through the NDB API). Before illustrating this, we add back the data we have just deleted:

mysql> INSERT INTO counties VALUES ('Berkshire','England');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

We’ll then run a simple C++ app to delete the ‘Berkshire’ row from the counties table. The full source for the app is available here but the key part of the code is this:

static void do_change(Ndb &myNdb)
{
const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
const NdbDictionary::Table *countiesTable= myDict->getTable("counties");

char royalBorough[255];
royalBorough[0]=9;
strcpy(royalBorough + 1, "Berkshire");

if (countiesTable == NULL)
APIERROR(myDict->getNdbError());

NdbTransaction *myTransaction= myNdb.startTransaction();
if (myTransaction == NULL) APIERROR(myNdb.getNdbError());

NdbOperation *myOperation= myTransaction->getNdbOperation(countiesTable);
if (myOperation == NULL) APIERROR(myTransaction->getNdbError());

std::cout << "About to delete " << royalBorough << "n";

myOperation->deleteTuple(); 
myOperation->equal("county", (const char *) royalBorough);

if (myTransaction->execute( NdbTransaction::Commit ) == -1)
APIERROR(myTransaction->getNdbError());

myNdb.closeTransaction(myTransaction);
}

We then run this code and confirm that the cascaded delete has worked:

billy@ws2:~/projects/fk$ ./testfk localhost:1186
About to delete         Berkshire

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Oxfordshire     | England |
+-----------------+---------+
3 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Shrewsbury | Shropshire  |
| Oxford     | Oxfordshire |
+------------+-------------+
2 rows in set (0.00 sec)

Comparison with InnoDB’s Foreign Keys

As mentioned, we’ve attempted to mirror the way that InnoDB works as much as possible in the way that it implements Foreign Keys as that’s what we think will allow people to move more easily to MySQL Cluster when appropriate. There are a couple of exceptions though:

  • InnoDB doesn’t support “No Action” constraints, MySQL Cluster does
  • You cannot set up FKs between 2 tables where one is stored using MySQL Cluster and the other InnoDB.
  • You cannot change primary keys through the NDB API which means that the MySQL Server actually has to simulate such operations by deleting and re-adding the row. If the PK in the parent table has a FK constraint on it then this causes non-ideal behaviour. With Restrict or No Action constraints, the change will result in an error. With Cascaded constraints, you’d want the rows in the child table to be updated with the new FK value but, the implicit delete of the row from the parent table would remove the associated rows from the child table and the subsequent implicit insert into the parent wouldn’t reinstate the child rows. For this reason, an attempt to add an ON UPDATE CASCADE where the parent column is a primary key will be rejected.

MySQL Workbench

Viewing & updating Foreign Key Constraints in MySQL Workbench

Viewing and updating Foreign Key Constraints in MySQL Workbench

While not really the subject of this post, MySQL Workbench is a great tool for database developers and one of the features is the creation, modification and reverse engineering of schemas. For example, you could import your existing schema, add a new table and then introduce a FK constraint between an existing table and your new one. Previously, WorkBench would only allow you to play with FKs for InnoDB tables.

Closing remarks

We believe that this is going to enable a whole new set of applications exploit the advantages of MySQL Cluster (on-line scalability, real-time performance, NoSQL APIs, real-time performance, on-line schema changes,…) either because:

  • they want to simplify their application by pushing these referential checks down into the database – letting the developer focus more of their time on extending the application;
  • because they’re stuck with 3rd party middleware that is dependent on FKs
  • or that the application is already so dependent on FKs that it would be too much pain to remove them.

If you are new to MySQL Cluster then here are some useful resources to get you started:





MySQL Cluster 7.3 GA – Includes MySQL Cluster Auto-Installer

MySQL Cluster 7.3 has just been declared Generally Available (and with it the new MySQL Cluster Auto-Installer) – download it from here.

Deploying a well configured cluster has just got a lot easier! Oracle have released a new auto-installer/configurator for MySQL Cluster that makes the processes extremely simple while making sure that the cluster is well configured for your application. A single command launches the web-based wizard which then steps you through configuring the cluster; to keep things even simpler, it will automatically detect the resources on your target machines and use these results together with the type of workload you specify in order to determine values for the key configuration parameters.

For a broader view of what is being released – check out this MySQL Cluster 7.3 GA article.

Tutorial Video

Before going through the detailed steps, here’s a demonstration of the auto-installer in action (note that this is actually an earlier version but the idea is the same)…

Downloading and running the wizard

The software can be downloaded as part of the MySQL Cluster package from the MySQL Cluster Download page. To run on Windows, just double click setup.bat – note that if you installed from the MSI and didn’t change the install directory then this will be located somewhere like C:Program Files (x86)MySQLMySQL Cluster 7.3. On Linux, just run ndb_setup from the bin folder.

If you launch the installer from a desktop environment then the first page of the wizard will automatically be opened in your web browser, if not then just browse to the URL that is displayed on the command line.

Creating your cluster

Figure 1 Specify hosts and application type

Figure 1 Specify hosts and application type

After the initial splash-screen you’re presented with the page shown in Figure 1 where you specify the list of servers that will form part of the cluster.
You also get to specify what “type” of cluster you want; if you’re experimenting for the first time then it’s probably safest to stick with “Simple testing” but for a production system you’d want to specify the application type and query profile, i.e. write-intensive.

 

 

 

 

Figure 2 Auto-discovery of host resources

Figure 2 Auto-discovery of host resources

On the next page (Figure 2), you will see the wizard attempt to auto-detect the resources on your target machines. If this fails (e.g. if you don’t have an accessible SSH server on the target hosts) then you can enter the data manually. You can also overwrite the resource-values – for example, if you don’t want the cluster to use up a big share of the memory on the target systems then just overwrite the amount of memory.

It’s also on this page that you can specify where the MySQL Cluster software is stored on each of the hosts (if the defaults aren’t correct) – this should be the path to where you extracted the MySQL Cluster tar-ball file – as well as where the data (and configuration files) should be stored. You can just overwrite the values or select multiple rows and hit the “edit” button.

Figure 3 Review Cluster topology

Figure 3 Review Cluster topology

The following page (Figure 3) presents you with a default set of nodes (processes) and how they’ll be distributed across all of the target hosts – if you’re happy with the proposal then just advance to the next page.

Alternatively, you can add extra nodes, move nodes from one host to another (just drag and drop), delete nodes or change a node from one type to another.

 

 

 

Figure 4 Review configuration parameters

Figure 4 Review configuration parameters

On the next screen (Figure 4) you’re presented with some of the key configuration parameters that have been configured (behind the scenes, the installer sets many more) that you might want to override; if you’re happy then just progress to the next screen. If you do want to make any changes then make them here before continuing. Note that you can enable the advanced configuration option here in order to view/modify more parameters.

 

 

 

Figure 5 Deploy the Cluster

Figure 5 Deploy the Cluster

With the click of a button, the final screen (Figure 5) lets you deploy (copy the correct configuration settings to the hosts and create the directory structures) and start the Cluster.

If you prefer or need to start the processes manually, this page also shows you the commands that you’d need to run (as well as the configuration files if you need to create them manually).

A traffic light display shows the various Cluster nodes (processes) coming into service.

 

Figure 6: Confirm that all nodes are running

Figure 6: Confirm that all nodes are running

Finally, you can confirm that all of the processes (nodes) are up and running (note that any API slots that don’t have MySQL Servers using them won’t show as running – that’s as expected):

As always it would be great to hear some feedback especially if you’ve ideas on improving it or if you hit any problems.





Using JavaScript and Node.js with MySQL Cluster – First steps

MySQL Cluster driver for JavaScript/Node.js

We’re very pleased to announce that MySQL Cluster 7.3 has gone GA; for a full run-down of the new features and enhancements, take a look at the "MySQL Cluster 7.3 New Features for Internet-Scale Performance with Carrier-Grade Availability" white paper but this post will focus on just one of the features – the MySQL Cluster JavaScript Driver for Node.js. The post will step you through setting everything up so that you can get your first Node.js code reading and writing from MySQL Cluster.

Background

MySQL Cluster is a distributed, shared nothing database that provides linear scalability, 99.999% availability and high read/write throughput with low latency. SQL is just one way of accessing data stored in MySQL Cluster – there are also a number of native NoSQL APIs that go straight to the data without any interim conversion to SQL. These APIs include C++, Memcached, Java, JPA and REST – in MySQL Cluster 7.3 JavaScript/Node.js is added to the list.

Node.js (typically referred to simply as "Node") is a platform for running fast, scalable applications using JavaScript. For maximum performance, Node.js is built around a non-blocking, event-driven architecture – just like MySQL Cluster and so they fit together very well.

The MySQL Cluster JavaScript Driver for Node.js is just what it sounds like it is – it’s a connector that can be called directly from your JavaScript code to read and write your data. As it accesses the data nodes directly, there is no extra latency from passing through a MySQL Server and need to convert from JavaScript code//objects into SQL operations. If for some reason, you’d prefer it to pass through a MySQL Server (for example if you’re storing tables in InnoDB) then that can be configured.

As with all of the other APIs that we offer, you’re able to mix & match access methods; while you access the database using JavaScript objects, these will be mapped to rows in database tables and this same data can simultaneously be accessed by other NoSQL API or through SQL.

MySQL Cluster Auto-Installer

MySQL Cluster Auto-Installer

Installing MySQL Cluster

Obviously, to try out our JavaScript code we’ll need a database to access. The simplest way to do this is to use the new auto-installer that’s part of MySQL Cluster 7.3; this provides a browser-based GUI that configures and runs a MySQL Cluster database that’s been configured for your application and environment. Using this installer is described in the "MySQL Cluster Auto-Installer" post.

 

 

 

Setting up the Database

Foreign Key constraints between tables

Tables with Foreign Key constraint

A simple database schema is being used – a single table would really be enough but we’ll actually create two in order to demonstrate that Foreign Key constraints (Foreign Keys are another new feature in MySQL Cluster 7,3) are still enforced when using the NoSQL APIs.

These two tables are set up as follows:

[billy@bob]$ mysql --protocol=tcp -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.10-ndb-7.3.1-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CREATE TABLE test.counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
country VARCHAR(30)) ENGINE=ndb;
Query OK, 0 rows affected (1.17 sec)

mysql> CREATE TABLE test.towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) 
ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
Query OK, 0 rows affected (1.00 sec)

Installing Node.js

Node.js can be downloaded from Node.js.org.

If you’re using Windows then the MSI will guide you through the process – though when you’ve finished you’ll need to manually run the following from the cmd prompt:

npm install -g node-gyp

On Linux, you’ll need to manually build and install Node.js after you’ve downloaded it:

[root@bob node-v0.10.7]# ./configure
[root@bob node-v0.10.7]# make
[root@bob node-v0.10.7]# make install

Building mysql-js (the JavaScript driver for MySQL Cluster)

Regardless of what operating system is being used, the driver must be built before it can be used. Furthermore, to build the driver you need to have a local MySQL Cluster package (so that it can access the NDB API client library).

Depending on the exact Linux distro and version being used, some dependencies may need to be installed first:

[root@bob]$ yum install gcc
[root@bob]$ yum install gcc-c++
[root@bob]$ npm install mysql
[root@bob]$ npm install -g node-gyp

Once that has been done, the driver can be built – the following should be run from within the share/nodejs directory within the MySQL Cluster package:

[billy@bob nodejs]$ node configure.js 
[billy@bob nodejs]$ node-gyp configure build -d

Example Code

To begin with, the complete code is provided and then each component is explained.

var nosql = require('..'); 

var Town = function(name, county) {
    if (name) this.town = name;
    if (county) this.county = county;
};

// create basic object<->table mapping
var annotations = new nosql.TableMapping('towns').applyToClass(Town);

//check results of find
var onFind = function(err, result) {
    console.log('onFind.');
    if (err) {
        console.log(err);
    } else {
        console.log('Found: ' + JSON.stringify(result));
    }
    process.exit(0);
};

//check results of insert
var onInsert = function(err, object, session) {
    console.log('onInsert.');
    if (err) {
        console.log(err);
    } else {
        console.log('Inserted: ' + JSON.stringify(object));

        // Now read the data back out from the database
        session.find(Town, 'Maidenhead', onFind);
    }
};

// insert an object
var onSession = function(err, session) {
    console.log('onSession.');
    if (err) {
    console.log('Error onSession.');
        console.log(err);
        process.exit(0);
    } else {
        var data = new Town('Maidenhead', 'Berkshire');
        session.persist(data, onInsert, data, session);
    }
};

var dbProperties = nosql.ConnectionProperties('ndb');

console.log('Openning session');

// connect to the database
nosql.openSession(dbProperties, Town, onSession);

console.log('Openned session');

Step 1 Import API software

The first step is for your JavaScript application to specify that it will use the new API (mysql-js); this will work if this file is stored in a directory under cluster-install/share/nodejs:

var nosql = require('mysql-js');

Step 2 Define JavaScript class

A class should be defined that will be used by the application and will also be mapped to the towns table that has already been created. The class is defined through the specification of its constructor:

var Town = function(name, county) {
    if (name) this.town = name;
    if (county) this.county = county;
};

Step 3 Define schema mappings

MySQL Cluster is a relational database while JavaScript works with objects. To get the best of both worlds, mappings can be set up between MySQL tables (or individual columns) and JavaScript objects – in this case it’s kept very simple and the Town class is mapped to the towns table:

var annotations = new nosql.TableMapping('towns').applyToClass(Town);

Note that you can also define mappings at a finer level – only mapping to specific columns within a table and allowing the class members to have different names to the table columns.

Step 4 Connect to the database

In this step, the application connects to the database to indicate that it wants to access the data nodes directly rather than via the MySQL Server (and hidden conversion to SQL), the ConnectionProperties is set to ndb (rather than mysql). Additionally, a callback function (onSession) is passed as an argument. Recall that this is an event-driven, non-blocking, asynchronous API and so after calling openSession, control will be immediately returned to the application – without waiting for the session to actually be established – the application can then go away and perform other useful work. Once the session has been created, the callback function (onSession) will be invoked, allowing the application to take the next steps.

var dbProperties = nosql.ConnectionProperties('ndb');
nosql.openSession(dbProperties, annotations, onSession);

Step 5 Store some data

Note that the code in this step is the implementation of the call back function (onSession) that we passed in as a parameter to openSession in the previous step. This function simply instantiates a Town JavaScript object and then writes it to MySQL Cluster through the persist method. Once more, a callback function is passed in (onInsert) which will be invoked once the data has been stored in the database. Note that any objects specified after the callback function in the parameter list (in this case, the Town object data) will be provided as parameters to the callback function when it’s executed – note that the session parameter is also included and the framework will pass that as a parameter to the onInsert callback function.

var onSession = function(err, session) {
    if (err) {...} else {
        var data = new Town('Maidenhead', 'Berkshire');
        session.persist(data, onInsert, data, session);
    }
};

Step 6 Verify insert succeeded & initiate reading of data from the database

Next, our callback function (onInsert) is called and the object parameter contains the Town that had been passed into the persist method in the previous step, together with the session variable. This function simply checks that there wasn’t an error and then displays the Town that has been stored in the database. Again, a callback function is provided – onFind – that will be called once the data has been found.

var onInsert = function(err, object, session) {
    if (err) { ... } else {
        console.log('Inserted: ' + JSON.stringify(object));
        session.find(Town, 'Maidenhead', onFind);
    }
};

Step 7 Process the data read back from the database

Finally, onFind is called-back and it handles the processing of the results (whether it is an error or the Town object in the form of the result parameter).

var onFind = function(err, result) {
    console.log('onFind.');
    if (err) { ... } else {
        console.log('Found: ' + JSON.stringify(result));
    }
    process.exit(0);
};

The on-line documentation for mysql-js describes the API in full and includes some further examples.

Running the application

Before running the application, the location of the NDB API client library must be provided (this is with the MySQL Cluster package):

[billy@bob my_samples]$ export LD_LIBRARY_PATH=/home/billy/cluster_7_3/lib

The example code should be stored in a file called my-test.js in a directory under cluster-install/share/nodejs/my_samples. To run the application, just run the following (spoiler – there will be a database error!):

[billy@bob my_samples]$ node my-test.js 
Openning session
Openned session
Connected to cluster as node id: 50
onSession.
onInsert.
{ message: 'Error',
  sqlstate: '23000',
  ndb_error: null,
  cause: 
   { message: 'Foreign key constraint violated: No parent row found [255]',
     sqlstate: '23000',
     ndb_error: 
      { message: 'Foreign key constraint violated: No parent row found',
        handler_error_code: 151,
        code: 255,
        classification: 'ConstraintViolation',
        status: 'PermanentError' },
     cause: null } }

This error is seen because the code is attempting to add an object/row to the towns table where the county doesn’t already have an entry in the counties table – this breaks the Foreign Key constraint that was included when the tables were created.

To get around this, the required row should be added to the counties table:

[billy@bob my_samples]$ mysql --protocol=tcp -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.6.10-ndb-7.3.1-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> INSERT INTO test.counties VALUES ('Berkshire', 'England');
Query OK, 1 row affected (0.25 sec)

The code can then be executed again – this time successfully:

[billy@drbd1 my_samples]$ node my-test.js 
Openning session
Openned session
Connected to cluster as node id: 50
onSession.
onInsert.
Inserted: {"town":"Maidenhead","county":"Berkshire"}
onFind.
Found: {"county":"Berkshire","town":"Maidenhead"}

What next?

Please give this a try – especially with your own code – and let us know how you get on. We’re especially interested in what features you’d like to see added – embedded objects, lists, joins….