RockDawg Posted January 7, 2012 Posted January 7, 2012 I have mysql installed via the unmenu package on my server. I use it for my music and video dbs for XBMC. My problem is that I keep getting screwed up/corrupted dbs somehow. I've been doing a lot of work on my server lately and this has happened 4-5 times. I recently installed some Supermicro drive hot swap drive cages and when I booted it back up my dbs were screwed up. I've also been playing around with upgrading from Unraid 4.5 to 5.14. It happened then too. Because it seems I noticed some serious slowdowns with parity checks, I've downgraded a few times. Each time my dbs are screwed up when I boot back up. Each time via the terminal I ran "killall mysqld" to stop MySQL and I performed a proper shutdown. If I try to view them through HeidiSQL, they either show as completely empty or say a bunch of the table files can't be found. The tables it can find all show a size of 1KB with no rows. It hasn't been a major problem because I have a script that does a daily backup and I've just been restoring the dbs from the backup. It's beginning to frustrate me so I thought I'd ask and see if anybody can help me figure this out. I'm assuming I'm doing something wrong, but I have no idea what. This last time I downgraded to 4.7 and I screwed up and forgot to print out my drive assignments. When I rebooted, Unraid showed all my disks were incorrect. I assigned the correct drive as the Parity drive and since I have run about 5 parity checks in that last week and they all came back fine and because I hadn't written anything to the server today, I performed the "Trust my array procedure". Everything else seems fine, but, of course, my dbs are screwed up again. I have attached the log from that last 4.7 downgrade boot. If I simply kill mysql, stop the array and reboot, the dbs come back fine. I can't figure out what is happening to cause this problem. log.txt
Joe L. Posted January 9, 2012 Posted January 9, 2012 Anyone? stop the database before stopping the array.
prostuff1 Posted January 9, 2012 Posted January 9, 2012 Isn't that what killall mysqld does? No, killall kills the process and may not allow mysql to stop properly. There is probably a "mysqld stop" command or something similar.
RockDawg Posted January 9, 2012 Author Posted January 9, 2012 I'll look into that, but what's weird is that I've always used 'killall mysqld' and the only time I have a problem is when I do something major to Unraid. I have used killall and rebooted many times without problem.
prostuff1 Posted January 9, 2012 Posted January 9, 2012 I'll look into that, but what's weird is that I've always used 'killall mysqld' and the only time I have a problem is when I do something major to Unraid. I have used killall and rebooted many times without problem. You may have just gotten lucky. The DB may have been in a state where issuing the killall did/would not effect it. Other times that could have been a different story.
RockDawg Posted January 11, 2012 Author Posted January 11, 2012 According to what I could find on Google, the command in Slackware should be: /etc/rc.d/rc.mysqld stop When I run that I get an error saying permission denied. I'm logged in as root. Permissions for rc.mysqld show as: -rw-r--r-- 1 root root 2585 Oct 5 2008 rc.mysqld Anybody have any ideas?
prostuff1 Posted January 12, 2012 Posted January 12, 2012 According to what I could find on Google, the command in Slackware should be: /etc/rc.d/rc.mysqld stop When I run that I get an error saying permission denied. I'm logged in as root. Permissions for rc.mysqld show as: -rw-r--r-- 1 root root 2585 Oct 5 2008 rc.mysqld Anybody have any ideas? chmod 775 /etc/rc.d/rc.mysqld then /etc/rc.d/rc.mysqld stop
Zeron Posted January 13, 2012 Posted January 13, 2012 I'll look into that, but what's weird is that I've always used 'killall mysqld' and the only time I have a problem is when I do something major to Unraid. I have used killall and rebooted many times without problem. You may have just gotten lucky. The DB may have been in a state where issuing the killall did/would not effect it. Other times that could have been a different story. killall just send a SIGTERM (unless you tell it otherwise). mySQL qill handle SIGTERM and shutdown cleanly. killall mysqld will not cause database corruption.
Joe L. Posted January 13, 2012 Posted January 13, 2012 I'll look into that, but what's weird is that I've always used 'killall mysqld' and the only time I have a problem is when I do something major to Unraid. I have used killall and rebooted many times without problem. You may have just gotten lucky. The DB may have been in a state where issuing the killall did/would not effect it. Other times that could have been a different story. killall just send a SIGTERM (unless you tell it otherwise). mySQL qill handle SIGTERM and shutdown cleanly. killall mysqld will not cause database corruption. Pretty sure that the rc.stop just uses killall itself. What could cause issues/corruption is if you do not give the database time to shut itself down after sending it the termination signal before shutting down the server. Joe L.
RockDawg Posted January 14, 2012 Author Posted January 14, 2012 Sorry it took so long to get back to this. Been busy. Anyway, I have to killall mysqld because unRAID can't unmount the drive it's using until I do. As soon as the drive unmounts and unRaid lets me, I shut down. Is it possible for the db to not be shutdown, but the drive still unmount?
talkto_menow Posted January 14, 2012 Posted January 14, 2012 I had similar problem couple time. Initially I installed MySQL on Cache drive and then when I had to force shutdown and reattach drives. MySQL was gone or corrupted. XBMC could not connect to tables. Therefore I installed MySQL on disk1. If you take a look at the folders, you will notice that databases and tables are stored separately from MySQL setup/install files, so if you have to force unRaid shutdown, they really do not become corrupted, however MySQL tables that hold users and databases structure can be wiped out. As a result, when you start XBMC, it looks like libraries are gone. To solve this problem. I start unRaid and let it load, then shutdown and start unRaid again. (using LIGHTTP scripted shutdown). If you open unMenu you will see message that some files (2) were synced. Now when I open XBMC everything is back the way it was before, library is fixed.
RockDawg Posted January 14, 2012 Author Posted January 14, 2012 Thanks for the reply. I noticed that the db's are stored in their own folders and based on the size, they looked like everything was still there, but I had no idea if maybe something was corrupted that made that data unusable. While XBMC couldn't read the db, neither could HeidiSQL. So you're saying that parity rebuilt the db files causing the problem? I've seen the LIGHTTP package in unmenu, but I never knew what it was used for so I never installed it. What exactly does it do and is the shutdown script part of it or something you added to it? Is it better than that the powerdown script? Come to think of it, I wonder if the way that poerdown script works if it will corrupt my db on a shutdown. I guess I need to check that.
talkto_menow Posted January 14, 2012 Posted January 14, 2012 Actually parity protects MySQL setup and databases and rebuilds corrupted or missing files. MySQL should be enabled to reinstall after reboot. So when your unRaid is forced to restart, mysql/mysql folder is wiped, and new files are installed. Original settings like user and created databases are gone. Folders containing XBMC tables (xbmc_video & xbmc_video) stay intact. Now when you power up your system, MySQL will be installed again. If you start XBMC you will probably see that Libraries are empty. It's because user 'xbmc' does not exist yet. I found that when you restart unRaid again, parity will fix (sync up) mysql.user table and everything else that was wiped and XBMC can connect to MySQL server. I went through this process at least 10 times, my 2 year old son likes to unplug things around the house. It works every time. So parity solves the problem, and if you think about it, files are really not corrupted. When I had a problem with my Cache drive, I recreated manually user and databases in MySQL, then I copied backup of "xbmc_video" folder to new setup (replace original) and I was able to connect to server using XBMC. Unfortunately, these tables were not the right one, but is possible to restore Libraries this way too. XBMC has a funny way of storing playstate/bookmark/watched information. It is not file path related, instead when you scan for new items, they will receive ID number and everything else relates to it. Now, when your Libraries are gone, there is really no way or recreating bookmarks and watched state. Therefore you should export your libraries to xml file within XBMC. To backup MySQL use command line mysqldump. To do that telnet to your unRaid server and type in: /usr/bin/mysqldump -u root -p mysql > /mnt/user/BackupFolder/mysql.sql This should backup mysql settings (user & databases info) To restore reverse sign "<": /usr/bin/mysqldump -u root -p mysql < /mnt/user/BackupFolder/mysql.sql To backup ALL MySQL tables: /usr/bin/mysqldump -u root -p -A > /mnt/user/BackupFolder/mysqlbackup.sql Please take a look at this free application : MySQL Workbench It has a nice export/import feature
RockDawg Posted January 15, 2012 Author Posted January 15, 2012 Well, I'm sure it's probably the wrong way to do it, but I don't have a user 'XBMC'. I just use 'root'. And if I'm not mistaken, root is always there. Technically, my problem isn't connecting to the db, there's just nothing there when I do or it says table files are missing. I have a script that backs up the dbs every night and keeps 10 daily, weekly and monthly copies. It has saved me quite a few times now because of this problem. I'm trying to figure out how to prevent it rather than recover from it.
talkto_menow Posted January 15, 2012 Posted January 15, 2012 It seems like user "root" is not connecting to proper database and that is why your libraries are empty. Below I included steps for setting up MySQL + XBMC (multiple profiles) on unRaid. I had to repeat this steps on couple occasions and it worked for me each time. Step 1. Install MySQL on unRaid server using unMenu Pkg Manager. Enable re-install upon re-boot Hostname: Tower Disk Label: disk1 MySQL Root Password: (leave empty) First Database: tower Database user password: password Save, install and reboot your machine Step 2. Telnet to unRaid server and create XBMC users Login to MySQL server: /usr/bin/mysql -u root - p Create user: CREATE USER 'xbmc' IDENTIFIED BY 'xbmc'; CREATE USER 'xbmc1' IDENTIFIED BY 'xbmc1'; * user 'xbmc' is for Profile "A" in XBMC **user 'xbmc1'is for Profile "B" in XBMC Grant permissions to users: GRANT ALL ON *.* TO 'xbmc'; GRANT ALL ON *.* TO 'xbmc1'; 3. Create Databases. For Profile "A" CREATE DATABASE xbmc_video; CREATE DATABASE xbmc_music; For Profile "B" CREATE DATABASE xbmc_video1; CREATE DaTABASE xbmc_music1; 4. Start XBMC and create profiles under System/Profiles. Enable login screen Now go to userdata folder. It should be located in: C:\Users\YourUserName\AppData\Roaming\XBMC\userdata For Profile "A" Open Notepad and copy/paste following lines: <advancedsettings> <videodatabase> <type>mysql</type> <host>tower</host> <port>3306</port> <user>xbmc</user> <pass>xbmc</pass> <name>xbmc_video</name> </videodatabase> <musicdatabase> <type>mysql</type> <host>tower</host> <port>3306</port> <user>xbmc</user> <pass>xbmc</pass> <name>xbmc_music</name> </musicdatabase> </advancedsettings> Save this files as advancedsettings.xml and place it in userdata folder For Profiles "B" there should be a folder under \userdata\profiles\profileB Open Notepad and copy/paste following lines: <advancedsettings> <videodatabase> <type>mysql</type> <host>tower</host> <port>3306</port> <user>xbmc1</user> <pass>xbmc1</pass> <name>xbmc_video1</name> </videodatabase> <musicdatabase> <type>mysql</type> <host>tower</host> <port>3306</port> <user>xbmc1</user> <pass>xbmc1</pass> <name>xbmc_music1</name> </musicdatabase> </advancedsettings> Save this files as advancedsettings.xml and place it in \userdata\profiles\profileB folder 5. Start XBMC, there should a liittle delay since XBMC creates necessary tables in MySQL I hope this solves your problem. If you do not want to setup separate profile than just create user 'xbmc', grant permissions and create advancedsettings.xml in userdata folder of XBMC. Do you mind sharing your script for backing up databases?
RockDawg Posted January 18, 2012 Author Posted January 18, 2012 According to what I could find on Google, the command in Slackware should be: /etc/rc.d/rc.mysqld stop When I run that I get an error saying permission denied. I'm logged in as root. Permissions for rc.mysqld show as: -rw-r--r-- 1 root root 2585 Oct 5 2008 rc.mysqld Anybody have any ideas? chmod 775 /etc/rc.d/rc.mysqld then /etc/rc.d/rc.mysqld stop I'm sorry! I completely overlooked this post. I didn't mean to ignore your help. Your commands work to stop mysql, but after a reboot I don;t have permission again. I assume that means I need to put it in the go script. No biggie. One problem though. When I try to start mysql the same way, it doesn't work. Here's what I get: Linux 2.6.32.9-unRAID. root@Tower1:~# /etc/rc.d/rc.mysqld start root@Tower1:~# nohup: redirecting stderr to stdout Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysql/mysql.pid 120117 22:53:51 mysqld ended
soana Posted November 5, 2013 Posted November 5, 2013 How did you solve this issue. I have a unraid 5.0 and get the same error the only way I can start the mysql server is to make changes in the unmenu and re-enable at boot. Linux 2.6.32.9-unRAID. root@Tower1:~# /etc/rc.d/rc.mysqld start root@Tower1:~# nohup: redirecting stderr to stdout Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysql/mysql.pid 120117 22:53:51 mysqld ended
RockDawg Posted November 6, 2013 Author Posted November 6, 2013 If I remember correctly, I just removed MySQL and reinstalled.
lionelhutz Posted November 7, 2013 Posted November 7, 2013 Try the 5.5 version plugin instead of unMENU. I found the 5.5 version works well but the older version was a POS constantly having corrupt table issues. Also, the parity doesn't fix anything on the data drives when unRAID starts so that theory is BS. Just saying don't bother listening to it. I run it on the cache drive just fine.
soana Posted November 7, 2013 Posted November 7, 2013 OK, I'll give it another try. Last month I gave up on the 5.5 (issues with logging in to mysql) but now I have a new setup with a cache drive...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.