phpMyAdmin Works on HESA

I spent Labor Day weekend working on the Home Environmental Sensor Array on my Raspberry Pi.  I want to start reading and writing data on MySQL tables on the Raspberry Pi.  In order to make this easier, I need to use phpMyAdmin on my laptop to manage the MySQL databases on the Raspberry Pi.  There were several things I had to do to get this working.

First, phpMyAdmin tries to only connect to the MySQL server on the localhost.  I found a web page that explained how to get phpMyAdmin to connect to MySQL on another device.  Basically, I had to edit the config.inc.php file in the /etc/phpmyadmin folder.  The lines below were added after the section like it that setup the localhost connection.

$cfg['Servers'][$i]['verbose']         = 'HESA';$cfg['Servers'][$i]['host']            = 'hesa.local';$cfg['Servers'][$i]['port']            = '3306';$cfg['Servers'][$i]['connect_type']    = 'tcp';$cfg['Servers'][$i]['extension']       = 'mysqli';$cfg['Servers'][$i]['compress']        = FALSE;$cfg['Servers'][$i]['auth_type']       = 'cookie';$i++;

One thing to note is that these lines can be added to this file for every server that you want to control with this copy of phpMyAdmin.

Next, I had to go to the Raspberry Pi and tell MySQL to allow connections on the public internet connection and port 3306.  This is done by editing the /etc/mysql/my.cnf file and changing the bind-address variable in the [mysql] section to the local IP.  In my case, it looked like this:

bind-address = 192.168.0.58

Finally, I had to setup security for the users in MySQL.  I started up the mysql command line tool and executed a command like this:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Now that I am documenting all of this, it seems pretty simple. Its hard to believe that I spent so much time trying to get this to work.  Here is the end result.  Now, there is a Server Choice dropdown that shows all the servers, I can connect to from my laptop.

After getting this working, I created tables for the data that I store on the rynok.org website.  I also mostly copied data from the production site to the database on the Raspberry Pi.  The RJ comments table did not fully copy.  There is probably something wrong with the variable size for the id column.  I'll deal with that later.

HESA is Populating Data to MySQL Server

For the last week or so, I have been working on MySQL coding in python on my Home Environmental Sensor Array.  This weekend, I put the finishing touches on the python code in the SQL library.  I have standard functions to connect and disconnect from the server, create an INSERT statement, submit a query, and process the results.  This makes it relatively easy to add SQL functionality to a python program.

Next, I added code to the HESA python program so it writes status records to a MySQL database on my website every hour or so.  The current water sensor status is recorded (which is a bit strange since the status is either 0 or 1 -- there is either water in the basement or not).  Right now, the HESA basically stops running if water is detected.  In the future, it may keep running and keep recording various readings.  Then, this status may be more useful.  Also, it will record false positives and give me the opportunity to do some analysis.

The other thing that the HESA measures right now is the number of times that current was detected on the input pin, i.e., the pin was set HIGH.  The HESA looks for power on the input pin for about one second.  During that second, it takes 1,000 readings.  The pin must be set HIGH for 500 readings in a row for the HESA to determine that there is water in the basement.  There is a variable that keeps a running total of how many individual readings were HIGH in the past hour.  That number is written to the database.

Each individual measurement is written as a separate row in the database.  This way, I don't have to add a new column for every new thing I want to measure.  I could measure the temperature in every room in my house and record a new row for each room.  This is modeled after the SAP plant maintenance measuring point functionality.  Each device or room I want to take measurements for would have a record in a table.  Each thing I want to measure on each device or room would have a record in another table.  The measurements themselves that are recorded are related to one of the measuring points.  This should work out well.


Finally, I made a PHP web page to display the most recent results.  It reads the last 48 readings from the database and displays them in a table.  It's not really very impressive at this point (well, maybe it never would be).  In the future, I would like to add more sensors to the HESA to measure temperature, humidity, CO2, radon, etc.  Then, the readings might be a bit more interesting.

In other HESA news, I was getting a lot of false positives where it detected water when there was none.  I think that was because I was using an electrical cable from a lamp.  The ends of the cable were together but covered in insulation.  They should not have passed electricity between them but apparantly, they were.  Now, I separated the ends of the cable and attached them to opposite sides of a four inch PVC pipe.  I have not had any false positives since I made that change.

MySQL and Python

Over the weekend, I decided to write some python code to work with MySQL databases.  I already have experience working with MySQL and PHP and I have a small library of PHP functions to access MySQL.  The goal was to get the same functionality working in python.  Then, I can start storing data from the HESA in MySQL tables.

The first thing I had to do was install a python library.  I did this by typing

sudo apt-get install build-essential python-dev libmysqlclient-dev

at the command prompt.  This library is supposed to allow PIP to install the actual python package. 

Next, I had to install PIP, the app that lets you install python packages.  I did this by typing 

sudo apt-get install python-pip

Once PIP was installed, I used it to install the MySQL python library by typing

sudo pip install MySQL-python

The documentation for MySQL and python is not really that great.  I spent way too much time researching how to do all this.  Once I got this far however, I was able to talk to the MySQL server.

I found a simply python program to connect to a SQL server and display the version of the database.  I was able to use it to query both my local copy of MySQL and my version on the internet.

Next, I converted all of my PHP SQL code to python functions.  I have functions to connect and disconnect from a SQL server.  I also have functions to create insert statements, fix values in insert statement to guard against injection attacks, and execute a SQL statement and return the results.  I was able to successfully test all the functions with my test database.

Now that I have my SQL libraries, I will create a table in my test database to store readings from the HESA.  If only I had the HESA reading stuff...