Community
OpenEnergyMonitor

Community

Can't connect to local MySQL server


(Julio Soto) #1

Hi there!

Just yesteday my emonSD-26Oct17 decided to stop working properly.

Realised I had a problem when feeds were not updating and cannot connect to the web interface getting the message:

Can’t connect to database, please verify credentials/configuration in settings.php
Error message: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

Searched for similar topics and most of them were related to lack of disk space, what that not seems to be my problem:

Filesystem Size Used Avail Use% Mounted on
/dev/root 3.4G 3.1G 172M 95% /
devtmpfs 481M 0 481M 0% /dev
tmpfs 486M 0 486M 0% /dev/shm
tmpfs 486M 6.7M 479M 2% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 486M 0 486M 0% /sys/fs/cgroup
tmpfs 40M 6.6M 34M 17% /var/lib/openhab
tmpfs 1.0M 0 1.0M 0% /var/lib/dhcpcd5
tmpfs 1.0M 0 1.0M 0% /var/lib/dhcp
tmpfs 50M 376K 50M 1% /var/log
tmpfs 1.0M 0 1.0M 0% /var/tmp
tmpfs 30M 60K 30M 1% /tmp
/dev/mmcblk0p3 3.8G 54M 3.6G 2% /home/pi/data
/dev/mmcblk0p1 60M 22M 39M 37% /boot
tmpfs 98M 0 98M 0% /run/user/1000

if I try to manually start mysql server by ssh: sudo /etc/init.d/mysql start I get a:

[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.
 failed!

Also a systemctl status mysql.service returns a:

systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since Mon 2018-12-31 15:28:01 CET; 32s ago
Process: 17981 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Dec 31 15:28:01 emonpi mysql[17981]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Dec 31 15:28:01 emonpi systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 31 15:28:01 emonpi systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Dec 31 15:28:01 emonpi systemd[1]: Unit mysql.service entered failed state.

And also

journalctl -xn
– Logs begin at Mon 2018-12-31 14:52:07 CET, end at Mon 2018-12-31 15:29:16 CET. –
Dec 31 15:29:16 emonpi mqtt_input[21020]: #2 {main}
Dec 31 15:29:16 emonpi mqtt_input[21020]: thrown in /var/www/emoncms/scripts/phpmqtt_input.php on line 61
Dec 31 15:29:16 emonpi mqtt_input[21020]: Fatal error: Uncaught exception ‘ErrorException’ with message 'mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL s
Dec 31 15:29:16 emonpi mqtt_input[21020]: Stack trace:
Dec 31 15:29:16 emonpi mqtt_input[21020]: #0 [internal function]: exceptions_error_handler(2, ‘mysqli::mysqli(…’, ‘/var/www/emoncm…’, 61, Array)
Dec 31 15:29:16 emonpi mqtt_input[21020]: #1 /var/www/emoncms/scripts/phpmqtt_input.php(61): mysqli->mysqli(‘localhost’, ‘emoncms’, ‘emonpiemoncmsmy…’, ‘emoncms’, '3
Dec 31 15:29:16 emonpi mqtt_input[21020]: #2 {main}
Dec 31 15:29:16 emonpi mqtt_input[21020]: thrown in /var/www/emoncms/scripts/phpmqtt_input.php on line 61
Dec 31 15:29:16 emonpi systemd[1]: mqtt_input.service: main process exited, code=exited, status=255/n/a
Dec 31 15:29:16 emonpi systemd[1]: Unit mqtt_input.service entered failed state.
lines 1-11/11 (END)

By the way, so not use mqtt on any way…

Finally when I look at the logs,

/var/log/mysql.log is empty
while
/var/log/mysql/error.log provides the following that may give you info but it seems to be too much for me…

181231 15:27:28 mysqld_safe Logging to ‘/var/log/mysql/error.log’.
181231 15:27:28 mysqld_safe Starting mysqld daemon with databases from /home/pi/data/mysql
181231 15:27:29 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
181231 15:27:29 [Note] /usr/sbin/mysqld (mysqld 5.5.62-0+deb8u1) starting as process 18393 …
181231 15:27:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
181231 15:27:29 [Note] Plugin ‘FEDERATED’ is disabled.
181231 15:27:29 InnoDB: The InnoDB memory heap is disabled
181231 15:27:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
181231 15:27:29 InnoDB: Compressed tables use zlib 1.2.8
181231 15:27:29 InnoDB: Using Linux native AIO
181231 15:27:29 InnoDB: Initializing buffer pool, size = 128.0M
181231 15:27:29 InnoDB: Completed initialization of buffer pool
181231 15:27:29 InnoDB: highest supported file format is Barracuda.
181231 15:27:29 InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 536870912:2147483688, should be 0:56!
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 56.
InnoDB: You may have to recover from a backup.
181231 15:27:29 InnoDB: Page dump in ascii and hex (16384 bytes):
[HERE THE HEX DUMP TOO LONG TO POST]
InnoDB: End of page dump
181231 15:27:29 InnoDB: Page checksum 1946727932, prior-to-4.0.14-form checksum 206875172
InnoDB: stored checksum 541252215, prior-to-4.0.14-form stored checksum 794500790
InnoDB: Page lsn 0 183009, low 4 bytes of lsn at page end 183009
InnoDB: Page number (if stored to page already) 2147483688,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 536870912
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 56.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
181231 15:27:29 InnoDB: Assertion failure in thread 1992073744 in file buf0buf.c line 3623
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:27:29 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346093 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 0 thread_stack 0x30000
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
181231 15:27:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Someone can point me on the right direction to solve it?

Thanks a lot and happy New Year!

Julio


(Julio Soto) #2

Just to clarify the above:
I do not use MQTT and will never use in the next future. Therefore I pay no attention to the MQTT errors; in fact I purged all the MQTT packages.

Thank you


(Julio Soto) #3

Update:
Following several instructions, in particular the one found here
Added in /etc/mysql/my.cnf the following line under [mysqld]
innodb_force_recovery=n (where n may vary from 1 to 6)

Tried after each change to mannually bring up mysqld by doing

/usr/sbin/mysqld

With no luck at all :frowning:

Just in order to provide more info, my /home/pi/data/mysql/ contains the following:

ls -la
total 28795
drwx------ 5 mysql mysql 1024 Jan 2 15:58 .
drwxrwxrwx 14 pi pi 1024 Jul 23 09:38 …
-rw-r–r-- 1 root root 0 Jan 26 2016 debian-5.5.flag
drwx------ 2 mysql mysql 1024 Jul 13 14:28 emoncms
-rw-rw---- 1 mysql mysql 18874368 Dec 31 14:52 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jan 2 15:58 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jan 26 2016 ib_logfile1
drwx------ 2 mysql root 3072 Dec 31 14:48 mysql
-rw------- 1 root root 6 Dec 31 14:48 mysql_upgrade_info
drwx------ 2 mysql mysql 1024 Dec 31 14:47 performance_schema


(Trystan Lea) #5

Hello @Jujonet, O dear!

I notice it says:

InnoDB: Database page corruption on disk or a failed

There are some suggestions here that may be useful:

http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/

“Add innodb_force_recovery=1 to your my.cnf”

similar to suggestion here: https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html.


(Julio Soto) #6

Yeap. Did it but stucked in:

Add  `innodb_force_recovery=1`  to your my.cnf In case your server doesn’t want to come back, you may further increase this number from  **1**  to  **6** , check MySQL [manual](http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html) to see what the differences are.
[...]

Now you have your database back up and running, but in  **recovery mode** .[...]

No matter what I was doing; mysql is not running.

Finally had to restore a full backup from months ago, and assume I had lost data (not a big deal at the end of the day as it is only my home water usage).

Edit: Guess must be something related to physical damage in the SD Card; courius as it is only 8 months old… Need to check the old card when I have time…

Anycase thanks for the answer and for the great job done!

Reagrds


(Trystan Lea) #7

Good to hear you have it back up and running from a backup. Did you try increasing “innodb_force_recovery” up to 6?


(Julio Soto) #8

Yes.
Started at 1 and increased to 6 (after backuping my /home/pi/data)
No luck; mysql never went up :frowning: