{"id":272,"date":"2008-05-27T14:52:57","date_gmt":"2008-05-27T14:52:57","guid":{"rendered":"http:\/\/dalelane.co.uk\/blog\/?p=272"},"modified":"2008-05-27T16:19:58","modified_gmt":"2008-05-27T16:19:58","slug":"accessing-mysql-from-perl-on-slugos","status":"publish","type":"post","link":"https:\/\/dalelane.co.uk\/blog\/?p=272","title":{"rendered":"Accessing MySQL from Perl on SlugOS"},"content":{"rendered":"

I’ve written about my CurrentCost meter<\/a> that I’m using to monitor my home electricity usage, and the small home server<\/a> that I’ve set up to collect the data. <\/p>\n

Yesterday, I decided to make a start on collecting the data. My plan was to copy what Nick had done<\/a> and create a MySQL database to store the info, with a table to store a timestamp and the watt reading from the CurrentCost meter. <\/p>\n

CREATE TABLE currentcostdl (  \r\n    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  \r\n    power FLOAT NOT NULL,  \r\n    primary key(time));<\/pre>\n
I started with Nick’s Python scripts, but didn’t get very far.  <\/p>\n
The server, a NSLU-2 (or “slug”) is running SlugOS<\/a>.  <\/p>\n
The problem was that there is no python-mysqldb package in the SlugOS repositories. I did try downloading the source for it from sourceforge<\/a> to build it myself, but struggled to get the dependencies I needed to make it – urllib in particular was a big pain.  <\/p>\n
I also tried the debian package<\/a> but again dependencies on SlugOS got in the way. <\/p>\n
So I gave up on that and decided to do it myself using Perl – Perl and MySQL had to be easier, right? <\/p>\n
Erm… not so much \ud83d\ude42   <\/p>\n
In short, I ran into the same problem. There isn’t a MySQL module for Perl in the SlugOS repositories.<\/p>\n
And building the standard Perl ones runs into dependency problems. <\/p>\n
Then I found a pure Perl implementation<\/a> on CPAN. From the module description: <\/p>\n
This module implements network protocol between server and client of MySQL, thus you don’t need external MySQL client library like libmysqlclient for this module to work. It means this module enables you to connect to MySQL server from some operation systems which MySQL is not ported.\n<\/p><\/blockquote>\n
Hurrah, this sounded like what I needed. <\/p>\n
So I downloaded the archive<\/a>, and unzipped it.  <\/p>\n
There was a Makefile.PL perl script to run. Before I could run it, I needed to install a few pre-requisite Perl modules: <\/p>\n
ipkg install perl-module-io perl-module-extutils-makemaker perl-module-extutils-makemaker-config perl-module-vars perl-module-config-heavy perl-module-autosplit perl-module-cwd perl-module-extutils-install perl-module-extutils-packlist perl-module-extutils-command perl-module-extutils-command-mm perl-module-extutils-my perl-module-extutils-manifest perl-module-extutils-mm perl-module-extutils-mm-unix perl-module-file-spec-unix perl-module-exporter-heavy perl-module-carp-heavy perl-module-file-glob perl-dev perl-module-pod-man perl-module-getopt-long perl-module-test perl-module-test-harness <\/pre>\n
Once run, this creates the make files you need – so it’s make<\/code> and make install<\/code> as normal. <\/p>\n
It seemed to build okay, but my first attempt to use it failed with a runtime dependency: <\/p>\n
dalelane@minipooter:~$ perl -e 'use Net::MySQL;' \r\nCan't locate Digest\/SHA1.pm in @INC  \r\nBEGIN failed--compilation aborted at \/usr\/local\/share\/perl\/5.8.8\/Net\/MySQL.pm line 681. \r\nCompilation failed in require at -e line 1. \r\nBEGIN failed--compilation aborted at -e line 1.<\/pre>\n
I needed Digest\/SHA1 – but that’s not available in the SlugOS repositories: <\/p>\n
dalelane@minipooter:~$ ipkg list perl-module-digest* \r\nperl-module-digest - 5.8.8-r25 - perl module digest \r\nperl-module-digest-base - 5.8.8-r25 - perl module digest-base \r\nperl-module-digest-file - 5.8.8-r25 - perl module digest-file \r\nperl-module-digest-md5 - 5.8.8-r25 - perl module digest-md5 \r\ndalelane@minipooter:~$<\/pre>\n
I started looking for a digest-sha1 module. I found the source for an Optware one on ipkgfind<\/a>.  <\/p>\n
First attempt to build it failed because of missing dependency: <\/p>\n
root@minipooter:\/var\/tmp\/Digest-SHA1-2.11$ make \r\ncp SHA1.pm blib\/lib\/Digest\/SHA1.pm \r\nmake: *** No rule to make target `\/usr\/share\/perl\/5.8\/ExtUtils\/typemap', needed by `SHA1.c'.  Stop.<\/pre>\n
There is no perl-module-extutils-typemap for SlugOS, so I got my own copy of the two files I needed from CPAN: <\/p>\n
wget http:\/\/ftp.funet.fi\/pub\/CPAN\/src\/perl-5.8.8.tar.gz \r\ntar -xzvf perl-5.8.8.tar.gz \r\ncp perl-5.8.8\/lib\/ExtUtils\/typemap \/usr\/lib\/perl5\/5.8.8\/ExtUtils\/typemap \r\ncp perl-5.8.8\/lib\/ExtUtils\/xsubpp \/usr\/lib\/perl5\/5.8.8\/ExtUtils\/xsubpp<\/pre>\n
Then I edited the Digest-SHA1 Makefile so that it wouldn’t try and build them itself – replacing the XSUBPP line with: <\/p>\n
XSUBPP = \/usr\/share\/perl\/5.8\/ExtUtils\/xsubpp<\/pre>\n
Running the make for Digest-SHA1 needed one more pre-req module:  <\/p>\n
ipkg install perl-module-re<\/pre>\n
Unfortunately, it then failed due to missing executable: <\/p>\n
\/bin\/sh: ccache: not found<\/pre>\n
I needed to find ccache<\/code> for SlugOS. I couldn’t find a pre-compiled version anywhere, so I downloaded the source for ccache<\/code> from samba.org<\/a>. <\/p>\n
Initial attempts to compile it failed because it was missing ‘as’ (assembler?<\/em>) <\/p>\n
arm-linux-gcc: error trying to exec 'as': execvp: No such file or directory<\/pre>\n
I hadn’t done any compiling on the slug before, and a quick attempt to compile a ‘Hello World’ C app showed that I hadn’t got a working C compiler. From what I’ve read, SlugOS-Native is a good way to get your system set up for this. <\/p>\n
I downloaded SlugOS-Native from ipkgfind<\/a> and tried installing it. It is a tiny package that includes a ton of pre-reqs – the idea is that installing this forces ipkg<\/code> to go and install everything you need for native development on the Slug. <\/p>\n
Note: I had to ignore a dependency on the monotone source control package, but that didn’t seem to be a problem – it continued to successfully install a lot of<\/strong><\/u> development\/compiler packages. In fact, so many that I Ctrl-C’d it after a while, as it was installing a ton of perl modules that I didn’t think I’d want.<\/em> <\/p>\n
With a proper development environment now set up, I was back to trying to build ccache<\/code>: <\/p>\n
.\/configure \r\nmake \r\nmake install<\/pre>\n
This worked fine, so I was back to trying to make digest-sha1. It failed on another perl module dependency: <\/p>\n
ipkg install perl-module-extutils-mkbootstrap<\/pre>\n
But with that out of the way, I was then able to: <\/p>\n
make \r\nmake install<\/pre>\n
So I was back to trying out the Perl MySQL module – which had failed on the runtime dependency on Digest\/SHA1 in the first place! <\/p>\n
perl -e 'use Net::MySQL;'<\/pre>\n
Success! <\/p>\n
Or so I thought. A quick perl test script to connect to my currentcost database failed: <\/p>\n
Can't locate auto\/Digest\/SHA1\/reset.al in @INC <\/pre>\n
 The reset method in SHA1 called the method in Digest base.pm, so this was easily fixed by installing  <\/p>\n
ipkg install perl-module-digest-base<\/pre>\n
And it now works fine.  <\/p>\n
My code to insert an update into my MySQL table looks something like this: <\/p>\n
use Net::MySQL; \r\n\r\nmy $mysql = Net::MySQL->new( \r\n   database => 'currentcostdb', \r\n   user     => 'dalelane', \r\n   password => 'myPassw0rd' \r\n); \r\n\r\nmy $insertquery = \"INSERT INTO currentcostdl (power) VALUES ($watts)\"; \r\n\r\n$mysql->query($insertquery); \r\n\r\nprintf \"Affected row: %d\\en\", $mysql->get_affected_rows_length; \r\n\r\n$mysql->close;<\/pre>\n
Hurrah – it works. <\/p>\n
Okay, as Gareth pointed out<\/a> this is really overkill. The reason why API support for MySQL on the Slug isn’t great is probably because MySQL isn’t the best choice of DB to run! Support for other less resource-intensive databases, such as SQLite is much better, and I could have got up and running in a fraction of the time with a couple of ‘ipkg install’ commands: <\/p>\n
root@minipooter:\/var\/tmp$ ipkg list | grep -i sqlite \r\nlibsqlite-bin - 2.8.17-r2 - An Embeddable SQL Database Engine \r\nlibsqlite-dbg - 2.8.17-r2 - An Embeddable SQL Database Engine \r\nlibsqlite-dev - 2.8.17-r2 - An Embeddable SQL Database Engine \r\nlibsqlite0 - 2.8.17-r2 - An Embeddable SQL Database Engine \r\nlibsqlite3-0 - 3.5.2-r0 - An Embeddable SQL Database Engine \r\nlibsqlite3-dev - 3.5.2-r0 - An Embeddable SQL Database Engine \r\npython-sqlite3 - 2.5.1-ml5 - Python Sqlite3 Database Support \r\nsqlite3 - 3.5.2-r0 - An Embeddable SQL Database Engine \r\nsqlite3-dbg - 3.5.2-r0 - An Embeddable SQL Database Engine<\/pre>\n
But that wouldn’t have been nearly as much fun \ud83d\ude09 <\/p>\n
The slug is now sending updates to the realtime server<\/a> here, and storing them in a local MySQL database.  <\/p>\n
Now I’ve just gotta think of something to do with the data!<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve written about my CurrentCost meter that I’m using to monitor my home electricity usage, and the small home server that I’ve set up to collect the data. Yesterday, I decided to make a start on collecting the data. My plan was to copy what Nick had done and create a MySQL database to store […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,3],"tags":[194,105,213,197,211,212,199,200],"class_list":["post-272","post","type-post","status-publish","format-standard","hentry","category-code","category-tech","tag-currentcost","tag-mysql","tag-nslu","tag-nslu2","tag-perl","tag-python","tag-slug","tag-slugos"],"_links":{"self":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/272","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=272"}],"version-history":[{"count":0,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/272\/revisions"}],"wp:attachment":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=272"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}