Home / server / Questions / 1146267
Accepted
trejder
Asked: 2023-10-21 01:43:42 +0800 CST

MariaDB on Windows 11 Pro fails due to privilege tables' file problems

  • 772

About 5-6 days after each fresh installation of (the newest) XAMPP 8.2.4 on Windows 11 Pro (10.0.22621 Build 22621), MariaDB 10.4.28 on-board it starts to fail during every run:

Sometimes I am getting an error directly in XAMPP:

Attempting to start MySQL app...
Status change detected: running
Status change detected: stopped
Error: MySQL shutdown unexpectedly.
This may be due to a blocked port, missing dependencies, 
improper privileges, a crash, or a shutdown by another method.

I am a total newbie, but I think there isn't anything "unusual" in mysql_error.log:

[Note] Starting MariaDB 10.4.28-MariaDB source revision c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7 as process 20488
[Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
[Note] InnoDB: Uses event mutexes
[Note] InnoDB: Compressed tables use zlib 1.2.12
[Note] InnoDB: Number of pools: 1
[Note] InnoDB: Using SSE2 crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: 128 out of 128 rollback segments are active.
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file 'C:\XAMPP\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
[Note] InnoDB: File 'C:\XAMPP\mysql\data\ibtmp1' size is now 12 MB.
[Note] InnoDB: 10.4.28 started; log sequence number 1972203; transaction id 2830
[Note] InnoDB: Loading buffer pool(s) from C:\XAMPP\mysql\data\ib_buffer_pool
[Note] Plugin 'FEEDBACK' is disabled.
[Note] Server socket created on IP: '::'.

But the Windows Event Log has seriously puzzling entries.

For each run I am getting either only this:

Aborting
Fatal error: Can't open and lock privilege tables: Incorrect file format 'db'

Or the above followed by:

mysqld.exe: Aria engine: checkpoint failed
mysqld.exe: Can't sync file 'C:\XAMPP\mysql\data\aria_log.00000001' to disk (Errcode: 9 "Bad file descriptor")
mysqld.exe: Error writing file 'C:\XAMPP\mysql\data\aria_log.00000001' (Errcode: 9 "Bad file descriptor")

The "Bad file descriptor" is something that rings the "broken disk" bell in my head, but Windows say no:

enter image description here

(...)
0 bad file records processed.
Phase duration (Bad file record checking): 1.61 milliseconds.
(...)
Windows has scanned the file system and found no problems.
No further action is required.

I tried the "idiot" way of simply deleting the aria_log.00000001. All I achieved was:

  • One time startup of MariaDB
  • Adding extra line to log that aria_log.00000001 was not found
  • Creating and using aria_log.00000002, aria_log.00000003 etc.

But that was just a "virtual" run. All the clients could see that MariaDB is running (XAMPP having Status change detected: running as the last log line and no error), but in fact it was some kind of a weird "lock" that clients cannot detect this fact and hangs without timeout forever:

enter image description here

The same goes for browsers, which shows "circling circle" forever, until I manually kill db.

After that everything goes back to "normal" meaning Error: MySQL shutdown unexpectedly. on each and every run.

I haven't tried the "bruteforce mode" (here). When the serie of "unexpected shutdowns" starts, I am simply:

  • Uninstalling XAMPP (backing up data)
  • Deleting the whole C:\XAMPP folder
  • Doing fresh-install of XAMPP 8.2.4

After this, I am always "good for the next couple of days". Usually five to six.

I've been using XAMPP for Windows for 10+ years, on Windows versions starting XP and never experienced any problems. Until now.

Is there anything that I can do, except for getting rid of XAMPP forever and trying some other LAMP (since re-install doesn't bring anything)?

EDIT: There was no update neither to Maria DB nor the whole server-like system (Apache/XAMPP). Everything sticks on XAMPP 8.2.4 and MariaDB 10.4.28strong text for at least half of year. The only "updates" are my constant sequences of uninstall-install back (did so right now).

EDIT: This is Windows 11 Pro (10.0.22621 Build 22621), so underlying storage system is a single SSD drive (KBG50ZNS512G NVMe KIOXIA 512 GB; 475 GB true capacity). This is a brand new laptop, so the disk has been running for 2,5 weeks so far.

windows

2 Answers

  1. Try the following command to repair the privilege table mysql_upgrade --force -uroot -p

    • 1
  2. Best Answer

    One may consider one of the following solutions.

    Data Backup

    Various reports (here and here) suggests that the solution might be:

    1. Navigate to XAMPP MariaDB directory (c:\xampp\mysql)
    2. Create some backup folder like DataBackup (just in case)
    3. Copy contents of backup and data folders into DataBackup folder
    4. Copy an overwrite all contents of data folder with contents of backup folder
    5. Copy the ibdata1 file from DataBackup\data folder into data overwriting the file
    6. Try to restart MySQL.

    Many users here has reported that the above solutions works. I did not tried it.

    File Fixing

    By a total accident, I have found a file in mysql\bin folder called [username].err where [username] is your Windows username. Its contents showed what was (see below) the true nature of the problem:

    Cannot find checkpoint record at LSN (1,0x5dd7)
[ERROR] Plugin 'Aria' init function returned error.
[ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

    And also has suggested the possible solution:

    [ERROR] mysqld: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files

    I did not tried this solution as well.

    Database Server Upgrade

    Instead of trying any of these two methods I simply updated my MariaDB database version to the newest one.

    This solution required me to do some magic on my MariaDB users because newest MariaDB server is using different authentication method (GSSAPI plugin) instead of the old version (native MySQL authentication).

    Unfortunately this solution also was working for just a 2-3 days and after that MariaDB started crashing with exactly the same behavior, in exactly the same circumstances and without any chances to recover.

    Run it as a Windows System Service

    While reading some more threads, I have figured out someone claiming that "all those *nix-services works more stable when running as a system service in Windows".

    I don't know how much truth there is in this claim, but since XAMPP allows that easily, I have given this solution a try:

    enter image description here

    This solution seems to be working (at least for now).

    Last resort. No stop or pseudo-backup

    If the above solution starts failing as all others then the last chance for me is an old-dirty trick of simply not stopping MariaDB at all and hibernating may dev PC instead of turning it off.

    Sounds crazy, but as one of my friends has proven... works.

    All reports (linked here and all those that I have read) says that problems with MariaDB starts on first or second shutdown. So... let's not let her take even a inch of sleep! :]

    Another "stupid" and "hardcore" solution is to compress entire XAMPP installation when MariaDB still works and then "recreate" it back to that stage, whenever it stops working. Something a bit similar to "hardcore data overwrite" mentioned here.

    For me fortunately this is just a dev laptop where I don't give a damn what data database contains as long as it works. This solution isn't of course for all these scenarios where data is important and cannot be simply overwritten or rolled back couple of days.

    • 0