Difference between revisions of "ACP with Mariadb"
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
'''''<font color=blue>Page is under construction</font>'''''
+
'''''<font color=blue>Page is under construction, but should be complete enough to use for dev environment. </font>'''''
  
 
Steps to setup the ACP database for use with Mariadb
 
Steps to setup the ACP database for use with Mariadb
Line 11: Line 11:
 
*Enable networking - port 3306
 
*Enable networking - port 3306
 
*Finish installing with defaults
 
*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:
 +
'''<code>C:\Program Files\MariaDB 10.x\bin</code>'''<br />
 +
* Enter
 +
'''<code>mysql -u root</code>'''
 +
 +
(If you get the error: <nowiki>''</nowiki> 'mysql' is not recognized as an internal or external command,
 +
operable program or batch file.<nowiki>''</nowiki>)
 +
 +
* 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.:
 +
 +
'''<code>C:\Program Files\MariaDB 10.x\bin</code>'''
 +
* Click OK to close all dialog boxes.
 +
 +
=== 4. Reset the Root Password ===
 +
* Switch to the `mysql` database:
 +
 +
<code>USE mysql;</code>
 +
 +
For MariaDB 10.4 and above:
 +
 +
<code>ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
 +
FLUSH PRIVILEGES;</code>
 +
 +
(For earlier than MariaDB 10.4)
 +
<code>UPDATE user SET password = PASSWORD('new_password') WHERE user = 'root';
 +
FLUSH PRIVILEGES;</code>
 +
 +
=== 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===
 
===Setup HeidiSQL===
−
''Optional'' - Allows you to use a GUI, but all can be done with the command prompt. *Launch 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)
 
(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  
 
*Click new session at bottom left - then you can leave all as default for now  
Line 24: Line 91:
 
**leave the rest default and click [Open]  
 
**leave the rest default and click [Open]  
 
'''Expected: New session starts - there are no user databases yet'''
 
'''Expected: New session starts - there are no user databases yet'''
 +
 
===Add a new database===
 
===Add a new database===
 
*In HeidiSQL right click on the session name ('''Unnamed''' unless you named it) > Select '''<code>Create New ></code>''' > '''<code>Database</code>''' > '''<code>suan_subscriptions</code>''' > Press '''<code>[OK]</code>'''
 
*In HeidiSQL right click on the session name ('''Unnamed''' unless you named it) > Select '''<code>Create New ></code>''' > '''<code>Database</code>''' > '''<code>suan_subscriptions</code>''' > Press '''<code>[OK]</code>'''

Latest revision as of 11:51, 20 January 2025

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

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

2. Start MariaDB in Safe Mode

  1. Open **Command Prompt** or **PowerShell** with administrative privileges.
  2. 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

  • Enter

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:

USE mysql;

For MariaDB 10.4 and above:

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

(For earlier than MariaDB 10.4) UPDATE user SET password = PASSWORD('new_password') WHERE user = 'root'; 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.

Comments


You are not allowed to post comments.