Geert JM Vanderkelen

There have been some request to have some reconnect possibilities in Connector/Python. I’m wondering now whether there should be some automatic reconnect on certain errors within the database driver.

My personal feeling is to have no automatic reconnect within Connector/Python and the programmer has to come up with retrying transactions herself.

For example:

	cnx.disconnect() # For testing..
	tries = 2
	while tries > 0:
		tries -= 1
		try:
			cursor.execute("INSERT INTO t1 (c1) VALUES ('ham')")
			cnx.commit()
		except mysql.connector.InterfaceError:
			if tries == 0:
				print "Failed inserting data after retrying"
				break
			else:
				print "Reconnecting.."
				cnx.reconnect()
		else:
			break

The above mimics how you would handle transactions and trying them reconnecting. I have ideas how to get this into Connector/Python, but it would not really fit PEP-249.

Would the above use case of reconnecting be enough?

Today we registered MySQL Connector/Python with the Python Package Index (PyPI). It makes installing your favorite connector even easier (provided you first install setuptools or pip):

shell> easy_install mysql-connector
shell> pip install mysql-connector

Please report problems either using Launchpad or MySQL Bugs website.

In addition to reporting MySQL Connector/Python bugs on Launchpad, it is now also possible to enter them using http://bugs.mysql.com.

After more than 6 years doing MySQL Support for MySQL AB, Sun Microsystems, and Oracle, it’s time for a change. Time to get back to development!

As of November 2011 I’ll be working full-time on MySQL Connector/Python and other goodies within the MySQL development team at Oracle. Before, this was more or less a pet project done after working hours. However, with the birth of our son Tomas more than a year ago, I’ve been slacking and family got priority.

The idea is to make MySQL Connector/Python the best choice for connecting to MySQL from within your Python code. We still got a long road ahead of us, but I’m confident that we are on the right track.

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.

I was young. I had long(er) hair. The only thing that didn’t change was my goatee.. Thanks to Sander, a friend in Belgium, we recovered the older logo.

1998: The original fails, I must have it on some disks. I think I made this while in Le Mans (France), using some program on Windows. Note that ‘kemuri‘ is Japanese for ‘smoke’, so the S somehow weaves along the K.

2001: Or was it 2002.. Revamped the logo so it was easier to print on paper. You know, making invoices. Not sure where I got the idea from, but maybe it was from VA Linux logo (had a rack mountable server from them). I’m sure this one was made in a basement in Brussels.

A collection of thoughts, quotes, jokes, .. which I posted as status updates on Facebook. My favorites are bold (no pun intended).

They say sucking is a newborn instinct we forget; some people, however, keep sucking their whole lives.

Love has the symbol ♡; what symbol would hate have?

Putting MySQL Cluster on virtual machines and expect it to behave.

If only my wallet was as big as my heart..

If you dwell long enough in shit, you forget it stinks.

A System that is not Intuitive is not a System.

Sometimes it’s better to wait for the fire to see how to actually extinguish it. Standing ready with a bucket of water, just to find out that the flames come out of a frying pan is not productive.

What’s a Miss eating a steak? .. A mistake!

What’s a t-shirt with an issue? .. A tissue!

I am connected with my wife. Not through Facebook, but in real life.

Humans are wireless fetuses.

The MySQL Community dissolved into an Ecosystem.

A fart is like thunder: you hear it first, you smell it later.

So much websites, so little information.

(If you are crazy enough to use one of the above quotes: you can, but let me know where you used it and of course, credit me. Oh, I’m so vain!)