MQTT MySQL gateway

Previously I've made a couple of sensors that send data with MQTT. Of course that data should be stored in a database. Considering that I'm probably going to make more wireless sensors that send data for example from our heating system it's best to write a easily configurable MQTT to database gateway. This gateway should also be able to send new values from database to MQTT nodes for displaying, as some data is gathered in other ways and inserted directly to db, for example OWFS(1-wire) or homegrown pulse counters.

So the basic functionality would be:

• Recieve data from MQTT, process and save it to MySQL
• Follow MySQL binlog and send saved data to MQTT

For input I need to have a table that stores:

• Subscribed topic.
• How to get raw sensorid from topic, maybe regexp.
• How to parse data from message.
• Small script that processes the data so it's ready to store.

And a second table that's used to convert raw sensorid's to db sensorid's:

• Rawid
• Sensorid

For output I need a table that stores:

• Sensorid
• Topic to push data to
• How to process data before sending

There are still some features to implement, like outputting data from db to MQTT, need to think how this should be done with some kind of plugin system, MySql will probably use binlog to read data, but other databases have other mechanisms.

There are depencies for Paho-MQTT client, pep3143daemon and SQLAlchemy. More detailed dependencies in requirements.txt

Code in GitHub https://github.com/mika-koivusaari/mqtt_db_gateway.

Wifi temperature sensor

I have need for some temperature sensors where I'm too lazy install cables or the need is only for a short while. I also wanted to try micropython on ESP8266, so why not combine the two.

Installing micropython on ESP8266 is pretty simple, but somewhat time consuming the first time. Micropythons ESP8266 has good build instructions. Same goes goes for the needed esp open sdk. Building the esp-open-sdk takes pretty long time, but you don't have to update it that often.

What I want to do is have the eps8266 measure temperatures once a minute and then send the values with MQTT. Because I'm using DS18b20 sensors and they have a unique address I can just add that to the MQTT topic, something like /house/temperatures/1wire/285cb4ce0100009a and then have time and value in the message.

Micropython has a separate library repository that has MQTT library, but trying to just import that on ESP8266 gives an error and it seems that error is because there is not enough ram to compile to source, so the only way to use it is to precompile it by copying it to modules directory before building the firmware. That way it gets precompiled and you can use just like any other python module.

I'm thinking of sending the time in the message along with the value, for that I need to keep somewhat accurate time on the ESP8266.  I wrote a little script to see how accurate the rtc was, and it drifted about ten minutes in five hours, so I definitely should check the time every time I wake up from deep sleep. Script can be found here https://gist.github.com/mika-koivusaari/90febd8a101412bf0e5c71d9c736e855

Fritzing drawing of the connections. There isn't much stuff so I just soldered it all on the board. Sensors, power and battery monitor leads are of course with connectors.  I actually used a 18650 cell but didn't have a Fritzing part for that. I also used a dc - dc converter instead of a linear regulator.

Finished product, held together with heat shrink tube and hot glue:)

Code is pretty simple.

• Wake up
• Check if stop pin is down
• Make measuments (only temperature and battery voltage at this time)
• Wait for wifi connection
• Connect to MQTT broker
• Send values
• Go to deep sleep

Code and Fritzing files can be found in https://github.com/mika-koivusaari/remote_sensor.

There's probably some bugs still that I have to sort out. But for future I might add some different sensors, for example humidity. Only send measured data every x minutes and store it between sends, to conserve battery. Maybe think some more about MQTT topics so they would be logical and extendable. Some more configuration options, and a local ntp server.

Next I should probably code a MySql - MQTT gateway so I can store those readings:)

You have mail 2

After having done the hardware for checking mail I had to make some kind of user interface to show that data. I had previously used an old IPhone to display temperature and power usage, so it was an easy choice to continue using it.

Previously I had just used a simple php script to fetch and display data, now I wanted to try python and Flask. I used a tutorial found here to get started.

Code is pretty simple, temperature and electricity usage is fetched every time from database, but this page is usually only open on the extra phone that displays this data so there shouldn't be any reason to cache that data. That data is also updated to database every minute so it should be refreshed every time. Mail status is received over mqtt and it's sent only when it changes so it has to be cached.

I also made a couple of changes to the hardware/firmware section, for the firmware I set the retain flag in the mqtt message so that when clients connect they get the last know value of mail. For the hardware I added a bigger antenna because I have some trouble with the wifi reception. Reception is still not as good as I would like, I have to think about it. Maybe second wifi ap, I'm also thinking if I should build a separate network for IoT-devices, currently there is only one wifi device, but that could easily change. I also changed the psu to one that outputs more current and has a lower quiescent current as I thought that the wifi problem might be because the ESP8266 is not getting enough current to transmit, but that didn't solve the problem. Before I had a cheap chinese buck/boost converter, and now I have https://www.pololu.com/product/2830 which can ouput more current, but is only a buck converter and needs at least 4 volts to output 3.3 volts. So I had to replace the three aaa batteries with four aa batteries to get enough voltage.

Also there is a problem with the mailbox itself:) It let's sunlight through somewhere and that's enough so we can't see if the led is on or not. I'll have to think how to put some seals there.

Future improvements

Hardware

• Monitor battery state.
• Don't turn radio on on wake up, only when we need to send. NodeMCU bug might prevent this currently.

Software

• Move temperature and electricity to mqtt also.
• Add today's forecast.

Software can be found here https://github.com/mika-koivusaari/data_display_phone

You have mail

Have you ever wondered has the mail arrived yet? I got my ESP8266 some while ago and was wondering what to do with it. After a while I thought it would be perfect for checking if mail had arrived, and I mean the traditional physical kind with envelopes.

Hardware

I figured some kind of light gate would be good for detecting if there is something in the mailbox. At first I tried with ldr and some led's, but couldn't get it to work. Either it didn't notice the led or ambient light was enough. Probably if I had a different type of ldr with different resistance it could have worked, but I didn't. So I used a TSL257 that I had lying aroung, and it seems to do the trick.

On the hardware side everything is simple, TSL257 connected to input pin, and a led with a transistor connected on a output pin. Because Esp8266 can only output a maximum of 12mA on it's pins I put a transistor to drive the led, so I could drive it at a higher amperage. Fritzing ESP-201 module from https://github.com/ydonnelly/ESP8266_fritzing.

Since I use deep sleep between checks XPD has to be connected to reset pin(black wire). Resistors on top of the Esp-201 and the yellow wire are for normal boot mode, as the module doesn't have these ready.

If pin2 (gpio4) is pulled low then the normal check and sleep is not done, this is so you can update lua scripts without reflashing the whole thing.

For a power supply I use three aaa batteries in series and a step down converted to get the 3.3 volts that is needed.

Since the circuit was so tiny I didn't use a pcb but just soldered the components together. Wires going to top right are for led and wires going bottom left are for TSL257.

I used a ESP-201 module, because it has an external antenna, and it's small and cheap. Because it doesn't have a usb connector it also has slightly smaller power usage, but I have to use a separate usb to serial module for programming. At first I used cheap clone ordered from aliexpress, but kept getting errors when flashing. So I bought a different module from a local shop and it worker like a charm. Others have also had trouble with cheap usb to serial modules when flashing ESP8266 modules.

Here is the device installed. Installation is not permanent, so I can better debug or change something if need be. Also we will probably get a new mailbox soon. Which would be good because our current mailbox is made from plastic and it lets light in just enough that when sun is shining it interferes. I had to tape the inside to block sun light.

And here is the inside.

Software

For programming I wanted to use NodeMCU and lua. I wanted to see if a interpreted language would be good enough, and it also has mqtt support.

Script itself is pretty simple:

1. wakeup
2. check if we see light, if so then there is too much light -> error
3. turn on led
4. check if we see light, light -> no mail, no light -> mail
5. send message
6. sleep

Programming with NodeMCU is pretty straightforward. But because it's event driven there is a bit of extra work with callbacks for this kind of batch job.

Code can be found here https://github.com/mika-koivusaari/you_have_mail.

Future improvements

After deep sleep check mail status first, and only if it has changed start modem and send message. Not sure how much this would improve battery life, but if it seems too short then I'll try.

Report battery status. Esp8266 only has adc range from 0 to 1 volts, so I would need to scale the voltage from three aaa batteries to that. Need to look into that sometime, it would probably be useful for other projects too.

Look for mqtt broker with mDNS, NodeMCU doesn't current have a mDNS client, so this will have to wait for that.

Next I'll have to make some kind user interface so I can actually see when the mail arrives.