Tema para especialistas en BBDD weewx

[Xavi-EA5ZF]

Hola, se  me ha terminado la buena suerte y como no, en periodo estival en el que no resido en la misma casa y no puedo hacer el mismo mantenimiento de la web, me he dado cuenta de que he tenido un cata-crash en el weewx, y ahora voy a tener que pensar en una estrategia adecuada para la mejor recuperación del contenido.
El tema es que un día petó el sistema weewx, dándo un mensaje de database is malformed.
Eso parece indicar que la memoria en la que está se ha dañado, y con ella parte de la BBDD.
El problema es que lo indicado es recuperar un backup anterior y actualizar desde él aprovechando la memoria de la estación, pero al revisar un poco los log veo que ya hace días que estaba avisando.
El tema  es que puedo retrotraerme a una sana de hace un mes o mas y luego he de actualizar, pero no puedo hacerlo desde la consola y debo hacerlo desde lo que pueda exportar desde la base de datos que hoy  mismo está activa.
No sé cual sería la mejor forma de hacerlo, supongo que una vez copiada la base de datos anterior, había que ir sacando via SQL los datos modificados de la base de datos e ir metiéndolos via importación.
Supongo que el comando sería tabla a tabla parecido a este:
SELECT * FROM nombre_tabla WHERE DATETIME > epoch_final_bbdd_antigua;
y exportando esto a CSV para luego importarlas una a una cada una de las mas de 40 tablas.
Es correcto o hay alguna forma mejor?

El comando .DUMP avisa que hay errores y no me fío de utilizarlo, aunque no sé que podría pasar si recupero la base de datos anterior y luego le doy a importar el DUMP eliminando sus comandos iniciales de borrado de las tablas.

Lo dicho, el tema es para especialistas...

[Xavi-EA5ZF]

Bueno, el tema parece que ya lo he resuelto.
Aviso a navegantes: la BBDD estaba corrupta desde hace meses y el weewx no avisaba.
How-to:
He estado mirando diferentes copias de la BBDD hasta encontrar una tabla archive con todos los datos y que no tenga ningún error.
El error se nota en que no te informa del nº real de registros o bien si al exportar datos da el error de "database malformed"
Una vez con una copia correcta pero desactualizada de la BBDD he exportado la tabla archive.
Desde diferentes copias mas actuales de la BBDD he exportado y aislado los datos complementarios para actualizarla a fecha de hoy, al final he tenido que mezclar datos de 4 bases de datos diferentes para completar el total de días y no perder nada.
Una vez todo esto en ficheros de texto SQL, el tema es crear una BBDD vacía y nueva e importar los datos de las tablas archive previamente exportados.
Me hice un excel para ir controlando y no perder nada:
Desde epochtime xxxx a yyyy
desde epochtime yyyyy a zzzzz ... etc
Una vez todo importado, la base de datos es correcta y ya no muestra lo de malformed, solo falta recrear las tablas accesorias con el wee_database -- rebuild_diary (creo recordar que es así)

Todo esto me ha hecho pensar en una estrategia buena para próximas ocasiones, creo que lo mejor es alternar varios tipos de copia, por una parte la fisica que es copiar el fichero weewx.sdb del directorio /var/lib/weewx y por otra ir haciendo exportaciones de datos e ir controlando que no se hagan con errores.
Para esto hay que entrar en la bbdd con sqlite3 indicarle un fichero para el dump (el dump es un fichero de texto plano SQL) y luego realizarlo:
sqlite3 /var/lib/weewx/weewx.sdb
.output backup.dmp
.dump
.quit

o bien:
sqlite3 /var/lib/weewx/weewx.sdb
.output backup.dmp
select * from archive;
.quit

Me falta hacer el script para automatizarlo, como no tengo tiempo por temas familiares graves en un próximo capitulo iré probando y actualizando el post con el script resultante.
Saludos.
Xavi

[Xavi-EA5ZF]

# Copia de la base de datos weewx semanalmente
5  0    * * 1 root /home/pi/dump_archive >dev/null
10 0    * * 1 root /home/pi/dump_weewx >dev/null

Añadir esto al crontab y he puesto en /home/pi dos ficheros, uno para la tabla archive y el otro para el dump de la base de datos total.
cd /home/pi
sqlite3 /var/lib/weewx/weewx.sdb .dump > dump_backup.txt


y el otro:
cd /home/pi
sqlite3 /var/lib/weewx/weewx.sdb 'select * from archive;' > dump_archive.txt

A partir de aquí cada cual que piense y haga lo que decida... copia física, copia lógica o ambas o ninguna.

[Xavi-EA5ZF]

He encontrado una posible solución a la corrupción de datos de la BBDD de weewx.
Aparte de una buena y sana politica de realización de copias de la BBDD, adjuntaré un script para sanear la BBDD que al menos a mí me ha ayudado a volver a tener la BBDD operativa.
En mi caso no se ha perdido ningún dato, pero nunca está de mas tras la recuperación hacer algunas pruebas y cerciorarse de la continuidad de los datos almacenados.
El script es este;

#!/bin/sh
#
# repairsdb.sh
#
# requires sqlite3 (apt install sqlite3)
# adjust following line with path/file.sdb
DBFILE="/var/lib/weewx/weewx.sdb"
# dump
echo "Dump corrupted sqlite file $DBFILE to $DBFILE.dump.gz"
echo '.dump' | sqlite3 $DBFILE | gzip -c >$DBFILE.dump.gz
# backup sdb
echo "Rename corrupted sqlite file $DBFILE to $DBFILE.backup"
mv $DBFILE $DBFILE.backup
# rebuild sdb from dump
echo "Building proper sqlite file $DBFILE from dump $DBFILE.dump.gz"
zcat $DBFILE.dump.gz | sqlite3 $DBFILE
echo "Done !"

Espero que sea de utilidad.

Adjunto también la utilidad de dump de la BBDD:

#!/bin/sh
#
# dump_archive.sh
#
# requires sqlite3 (apt install sqlite3)
# adjust following line with path/file.sdb
DBFILE="/var/lib/weewx/weewx.sdb"
# dump
echo "Dump BBDD archive file $DBFILE to $DBFILE.dump.gz"
echo '.dump' | sqlite3 $DBFILE | gzip -c >$DBFILE.dump.gz
mv $DBFILE.dump.gz /home/pi/

Y la de exportación de todos los datos...

cd /home/pi
# sqlite3 /var/lib/weewx/weewx.sdb 'select * from archive;' > dump_archive.txt
DBFILE="/var/lib/weewx/weewx.sdb"
# dump
echo "Dump sqlite archive file $DBFILE to $DBFILE.dump.gz"
echo 'select * from archive;' | sqlite3 $DBFILE | gzip -c >/home/pi/dump_archive.txt.gz

[Chema]

hola
continuando un poco el tema de este hilo y evitar abrir o duplicar otro, me gustaría saber si es posible (explicado paso a paso) que WeeWX haga la mayor parte de sus operaciones en un USB externo (actualizar la BD básicamente y otras operaciones que requieran escritura/lectura de forma asidua ) y así reducir los ciclos de escritura/lectura en la tarjeta SD.

Lo pregunto porque tras seis meses de uso 24/7 de la Raspberry, está empezando a comportarse de forma 'extraña' (pierde la conexión del router de forma imprevisible tardando en volver a estar online minutos o incluso horas) y he pensado que puede ser cosa de la propia tarjeta SD que aunque sólo está al 10% quizás la esté penalizando tanto acceso de escritura/lectura.

saludos.

[jmviper]

Hola

Yo tengo weewx grabando a base de datos y generando los archivos del skin cada minuto casi dos años y aparentemente no encuentro nada raro en la SD ni en el funcionamiento del SO...

Puedes hacer que weewx cree la base de datos en otro lugar como un pendrive externo configurándolo en weewx.conf


[DatabaseTypes]
   
    # Defaults for SQLite databases
    [[SQLite]]
        driver = weedb.sqlite
        # Directory in which the database files are located
        SQLITE_ROOT = /var/lib/weewx

En la parte en negrita pones la dirección del pendrive y ten en cuenta que tendrá que ser montado al iniciar la raspi.

[Chema]

Hola

Yo tengo weewx grabando a base de datos y generando los archivos del skin cada minuto casi dos años y aparentemente no encuentro nada raro en la SD ni en el funcionamiento del SO...

Puedes hacer que weewx cree la base de datos en otro lugar como un pendrive externo configurándolo en weewx.conf


[DatabaseTypes]
   
    # Defaults for SQLite databases
    [[SQLite]]
        driver = weedb.sqlite
        # Directory in which the database files are located
        SQLITE_ROOT = /var/lib/weewx

En la parte en negrita pones la dirección del pendrive y ten en cuenta que tendrá que ser montado al iniciar la raspi.

Gracias!quizás es probable que no sea tema de la SD, no sé, la verdad es que me está dando algún que otro dolor de cabeza el asunto pero ya no sé qué pensar.... de todas formas probaré tu consejo.

[PacoJavi]

Hola,

Yo lo tengo montado de una manera parecida a la que tu comentas.
En toda SD con raspbian hay 2 particiones: "/boot" y "/root". La primera unicamente (a grosso modo) sirve para arrancar el sistema operativo, mientras que la segunda es la partición que contiene el SO propiamente dicho, y es aquí donde se realiza toda escritura/borrado.

Para que estos ciclos de escritura/borrado sean hechos en un usb externo, tienes 2 opciones: o poner raspbian en un usb externo con ambas particiones y usar solo este, sin SD, o indicarle a la SD que el sistema operativo donde ha de escribir (/root) se encuentra en un usb externo. Es decir, usar la SD para arrancar la raspi y funcionar con el USB externo.
En este último caso, en caso de que fallase el USB, solo habría que cambiar el identificador del mismo en la SD y poner uno nuevo. Y a funcionar.

Yo tengo 2 sistemas funcionando de este modo y de momento van de perlas (voy a tocar madera por si acaso). Ambos llevan casi 2 años funcionando sin problemas.

Aquí tienes un tutorial paso a paso para que veas como se hace, por si te interesa: http://www.kupply.com/move-your-raspberry-pi-system-to-usb-in-10-steps/

[jmviper]

Hola PacoJavi

Una puntualización, /root es la carpeta personal del usuario root al igual que /home/pi es la carpeta personal del usuario pi.

De donde salen todas las particiones es de "/" que es la carpeta raíz (root) del sistema y del que penden todas ellas. Se puede ver con ls -l / en el terminal.

Otras particiones del sistema se pueden ver también con df -h

Como dije antes, poniendo la BBDD de weewx en el USB no hace falta poner todo el SO en él. No tiene por que haber problema en que el SO esté en la tarjeta micro SD.

Saludos

[Chema]

Holauna cuestión rápida ¿cómo puedo añadir registros a mi actual base referidos al año pasado? lo pregunto porque tras el 'apagón' de estos días he estado 4 días sin datos (los primeros del año) hasta que ha empezado a funcionar la cosa pudiendo recuperar, hoy día 9, los registros guardados en la Davis desde el día 4. El tema es que tengo la BBDD 'antigua' la del año pasado en copia de seguridad y me gustaría poder añadir todos sus registros a esta nueva BBDD. ¿es posible?

[jmviper]

Hola JosMar

Veamos... pon la BBDD antigua en /var/lib/weewx con otro nombre que no sea weewx.sdb y sitúate en ese directorio

Detén weewx y haz copia de seguridad de las dos BBDD que tienes a otro directorio y ejecuta estos comandos:


sudo sqlite3 basededatos1.sdb .dump > temporal.sdb

sudo sqlite3 basededatos2.sdb .dump >> temporal.sdb

sudo sqlite3 weewx.sdb .dump < temporal.sdb

sudo rm temporal.sdb

Pon en marcha weewx a ver cómo va.

basededatos1.sdb y basededatos2.sdb serán las BBDD a unir

Tardará su tiempo en cada operación y puede que dé errores al unirlas.

Lo importante es hacer copia de seguridad de ambas por si saliera mal la operación.

[jmviper]

He modificado el mensaje anterior ya que había puesto las BBDD con extensión .sql en vez de extensión .sdb

[jantoni]

Ummmmm........

No sé si este método funcionará. El comando .dump, al menos lo que yo recuerdo vagamente, vuelca la estructura de una base de datos en un fichero de texto, pero no recuerdo que sirva para insertar datos en una tabla o base da datos.

Tendría que revisar la documentación.

El modo más seguro es el de “insert into tabladedestino select from tabladeorigen”

No obstante, aunque funcione un método u otro, preveo muchos dolores de cabeza con las inconsistencias.

[jantoni]

De todos modos.....la premisa es....

Tocad la base de datos lo menos posible.

De ese modo weewx lleva funcionando 6 años en mi vieja raspberry Pi sin ningún tipo de problema

[jmviper]

No sé si este método funcionará. El comando .dump, al menos lo que yo recuerdo vagamente, vuelca la estructura de una base de datos en un fichero de texto, pero no recuerdo que sirva para insertar datos en una tabla o base da datos.

.dump vuelca la estructura y datos para ser insertados en pantalla o a un archivo de texto como dices (> archivodump) de manera que queda con los comandos para ser volcados a una nueva base de datos.

Esto sería el principio de ese fichero de volcado:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `barometer` REAL, `pressure` REAL, `altimeter` REAL, `inTemp` REAL, `outTemp` REAL, `inHumidity` REAL, `outHumidity` REAL, `windSpeed` REAL, `windDir` REAL, `windGust` REAL, `windGustDir` REAL, `rainRate` REAL, `rain` REAL, `dewpoint` REAL, `windchill` REAL, `heatindex` REAL, `ET` REAL, `radiation` REAL, `UV` REAL, `extraTemp1` REAL, `extraTemp2` REAL, `extraTemp3` REAL, `soilTemp1` REAL, `soilTemp2` REAL, `soilTemp3` REAL, `soilTemp4` REAL, `leafTemp1` REAL, `leafTemp2` REAL, `extraHumid1` REAL, `extraHumid2` REAL, `soilMoist1` REAL, `soilMoist2` REAL, `soilMoist3` REAL, `soilMoist4` REAL, `leafWet1` REAL, `leafWet2` REAL, `rxCheckPercent` REAL, `txBatteryStatus` REAL, `consBatteryVoltage` REAL, `hail` REAL, `hailRate` REAL, `heatingTemp` REAL, `heatingVoltage` REAL, `supplyVoltage` REAL, `referenceVoltage` REAL, `windBatteryStatus` REAL, `rainBatteryStatus` REAL, `outTempBatteryStatus` REAL, `inTempBatteryStatus` REAL);
INSERT INTO archive VALUES(1577833200,16,1,1033.9184870972262242,1019.0710743604474153,1033.3511365830867489,12.5,7.6111111111111124927,64.000000000000003552,88.000000000000007105,0.96561120001789539912,64.555805166451261101,3.2187040000596525146,60.999999999999996447,0.0,0.0,5.7513713696524799701,7.6111111111111124927,7.6111111111111124927,0.0,0.0,0.0,7.600000000000001421,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,88.09999999999998721,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,5.0499999999999998223,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO archive VALUES(1577833260,16,1,1033.9269017587111587,1019.0790431527625425,1033.3591975949421026,12.5,7.5555555555555526936,64.000000000000003552,87.787878787878774744,1.3167425454789487559,75.18549021140406019,3.2187040000596525146,67.000000000000001776,0.0,0.0,5.6616930498190027876,7.5555555555555526936,7.5555555555555526936,0.0,0.0,0.0,7.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,88.200000000000002842,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,5.0499999999999998223,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

…………….


Crea las tablas e inserta los valores. La comodidad es que puedes editar los valores, copiar y pegar los que quieras o no etc etc con un editor de texto.

Luego se vuelca a la base de datos que se quiera con bbddnueva < archivodump.

Lo más importante de la BBDD de weewx es la tabla archive que son los datos de la estación. Siempre que se tenga se pueden quitar las tablas de diarios (drop-daily) o regenerarlos (rebuild-daily).

Lo normal es lo de insert into etc etc... pero con dump también se puede hacer y sobre todo para unir dos BBDD como he puesto antes. A fin de cuentas es como exportar una base de datos de MySQL con phpmyadmin o con mysqldump en la línea de comandos.