Tag Archives: ndb api

Install and Test the MYSQL Cluster NDB API

9 Jan

Here are the steps I followed to get the C++ NDB API example code working.

Configure API Node in MySQL Cluster

  • In this step, we configure an API Node for the MySQL Cluster.
  • Run ALL these commands on the instance running the Mgmt Node
    • First, Shutdown the Mgmt and Data Nodes.
      • ndb_mgm -e shutdown
    • Edit the config.ini file and add an entry for the API node
      • gedit /opt/mysqlcluster/deploy/conf/config.ini
        ...
        [api]
        ...
    • For now the entry with one line above is sufficient, which allows API nodes from any host to connect. In
      the future we can add hostname sub-entries for security purposes.
    • Restart the Management Node
      • ndb_mgmd -f /opt/mysqlcluster/deploy/conf/config.ini –initial –configdir=/opt/mysqlcluster/deploy/conf
    • Verify that the API node is active
      • ndb_mgm -e show
  • Login to EACH instance running the Data Node and restart them:
    • ndbd -c localhost:1186 OR ndbd -c domU-12-31-39-04-D6-A3.compute-1.internal:1186
  • Ensure that the MySQL Node is running
    • mysqld –defaults-file=/opt/mysqlcluster/deploy/conf/my.cnf –user=root &

Setup NDB API example directory

  • mkdir -p /opt/ndbapi/example
  • cd /opt/ndbapi/example
  • gedit example.cpp
  • Copy/Paste the following code to the C++ file example.cpp:
    • NOTE: IF RUNNING ON EC2 REPLACE CONNECTSTR below with hostname of the Management Node
      #include <stdio.h>
      #include <stdlib.h>
      
      #include <NdbApi.hpp>
      
      #define CONNECTSTR "localhost"
      
      Ndb_cluster_connection* example_init()
      {
        Ndb_cluster_connection* conn;
      
        // initialise MySQL and Ndb client libraries   if( ndb_init() )
        {
          exit(EXIT_FAILURE);
        }
      
        // prepare connection to cluster   conn = new Ndb_cluster_connection(CONNECTSTR);
      
        // initiate connection   if( conn->connect(4, 5, 1) )
        {
          fprintf(stderr, "Unable to connect to cluster within 30 seconds.\n");
          exit(EXIT_FAILURE);
        }
      
        // wait for data (ndbd) nodes   if(conn->wait_until_ready(30, 0) < 0)
        {
          fprintf(stderr, "Cluster was not ready within 30 seconds.\n");
          exit(EXIT_FAILURE);
        }
      
        return conn;
      }
      
      void example_end(Ndb_cluster_connection* conn)
      {
        // terminate connection   delete conn;
      
        // shut down MySQL and Ndb client libraries   ndb_end(2);
      }
      
      int main(int argc, char** argv)
      {
        Ndb_cluster_connection* conn;
      
        conn = example_init();
      
        printf("Connection established.\n");
      
        //example_end(conn); 
        return EXIT_SUCCESS;
      }
  • Note the #include <NdbApi.hpp> this is referring to a NDB API header file that all client programs must include.
  • The Ndb_cluster_connection class and other classes referred to in this example are available in either a static library (libndbclient.a) or
    a shared library (libndbclient.so)

Resolving NDB API dependencies

NDB API Library Dependencies

  • Compiling C++ programs that use the NDB APIs requires either the libndblclient.so (dynamic) or libndbclient.a (static) libraries.
  • Neither of these are available in the MySQL Cluster 7.2.1 Beta Binary download
    • There is a libndbclient_static.a file in the download. It’s not clear if this is a bug or intentional
[root@myhost ndb-bindings-0.7.1]# find / -name *ndbclient* -print

--- MYSQL CLUSTER 7.1.15 GA, BINARY DOWNLOAD
/opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.so
/opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.a
/opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.so.4.0.0
/opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.so.4
/opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.la

-- MYSQL CLUSTER 7.2.1 BETA, BINARY DOWNLOAD
/opt/mysqlcluster/home/mysql-cluster-gpl-7.2.1-linux2.6-i686/lib/libndbclient_static.a
  • To verify I compared the contents of 2 ndbclient libraries:
    • ar -vt /opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/lib/libndbclient.a > file1
    • ar -vt /opt/mysqlcluster/home/mysql-cluster-gpl-7.2.1-linux2.6-i686/lib/libndbclient_static.a > file2
    • diff file1 file2
      • There are numerous classes missing in the libndbclient_static.a version.

NDB API Header Files

  • Compiling C++ programs that use the NDB APIs require the NDB API Header Files
  • Again, the 7.2.1 BETA BINARY DOWNLOAD doesn’t have these header files
--- MYSQL CLUSTER 7.1.15 GA, BINARY DOWNLOAD
[root@myhost /]# ls /opt/mysqlcluster/home/mysql-cluster-gpl-7.1.15a-linux-i686-glibc23/include/storage/ndb/ndbapi/
NdbApi.hpp       ndb_cluster_connection.hpp  NdbError.hpp           NdbIndexOperation.hpp      NdbInterpretedCode.hpp  NdbPool.hpp      NdbScanFilter.hpp
ndbapi_limits.h  NdbDictionary.hpp           NdbEventOperation.hpp  NdbIndexScanOperation.hpp  NdbOperation.hpp        NdbRecAttr.hpp   NdbScanOperation.hpp
NdbBlob.hpp      ndberror.h                  Ndb.hpp                NdbIndexStat.hpp           ndb_opt_defaults.h      NdbReceiver.hpp  NdbTransaction.hpp

-- MYSQL CLUSTER 7.2.1 BETA, BINARY DOWNLOAD
[root@myhost /]# ls /opt/mysqlcluster/home/mysql-cluster-gpl-7.2.1-linux2.6-i686/include/storage/ndb/ndbapi/
ls: /opt/mysqlcluster/home/mysql-cluster-gpl-7.2.1-linux2.6-i686/include/storage/ndb/ndbapi/: No such file or directory

Resolving the Library/Header File Issues

  • To resolve the issues mentioned above we download the source code distribution of MySQL Cluster 7.2.1.
  • mkdir -p /opt/ndbapi
  • cd /opt/ndbapi
  • Download the Source code distribution:
  • tar xvf mysql-cluster-gpl-7.2.1.tar.gz
  • cd mysql-cluster-gpl-7.2.1
  • Install CMAKE
    • yum install cmake
    • Note: Previous releases of MySQL Cluster used the GNU Autotools (configure/make/make install). Starting this version cmake is used instead on configure.
  • Generate the Makefile, compile and install
  • cmake .
  • make
  • make install
    • This will install the MySQL Cluster software in /usr/local/mysql
  • Note that compiling the source also generated the libndbclient_static.a file but also generates libndbclient.so file in the src
    directory as shown below.
-- MYSQL CLUSTER 7.2.1 BETA, SOURCE DOWNLOAD, COMPILED and INSTALLED

/usr/local/mysql/lib/libndbclient_static.a

-- Sill No Header files in installed location
ls /usr/local/mysql/include/storage/ndb/ndbapi/
ls: /usr/local/mysql/include/storage/ndb/ndbapi/: No such file or directory

-- But shared library is generated and header files are in the source code tree

ls /opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/libndbclient.so
ls /opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/libndbclient_static.a

[root@myhost mysql-cluster-gpl-7.2.1]# ls /opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/ndbapi
NdbApi.hpp       ndb_cluster_connection.hpp  NdbError.hpp           NdbIndexOperation.hpp      NdbInterpretedCode.hpp  NdbPool.hpp      NdbScanFilter.hpp
ndbapi_limits.h  NdbDictionary.hpp           NdbEventOperation.hpp  NdbIndexScanOperation.hpp  NdbOperation.hpp        NdbRecAttr.hpp   NdbScanOperation.hpp
NdbBlob.hpp      ndberror.h                  Ndb.hpp                NdbIndexStat.hpp           ndb_opt_defaults.h      NdbReceiver.hpp  NdbTransaction.hpp

Compile example.cpp

  • In the scenarios below:
    • -I options points to the various header files required for compilation
    • -L options points to the directories that contain the various libraries required for compilation
      • There are 2 locations specified in the examples below. One contains the libndbclient library and the second one contains other MySQL libraries required for
        compilation.
    • -l options points to the various libraries that exist in the directories setup by -L

Scenario 1. Compile against libndbclient_static.a, FAILS

  • cd /opt/ndbapi/example
  • Run the following gcc command to compile
    gcc -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/include/  -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/ndbapi -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/mgmapi  example.cpp -o example  -L/usr/local/mysql/lib -L/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/ -lmysqlclient_r -lpthread -lm -lrt -ldl -lndbclient_static

Scenario 1. Compile against libndbclient.so, SUCCESS

  • cd /opt/ndbapi/example
  • Run the following gcc command to compile
    gcc -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/include/  -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/ndbapi -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/mgmapi  example.cpp -o example  -L/usr/local/mysql/lib -L/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/ -lmysqlclient_r -lpthread -lm -lrt -ldl -lndbclient

Copy libndbclient.so to standard location

  • Copy the shared object to a standard location so this is available both during compile and runtime
    • cp /opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/libndbclient.so /usr/lib

Test example.cpp

  • cd /opt/ndbapi/example
  • ./example
    • You should see the message “Connection established.”.

ndbapi_simple.cpp: Full Insert/Update/Delete/Read Example

  • The ndbap_simple.cpp code was copied from the MySQL Cluster API site at http://dev.mysql.com/doc/ndbapi/en/ndb-examples-synchronous-transactions.html
  • In this example:
    • A Database called ndb_examples is created
    • A Table called api_simple is created in that database with 2 columns ATTR1 and ATTR2 and ENGINE=NDB
    • Multiple Transactions are started each creating 10 rows.
    • Multiple Transactions are started each updating ATTR2
    • 1 Transaction is created which deletes ATTR1=3
    • Multiple Reads are performed on the api_simple table
  • cd /opt/ndbapi/example
  • gedit ndbapi_simple.cpp
  • Copy/Paste the following code
    /*
     *  ndbapi_simple.cpp: Using synchronous transactions in NDB API
     *
     *  Correct output from this program is:
     *
     *  ATTR1 ATTR2
     *    0    10
     *    1     1
     *    2    12
     *  Detected that deleted tuple doesn't exist!
     *    4    14
     *    5     5
     *    6    16
     *    7     7
     *    8    18
     *    9     9
     *
     */
    
    #include <mysql.h>
    #include <mysqld_error.h>
    #include <NdbApi.hpp>
    // Used for cout #include <stdio.h>
    #include <iostream>
    
    static void run_application(MYSQL &, Ndb_cluster_connection &);
    
    #define PRINT_ERROR(code,msg) \
      std::cout << "Error in " << __FILE__ << ", line: " << __LINE__ \
                << ", code: " << code \
                << ", msg: " << msg << "." << std::endl
    #define MYSQLERROR(mysql) { \
      PRINT_ERROR(mysql_errno(&mysql),mysql_error(&mysql)); \
      exit(-1); }
    #define APIERROR(error) { \
      PRINT_ERROR(error.code,error.message); \
      exit(-1); }
    
    int main(int argc, char** argv)
    {
      if (argc != 4)
      {
        std::cout << "Arguments are <mysqld hostname> <mysqld port> <mysql cluster connect_string >.\n";
        exit(-1);
      }
      // ndb_init must be called first   ndb_init();
    
      // connect to mysql server and cluster and run application   {
    
        char * mysqld  = argv[1];
        int mysqld_port = atoi(argv[2]);
        const char *connectstring = argv[3];
    
        // Object representing the cluster     Ndb_cluster_connection cluster_connection(connectstring);
    
        // Connect to cluster management server (ndb_mgmd)     if (cluster_connection.connect(4 /* retries               */,
           5 /* delay between retries */,
           1 /* verbose               */))
        {
          std::cout << "Cluster management server was not ready within 30 secs.\n";
          exit(-1);
        }
    
        // Optionally connect and wait for the storage nodes (ndbd's)     if (cluster_connection.wait_until_ready(30,0) < 0)
        {
          std::cout << "Cluster was not ready within 30 secs.\n";
          exit(-1);
        }
    
        // connect to mysql server     MYSQL mysql;
        if ( !mysql_init(&mysql) ) {
          std::cout << "mysql_init failed\n";
          exit(-1);
        }
        if ( !mysql_real_connect(&mysql, mysqld, "root", NULL, NULL, mysqld_port, NULL, 0) )
           MYSQLERROR(mysql);
    
        //if (mysql_real_connect(&mysql, "127.0.0.1", "root", NULL, NULL, 5000, NULL, 0) == NULL) {      // printf("Error %u: %s\n", mysql_errno(&mysql), mysql_error(&mysql));       //exit(1);     //} 
        // run the application code     run_application(mysql, cluster_connection);
      }
    
      ndb_end(0);
    
      return 0;
    }
    
    static void create_table(MYSQL &);
    static void do_insert(Ndb &);
    static void do_update(Ndb &);
    static void do_delete(Ndb &);
    static void do_read(Ndb &);
    
    static void run_application(MYSQL &mysql,
           Ndb_cluster_connection &cluster_connection)
    {
      /********************************************
       * Connect to database via mysql-c          *ndb_examples
       ********************************************/
      mysql_query(&mysql, "CREATE DATABASE ndb_examples");
      if (mysql_query(&mysql, "USE ndb_examples") != 0) MYSQLERROR(mysql);
      create_table(mysql);
    
      /********************************************
       * Connect to database via NdbApi           *
       ********************************************/
      // Object representing the database   Ndb myNdb( &cluster_connection, "ndb_examples" );
      if (myNdb.init()) APIERROR(myNdb.getNdbError());
    
      /*
       * Do different operations on database
       */
      do_insert(myNdb);
      do_update(myNdb);
      do_delete(myNdb);
      do_read(myNdb);
    }
    
    /*********************************************************
     * Create a table named api_simple if it does not exist *
     *********************************************************/
    static void create_table(MYSQL &mysql)
    {
      while (mysql_query(&mysql,
        "CREATE TABLE"
        " api_simple"
        " (ATTR1 INT UNSIGNED NOT NULL PRIMARY KEY,"
        " ATTR2 INT UNSIGNED NOT NULL)"
        " ENGINE=NDB"))
      {
        if (mysql_errno(&mysql) == ER_TABLE_EXISTS_ERROR)
        {
          std::cout << "MySQL Cluster already has example table: api_simple. "
          << "Dropping it..." << std::endl;
          mysql_query(&mysql, "DROP TABLE api_simple");
        }
        else MYSQLERROR(mysql);
      }
    }
    
    /**************************************************************************
     * Using 5 transactions, insert 10 tuples in table: (0,0),(1,1),...,(9,9) *
     **************************************************************************/
    static void do_insert(Ndb &myNdb)
    {
      const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
      const NdbDictionary::Table *myTable= myDict->getTable("api_simple");
    
      if (myTable == NULL)
        APIERROR(myDict->getNdbError());
    
      for (int i = 0; i < 5; i++) {
        NdbTransaction *myTransaction= myNdb.startTransaction();
        if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
    
        NdbOperation *myOperation= myTransaction->getNdbOperation(myTable);
        if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
    
        myOperation->insertTuple();
        myOperation->equal("ATTR1", i);
        myOperation->setValue("ATTR2", i);
    
        myOperation= myTransaction->getNdbOperation(myTable);
        if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
    
        myOperation->insertTuple();
        myOperation->equal("ATTR1", i+5);
        myOperation->setValue("ATTR2", i+5);
    
        if (myTransaction->execute( NdbTransaction::Commit ) == -1)
          APIERROR(myTransaction->getNdbError());
    
        myNdb.closeTransaction(myTransaction);
      }
    }
    
    /*****************************************************************
     * Update the second attribute in half of the tuples (adding 10) *
     *****************************************************************/
    static void do_update(Ndb &myNdb)
    {
      const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
      const NdbDictionary::Table *myTable= myDict->getTable("api_simple");
    
      if (myTable == NULL)
        APIERROR(myDict->getNdbError());
    
      for (int i = 0; i < 10; i+=2) {
        NdbTransaction *myTransaction= myNdb.startTransaction();
        if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
    
        NdbOperation *myOperation= myTransaction->getNdbOperation(myTable);
        if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
    
        myOperation->updateTuple();
        myOperation->equal( "ATTR1", i );
        myOperation->setValue( "ATTR2", i+10);
    
        if( myTransaction->execute( NdbTransaction::Commit ) == -1 )
          APIERROR(myTransaction->getNdbError());
    
        myNdb.closeTransaction(myTransaction);
      }
    }
    
    /*************************************************
     * Delete one tuple (the one with primary key 3) *
     *************************************************/
    static void do_delete(Ndb &myNdb)
    {
      const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
      const NdbDictionary::Table *myTable= myDict->getTable("api_simple");
    
      if (myTable == NULL)
        APIERROR(myDict->getNdbError());
    
      NdbTransaction *myTransaction= myNdb.startTransaction();
      if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
    
      NdbOperation *myOperation= myTransaction->getNdbOperation(myTable);
      if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
    
      myOperation->deleteTuple();
      myOperation->equal( "ATTR1", 3 );
    
      if (myTransaction->execute(NdbTransaction::Commit) == -1)
        APIERROR(myTransaction->getNdbError());
    
      myNdb.closeTransaction(myTransaction);
    }
    
    /*****************************
     * Read and print all tuples *
     *****************************/
    static void do_read(Ndb &myNdb)
    {
      const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
      const NdbDictionary::Table *myTable= myDict->getTable("api_simple");
    
      if (myTable == NULL)
        APIERROR(myDict->getNdbError());
    
      std::cout << "ATTR1 ATTR2" << std::endl;
    
      for (int i = 0; i < 10; i++) {
        NdbTransaction *myTransaction= myNdb.startTransaction();
        if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
    
        NdbOperation *myOperation= myTransaction->getNdbOperation(myTable);
        if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
    
        myOperation->readTuple(NdbOperation::LM_Read);
        myOperation->equal("ATTR1", i);
    
        NdbRecAttr *myRecAttr= myOperation->getValue("ATTR2", NULL);
        if (myRecAttr == NULL) APIERROR(myTransaction->getNdbError());
    
        if(myTransaction->execute( NdbTransaction::Commit ) == -1)
          APIERROR(myTransaction->getNdbError());
    
        if (myTransaction->getNdbError().classification == NdbError::NoDataFound)
          if (i == 3)
            std::cout << "Detected that deleted tuple doesn't exist!" << std::endl;
          else
     APIERROR(myTransaction->getNdbError());
    
        if (i != 3) {
          printf(" %2d %2d\n", i, myRecAttr->u_32_value());
        }
        myNdb.closeTransaction(myTransaction);
      }
    }
  • Compile the ndbapi_simple.cpp file
    gcc -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/include/  -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/ndbapi -I/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/include/mgmapi  ndbapi_simple.cpp -o ndbapi_simple  -L/usr/local/mysql/lib -L/opt/ndbapi/mysql-cluster-gpl-7.2.1/storage/ndb/src/ -lmysqlclient_r -lpthread -lm -lrt -ldl -lndbclient
  • Run the program
    • ./ndbapi_simple 127.0.0.1 5000 localhost
      • 127.0.0.1 is the IP address of the MySQL Node
      • 5000 is the port of the MySQL Node
      • localhost is the hostname of the Mgmt Node

More NDB API Examples

References

Advertisements