Updated: November 25, 2020 12:44pm

MySQL Guide

PDF

MySQL can be used as a child POA or a store server, not as the PrismHQ root authority. A common use case for installing the MySQL version is at the store level because a MySQL database requires fewer computing resources than an Oracle database, making it more appropriate for smaller retail operations. Servers at the POA level, assuming they only replicate data to other Prism servers (not to the RIL Oracle database) can be either Oracle or MySQL. (The HQ server must be an Oracle database because it must replicate data back and forth with the RIL or Retail Pro 9 Oracle database).
Prism 1.14.7 and MySQL 5.7/8.0 Compatibility Information
MySQL 5.7 or 8.0 Server files
The current version of MySQL at the time of this document's release is 8.0.22; however, that version is NOT SUPPORTED. The latest supported version is 8.0.21.
Version 5.7.x is also supported.
MySQL is available in 32-bit and 64-bit versions. When downloading the MySQL Installer (.msi) you will see that only a 32-bit version is available. The MySQL Installer itself is 32-bit; however, when you launch the installer, on the Select Products and Features screen, you can select to install 32-bit or 64-bit versions of the MySQL server.
When you install the Prism server (MySQL version), the system verifies that you have a supported MySQL version (either 5.7 or 8.0) installed. If you are currently using MySQL 5.7 and want to upgrade to MySQL 8.0, see the "Migrating from MySQL 5.7 to 8.0" section for more information.
MySQL v5.7 32-bit binary files
When installing the Prism server, you must enter the path to the MySQL 5.7 binary files. These files must be v5.7 and 32-bit (even if running MySQL 8.0). Later in this document you will find information about how to navigate to the MySQL archive to download the correct version of these files.
5.7 64-bit server and 32-bit binary versions should match
If you are using MySQL 5.7, it is strongly recommended that the versions match for the 64-bit MySQL files and 32-bit MySQL binary files. A MySQL 5.7 installation with files from non-matching versions could cause undesired behavior. Some examples of issues that can arise are errors with PrismMQ such as "malformed packet" or "transaction must be active". This restriction does not apply to MySQL 8.0. If you are using MySQL 8.0, it does not matter which version of the 32-bit MySQL 5.7 binary files are used, as long as the 32-bit binaries folder is present somewhere on the system.

Requirements and Considerations
Run special script commands if Custom User is required
If the MySQL installation requires the "CUSTOM" user, a few special commands need to be run on the system (using the "root" user credentials) before installing the 1.14.7 update.
Log into MySQL as the root user and execute the following commands:
grant create user on *.* to 'rps'@'%';
grant reload on *.* to 'rps'@'%';
Running these commands grants the RPS user privileges to create other users (i.e. CUSTOM). In 1.14.6, there was no CUSTOM user and the RPS user didn't have privileges to create other users.

Copy MySQL 5.70 32-bit files to local machine
Same as before, the user must copy the MySQL 5.70 32-bit files to the local machine and enter the path to the files when installing the Prism Server (MySQL version).

Use Legacy Authentication
MySQL 8.0 offers users two authentication methods: a newer method that uses strong password encryption and a legacy method that is less secure but offers compatibility with MySQL 5.x. When installing MySQL 8.0, be sure to select "Use Legacy Authentication Method (retain MySQL 5.x Compatibility)".

Turn off Binary Logging
MySQL 8.0 enables binary logging by default and that can take up a lot of space. We recommend that you disable binary logging by editing the my.ini file and comment out log bin by placing the "#" character at the front of the line.

Basic Steps
These basic steps assume that you have already installed the Point of Authority (POA) and initialized the POA server machine. The basic steps listed here are described in more detail in the sections that follow.
At each MySQL machine:

  1. Download the MySQL files and the Visual C++ redistributables.
  2. Install the Visual C++ redistributables.
  3. Install MySQL using the downloaded .msi file.
  4. Download the Windows 32-bit .zip archive file, extract the contents, and then place the file in the C:\ drive. This folder must be present and referenced when installing the Prism MySQL server.
  5. Install the Prism Server. Choose the MySQL option for the server. During the install, you will point to the locaiton of the Windows 32-bit .zip archive MySQL folder. After the server is installed, install the Prism Proxy. (the Prism/V9 DRS component is not needed)
  6. Join the Prism server to the enterprise.
  7. Initialize the Prism server.

Required Minimum MySQL Version
Prism requires MySQL version 5.7. Please note that this is earlier than the current version (8.0). You will have to navigate to the MySQL archive to download the correct version. MySQL is available in 32-bit and 64-bit versions; however, only the 64-bit version is supported for RP Prism.

Migrating from MySQL 5.7 to 8.0
This section has high-level information about how to upgrade from MySQL 5.7 to MySQL 8.0 on a Prism system. For detailed information, refer to the MySQL documentation.
Step 1. Make a backup of the MySQL database
On the MySQL Workbench, select Server > Data Export.
Select the rpsods database. Select the Include Create Schema checkbox. You can select either radio button: Export to Dump Project Folder or Export to Self-Contained File.
Click Start Export. In our example, MySQL Data was exported to a self-contained file which can be found in the C:\Users\Documents\dumps folder.
Data export from mysql 5.7
 
Step 2. Uninstall MySQL
Use the MySQL Installer program and select to remove Server 5.7.xx
When uninstalling MySQL, do not delete the …\ProgramData\MySQL folder. To do this, make sure the "Remove the data directory" checkbox is cleared.
Mysql preserve data


Step 3. Install MySQL 8.0.21
It is important that you do not install the current MySQL version, which is MySQL 8.0.22. Instead, install MySQL 8.0.21. You can access the installer (.msi) file for 8.0.21 at the following link:
https://downloads.mysql.com/archives/installer

MySQL 8 installer download
Step 4. Restore the backup created in Step 1
In our example, we exported data to a self-contained file, so the same option is selected for the data import and we browsed to the location of the export file.
My sql data import

Step 5. Install Prism 1.14.7
Prism 1.14.7 includes an Apache upgrade. You must first uninstall Apache 2.4.27 and then install Apache 2.4.43 before installing the Prism stack. Prism 8.0 continues to use the 5.7 32-bit binary files. When installing the Prism Server (MySQL version), you must enter the path to the MySQL 5.7.xx 32-bit binaries folder. If doing an over-the-top install, the path to the MySQL my.ini file will be filled in automatically.
Prism server mysql install option

Install Prism MySQL Server
This section goes through the steps of installing the Prism Server (MySQL version).

  1. Run Windows Updates and reboot
  2. Install Apache
  3. Install the Prism Server. When installing the Prism server, you are prompted to select the type of server to install. The default choice is Oracle Server. Click the MySQL radio button.
    MySQL Server install option
    During the server install you are prompted to enter the path to the mysql-5.7.xx-win32 folder. Enter the path to the folder that you placed on the C:\ drive.  A message confirms the connection to the MySQL database is established.
    MySQL 32-bit install path
  4. When the Prism server install is finished, install the Proxy and Technician's Toolkit.

 
Version Check during New Install of Prism
During a new install of the Prism MySQL server, if you have a non-supported version installed, you will see a blank configuration screen (if you have a valid version, the screen is auto-populated with data). You will only be able to enter information for a valid 5.7 MySQL install.
If you enter information for a non-supported version and try to continue the install, you will receive the following error and you will not be able to proceed from this page until you either provide the correct information, or you cancel and install the correct version.

Expired MySQL Password
If a user's MySQL password has expired, that user will not be able to log in to Retail Pro Prism, and will receive an error message. By changing the 'password expiry lifetime' setting in the MySQL workbench, the user's password will again work and you can minimize the likelyhood of this occurance in the future.
Symptom: The User cannot access proxy or log in to Prism.
Problem: You will find this error in the Prism Logs -->"[Mysql] Your password has expired"
Solution:     Login to root in the Mysql Workbench. At menu bar, go to Server > Option file. Select the Security tab > Authentication > increase the default_password_lifetime (i.e. 99999) which is by default set to 360 days. Restart all Prism services.

Join MySQL Server to the Enterprise
After installing Prism on a non-root server (a child POA or a store server), add the server as a subordinate server to the HQ or POA in the Enterprise Manager area of Tech Toolkit.
Add server

Initialize Prism MySQL Server
Launch Prism and navigate to Connection Manager. There should be a shortcut for the Prism Proxy on the desktop. Double-click the shortcut to launch the Proxy. Windows 10 users should use the right-click "Run as administrator" option. Log in using the default sysadmin/sysadmin credentials. Click the Retail Pro button in the bottom-right corner and select Admin Console. Click Connection Manager on the Admin Console menu. Click the Prism Dashboard > Profiles tab.
Create profile for Initialization Profile and Day to Day Replication. On the Prism Dashboard > Profiles tab, click the Add button. In the Type field, select Prism to Prism. Enter a user-friendly Name for the profile. In the Resources area, select the resources to include in the Initialization. You can select each resource's checkbox or click the All link to select all resources.
initialization profile
After creating the Prism-to-Prism profile to send data to POA, you will have two profiles in the list on the Prism Dashboard: the profile you just created and the Core Resources profile (core resources is created automatically during JTE).  Remember, you will have to create a similar profile (e.g. POA 1 to Store Server) at the POA to send data to this store server.
Click the Initializations tab.  Click the Start Initialization button.
start initialization

You will see a screen that enables you to select the servers to initialize and the profile to use for the initialization. Select the checkbox for the server and select the Profile you created in the dropdown. Click the Start button.
start initialization
You can view progress status for the initialization. When initialization is finished, configure Permissions and Preferences.

Core Resources
When you join the enterprise, the system performs a type of mini-initialization and copies a group of resources known as the Core Resources from the POA to the subordinate system. As a result, when you go into the Profiles area on the Prism Dashboard, you will see a Core Resources profile already exists. The Core Resources profile is read-only. The Core Resources profile includes: controller, tenant, subsidiary, custom schema, transform design, season, tax area, price level, currency, store type and store.

Sub and Store Conflicts
When joining an enterprise, any numbering conflicts with existing subsidiaries or stores are displayed. It is common to have conflicts. In most cases, there is no action that needs to be taken. Later, the Subsidiary and Store numbers can be adjusted at each location to ensure that each server has a unique subsidiary/store combination. The Sub/Store Conflicts dialog tells the user (for example) "Your system includes a Sub 1, Store 001 and you are about to connect to an enterprise that already has a Sub 1, Store 001. Are you sure that's OK?" There will often be this type of conflict in a fresh installation. In most cases, you can ignore the conflict message. Set the store/sub, controller, etc. AFTER joining the enterprise rather than doing it DURING the join process. Click Continue. (The conflicts can be resolved later).
Failed/Stopped Initializations
Initialization can take a long time for larger databases and can sometimes fail to complete successfully. If an initialization fails or is stopped for whatever reason, here is what you should do:
Create a new Sender profile that starts from the resource after the last COMPLETED resource. For example, if the initialization was in the middle of the Inventory resource when the failure occurred, the new Sender profile should include Inventory and the rest of the resources to the bottom of the list. Run initialization again using the new Sender profile.
When you create a new Sender profile and restart, it may take a while to process the first resource (the resource that was being initialized when the failure occurred). This is because the program must do a slower UPDATE operation on each of the resource's records that are already in the tables. Once the program finishes the updates and reaches the unprocessed records for the resource, it switches to the much faster INSERT operation. There currently is no way to restart a failed/stopped initialization from the specific point of failure. The entire resource in which the failure occurred must be sent again.
Sample Profiles (Prism Dashboard)
The process of joining the enterprise and initializing servers goes more quickly and smoothly when you have a clear understanding of the basic set of profiles that are needed at each installation.

Profile Description
Core Resources This profile is created automatically when the store server joins the enterprise. During the JTE process, a mini-initialization is performed, populating the database with certain key info needed for the rest of the process.
D2D to POA  This profile should include all the data that you want to send back to the POA server.


Launch MySQL Workbench and Log In.

  1. Launch the MySQL Workbench.    
  2. Click the MySQL link.     
  3. Enter the password for the Root user that you defined during installation. Click OK.    The MySQL WorkBench launches. The Retail Pro data is stored in the RPROODS database.    

Increase Default wait_timeout in MySQL
A problem that users may encounter is leaving the machine running overnight (or for any extended period) and finding that MySQL has shut down. This can be seen in the logs as "MySQL server has gone away". This occurs because MySQL has a default wait_timeout setting of 8 hours. If there is no activity on the server for 8 hours (such as during the overnight time), the server will shut down.
If your business will be affected by the default wait_timeout setting, you should change the setting in MySQL to the desired time or create a script that will ensure the server has "activity" and will stay active.
The following links provide more information about the wait_time option.
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_wait_timeout

MySQL Performance Settings
The MySQL initialization (my.ini) is located in the c:\programdata\MySql\MySql Server 5.x folder of your 64bit Windows operating system. It is configured by default to a low baseline configuration. The following optimizations will improve read/write performance in the database by increasing the size of the memory pools, increasing packet sizes and enlarging the redo logs. These changes should be performed prior to your initial data load. To apply the change use the following instructions:

  1. Make a backup of the MY.INI file located in the c:\programdata\MySql\MySql Server 5.x folder.
  2. Open MY.INI in notepad or another plain text editor.
  3. Search for and change each of the parameters listed below and save the file.
  4. Restart the system.

Note: On a 4GB system, you should lower the memory allocation to MySQL to something less than 60%. On a system that has only 4GB available, using the normal memory allocations, memory problems can occur during Windows Updates.

Parameters
innodb_buffer_pool_size = this can be set anywhere in a range from 60% of memory on host to 80%.
innodb_log_buffer_size=16MB
innodb_log_file_size=512m
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_instances=4
innodb_file_per_table=1
innodb_thread_concurrency=0
innodb_open_files=1000
max_allowed_packet=16MB
thread_cache_size=40

How to Backup a MySQL Database Using the Command Line
Stores that use Retail Pro Prism on a MySQL database should create regular backups, just like any other Retail Pro system. This can be accomplished very easily by using a command line task to backup the MySQL database.
Step 1: Add the location of MySQL executables to the Environnment Variables Path.
Control Panel > System > Advanced System Settings > Environment Variables
Step 2: Once you have added the location to the Path, you can test the location by executing the following command in the command line prompt: "mysql -u root -p rprods"
Step 3: To backup the MySQL database, you will need to use the following mysqldump command:
Mysqldump -u USERNAME -p PASSWORD rprods > FILENAME.sql
This will create a single .sql file in the location that you ran the file from, in our example the C:\Users\sysadmin\rprods.sql file would be created.
WARNING: This will overwrite any files you currently have in this location, move and rename files as needed.