Aha ok great!
Looks like the setting that was available to provide access to the database update page is no longer working, I will look into that. Your best bet is to run a full system update by running the following on the emonpi command line:
/opt/openenergymonitor/EmonScripts/update/service-runner-update.sh all none
I tried that. It did loads of stuff and says system update done but I still get the same error “Database error, you may need to run database update”. Any ideas?
I don’t mind restoring back to where I was before and then just manually creating my feed definitions (it won’t take me that long).
what do you get if you type the following?
mysqlcheck -c -u emoncms -p emoncms (password emonpiemoncmsmysql2016)
or/and:
mysqlcheck -u emoncms -p --auto-repair --check emoncms
First command I get:
emoncms.app_config
Error : Incorrect information in file: ‘./emoncms/app_config.frm’
error : Corrupt
emoncms.dashboard
Error : Incorrect information in file: ‘./emoncms/dashboard.frm’
error : Corrupt
emoncms.demandshaper OK
emoncms.device OK
emoncms.feeds OK
emoncms.graph
Error : Incorrect file format ‘graph’
error : Corrupt
emoncms.input
Error : Index for table ‘./emoncms/input’ is corrupt; try to repair it
error : Corrupt
emoncms.multigraph
Error : Incorrect file format ‘multigraph’
error : Corrupt
emoncms.postprocess
Error : Index for table ‘./emoncms/postprocess’ is corrupt; try to repair it
error : Corrupt
emoncms.rememberme
Error : Index for table ‘./emoncms/rememberme’ is corrupt; try to repair it
error : Corrupt
emoncms.schedule
Error : Incorrect information in file: ‘./emoncms/schedule.frm’
error : Corrupt
emoncms.setup OK
emoncms.sync
Error : Incorrect information in file: ‘./emoncms/sync.frm’
error : Corrupt
emoncms.users
Error : Incorrect file format ‘users’
error : Corrupt
Second command I get:
emoncms.app_config
Error : Incorrect information in file: ‘./emoncms/app_config.frm’
error : Corrupt
emoncms.dashboard
Error : Incorrect information in file: ‘./emoncms/dashboard.frm’
error : Corrupt
emoncms.demandshaper OK
emoncms.device OK
emoncms.feeds OK
emoncms.graph
Error : Incorrect file format ‘graph’
error : Corrupt
emoncms.input
Error : Index for table ‘./emoncms/input’ is corrupt; try to repair it
error : Corrupt
emoncms.multigraph
Error : Incorrect file format ‘multigraph’
error : Corrupt
emoncms.postprocess
Error : Index for table ‘./emoncms/postprocess’ is corrupt; try to repair it
error : Corrupt
emoncms.rememberme
Error : Index for table ‘./emoncms/rememberme’ is corrupt; try to repair it
error : Corrupt
emoncms.schedule
Error : Incorrect information in file: ‘./emoncms/schedule.frm’
error : Corrupt
emoncms.setup OK
emoncms.sync
Error : Incorrect information in file: ‘./emoncms/sync.frm’
error : Corrupt
emoncms.users
Error : Incorrect file format ‘users’
error : Corrupt
Repairing tables
emoncms.app_config
Error : Incorrect information in file: ‘./emoncms/app_config.frm’
error : Corrupt
emoncms.dashboard
Error : Incorrect information in file: ‘./emoncms/dashboard.frm’
error : Corrupt
emoncms.graph
Error : Incorrect file format ‘graph’
error : Corrupt
emoncms.input
Error : Index for table ‘./emoncms/input’ is corrupt; try to repair it
error : Corrupt
emoncms.multigraph
Error : Incorrect file format ‘multigraph’
error : Corrupt
emoncms.postprocess
Error : Index for table ‘./emoncms/postprocess’ is corrupt; try to repair it
error : Corrupt
emoncms.rememberme
Error : Index for table ‘./emoncms/rememberme’ is corrupt; try to repair it
error : Corrupt
emoncms.schedule
Error : Incorrect information in file: ‘./emoncms/schedule.frm’
error : Corrupt
emoncms.sync
Error : Incorrect information in file: ‘./emoncms/sync.frm’
error : Corrupt
emoncms.users
Error : Incorrect file format ‘users’
error : Corrupt
Ok wow, that’s looks unrecoverable!
You can delete the emoncms database and then re-create, emoncms will do the rest.
The steps are:
mysql -u emoncms -p emoncms (password: emonpiemoncmsmysql2016)
DROP DATABASE emoncms;
then:
CREATE DATABASE emoncms DEFAULT CHARACTER SET utf8;
you may also need:
sudo mysql -e "CREATE USER 'emoncms'@'localhost' IDENTIFIED BY 'emonpiemoncmsmysql2016'; GRANT ALL ON emoncms* TO 'emoncms'@'localhost'; flush privileges;"
I guess that last command needs to be entered outside of the MariaDB? But I don’t know how to get out of that now to issue the command. Here is the log:
Bye
pi@emonpi:~ $ sudo mysql -e "CREATE USER 'emoncms'@'localhost' IDENTIFIED BY 'emonpiemoncmsmysql2016'; GRANT ALL ON emoncms* TO 'emoncms'@'localhost'; flush privileges;"
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'emoncms'@'localhost'
pi@emonpi:~ $ mysql -u emoncms -p emoncms
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5196
Server version: 10.3.29-MariaDB-0+deb10u1 Raspbian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [emoncms]> CREATE DATABASE emoncms DEFAULT CHARACTER SET utf8;
ERROR 1007 (HY000): Can't create database 'emoncms'; database exists
MariaDB [emoncms]> DROP DATABASE emoncms;
ERROR 1010 (HY000): Error dropping database (can't rmdir './emoncms', errno: 39 "Directory not empty")
MariaDB [emoncms]> CREATE DATABASE emoncms DEFAULT CHARACTER SET utf8;
ERROR 1007 (HY000): Can't create database 'emoncms'; database exists
MariaDB [emoncms]> CREATE DATABASE emoncms DEFAULT CHARACTER SET utf8;Ctrl-C -- exit!
Aborted
[Edit]
Please format text output and logs using the Preformatted Text option on the ribbon.
It seems to not be able to delete the database and I’m not sure of the best way to recover from that, plus it may not be a good sign in terms of further problems down the road… I think we should send you a new SD card so that you can start afresh.
Yes please then. Do I need to do something to arrange that?
Thank you
It might be the database on the original SD Card is corrupt.
If the card is new (and I think it is), @Duncan you will need to just flash it with a new image.
@TrystanLea can you just import the data and not the SQL database?
I am trying to flash the card. Etcher doesn’t seem to recognise the card. I have shared the card with linux. Etcher gave me 3 targets but all were roo small to allow me to choose any. I tried reformatting the sd card and now still getting same result. Maybe I just need to buy a new card with image already installed?
Hello @Duncan if you can email our support email at [email protected] with your last order number we will get a new SD card send to you free of charge.