ACP with Mariadb

Page is under construction, but should be complete enough to use for dev environment.

Steps to setup the ACP database for use with Mariadb

Install MariaDB server

download Mariadb 10.6.2 (Current tested version).
Launch the installer - accept defaults including Heidisql
Set a new root password when prompted
Check allow remote access for root for now.
Install as service - service name Mariadb
Enable networking - port 3306
Finish installing with defaults

Resetting MariaDB Root Password on Windows Server

If you have forgotten the root password for MariaDB on a Windows server, follow these steps to reset it:

1. Stop the MariaDB Service

Open **Services** (search for "Services" in the Start menu).
Find the **MariaDB** service.
Right-click the service and select **Stop**.

2. Start MariaDB in Safe Mode

Open **Command Prompt** or **PowerShell** with administrative privileges.
Navigate to the MariaDB installation folder, typically:

C:\Program Files\MariaDB 10.x\bin

mysqld.exe --skip-grant-tables

3. Log in Without a Password

Open a new Command Prompt.
Log in to MariaDB:

Open Command Prompt.

Navigate to the directory where MariaDB is installed. Typically, it is located at: C:\Program Files\MariaDB 10.x\bin

mysql -u root

If you get the error: 'mysql' is not recognized as an internal or external command, operable program or batch file.

Add MySQL/MariaDB to the System PATH

Press Win + R, type sysdm.cpl, and press Enter. Go to the Advanced tab and click on Environment Variables. Under System variables, find the Path variable, select it, and click Edit. Click New and add the full path to the bin folder, e.g.:

C:\Program Files\MariaDB 10.x\bin Click OK to close all dialog boxes.

4. Reset the Root Password

Switch to the `mysql` database:

```sql USE mysql;

UPDATE user SET password = PASSWORD('new_password') WHERE user = 'root'; FLUSH PRIVILEGES;

For MariaDB 10.4 and above:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; FLUSH PRIVILEGES;

5. Restart the MariaDB Service

Close the terminal running MariaDB in safe mode. Go back to Services. Right-click the MariaDB service and select Start.

6. Test the New Password

Open Command Prompt. Log in to MariaDB with the new password: mysql -u root -p Enter the new password when prompted.

Setup HeidiSQL

Optional - Allows you to use a GUI, but all can be done with the command prompt.

user: For now I have left the user as root because it is fewer steps and this is not yet for production.

Launch HeidiSQL

(You need to start a session too create a database)

Click new session at bottom left - then you can leave all as default for now
- Network type Mariadb or Mysql (default)
- Library Libmaria.dll (default)
- Hostname / IP 127.0.0.1 (default)
- leave unchecked prompt for credentials and Windows authentication (default)
- User: root
- Password - enter the password for root
- port 3306
- leave the rest default and click [Open]

Expected: New session starts - there are no user databases yet

Add a new database

In HeidiSQL right click on the session name (Unnamed unless you named it) > Select Create New > > Database > suan_subscriptions > Press [OK]

Install the MariaDB ODBC connector

Download the MariaDB ODBC 3.1 connector (still using 3.1 for now since know it works).
Launch the msi file and install with typical option

Continue with the setup

If you are setting up a production server, return to Ana:Suan/Steps_to_set_up_a_production_server#SSL certificate and continue the setup. For a development server you may be able to continue.

Configure the ACP Assets

Add the Suan assets models with Mariadb compatible MYSQL calls to the UI folder.

Add tables to the databse

Edit the DB driver info.ana library

In the library "*\ui\assets\DB driver info.ana"
- Ensure MariaDB 3.1 is selected in the pulldown menu for the Database driver
Enter 'suan_subscriptions' in the Database text input
Enter your username ('root' for now) in the myusername text input
Enter the password for the user 'root' in the mypassword text input
Save the library with the new settings -Select File form the top menu and click save.

Create and populate the tables

Open the model file "*\acp\ui\assets\Create Suan DB.ana"
Press the "Create the Tables" button
Press the "Populate the Tables" button

Expected: No errors - there will not be a message if the tables are successfully populated.

Check the database
- Using Heid SQL, select the suan_subscriptions database and insure the tables are present. You may need to right click and select refresh to see the tables. Select a table and click the data tab to ensure the table is populated with the correct schema and initial data. if the data tab is not present you can double a click a table to ensure it shows the first time.

Return to the setup page

You can return to the instructions for setting up a dev environment or production environment and use the database, as long as the assets are the mariadb assets.