MariaDB only backing up half information...

[ignitenz]

Now this IS frustrating!

I've just noticed the file size has been dramatically decreasing for the past 10 backups.

On the 22nd of Nov the file size was 8Mb and the last save, 2Mb! Then, with RDJ closed I update to the latest (lesser) file and restart RDJ only part of the information is restored! Even restoring the larger file makes no difference.

Only when I retrieve a backup that was transferred back from another PC does it behave, but only up to that last save.

What's going on here? Why does the DB decide seem to randomly save the info it feels like and not everything? Surely by hitting Backup, MariaDB would do just that, in it's entirety?

What am I missing?

Fortunately, I had a recent back up held on another PC, which once transferred and restored seems to be OK. But now when I try to backup, it only saves half the file. IE: 8Mb vs. 4Mb.

Any help would be much appreciated!

[Kai]

Hi,

I can't say anything about the problem you have, that the file size shrinks. But in the past I also had a lot of problems. There were always part missing, so I got a corrupted database after restoring and RadioDJ didn't start.

Since around 1 and a half year I'm using the free version of 'SQLBackupAndFTP' to backup my RadioDJ database and Website Database without any problems. The free version is limited to 2 databases (Also remote databases).
It's very easy to use and you can configure scheduled backups inside the program and decide where to store the backups. Possible locations are FTP, SFTP, FTPS, NAS, local, network folder, OneDrive Personal, Box, Backblaze B2, Yandex.Disk.

I my case the program makes backups of the RadioDJ database every 30 minutes and of the website database once a day. You can choose how many backups will be stored for each database. For example if you choose 30 and there are 30 backups stored, the oldest backup will be overwritten with the new one, so you do't have to delete anything manually.

I also tested restoring the backups several times. 'til now without any problems.

For more information or download you can go to the website of the developer:

https://sqlbackupandftp.com/

Maybe that will be a workaround for you.

[ignitenz]

Thanks Kai.

I'm not sure though how this will help as it seems as if the software you're recommending pretty much initilises the dB backup, which is what Maria does in itself.

The problem then remains. I have tried to save the file to another drive (from USB to C-drive), but this makes little difference. For example, When I re-installed the dB I had installed on another PC, which was near 8Mb and then made some basic changes, I then backed up the same dB which then produced a file of just under 4Mb to the C-drive.

Just tried another USB stick, no change. Still only half the file backed up.

Also updated to the latest Heidi (12.6) and tried again. No change.

So it would appear that I can no longer make changes, even add new music to the dB because the dB fails to save any new info. Worse still, it leaves out other changes previously made.

[stevewa]

Look at mariadb logs and event viewer for error messages about backup

Run a backup command from windows command prompt

mysqldump -u username -p --default-character-set=utf8 -N database > backup.sql

[ignitenz]

Thanks for your help Steve.

There are heaps of errors in the windows event viewer logs for MariaDB, all of them different.

Looking into the repair table though, the only thing which appears to error is in the image. I don't know if it's related, but probably should be corrected. Any idea how?

Cheers.

[ignitenz]

Also, I get this when running the script for sqldump...

[stevewa]

for cmd prompt, you'd need to cd (chg directory) into the folder where mariadb / mysql is

and you'd need to replace "database" with the name of the db to backup, and it will prompt you for your mysql db pwd


for db repairs, yikes, there are too many thing that can go wrong.
that would require you to do a lot of google searching to find out how to change your db engine etc.


you should get a valid backup first.

then create a new db, empty, then attempt to restore into to, but you'd probalby need to edit the backup file first, to remove any references to the db engine etc.


try running manual backup, then look for errors in the event view logs relating to the backup operation.

did some event happen to break your database, ie power outage...?

[ignitenz]

did some event happen to break your database, ie power outage...?

Not that I'm aware of, except one morning this week I checked and found RDJ, RBRecaster (with Stereo Tool plugin) were shut down. It was almost as if the PC had rebooted by itself. The really odd thing was that I had been listening off and on throughout the day, but the reports log stopped recording a day earlier - as did the history log! So, I was listening and there was no history for that whole day.

It could be the PC itself too. when I tried to backup the db to other removable drives, windows didn't recognise them at all when they were plugged in. Three ext drives, none were recognised. Weird. I also had Windows set to autostart RDJ and RBRecaster upon reboot, but that didn't happen if windows had rebooted.

I don't really have the time for indepth problem solving until the new year, so will probably leave until then. Seems to be working fine in the meantime, save not being able to properly backup the dB.

I appreciate your help though.

[ignitenz]

for cmd prompt, you'd need to cd (chg directory) into the folder where mariadb / mysql is

and you'd need to replace "database" with the name of the db to backup, and it will prompt you for your mysql db pwd

Hey @stevewa, now with the festive season done and dusted, I've found some time to deal with this issue.

I changed dir into where the MySQL folder is using CMD, however when I try to execute the command given I get the same message as before that 'mysqldump' is not recogised as an internal or external command, operable program or batch file.

Incidently, under Program Files/MariaDB 11.1, there is no MySQL folder that follows immediately after. There is a MYSQL folder under both /MariaDB 11.1/include, which gives an access denied message; or /MariaDB 11.1/data, which leaves the message I was referring to earlier in this post. Am I even in the right place?

I changed both the database and user credentials in the script, but still the same message.

Any ideas?

[stevewa]

MariaDB 11 uses Maria-dump

https://mariadb.com/kb/en/mariadb-dump/

Do a windows search to find that file on your computer

[ignitenz]

Hey Steve,

mariadb-dump yielded no results using windows search. There were a couple of files that showed when using 'dump' in the search in the Maria 11 folder. I extended the search wider by searching the whole Program Files directory with nothing matching mariadb-dump.

Any other ideas?

[stevewa]

What are the file names containing dump

[stevewa]

look inside C:\program files\mariadb 11.1\bin

that should be where mariadb-dump.exe is located

[ignitenz]

Thanks again Steve.

So I found it this time and I ran:

mariadb-dump -u username -p --default-character-set=utf8 -N radiodj2043 > backup.sql

which returned the message 'access is denied'. I even replaced the user name with root and still access is denied.

[stevewa]

jeez sorry i didn't realize you were a complete beginner here. 

after the -p you need to insert the password to your database for the username specified