Geert JM Vanderkelen

SQLAchemy has support for MySQL Connector/Python for a while now. Here is a little HOWTO showing how install both, and setup a database engine.

There are multiple ways of installing both projects, but here is the simplest using pip, whatever platform you use:

shell> pip install SQLAlchemy
shell> pip install mysql-connector-python 

Start your SQLAlchemy engines using a URL pointing to Connector/Python. Note the connect_args argument which passes extra connection arguments to Connector/Python. In the following example we set the MySQL session variable time_zone to UTC:

from sqlalchemy import create_engine

DB_URI = "mysql+mysqlconnector://{user}:{password}@{host}:{port}/{db}"

engine = create_engine(DB_URI.format(
  user ='sakila',
  password = 'yoursecret',
  host = '127.0.0.1',
  db = 'test'),
  connect_args = {'time_zone': '+00:00'}
  )

That’s it. Now just continue with SQLAlchemy as usual.

It is possible with MySQL Connector/Python to define your own cursor classes. A very good use case is to return rows as dictionary instead of tuples. This post shows how to do this using MySQL Connector/Python v1.0 and is an update for an older blog entry.

In the example below we are subclassing the MySQLCursor class to create a new class called MySQLCursorDict. We change the _row_to_python() method to return a dictionary instead of a tuple. The keys of the dictionary will be (unicode) column names.

from pprint import pprint
import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

cnx = mysql.connector.connect(user='root', database='test')
cur = cnx.cursor(cursor_class=MySQLCursorDict)
cur.execute("SELECT c1, c2 FROM t1")
rows = cur.fetchall()
pprint(rows)
cur.close()
cnx.close()

The output of the above script would be (formatted):

[
 {u'c1': 1,
  u'c2': 10},
 {u'c1': 2,
  u'c2': 20}
]

Depending on your needs, you can subclass from any class found in the mysql.connector.cursor module, but note that you will need to change some other methods to make it work.

This post explains how to debug MySQL Cluster 7.1, installed using the RPM packages, using gdb on a Linux box (Red Hat, Oracle Enterprise Linux, CentOS, ..).

When a data node crashes lots of information goes into the error log, trace files and out log. However, it makes sometimes sense when you can repeat the crash, to run the data node in debug mode, or using gdb.

First, using RPMs and a Linux distribution, make sure you have the ‘debuginfo’ package installed. For example, for Red Hat or Oracle Enterprise Linux on a 64-bit machine, this package would be called: MySQL-Cluster-gpl-debuginfo-7.1.15-1.rhel5.x86_64.rpm .

Create a file with the following commands, we will name it ‘ndbd.gdb’:

set pagination off
set logging overwrite on
set logging file ndbd_gdb_backtrace.txt
set logging on
run --foreground -c <YourMGM:1186> --ndb-nodeid=<YourID>
thread apply all bt
set logging off

Note line 5: pass the options to ‘run’ which you usually pass when starting ndbd, but leave the –foreground option.
Note line 3: you can save of course the logging file wherever you want to.

Then all you need to do is run gdb with the commands file and the ndbd binary you just created:

shell> gdb /usr/sbin/ndbd -x ndbd.gdb

A full backtrace of threads will be available in the logging file when ndbd crashes. This you can then upload when reporting bugs.

There are probably more options and things you can do, please post them in the comments!

There is a log file called ndb_<NodeID>_out.log created by the MySQL Cluster data nodes which can become quite big overtime. There is, unlike the cluster logs created by the management nodes, no rotation build in. So you have to revert to the basics and copy the file away, truncating the old one.

For example, if you want to ‘rotate’ the log file of data node with NodeID 3:

shell> mv ndb_3_out.log.1.gz ndb_3_out.log.2.gz
shell> cp ndb_3_out.log ndb_3_out.log.1
shell> cat /dev/null > ndb_3_out.log
shell> gzip ndb_3_out.log.1

It’s not elegant, and you might lose some entries, but it will help you keeping disk usage minimal. If you don’t need the log at all, just line 3 would do the trick.

You can use logrotate‘s copytruncate to achieve something similar.

This is a reply to the blog post Poor man’s MySQL replication monitoring. Haidong Ji had a few problems using MySQLdb (could use the ‘dict’ cursor) and apparently he doesn’t want to much dependencies. I agree that using the mysql client tool is a nice alternative if you don’t want to use any 3rd party Python modules. And the MySQL client tools are usually and should be installed with the server.

However, since MySQL Connector/Python only needs itself and Python, dependencies are reduced to a minimum. Here you’ll find a refactored version of Haidong’s version (can of course be made much more sophisticated) using the connector:

import sys
from socket import gethostname
import smtplib
import mysql.connector

emailSubject = "Replication problem on slave %s"
emailTo = "recipient@example.com"
emailFrom = "monitor-tool@example.com"

def runCmd(cmd):
    cnx = mysql.connector.connect(user='root',
                                  unix_socket='/path/to/mysql.sock')
    cur = cnx.cursor(buffered=True)
    cur.execute(cmd)
    columns = tuple( [d[0].decode('utf8') for d in cur.description] )
    row = cur.fetchone()
    if row is None:
        raise StandardError("MySQL Server not configured as Slave")
    result = dict(zip(columns, row))
    cur.close()
    cnx.close()
    return result

try:
    slave_status = runCmd("SHOW SLAVE STATUS")
except mysql.connector.Error, e:
    print >> sys.stderr, "There was a MySQL error:", e
    sys.exit(1)
except StandardError, e:
    print >> sys.stderr, "There was an error:", e
    sys.exit(1)
    
if (slave_status['Slave_IO_Running'] == 'Yes' and
    slave_status['Slave_SQL_Running'] == 'Yes' and
    slave_status['Last_Errno'] == 0):
    print "Cool"
else:
    emailBody = [
        "From: %s" % emailFrom,
        "To: %s" % emailTo,
        "Subject: %s" % (emailSubject %  gethostname()),
        "",
        '\n'.join([ k + ' : ' + str(v) for k,v in slave_status.iteritems()]),
        "\r\n",
        ]
    server = smtplib.SMTP("localhost")
    server.sendmail(emailFrom, [emailTo], '\r\n'.join(emailBody))
    server.quit()

The MySQL Cluster data node log files can become very big. The best solution is to actually fix the underlying problem. But if you know what you are doing, you can work around it and filter out these annoying log entries.

An example of ‘annoying’ entries is when you run MySQL Cluster on virtual machines (not good!) and disks and OS can’t follow any more; a few lines from the ndb_X_out.log:

2011-04-03 10:52:31 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Scanning Timers elapsed=100
2011-04-03 10:52:31 [ndbd] INFO     -- timerHandlingLab now: 1301820751642 sent: 1301820751395 diff: 247
2011-04-03 10:52:31 [ndbd] INFO     -- Watchdog: User time: 296  System time: 536
2011-04-03 10:52:31 [ndbd] INFO     -- Watchdog: User time: 296  System time: 536
2011-04-03 10:52:31 [ndbd] WARNING  -- Watchdog: Warning overslept 276 ms, expected 100 ms.
2011-04-03 10:53:33 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Performing Receive elapsed=100
2011-04-03 10:53:33 [ndbd] INFO     -- Watchdog: User time: 314  System time: 571
2011-04-03 10:53:33 [ndbd] INFO     -- timerHandlingLab now: 1301820813839 sent: 1301820813476 diff: 363
2011-04-03 10:53:33 [ndbd] INFO     -- Watchdog: User time: 314  System time: 571

You can’t set the log levels like you would do for the cluster logs produced by the management node. However, you can run the data nodes so they put messages to STDOUT and redirect it to a script:

ndbd --nodaemon 2>&1 | ndbd_logger.py /var/log/ndb_3_out.log &

And here’s the ndbd_logger.py script filtering out the ‘annoying’ messages. Extra candy: it fixes lines which do not have a timestamp!

import sys
import os
import socket
from time import strftime

FILTERED = (
  'Watchdog',
  'timerHandlingLab',
  'time to complete',
)      

def main():
  try:
    log_file = sys.argv[1]
  except IndexError:
    print "Need location for log file (preferable absolute path)"
    sys.exit(1)
  
  try:
    fp = open(log_file,'ab')
  except IOError, e:
    print "Failed openeing file: %s" % e
    sys.exit(2)
    
  while True:
    line = sys.stdin.readline().strip()
    if line == '':
      break
    for f in FILTERED:
      if line.find(f) > -1:
        continue
    if line.find('[ndbd]') == -1:
      line = strftime('%Y-%m-%d %H:%M:%S [ndbd] NA       -- ') + line
    fp.write(line + '\n')
    fp.flush()
  fp.write(strftime('%Y-%m-%d %H:%M:%S Closing log\n'))
  fp.close()

if __name__ == '__main__':
  main()

The above script can definitely be improved, but it shows the basics. I particularly like the timestamp fixing.

This is a placeholder for the actual post: VirtualBox: give Host-Only access to internet. The post deserved two titles.

This post describes a simple trick for VirtualBox to give host-only virtual machines access the internet or access your host from the guest using NAT. The title could also have been: “Easily access your NAT configured virtual machine.”

The original problem or requirements:

• The host should be able to browser the webserver on the Guest (the virtual machine)
• The guest should be able to access the outside world (intranet/internet)
• Use fixed IP address, no problems with DHCP et al.

Yes, you can use port-forwarding using NAT network interface for the virtual machine. But configuring this is just way to complicate and error prone. A few googles more, and I couldn’t find anything good.

The solution I came up with: configure 2 network interfaces for your Virtual Box host. Simple, isn’t it?

First, we need to make a new network in VirtualBox. You can do this in the GUI preference, which is a bit platform specific, but it’s not easy to miss.

Create a new network in VirtualBox, named like vboxnet2 with following settings (change to suit your needs):

• IPv4 Address: 10.88.12.0
• IPv4 Network Mask: 255.0.0.0
• Leave DHCP disabled if you don’t need it. Fixed IP address are just more predictable

Setup your virtual machine using 2 network adapters. The order doesn’t really matter, but you’ll need one NAT and one Host-Only:

1. NAT (not much to configure)
2. Host-Only using the network you created earlier, for example, vboxnet2

Boot your virtual machine, and configure the network interfaces in your guest OS (same order as previous point):

1. First interface using NAT, which you can use with the internal DHCP
2. Second interface using Host-Only will use a fixed IP address and netmask which you configured earlier for the vboxnet2 VirtualBox network. For example, 10.88.12.4.

Guest has access to outside, and host can access services running on guest. This is handy when you have a demo or develop using a service running in the virtual machine, and the virtual machine needs to access the outside world.

(If the above has been posted somewhere else: I was either blind, or it was not visible or indexed enough.)

Setting client flags with MySQL Connector/Python works a bit differently than the other MySQL Python drivers. This blog post describes how to set and unset flags, like the CLIENT_FOUND_ROWS.

The default client flags for the MySQL Client/Server protocol can be retrieved using the constants.ClientFlag class:

>>> from mysql.connector.constants import ClientFlag
>>> defaults = ClientFlag.get_default()
>>> print ClientFlag.get_bit_info(defaults)
['SECURE_CONNECTION', 'TRANSACTIONS', 'CONNECT_WITH_DB',
 'PROTOCOL_41', 'LONG_FLAG', 'MULTI_RESULTS',
 'MULTI_STATEMENTS', 'LONG_PASSWD']

To set an extra flag when connecting to MySQL you use the client_flags argument of connect()-method. For example, you’d like to have the CLIENT_FOUND_ROWS set:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS]
cnx = mysql.connector.connect(client_flags=extra_flags)

Similar, you can unset a flag passing a list of negative values, or all at the same time. For example, you’d like the CLIENT_FOUND_ROWS set, but you don’t want CLIENT_MULTI_STATEMENTS:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS, -ClientFlag.MULTI_STATEMENTS]
cnx = mysql.connector.connect(client_flags=extra_flags)

It is also possible to pass the client_flags an integer, but you need to get first the defaults, and do bitwise operations to set/unset flags yourself. At the moment of writing, there is a bug about this, see lp:695514, but I recommend using the list-method.

This blog post shows how to create a cursor class for MySQL Connector/Python which will allow you to cache queries. It will hold the query itself and the result in a global variable.

Note: this is a proof of concept and is only meant as a demonstration on how to extend MySQL Connector/Python.

Why query caching?

You are doing lots of queries that have the same result. It would be expensive to always run the same exact query. MySQL has already a query cache, and there is also memcached. But you like MySQL Connector/Python so much you’d like to do it yourself.

A cursor caching queries and their result

To demonstrate a simple implementation of a query cache, we inherit from an existing class: MySQLCursorBuffered. It will save the executed operation with their results in a ‘global’ variable. We call this cursor MySQLCursorQueryCache.

We take the buffered cursor because we’d like to save the result right away. Below you see we only changed two methods for MySQLCursorBuffered:

• .execute(): it will now first check using an md5 checksum whether we executed the query before. If we did, we set the make the cached result active. If not, we simply executed.
• ._handle_resultset(): called when .execute() did an operation which has a result set. The result we know save in the QUERY_CACHE global dict.

from hashlib import md5
import mysql.connector

QUERY_CACHE = dict()

class MySQLCursorQueryCache(mysql.connector.cursor.MySQLCursorBuffered):
    def execute(self, operation, params=None):
        self._qmd5 = md5(operation).digest()
        if QUERY_CACHE.has_key(self._qmd5):
            (self._rows, eof) = QUERY_CACHE[self._qmd5]
            self.rowcount = len(self._rows)
            self._handle_eof(eof)
            self._next_row = 0
        else:
            super(MySQLCursorQCache, self).execute(operation, params)
    
    def _handle_resultset(self):
        (self._rows, eof) = self.db().protocol.get_rows()
        self.rowcount = len(self._rows)
        self._handle_eof(eof)
        self._next_row = 0
        QUERY_CACHE[self._qmd5] = (self._rows, eof)
        try:
            self.db().unread_result = False
        except:
            pass
        self._qmd5 = None

The above code is a proof of concept, there is lots of room for improvement. For example, you need something to invalidate entries in the query cache.

How to use MySQLCursorQueryCache

def main():
    
    cnx = mysql.connector.connect(database='test')
    cur = cnx.cursor(cursor_class=MySQLCursorQueryCache)
    
    cur.execute("SELECT NOW()")
    print cur.fetchone()
    time.sleep(2)
    cur.execute("SELECT NOW()")
    print cur.fetchone()
    
    cur.close()
    cnx.close()

When you would use the default cursor, both executed SQL statements would produce a different result. The above produces the following output:

(datetime.datetime(2010, 11, 22, 21, 20, 4),)
(datetime.datetime(2010, 11, 22, 21, 20, 4),)

When you’d like to have some statements cached, and some not, just create a second cursor cursor_class=MySQLCursorBuffered (see Buffering results with MySQL Connector/Python).