[Version 2 Release] RaspberryPints - Digital Taplist Solution

[esdill]

@esdill ok, with both temperature probes, I see a significant delay in the page loading, but it does load eventually. I'll run strace on apache and see if I can figure out what is going on.

Mine would "eventually" load as well, the last try it took upwards of 6 hours to finally load, and then the temperatures on the screen did not update once it loaded.

The brand new fresh install seems to be working so far, but only time will tell.

 

[duncan.brown]

@RandR+ I think I know where the issue. After hooking up two temperature probes, the main RPints index.php is taking a very long time to load (47 seconds):

time curl http://10.0.1.31/ 1>/dev/null
0.00s user 0.01s system 0% cpu 47.040 total

This delay is caused by the length of time that it takes MariaDB to execute this query:

MariaDB [raspberrypints]> SELECT temp, tempUnit, COALESCE(NULLIF(p.notes,''), tl.probe) AS probe, takenDate AS takenDate FROM tempProbes p LEFT JOIN tempLog tl ON (tl.probe = p.name AND tl.takenDate = (SELECT MAX(takenDate) FROM tempLog tl2 WHERE tl2.probe = tl.probe))WHERE p.active = 1 ORDER BY tl.probe ASC;

+-------+----------+---------+---------------------+
| temp  | tempUnit | probe   | takenDate           |
+-------+----------+---------+---------------------+
| 21.80 | C        | Probe 1 | 2020-07-14 09:12:41 |
| 21.40 | C        | Probe 2 | 2020-07-14 09:12:42 |
+-------+----------+---------+---------------------+
2 rows in set (49.163 sec)

The join seems to be taking forever. The length of time this query is taking is growing as the number of rows in tempLog grows:

MariaDB [raspberrypints]> select count(*) from tempLog;
+----------+
| count(*) |
+----------+
|     2999 |
+----------+
1 row in set (0.010 sec)

By doing a fresh install, @esdill has emptied this table but the problem is going to come back as the number of rows grows. I have to go and do my day job now, but I will take a look at constructing a better SQL query tonight, if you don't get to it first.

 

[duncan.brown]

@RandR+ in the time it took me to write the above post, the time taken to execute that query increased by 50%

2 rows in set (1 min 0.221 sec)

 

[RandR+]

I don't have a large dataset for temps

I added over 50000 rows to my local test tempLog table and the query to get the latest time never returns, which explains what the page isn't loading (and no error). I rewrote the query to be a PHP loop to get the latest and it works with large amount of data. Getting latest should fix the loading

I'm going to rework the temp saving to make the original query work which should be the fastest way of doing this

 

[RandR+]

Will I still have to do what you posted above? Sounds like the raspberry pints website isn't up to date and will not work as is (with and without flow meters), right?

yes, the raspberry pints website and original code doesn't work on the latest Raspbian OSs so you either need to use an old OS or follow that post.

 

[duncan.brown]

@RandR+ I have an SQL query that works in less than a second:

MariaDB [raspberrypints]> SELECT tp.temp, tp.tempUnit, tn.notes as probe, tp.takenDate from tempProbes tn LEFT OUTER JOIN (SELECT tt.temp, tt.tempUnit, tt.probe, tt.takenDate FROM tempLog tt INNER JOIN (SELECT probe, MAX(takenDate) AS MaxDateTime     FROM tempLog   GROUP BY probe) groupedtt  ON tt.probe = groupedtt.probe  AND tt.takenDate = groupedtt.MaxDateTime) tp ON tp.probe = tn.name;
+-------+----------+---------+---------------------+
| temp  | tempUnit | probe   | takenDate           |
+-------+----------+---------+---------------------+
| 22.80 | C        | Probe 1 | 2020-07-14 09:57:34 |
| 22.00 | C        | Probe 2 | 2020-07-14 09:57:35 |
+-------+----------+---------+---------------------+
2 rows in set (0.252 sec)

 

[duncan.brown]

@RandR+ here you go:

https://github.com/rtlindne/RaspberryPints/pull/3
This also reverts your temporary fix.

 

[RandR+]

I have an SQL query that works in less than a second:

Thanks, but when ran against my dataset and it takes upwards of 2.5 seconds to complete.
if I fix how the data is stored in the database from the get-go I can use a simply query without joins to get the latest and that returns in under .2 seconds for this dataset

 

[duncan.brown]

@esdill @RandR+ that pull request does the trick. Both my temperature probes now work and the page loads in under a second. I added another commit to skip a probe if it's name in NULL to replicate the existing behavior.

 

[duncan.brown]

Thanks, but when ran against my dataset and it takes upwards of 2.5 seconds to complete.
if I fix how the data is stored in the database from the get-go I can use a simply query without joins to get the latest and that returns in under .2 seconds for this dataset

Great, that sounds like a better solution.

 

[duncan.brown]

Talking of temperature probes: I didn't want to use another wire in the cable to my keezer for +3V3. The DS18B20 probes I have can be power by the AlaMode +5V that I use to power my SF800s. I put a 4k7 resistor between my AlaMode board's 3V3 supply and the data line as a pull-up and then connect the data line to the GPIO pin on my Pi. This works fine and only requires one extra wire from my keezer for all the temperature signals. The DB18B20 has an open drain on the data line, so this works fine.

 

[RandR+]

Ok got the temp logging updated (again) now it will force each reading iteration to have the same timestamp so I can use my simple query that is fast

Also update the installer two main updates
1. added support for side by side installation with fermentrack.
2. added updating to stash changes before getting latest then after the update is complete, opening a compare so users can see their changes compared to the latest (unfortunately they need to manually apply their changes as the compare happens on temp files)

 

[esdill]

Ok got the temp logging updated (again) now it will force each reading iteration to have the same timestamp so I can use my simple query that is fast

Also update the installer two main updates
1. added support for side by side installation with fermentrack.
2. added updating to stash changes before getting latest then after the update is complete, opening a compare so users can see their changes compared to the latest (unfortunately they need to manually apply their changes as the compare happens on temp files)

I ran the update, my temperature probes show the probe address (28-xxxxxxxxx) instead of the names I have entered in the configuration, and when the screen refreshes sometimes there's one probe shown and sometimes there's two probes shown.

 

[RandR+]

now I cannot upload my brewery logo or my background

probably because it cleared the permissions on the directory with the new way of getting the latest, I will add in the permission fix into the installer.
for now try
sudo chmod +777 /var/www/html -R

my temperature probes show the probe address (28-xxxxxxxxx) instead of the names I have entered in the configuration

I will research and fix, I have a feeling I reverted back too far

 

[esdill]

probably because it cleared the permissions on the directory with the new way of getting the latest, I will add in the permission fix into the installer.
for now try
sudo chmod +777 /var/www/html -R


I will research and fix, I have a feeling I reverted back too

Thanks - I thought of the permissions and reset them and then it worked (I edited my reply as soon as I figured it out). Any idea why the update wipes out the brewery logo and the background each time it runs? (It's not a big deal, I have been re-uploading them each time ... but it would be nice if I did not need to).

 

[RandR+]

. Any idea why the update wipes out the brewery logo and the background each time it runs?

Probably because it removes them as conflicts and doesn't add them back in. I will work on a way to get them to stay when you update.

I found I did go back too far with my revert and fixed so now the probes will display their name not their device id.

Can you restart the flowmon service and see if that fixes the issue probes jumping back and forth between 1 and 2. I
Check /python/FlowMonitor.py line 703 to make sure it starts with takenDate and check line 719 to make sure it ends with takenDate)

 

[RandR+]

Any idea why the update wipes out the brewery logo and the background each time it runs?

I updated the installer to save off the images and force background and logo to stay as you had it. Also added in fixing the permissions on the directory after the update.

if you rerun
curl -L install.rpints.com | sudo bash
you will have the latest changes

 

[duncan.brown]

@RandR+ thanks, that works for me (although I merge your changes into my git repo, rather than running the installer).

 

[esdill]

Probably because it removes them as conflicts and doesn't add them back in. I will work on a way to get them to stay when you update.

I found I did go back too far with my revert and fixed so now the probes will display their name not their device id.

Can you restart the flowmon service and see if that fixes the issue probes jumping back and forth between 1 and 2. I
Check /python/FlowMonitor.py line 703 to make sure it starts with takenDate and check line 719 to make sure it ends with takenDate)

The temp probe names are now showing correctly. I verified FlowMonitor.py to show what you asked, and it does. I stopped the flowmon service, and then started the flowmon service ... and it still randomly shows one probe or two probes.

 

[duncan.brown]

Huh, same here...

 

[duncan.brown]

There's a race condition somewhere:

MariaDB [raspberrypints]> SELECT tp.temp, tp.tempUnit, tn.notes as probe, tp.takenDate from tempProbes tn LEFT OUTER JOIN (SELECT tt.temp, tt.tempUnit, tt.probe, tt.takenDate FROM tempLog tt INNER JOIN (SELECT probe, MAX(takenDate) AS MaxDateTime     FROM tempLog   GROUP BY probe) groupedtt  ON tt.probe = groupedtt.probe  AND tt.takenDate = groupedtt.MaxDateTime) tp ON tp.probe = tn.name;
+-------+----------+---------+---------------------+
| temp  | tempUnit | probe   | takenDate           |
+-------+----------+---------+---------------------+
| 22.90 | C        | Probe 1 | 2020-07-14 13:11:32 |
| 22.80 | C        | Probe 2 | 2020-07-14 13:11:33 |
+-------+----------+---------+---------------------+
2 rows in set (0.656 sec)

 

[RandR+]

and it still randomly shows one probe or two probes.

There's a race condition somewhere:

Ok my guess its when the temp is committed, I did some testing with forcing a delay between reading test probe 1 and test probe 2 and they both had the same time, but if I refreshed the page during the delay only one was shown.

I just need to force the database inserts in one commit to fix, should be later today

 

[duncan.brown]

I just need to force the database inserts in one commit to fix, should be later today

Yep, that looks like the issue. Committing all the rows at once in an atomic commit should fix it.

 

[RandR+]

Committing all the rows at once in an atomic commit should fix it.

yep I just committed the fix

 

[duncan.brown]

yep I just committed the fix

Success, thanks! I ran the select directly on the database a bunch of times to check and the times are synced.

 

[esdill]

Success, thanks! I ran the select directly on the database a bunch of times to check and the times are synced.

View attachment 689419

I also now have good temperature readings ... however now my pours are not registering (I hadn't tested the pours on the fresh install prior to this, so I do not know if the recent changes had an impact related to this or not). I have restarted the flowmeter service, and I have reset the Arduino, and no joy.

 

[RandR+]

however now my pours are not registering

Did you make sure to reconfigure your taps? You can turn on debugging, try a pour and look at /var/logs/rpints.log

Debugging in /python/Config.py change False#True to True like:
config['flowmon.debug' ] = True
#logging settings for pintdispatch
config['dispatch.debug' ] = True

Then restart the flowmonitor service and pour a beer
sudo /etc/init.d/flowmon restart

 

[esdill]

Did you make sure to reconfigure your taps? You can turn on debugging, try a pour and look at /var/logs/rpints.log

Debugging in /python/Config.py change False#True to True like:
config['flowmon.debug' ] = True
#logging settings for pintdispatch
config['dispatch.debug' ] = True

Then restart the flowmonitor service and pour a beer
sudo /etc/init.d/flowmon restart

2020-07-14 16:43:19 RPINTS: valve update: RPU:VALVE:0=0
2020-07-14 16:43:19 RPINTS: valve update: RPU:VALVE:1=0
2020-07-14 16:43:19 RPINTS: valve update: RPU:VALVE:2=0
2020-07-14 16:43:19 RPINTS: valve update: RPU:VALVE:3=0
2020-07-14 16:43:19 RPINTS: Could not import RFID Reader, RFID disabled. Assuming SPI not installed/configured
2020-07-14 16:43:21 RPINTS: starting setup...
2020-07-14 16:43:21 RPINTS: resetting alamode to try to force it to listen to us...
2020-07-14 16:43:22 RPINTS: giving it a short break to wake up again...
2020-07-14 16:43:24 RPINTS: reflashing Arduino via:
/usr/share/arduino/hardware/tools/avrdude -C/usr/share/arduino/hardware/tools/avrdude.conf -patmega328p -calamode -P/dev/ttyS0 -b115200 -D -Uflash:w:/var/www/html//arduino/raspberrypints/raspberrypints.cpp.hex:i
RPINTS: reflashing Arduino failed, moving on anyways, error was: Command '/usr/share/arduino/hardware/tools/avrdude -C/usr/share/arduino/hardware/tools/avrdude.conf -patmega328p -calamode -P/dev/ttyS0 -b115200 -D -Uflash:w:/var/www/html//arduino/raspberrypints/raspberrypints.cpp.hex:i' returned non-zero exit status 1
2020-07-14 16:43:30 RPINTS:
2020-07-14 16:43:30 RPINTS: starting WS server
2020-07-14 16:43:30 RPINTS: starting device monitors...
2020-07-14 16:43:30 RPINTS: starting command server
2020-07-14 16:43:30 RPINTS: resetting Arduino
2020-07-14 16:43:30 RPINTS: starting fan control
2020-07-14 16:43:30 RPINTS: Fan Control fanControl1 is Running
2020-07-14 16:43:30 RPINTS: Not Configured to run Fan
2020-07-14 16:43:32 RPINTS: 1Wire Temperature Thread 1 is Running
2020-07-14 16:43:32 RPINTS: waiting for Arduino to come alive

(I've already had two beers. but for the sake of research I poured myself another one) ...

By the way ... THANK YOU for everything you have done so far! If you ever find yourself in Port St Lucie, FL, stop in for a beer. I owe you at LEAST one.

 

[RandR+]

-P/dev/ttyS0

waiting for Arduino to come alive

Are you using Uno or Alamode?

The board isn't talking back to the Pi and I can see that you have it configured for serial communication which is alamode

 

[esdill]

Are you using Uno or Alamode?

The board isn't talking back to the Pi and I can see that you have it configured for serial communication which is alamode

It's a UNO and I selected USB when I ran the installer.

Arrrrhg! I'll run the installer and reconfigure and see if it fixes it. Of course that means I'll need a fourth beer, you know, just to make sure it's working.