Upgrading Mariadb and Switching From Xampp Instance in Windows 11

2023-06-14

If you have got XAMPP installed in your Windows 11 (it could be a dev. machine) and want to switch to an upgraded version as opposed to the one you’ve got bundled with XAMPP, you can follow the below steps so that your are able to leverage the upgrades.

I required the upgrade when I need to use the JSON_ARRAYAGG() function which requires MariaDB 10.5.x.

Once you have downloaded and installed it, add it your our PATH.

Once done, the following commands are to be run inside an Admin powershell. Open the shell and change pth to the location of the install, say: cd "c:\Program Files\MariaDB 10.5\bin"

sc.exe qc MySQL
sc.exe delete MySQL
sc.exe config MySQL binPath= "c:\Program Files\MariaDB 10.5\bin"
.\mysqld  --install
.\mysqld  --initialize
net start MySQL (or start from Windows Services)

If you are getting an error on firing net helpmsg 2185 a workaround could be to copy Database folders from older MySQL folder to the new one to see them in the new install - C:\Program Files\MariaDB 10.5\data as your old data folder might not be compatible with the new one.

This might open up another problem in form of the following error message: Unknown/unsupported storage engine: InnoDB and your data not showing inside the databases. Try to just copy ibdata1 file and see if your data shows up.

Open http://127.0.0.1/phpmyadmin/ in a browser and check for errors

SET PASSWORD FOR 'root'@localhost = PASSWORD("");
CREATE USER 'pma'@'localhost' IDENTIFIED BY '';

In case of error System error 1067 -

If (especially after a system crash or shutdown) you receive the following error and are not able to start MySQL service either from Powershell or Services - rename (or remove) the _ib_logfile0 and retry.

PS C:\Users\Tushar> net start MySQL
The MySQL service is starting...
The MySQL service could not be started.
A system error has occurred.
System error 1067 has occurred.
The process terminated unexpectedly.

Important to Note - you might loose your data by deleting these log, data files and might have to run migrations again. In times such as these, only DB backups can help.