ejfield
Posts: 17
Joined: Fri Oct 19, 2012 10:21 am

What is MySQL doing?

Fri Jun 10, 2016 8:33 am

I have a system based on a RPi, which runs, amongst other things, Apache and MySQL. The Web Server front end collects data and writes it into the MySQL database (relatively infrequently). A compiled 'C' program runs in an infinite loop in the background, reading the data out of the MySQL database.

After being used for a few hours, the user just switches off the power to the whole system. I am confident that at this time no User Data is being written into the database.

however, very occasionally, on startup, the C program will run for a few seconds, then crash. I'm beginning to suspect that this is due to the MySQL database being corrupted. (OK, my error handling is non-existent, but the system is headless anyway so there's not much I can do!).

So my question, or questions, are:
1. Could READING from the database at the time of power-off cause database corruption ?
2. Or, Is it possible that the MySQL server is doing something in the background on my database, which could cause corruption if the power suddenly goes off?

Any information welcome!
Ed

User avatar
davidcoton
Posts: 5150
Joined: Mon Sep 01, 2014 2:37 pm
Location: Cambridge, UK
Contact: Website

Re: What is MySQL doing?

Fri Jun 10, 2016 8:52 am

ejfield wrote:1. Could READING from the database at the time of power-off cause database corruption ?
I'm no SQL expert, but in and of itself, unlikely.
ejfield wrote:2. Or, Is it possible that the MySQL server is doing something in the background on my database, which could cause corruption if the power suddenly goes off?
This is possible, but again, I'm no expert.

HOWEVER, it is far more likely that the storage device (if either SD or USB flash) or the OS is doing something. The OS will do regular writes for its housekeeping. Storage devices do something called "wear levelling" to try to prolong device life. Switching off during these can do anything from file corruption to permanent storage hardware destruction.

Make sure there is some way to shut the system down properly before switching off. There are hardware systems available to help with this, though I haven't tried them (my use case is to keep a Pi running 24/7, with safe shutdown on mains power fail and automatic restart on power restoration).
Location: 345th cell on the right of the 210th row of L2 cache

Romonga
Posts: 123
Joined: Mon May 09, 2016 7:09 pm
Location: Montgomery Il

Re: What is MySQL doing?

Fri Jun 10, 2016 11:40 am

Any database system can see corruption with a hard power off of the device. Just because you might be in read mode, does not mean that the database is not doing work to maintain indexes and other important house keeping functions.

Not only can corruption happen to the database server, you can corrupt your Pi and leave it in a non-working state.

What I suggest is we examine why you need to power the device off and find a solution to that. Or, set up a CRON job to reboot the pi on some type of schedule.
You can run, but you will only die tired.

Romonga
Posts: 123
Joined: Mon May 09, 2016 7:09 pm
Location: Montgomery Il

Re: What is MySQL doing?

Fri Jun 10, 2016 2:48 pm

Sorry, Posted a reply and did not offer any form of help. Please review this link, it has some steps you can use to attempt to check if corruption is the cause and might fix it.

http://chepri.com/our-blog/mysql-innodb ... -recovery/
You can run, but you will only die tired.

mfa298
Posts: 1386
Joined: Tue Apr 22, 2014 11:18 am

Re: What is MySQL doing?

Mon Jun 13, 2016 10:02 am

ejfield wrote:I have a system based on a RPi, which runs, amongst other things, Apache and MySQL. The Web Server front end collects data and writes it into the MySQL database (relatively infrequently). A compiled 'C' program runs in an infinite loop in the background, reading the data out of the MySQL database.

After being used for a few hours, the user just switches off the power to the whole system. I am confident that at this time no User Data is being written into the database.
Even though you you're not writing data to the database MySQL will keep the database open (and has a record that it's running). If you just turn off the power MySQL will then have to go through a database recovery procedure which can take some time when it's next started up.

Also just killing the power to the Pi can lead to file system corruption (same as can happen with any computer if you just pull the plug out).

The best option is always to shutdown the Pi properly this will shut the database down in a clean state and then shutdown the operating system ensuring that all data is written to the SD card.

ejfield
Posts: 17
Joined: Fri Oct 19, 2012 10:21 am

Re: What is MySQL doing?

Thu Jun 16, 2016 7:59 am

Thanks for all your replies. You all seem to be confirming what I suspected, that MySQL is probably doing some housekeeping in the background, which could be causing the corruption on power-off.

Firstly, a controlled shut-down is not an option. As I mentioned, the system is headless, and the user has no way of initiating a proper shut-down. This is not uncommon in embedded systems, which after all is one of the areas that the RPi is targeted at. In the early days of the Pi, the recommended way of turning it off was to pull the plug out! I've never been comfortable with this though

I'm not worried about the OS itself - I've gone to considerable length to "harden" the system against power-off, and all the partitions on the OS SD card are read-only. The MySQL DB is stored on a USB stick.

I could add a "shutdown" button to the user interface, but I have absolute confidence that the user would "forget" to press it before hitting the off switch. The user would have to initiate the shutdown, then wait for a minute or so before hitting the off switch, which is asking a lot! In any case, this doesn't help with unexpected power-fail, which is part of the problem. I will probably do this anyway.

In "normal" operation, the system is not required to retain any data between runs, so I could simply delete and re-create the database on power-up. That should fix the majority of corruption events. I will probably do this as well.

That just leaves the issue of UNEXPECTED power fail. Ideally, I would want to retain the data in this case, so the system can carry on from where it left off when power is restored. However, this conflicts with the possible solution in the paragraph above.

I have considered implementing a hardware solution, which would have to monitor the power input, switch to some kind of backup and do a controlled shutdown on power fail, and re-start when main power is restored. While not that difficult, it feels like a bit of an overkill. And it adds cost to the system. I'm still considering this.

An interesting problem, thanks for your input.
Ed

mfa298
Posts: 1386
Joined: Tue Apr 22, 2014 11:18 am

Re: What is MySQL doing?

Fri Jun 17, 2016 8:42 am

ejfield wrote:Firstly, a controlled shut-down is not an option. As I mentioned, the system is headless, and the user has no way of initiating a proper shut-down. This is not uncommon in embedded systems, which after all is one of the areas that the RPi is targeted at. In the early days of the Pi, the recommended way of turning it off was to pull the plug out! I've never been comfortable with this though

I'm not sure that just pulling the plug without a normal shutdown was ever recommended. Pulling the plug after shutting the system down was (and still is) one of the easier ways to remove power.
ejfield wrote: I could add a "shutdown" button to the user interface, but I have absolute confidence that the user would "forget" to press it before hitting the off switch. The user would have to initiate the shutdown, then wait for a minute or so before hitting the off switch, which is asking a lot! In any case, this doesn't help with unexpected power-fail, which is part of the problem. I will probably do this anyway.
I would do this as a matter of priority, at least then if they failed to follow the correct procedure you can state the issues are from not following the correct procedure.

Doing this would also mean you can do any database cleanup during shutdown, so if the database exists on startup you know there was a power failure rather than clean shutdown.

A step up from that might be including a micro-controller in the loop with some sort of heartbeat in the loop. User triggers the shutdown via the micro-controller and after the micro-controller detects the heartbeats have stopped it can remove power to the pi (possibly with a short delay) and indicate to the user the system is in standby mode so can be removed from power if required.


ejfield wrote: I have considered implementing a hardware solution, which would have to monitor the power input, switch to some kind of backup and do a controlled shutdown on power fail, and re-start when main power is restored. While not that difficult, it feels like a bit of an overkill. And it adds cost to the system. I'm still considering this.
Other than cost of batteries having something else in the power loop shouldn't cost too much, especially if you factor in the time wasted and support costs from having dealing with an unclean shutdown.

Shea
Posts: 113
Joined: Fri Nov 25, 2011 7:16 pm
Location: Markham, Canada

Re: What is MySQL doing?

Fri Jun 24, 2016 6:38 pm

Headless system shutdown is possible with Apache. You can build a php page with a button to create a file on /tmp, and use crontab to monitor /tmp. Cron can then perform the shutdown when file is found.

Return to “Advanced users”