Feedwriter failed, dashboard no longer accessible, caused by: The table 'feed_174' is full in /var/www/emoncms/Modules/feed/engine/MysqlTimeSeries.php:671

Luiflm2 · 2024-12-15T16:55:27.188Z

I could no access my dashboard (localhost) today, node-red reported for all Feeds: warning: "Error: connect ETIMEDOUT 127.0.0.1:80".
After some analysis, I was able to determine that the feedwriter was disabled.
After several attempts to get the feedwriter.service to run, I had the error messages:

`Dez 15 16:04:01 emonpi5 feedwriter[24016]: PHP Fatal error: Uncaught mysqli_sql_exception: The table 'feed_174' is full in /var/www/emoncms/Modules/feed/engine/MysqlTimeSeries.php:671
Dez 15 16:04:01 emonpi5 feedwriter[24016]: Stack trace:
Dez 15 16:04:01 emonpi5 feedwriter[24016]: #0 /var/www/emoncms/Modules/feed/engine/MysqlTimeSeries.php(671): mysqli->query()
Dez 15 16:04:01 emonpi5 feedwriter[24016]: #1 /var/www/emoncms/Modules/feed/engine/RedisBuffer.php(193): MysqlTimeSeries->post_bulk_save()
Dez 15 16:04:01 emonpi5 feedwriter[24016]: #2 /var/www/emoncms/Modules/feed/engine/RedisBuffer.php(151): RedisBuffer->process_feed_buffer()
Dez 15 16:04:01 emonpi5 feedwriter[24016]: #3 /var/www/emoncms/scripts/feedwriter.php(62): RedisBuffer->process_buffers()
Dez 15 16:04:01 emonpi5 feedwriter[24016]: #4 {main}
Dez 15 16:04:01 emonpi5 feedwriter[24016]: thrown in /var/www/emoncms/Modules/feed/engine/MysqlTimeSeries.php on line 671`

Feed 174 uses MYSQL Memory as Engine.
The Size was 16MB, a reboot did not help, nor did a Clear or Trim. Only a delete has helped here.
Since I have other feeds that use MYSQL Memory as Engine, I have noticed that they do not get reduced after a reboot.
I would be grateful for a Solution.

Server Information

Services

emonhub :- Active Running
emoncms_mqtt :- Active Running
feedwriter :- Active Running - sleep 300s 196 feed points pending write
service-runner :- Active Running
redis-server :- Active Running
mosquitto :- Active Running
emonPiLCD :- Inactive loaded inactive dead
emoncms_sync :- Inactive loaded inactive dead
demandshaper :- Not found or not installed

Emoncms

Version :- low-write 11.6.4
Git :-
- URL :- GitHub - emoncms/emoncms: Web-app for processing, logging and visualising energy, temperature and other environmental data
- Branch :- * stable
- Describe :- 11.6.4
Components :- Emoncms Core v11.6.4 | App v2.8.7 | EmonHub Config v2.1.5 | Dashboard v2.4.0 | Device v2.2.6 | Graph v2.2.4 | Network Setup v1.0.5 | Backup v2.3.3 | Network v3.1.6 | Postprocess v2.4.7 | Sync v3.1.4 | Usefulscripts v2.3.11 | Avrdude-rpi v1.0.3 | Emonhub v2.6.14 | EmonPiLCD v2.0.1 | EmonScripts v1.8.4 | RFM2Pi v1.4.2

Server

CPU :- Cortex-A76 | 1 Threads(s) | - Sockets(s) | 108,00MIPS |
OS :- Linux 6.6.31+rpt-rpi-v8
Host :- emonpi5 | emonpi5 | (10.255.255.27)
Date :- 2024-12-15 17:46:12 CET
Uptime :- 17:46:12 up 1:00, 0 user, load average: 0,16, 0,15, 0,17

Memory

RAM :- Used: 13,30%
- Total :- 3.9 GB
- Used :- 531.03 MB
- Free :- 3.38 GB
Swap :- Used: 0,00%
- Total :- 200 MB
- Used :- 0 B
- Free :- 200 MB

Disk

**** :- - / :- Used: 29,94%
- Total :- 14.69 GB
- Used :- 4.4 GB
- Free :- 9.62 GB
- Read Load :- 21.53 B/s
- Write Load :- 1.02 B/s
- Load Time :- 113 days 1 hours 43 mins
/boot/firmware :- Used: 18,64%
- Total :- 509.99 MB
- Used :- 95.05 MB
- Free :- 414.94 MB
- Read Load :- n/a
- Write Load :- n/a
- Load Time :- n/a
/var/opt/emoncms :- Used: 0,39%
- Total :- 43.32 GB
- Used :- 172.5 MB
- Free :- 40.95 GB
- Read Load :- 0.7 B/s
- Write Load :- 0.58 B/s
- Load Time :- 113 days 1 hours 43 mins
/var/log :- Used: 11,41%
- Total :- 50 MB
- Used :- 5.7 MB
- Free :- 44.3 MB
- Read Load :- n/a
- Write Load :- n/a
- Load Time :- n/a

HTTP

Server :- Apache/2.4.61 (Raspbian) HTTP/1.1 CGI/1.1 80

MySQL

Version :- 10.11.3-MariaDB-1+rpi1
Host :- 127.0.0.1 (127.0.0.1)
Date :- 2024-12-15 17:46:11 (UTC 01:00‌)
Stats :- Uptime: 3639 Threads: 29 Questions: 70485 Slow queries: 0 Opens: 359 Open tables: 349 Queries per second avg: 19.369

Redis

Version :-
- Redis Server :- 7.0.15
- PHP Redis :- 6.0.3-dev
Host :- localhost:6379
Size :- 1923 keys (1.78M)
Uptime :- 0 days

MQTT Server

Version :- Mosquitto 2.0.11
Host :- localhost:1883 (127.0.0.1)

PHP

Version :- 8.1.29 (Zend Version 4.1.29)
Run user :- User: www-data Group: www-data video Script Owner: pi
Modules :- apache2handler calendar Core ctype curl date exif FFI fileinfo filter ftp gd gettext hash iconv json libxml mbstring mosquitto v0.4.0mysqli mysqlnd vmysqlnd 8.1.29openssl pcre PDO pdo_mysql Phar posix readline redis v6.0.3-devReflection session shmop sockets sodium SPL standard sysvmsg sysvsem sysvshm tokenizer Zend OPcache zlib

Client Information

HTTP

Browser :- Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:109.0) Gecko/20100101 Firefox/115.0
Language :- de,en-US;q=0.7,en;q=0.3

Window

Size :- 1903 x 940

Screen

Resolution :- 1920 x 1080

TrystanLea · 2024-12-17T17:13:14.720Z

Hello @Luiflm2

It looks like this might be being caused by the size of the in memory feed reaching the maximum in memory table size default in mariadb/mysql.

Checking an emonSD image here I can see the default max size is 16MB which matches the size you are seeing…

Could you try:

sudo nano /etc/mysql/conf.d/mysql.cnf

this should bring up a mostly empty file with just the heading [mysql], add the section titled [mysqld] to the bottom e.g:

[mysql]

[mysqld]
tmp_table_size=128MB
max_heap_table_size=128MB

Restart mariadb:

sudo systemctl restart mariadb.service

That will hopefully sort it…

Luiflm2 · 2024-12-21T12:11:43.117Z

Thank you for your Feedback, I have implemented the configuration and will continue testing.
I wish you and the Team a Merry Christmas and thank you for being there for us. Of course I also wish everyone else in the forum a Merry Christmas.