Import from USB drive - deleted emoncms database

Emoncms Installation
1

I finally took some time to upgrade my old emoncms (emonpi) install to the newest image.

I attempted to import the data from my old install using the import from USB option, it found my old data and began to import, but then almost immediately failed.

The script appears to delete the emoncms SQL database but then doesn’t create a new one and the script ploughs on trying to connect to a missing database.

=== USB Emoncms import start ===
2020-09-19-16:28:50
Backup module version:
    "version"      : "2.2.4"
EUID: 1000
Reading /opt/emoncms/modules/backup/config.cfg....
Location of data databases: /var/opt/emoncms
Location of emonhub.conf: /etc/emonhub
Location of Emoncms: /var/www/emoncms

Scanning for USB card reader:
- Found: /dev/disk/by-id/usb-Generic-_SD_MMC_158F84688461-0:0 at /dev/sda
**Using: /dev/disk/by-id/usb-Generic-_SD_MMC_158F84688461-0:0 at /dev/sda**
- Found: /dev/disk/by-id/usb-Generic-_MicroSD_M2_158F84688461-0:1 at /dev/sdb
**Using: /dev/disk/by-id/usb-Generic-_MicroSD_M2_158F84688461-0:1 at /dev/sdb**
- No card reader found on sdc
Mounting old SD card boot partition
mount: /media/old_sd_boot: special device /dev/sdb1 does not exist.
Mounting old SD card root partition
mount: /media/old_sd_root: special device /dev/sdb2 does not exist.
Mounting old SD card data partition
mount: /media/old_sd_data: special device /dev/sdb3 does not exist.

Stopping services..
Read MYSQL authentication details from settings.php
stopping mysql
Manually deleting old mysql emoncms database
Manual install of emoncms database
could not find mysql database
Setting database ownership
chown: cannot access '/var/lib/mysql/emoncms': No such file or directory
chown: cannot access '/var/lib/mysql/emoncms': No such file or directory
starting mysql
checking database
Updating Emoncms Database..
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /var/www/emoncms/Lib/dbschemasetup.php on line 247

Warning: mysqli::query(): Couldn't fetch mysqli in /var/www/emoncms/Lib/dbschemasetup.php on line 247
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /var/www/emoncms/Lib/dbschemasetup.php on line 247

Update: I used a different USB SD card adapter and that device only shows :

Scanning for USB card reader:
- Found: /dev/disk/by-id/usb-Generic_STORAGE_DEVICE_000000000272-0:0 at /dev/sda
**Using: /dev/disk/by-id/usb-Generic_STORAGE_DEVICE_000000000272-0:0 at /dev/sda**
- No card reader found on sdb
- No card reader found on sdc
Mounting old SD card boot partition
Mounting old SD card root partition
Mounting old SD card data partition

This time the import process appears to be running correctly and has mounted the old partitions correctly.

2

One for @TrystanLea, but a limitation of the script is the selection of the right USB device.

I have suggested this could be selectable from the UI, but that has not been implemented.

3

I hit this same problem today. It is doubly frustrating because each time it fails you have to re-flash the new SD card and start all over again (unless your an expert and can recreate the database and do whatever else is required to recover) - that’s best part of an hour gone each time.

I got round it by editing the usb-import script to only look for the diskname containing sdb, which was where my old SD card happened to be mounted.

Noting @borpin’s idea of allowing the user to select the correct USB device, which I suspect might be a decent challenge to implement and test, as an alternative @TrystanLea is it feasible to have the script test for the existence of /dev/sda1, /dev/sda2 and /dev/sda3 (or sdb* or sdc* as appropriate) as a secondary check before trying to mount them? It seems very unlikely that another (incorrect) disk would have this same arrangement of partitions.

I tried to script it myself but my bash knowledge is not up to the mark.

Supplementary point: the SD-card reader I used has sda/b/c & d, as it’s a multi-format thing. It might be unlikely, but the current script only checks the first 3 of those, so if your SD-card happened to be on /dev/sdd you’d be right out of luck.

4

It shouldn’t be. Other parts of the admin page scans for the tty ports and makes that selectable.

Possibly the trick will be to scan the SD card for a specific directory or folder on each of the mounted partitions.

5

I’ve improved the logic a bit in the script to not delete the mysql database until it has confirmed it has a access to the mysql database on the SD card reader to copy over, see commit: Update usb-import.sh · emoncms/backup@dced193 · GitHub

If it cant access either directories it will stop the script all together

if [ -d /var/lib/mysql/emoncms ]; then
    # Old structure
    if sudo test -d "/media/old_sd_data/mysql/emoncms"; then
        echo "Copying over mysql database from SD card (old structure)"
        sudo rm -rf /var/lib/mysql/emoncms
        sudo cp -rv /media/old_sd_data/mysql/emoncms /var/lib/mysql/emoncms
    # New structure
    elif sudo test -d "/media/old_sd_root/var/lib/mysql/emoncms"; then
        echo "Copying over mysql database from SD card (new structure)"
        sudo rm -rf /var/lib/mysql/emoncms
        sudo cp -rv /media/old_sd_root/var/lib/mysql/emoncms /var/lib/mysql/emoncms
    else
        echo "could not find mysql database"
        exit 1
    fi
fi
6

Any chance of adding a selector dropdown for the USB selection?

7

I think @PeteF’s suggestion of the partition check might be a easier way to go and probably worthwhile anyway. Perhaps alongside a note to suggest only connecting one USB reader/drive at once whilst performing the import process?

8

You can try it and see. As it is the recommended method to migrate, it needs to be more robust.