MySQL and MariaDB mysqld.exe DB corruption

[tegstewart]

I've been having a lot of problems with DB corruption. Originally I was using the version of mysql that came with the one clic setup, but I've since upgraded to MariaDB 10.1.21 and it's still happening.

I thought the issue was that I wasn't cleanly shutting down mysqld.exe when rebooting the system, but I'm now shutting it down via mysqladmin every single time and it's still happening. This morning the user table was corrupted, so I had to run it with the --skip-grant-tables to get access to fix it.

It's nice having to learn how to deal with this stuff since I don't have any formal DB background, but it's also rather tedious. Has anybody else had similar experiences?

I'm running Win10.

[ontheDOL]

i only had this problem once, and i think it was from when i didn't shut it down properly as well.
But now i make sure i shut it down and i've had no issues.

I use UniServer zero XIII for my mysql server and heidisql

ps. i have no experience with dbs either

[tegstewart]

I just noticed that when it's acting up it doesn't respond to shutdown commands, either via mysqladmin or by running SHUTDOWN from SQLyog. I wonder if that might be the issue.

Changing my start_mysql.cmd to use the --skip-grant-tables option keeps that from being an issue, but it's a really hacky solution.

I'll have to give Uniserver a try.

[ontheDOL]

yeah i really dont know too much about it, but Uniserver was on one of the guide pages on here. Pretty simple, lightweight interface , and whenever i've clicked " Stop MySQL" its always worked

[PlanarChaosRvrtwo]

Its pretty simple mysql and mariadb dont merge well i would mention to use hedisql and mariadb

[Velloro]

Odd you are having issues. I'm using MariaDB on Origins for our beta and it's been quite stable, although player load is rather low since it in beta. I don't expect to run into any database issues though.

In your case, perhaps it would have been better to conduct a full database dump to an .sql file using HeidiSQL and fully uninstalling MySQL before installing MariaDB and importing your database using the .sql

If you launch a serious server, regularly scheduled DB backups are an absolute must for maintaining data integrity in the event of any failure. I suggest conducting them several times a week if not daily. It can be a huge PITA when you lose player's hard work.

[tegstewart]

I'm just doing a local server that I'm developing and playing around with. I don't have any plans to make it public, so that's not really a concern.

[Graveen]

Well, exactly:

- the actual DOLSetup.exe is running mysql as a hack, so it can be interesting to install MariaDB or Mysql as a single instance.
- the SQL engine is an important part of the data safety: InnoDb was a transactionnal engine - safe but slow - where MyIsam was rather a speed oriented engine (faaaaast but not so tolerant on hard shutdown). You can toy with this in MariaDB (google what Aria is, iirc it is more like MyIsam)

I'm not exactly sure where the errors came from but i feel - at least on storm - some errors appeared (basically npcequipment not found). I have 2 possibilities:
- when an NPC is created IG, not npce is rattached (so purely dol core issue)
- an issue similar than yours (data corruption on start / shutdown / crash)

I remember putting some tables at InnoDb, but it was really too slow (but i could have did this before inventory revamp :p)

[PlanarChaosRvrtwo]

The gear issue on dol is couse there diffrent entries for same npcequipment template that were once splitted by properties or package id but now at these days it try to load both sets in and fail on second couse allready something inserted you can check by pick an specific id via db you see on error screen.
On other hand storm have in the sourcecode an folder that load some stuff in on each reboot and some of those files creating duplicates also