Error Logging for CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

We will learn to set up error logging for CockroachDB to increase efficiency in tracking bugs, responding to user issues, and maintaining applications. This is how this lesson will go:

  • We will learn how to store trapped errors by creating a Cockroach table for error logging, use Python to save the relevant error parameters to that CockroachDB table, and even send an email to the database administrator (dba) to let the admin know an error occurred.
  • We will use try, except, execute, and concatenation from Python and CREATE TABLE and INSERT from Cockroach, including a few other features from both technologies.

Prerequisites

  • SQL: Novice-to-beginner understanding of writing Cockroach SQL, using the dBeaver tool or other database management tool.
  • INSERT: Executing the “INSERT” SQL command to add a record of data to Cockroach via a Python application.

What is error trapping?

Error checking or “trapping” is where you build “check and balance” into your applications at key points where you guess errors may occur. Including error trapping in your Python-Cockroach application has the following benefits:

  • Increased elegance/efficiency and trust for/from the user. Rather than an application just freezing or causing other unpredictable or even scary issues, you can gracefully handle the error and/or inform the user what has happened.
  • Gather data that could be important for debugging.
  • Have useful information to help with determining responsibility for the error if more than one user, admin, tester, or dba is involved in application creation and/or testing.

Syntax

1
2
3
4
5
6
try:
    # Code here to test for errors.
except psycopg2.Error as txtErrorMsg:
    # Action to take if an error happened.
else:
    # action to take if NO error occurred.

Analysis

  • try: This is where we TRY executing some Python scripts for the interpreter to assess.
  • except: If there was an error, the error message is returned. Here we tell it to place that error message into the “txtErrorMsg” variable.
  • else: What to do if no error occurred.

Now to increase clarity by plugging some real scripts into the syntax we used above:

1
2
3
4
5
6
try:
    cursorCRDB.execute(txtSQL)
except psycopg2.Error as txtErrorMsg:
    print("Error! Decodeion: " + s + " SQL tried: " + txtSQL)
else:
    print("No error! Smooth sailing!")

Create Cockroach table for logging

The second part of this four piece puzzle is logging the error with Python to a CockroachDB database. We will start with creating a table we will use to store our error log.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE public.tblLogErrors (
    id serial NOT NULL,
    idSession int4 NULL DEFAULT 0,
    idUser int4 NULL DEFAULT 0,
    txtErrorMsg VARCHAR(4096) NULL,
    txtSQLtried VARCHAR(4096) NULL,
    txtNotes VARCHAR(4096) NULL,
    boolEmailSent bool NULL DEFAULT FALSE,
    dateWhen DATE NULL DEFAULT now(),
    CONSTRAINT tblLogErrors_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tblLogErrors_id_idx ON public.tblLogErrors USING btree (id);

Note: After creating the table above, if the default value for the id column wasn’t set to “nextval(‘tblLogErrors_id_seq’::regclass)” then copy this value in quotes (without the quotes) into that field in your CockroachDB database. Why do this? We are initializing that column to be an auto-incrementing and indexed column so that every time you insert a record, the “id” column will increase by one and always be a unique index for the “tblLogErrors” table.

Next we build the SQL needed to insert data into our new “tblLogErrors” table:

SQL for error logging

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
s = ""
s += "INSERT INTO tblLogErrors"
s += "("
s += "idUser"
s += ", txtErrorMsg"
s += ", txtSQLtried"
s += ", txtNotes"
s += ", boolEmailSent"
s += ") VALUES ("
s += "(%idUser)"
s += ", '(%txtErrorMsg)'"
s += ", '(%txtSQLtried)'"
s += ", '(%txtNotes)'"
s += ", (%boolEmailSent)"
s += ", {
s += "
'idUser': idUser"
s += "
, 'txtErrorMsg': txtErrorMsg"
s += "
,'txtSQLtried': txtSQLtried"
s += "
,'txtNotes': txtNotes"
s += "
,'boolEmailSent': boolEmailSent"
s += "
}"
s += "
)"
cursorCRDB.execute(s)

Analysis: As you can see, we have used a parameterized SQL code to INSERT a record into the table we’ve named “tblLogErrors”. For that record, we are sending five values: “idUser”, “txtErrorMsg”, “txtSQLtried”, “txtNotes”, and “boolEmailSent”. The names of these columns should explain their purpose.

The final functionality we want is to send an email to the dba so he knows an error occurred.

Email to admin

We begin by initializing a Mail object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
    EMAIL_SERVER = myConfigData['EMAIL_SERVER']
    EMAIL_PORT = myConfigData['EMAIL_PORT']
    EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]
    mail = Mail()

The above scripts serves to create and set up a mail object called “mail” that we will make use of below to send out emails.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    s = ""
    s += "Dear DB Admin<br>"
    s += "<br>"
    s += "An exception occurred in your application." + "<br>"
    s += "Potentially relevant information:<br>"
    s += "User ID: " + idUser + "<br>"
    s += "Error Message: " + txtErrorMsg  + "<br>"
    s += "The SQL: " + txtSQLtried  + "<br>"
    s += "Notes: " + txtNotes  + "<br>"
    s += "<br>"
    msg = Message(
        body = s,
        subject = "Application Exception",
        recipients = txtEmailAddr_admin,
        sender = txtSender,
        reply_to = txtSender
        )
    mail.send(msg)

The next part will send an email to the database administrator. Here is the detailed analysis:

  • msg = Message: Plug in all our variables in order to create a Flask email message object.
  • mail.send: Send the message object we built above.

Now that we have shown the most complex parts required to build an error logging application, it’s time to examine a full listing of our source scripts:

Trap and log errors in CockroachDB

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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
from flask import Flask
import psycopg2
from flask_mail import Mail
from flask_mail import Message
from flask import render_template

app = Flask(__name__)
@app.route("/")

# database credentials
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "56238"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

@app.route("/")

def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
        EMAIL_SERVER = myConfigData['EMAIL_SERVER']
        EMAIL_PORT = myConfigData['EMAIL_PORT']
        EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]

def vars():
    idUser = 1
    txtErrorMsg = ""
    txtSQLtried = ""
    txtNotes = ""
    boolEmailSent = False

def main():
    s = ""
    s += "INSERT INTO tblLogErrors"
    s += "("
    s += "idUser"
    s += ", txtErrorMsg"
    s += ", txtSQLtried"
    s += ", txtNotes"
    s += ", boolEmailSent"
    s += ") VALUES ("
    s += "(%idUser)"
    s += ", '(%txtErrorMsg)'"
    s += ", '(%txtSQLtried)'"
    s += ", '(%txtNotes)'"
    s += ", (%boolEmailSent)"
    s += ", {
    s += "
'idUser': idUser"
    s += "
, 'txtErrorMsg': txtErrorMsg"
    s += "
,'txtSQLtried': txtSQLtried"
    s += "
,'txtNotes': txtNotes"
    s += "
,'boolEmailSent': boolEmailSent"
    s += "
}"
    s += "
)"
    try:
        cursorCRDB.execute(s)
    except psycopg2.Error as e:
        txtMessage = "
Cockroach Database error: " + e + "/n SQL: " + s
        smtp_config("
config_01")
        txtSender = "
db_admin@500rockets.com"
        mail = Mail()
        # build the email message
        s = "
"
        s += "
Dear DB Admin<br>"
        s += "
<br>"
        s += "
An exception occurred in your application." + "<br>"
        s += "
Potentially relevant information:<br>"
        s += "
User ID: " + idUser + "<br>"
        s += "
Error Message: " + txtErrorMsg  + "<br>"
        s += "
The SQL: " + txtSQLtried  + "<br>"
        s += "
Notes: " + txtNotes  + "<br>"
        s += "
<br>"
        # send the email
        msg = Message(
            body = s,
            subject = "
Application Error",
            recipients = txtEmailAddr_admin,
            sender = txtSender,
            reply_to = txtSender
            )
        mail.send(msg)
        # show error page
        return render_template("
exception.html", txtMessage = txtMessage)

    cursorCRDB.close()

# For command line testing:
if __name__ == "
__main__":
    app.run(debug = True)

One thing to note about the application above: It’s really a response to errors more than showing the original error get trapped that would supplied us with the id of the user who set off the exception, the SQL script that set the error off, it it was that, etc. We build our error logging table so that it could handle most kinds of circumstances that might come up, including ones that don’t involve SQL going awry. But yes, WITHIN our application created to deal with errors, we do have one example of error trapping you can learn about.

Conclusion

In this tutorial document we showed how to build an application for error logging in Python and Cockroach to check for errors, insert a record into a table, and send the admin an email with the details known about the error. Part of that process involved using some other Python and CockroachDB functions and features, including but not limited to CREATE TABLE, INSERT, string concatenation, execute, and render_template. Finally, we shared all the Python source scripts for this project.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.