Fixing MySQL Bug#2: now MySQL makes toast!

April 1, 2016

Author

Alexander Rubin

MySQL

Share this Post:

Historical MySQL Bug#2, opened 12 Sep 2002, states that MySQL Connector/J doesn’t make toast. It hasn’t been fixed for more than 14 years. I’ve finally created a patch for it.

First of all: why only fix this for MySQL Connector/J? We should make sure the server can do this for any implementation! With this fix, now MySQL server (starting with version 5.1) can make toast.

There are few dependences though (see assembled setup picture):

Raspberry Pi + PiFace shield

Power switch relay (I’ve used an IoT Power Relay)

Toaster oven (any cheap mechanical model will work)

Patch:

Make_toast binary, which is run on the Raspberry Pi and PiFace interface (you’ll need to install the PiFace library):

#!/usr/bin/python<br>import sys<br>from time import sleep<br><br>if len(sys.argv) == 2 and sys.argv[1].isdigit():<br>        toast_time = sys.argv[1]<br>else:<br>        toast_time = 10<br><br>print "Toasting for " + str(toast_time) + " seconds..."<br><br>import pifacedigitalio as p<br>try:<br>	p.init()<br>	p.digital_write(7,1)<br>	sleep(float(toast_time))<br>	p.digital_write(7,0)<br>except (KeyboardInterrupt, SystemExit):<br>        print "Exiting and turning off heat..."<br>	p.digital_write(7,0)<br>        sys.exit(1)<br><br>print "Your toast is ready! Enjoy! "

1

#!/usr/bin/python import sys from time import sleep if len(sys.argv) == 2 and sys.argv[1].isdigit(): toast_time = sys.argv[1] else: toast_time = 10 print "Toasting for " + str(toast_time) + " seconds..." import pifacedigitalio as p try: p.init() p.digital_write(7,1) sleep(float(toast_time)) p.digital_write(7,0) except (KeyboardInterrupt, SystemExit): print "Exiting and turning off heat..." p.digital_write(7,0) sys.exit(1) print "Your toast is ready! Enjoy! "

MySQL UDF, based on lib_mysqludf_sys, which calls the make_toast binary:

char* make_toast(<br>        UDF_INIT *initid<br>,       UDF_ARGS *args<br>,       char* result<br>,       unsigned long* length<br>,       char *is_null<br>,       char *error<br>){<br>        FILE *pipe;<br>        char line[1024];<br>        unsigned long outlen, linelen;<br>        char buf[40];<br><br>        result = malloc(1);<br>        outlen = 0;<br>        sprintf(buf, "make_toast %s", args->args[0]);<br><br>        pipe = popen(buf, "r");<br><br>        while (fgets(line, sizeof(line), pipe) != NULL) {<br>                linelen = strlen(line);<br>                result = realloc(result, outlen + linelen);<br>                strncpy(result + outlen, line, linelen);<br>                outlen = outlen + linelen;<br>        }<br><br>        pclose(pipe);<br><br>        if (!(*result) || result == NULL) {<br>                *is_null = 1;<br>        } else {<br>                result[outlen] = 0x00;<br>                *length = strlen(result);<br>        }<br><br>        return result;<br>}<br>

1

char* make_toast( UDF_INIT *initid , UDF_ARGS *args , char* result , unsigned long* length , char *is_null , char *error ){ FILE *pipe; char line[1024]; unsigned long outlen, linelen; char buf[40]; result = malloc(1); outlen = 0; sprintf(buf, "make_toast %s", args->args[0]); pipe = popen(buf, "r"); while (fgets(line, sizeof(line), pipe) != NULL) { linelen = strlen(line); result = realloc(result, outlen + linelen); strncpy(result + outlen, line, linelen); outlen = outlen + linelen; } pclose(pipe); if (!(*result) || result == NULL) { *is_null = 1; } else { result[outlen] = 0x00; *length = strlen(result); } return result; }

Usage:

mysql> call make_toast(300)

1	mysql> call make_toast(300)

Demo picture (thanks to my colleague Fernando Laudares Camargos), actual video will follow:

Implementation details:

Hardware/wiring

The relay switch powers on the toaster oven, and no modifications are needed to the oven itself. Make sure the timer is set to 30 min initially, the Raspberry Pi/MySQL UDF will now control how long you toast the bread.

The setup wiring is super easy (but may be counterintuitive if you are used to working with Arduino): use the output pins (image) and connect 5v on the PiFace to the “+” sign on the relay switch, and one of the pins to the “-” sign on the relay switch.

Software install

Install PiFace software and Python bindings

Test the make_toast python script

Add user “mysql” to the spi and gpio groups so it can manipulate pins:

Shell

# gpasswd -a mysql gpio # gpasswd -a mysql spi

1

# gpasswd -a mysql gpio # gpasswd -a mysql spi