Are Stored Procedures available with MySQL Cluster?

Stored procedures are implemented in a MySQL Server and can be used regardless of the storage engine being used for a specific table. One inference from this is that they won’t work when accessing the Cluster database directly through the NDB API.

This leads to the question of whether or not that limitation actually restricts what you can achieve. This article gives a brief introduction to stored procedures and looks at how the same results can be achieved using the NDB API.

Stored procedures provide a rudimentary way of implementing functionality within the database (rather than in the application code). They are implemented by the database designer and have the ability to perform computations as well as make changes to the data in the database. A typical use of stored procedures would be to control all access to the data by a user or application – for example, to impose extra checks on the data or make sure that all linked data is updated rather than leaving it to the user or application designer to always remember to do it. To impose this, the DBA could grant permission to users to call the stored procedures but not write to the tables directly.

This functionality can be very useful when the data is being accessed through the SQL interface. If using the NDB API then you have the full power of the C++ language at your disposal and so a designer can code whatever checks and side effects are needed within a wrapper method and then have applications use those methods rather than accessing the raw NDB API directly for those changes.

There is one piece of functionality available using stored procedures which could be very helpful to applications using the NDB API – triggers. The rest of this article explains what triggers are; how they’re used and how that same results can be achieved using the NDB API.

Triggers

Triggers allow stored code to be invoked as a side effect of SQL commands being executed on the database through a MySQL Server. The database designer can implement a stored procedure and then register it to be invoked when specific actions (INSERT, DELETE etc.) are performed on a table.

The following example shows how a simple stored procedure can be implemented and then registered against a table.

The stored procedure in this example is triggered whenever a new tuple is inserted into the ASSETS table. The procedure then inserts the asset’s name into the AUDIT_LOG table. If the tuple is deleted from the ASSETS table then the entry in the AUDIT_LOG table remains intact.

The following screen capture shows the results when adding a tuple to the table that contains the trigger.

Note that as the trigger and stored procedure are implemented in the MySQL Server, they need to be separately defined in all of the MySQL Server instances where they are needed.

The following NDB API code adds a new tuple to the ASSETS table in much the same way as was done through SQL above (Note: my C++ is very rusty and so there will be glitches in this code – especially for string handling).

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

<span style="color: #800000;">#include <NdbApi.hpp>

#include <stdio.h>

#include <string.h>

#include <iostream>

#include <cstdlib>

staticvoidrun_application(Ndb_cluster_connection&,char*);

#define PRINT_ERROR(code,msg)

std::cout<<"Error in "<<__FILE__<<", line: "<<__LINE__

<<", code: "<<code

<<", msg: "<<msg<<"."<<std::endl

#define APIERROR(error) {

PRINT_ERROR(error.code,error.message);

exit(-1);}

intmain(intargc,char**argv)

{

if(argc!=3)

{

std::cout<<"Arguments are <connect_string cluster><asset_name>.n";

exit(-1);

}

ndb_init();

// connect to cluster and run application

{

constchar*connectstring=argv[1];

char*asset_name=argv[2];

// 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);

}

// 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);

}

// run the application code

run_application(cluster_connection,asset_name);

}

ndb_end(0);

return0;

}

staticvoiddo_insert(Ndb&,char*);

staticvoidrun_application(Ndb_cluster_connection&cluster_connection,

char*asset_name)

{

/********************************************

* Connect to database via NdbApi *

********************************************/

// Object representing the database

Ndb myNdb(&cluster_connection,"test");

if(myNdb.init())APIERROR(myNdb.getNdbError());

do_insert(myNdb,asset_name);

}

staticvoiddo_insert(Ndb&myNdb,char*asset_name)

{

constNdbDictionary::Dictionary*myDict=myNdb.getDictionary();

constNdbDictionary::Table *myTable=myDict->getTable("ASSETS");

charstr[20];

str[0]=strlen(asset_name);

strcpy(str+1,asset_name);

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->insertTuple();

myOperation->setValue("NAME",str);

myOperation->setValue("VALUE",555);

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

APIERROR(myTransaction->getNdbError());

myNdb.closeTransaction(myTransaction);

}

</span>

This code can then be executed and then the effects verified using SQL commands through the MySQL Server – note that the stored procedure has not been triggered and so the name has not been copied into the AUDIT_LOG table.

It could easily be argued that triggers are not required when using the NDB API – simply code a wrapper method that also applies the required side effects. However, it is possible to come up with scenarios where triggers would be much more convenient – for example if the application is already littered with accesses to the data and you want to retrofit the side effect.

Fortunately, the NDB API includes the ability to register triggers against operations for a specific table. The code that follows implements a process that waits for an INSERT to be performed on the ASSETS table and then creates an entry in the AUDIT_LOG table just as the earlier stored procedure did.

A major advantage of this approach is that the trigger is implemented within the Cluster database and so is invoked regardless of where the INSERT is requested – whether it be through the NDB API or through any of the MySQL Servers. This is shown in the results that follow.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

<span style="color: #800000;">mysql>drop trigger ins_asset;

Query OK,0rows affected(0.00sec)

mysql>drop procedure log_it;

Query OK,0rows affected(0.00sec)

mysql>insert into ASSETS values("Printers",200);

Query OK,1row affected(0.00sec)

mysql>select *from ASSETS;

+----------+-------+

|NAME|VALUE|

+----------+-------+

|Keyboard|555|

|Computer|350|

|Monitor|555|

|Printers|200|

+----------+-------+

4rows inset(0.00sec)</span>

<span style="color: #800000;">mysql>select *from AUDIT_LOG;

+-----------+

|NOTE|

+-----------+

|Printers|

|Keyboard|

|Computer|

+-----------+

4rows inset(0.00sec)</span>

Note that I first removed the original trigger and stored procedure that were defined in the MySQL Server.

There is another key difference between MySQL triggers and NDB events – triggers are executed as part of the MySQL transaction making the main database change whereas NDB events happen asynchronously. The effect of this is:

The original transaction will commit succesfully before the side effects have been processed

If the process waiting for the event disappears then the side effect will not be processed – for this reson, you may want to consider an audit/clean-up scripts to cover these cases.

Conclusion

Stored procedures are fully supported for users or applications which access a Cluster database through a MySQL Server (whether directly using SQL or through any of the numerous connectors that are available). Applications which access the database through the NDB API have the full flexibility of C++ to implement functionality that can achieve the same results. Triggers are available whichever method is used to access the database – albeit with different implementations and slightly different functionality.