MySQL Guide
This topic explains how retailers can use a MySQL database with Prism including information about how to:
- Download and Install MySQL Server and Workbench: This section has information about where to find and download the required MySQL files and install the basic components needed by Prism.
- Install Prism Server (MySQL version): This section has information about installing the Prism server (MySQL version) and other Prism components. This section also has basic information about joining the server to the enterprise and initializing the server.
- View Prism data in MySQL: This section has information about launching Workbench to view Prism data.
Version Compatibility
Prism 2.1 and later supports MySQL version 8.0.x. MySQL 5.7 or earlier is not supported with Prism 2.1 and later versions.
We do NOT recommend using MySQL 32-bit and recommend only using the 64-bit version. However, the MySQL 64-bit installer does not supply the 32-bit drivers and because Prism is a 32-bit application it requires the MySQL 32-bit drivers to connect to the 64-bit version of the database. That's WHY users must also supply the MySQL 5.7 32-bit drivers.
If you are currently using MySQL 5.7 with Prism 1.14.7 or 2.0:
1. Upgrade from MySQL 5.7 to 8.0 using the Windows ZIP Distribution Method as described in the Upgrade from MySQL 5.7 to 8.0 topic. The Windows ZIP Distribution Method is required for cross-version upgrades like from 5.7 to 8.0
2. After successful upgrade from 5.7 to 8.0, run the installer for the current version of MySQL 8.0.x to update folders, registry keys, etc. At the time of writing the current version is 8.0.31.
3. Install Prism 2.1.
To install Prism 2.1 into a clean, empty MySQL 8.0 database:
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:
- Download the MySQL files and the Visual C++ redistributables.
- Install the Visual C++ redistributables.
- Install MySQL 8.0.x using the downloaded .msi file.
- Download the MySQL 5.7 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.
- Install the Prism Server. Choose the MySQL option for the server. During the install, you will point to the location of the Windows 32-bit .zip archive MySQL folder. After the server is installed, install the Prism Proxy.
- Join the Prism server to the enterprise.
- Initialize the Prism server.
Download MySQL Files and Visual C++ Redistributables
Navigate to the mysql installer archives page: https://downloads.mysql.com/archives/installer
You have the option of downloading the entire install or the web version, which will pull the files from the web. Click the Download link for the desired MySQL installer. By default, the file will be saved to the \Downloads folder.
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, you can use it to install 32-bit or 64-bit versions.
MySQL Installer "Select Products and Features" screen where users can select to install either the 64-bit or 32-bit (x86) versions of the MySQL server:
Download the MySQL 5.7 Windows 32-bit .zip Archive
After downloading the installer, click the Archives tab.
In the Product Version dropdown, select a 5.7 version (e.g. 5.7.32).
Click the Download link for the Windows 32-bit ZIP Archive. Select Save File and click OK. After download, extract the .zip files and copy the folder to the root C:\ drive of the workstation. When installing the Prism MySQL server, you will need to enter the path to the folder.
Install Visual C++ Redistributables
Download and run each of the following Visual C++ redistributable installers.
| Visual C++ Redistributable | Link |
|---|---|
| vc_redist.x64 (2015).exe | https://www.microsoft.com/en-us/download/details.aspx?id=48145 |
| vc_redist.x86 (2015).exe | https://www.microsoft.com/en-us/download/details.aspx?id=48145 |
| vcredist_x64 (2013).exe | https://www.microsoft.com/en-us/download/details.aspx?id=40784 |
| vcredist_x86 (2013).exe | https://www.microsoft.com/en-us/download/details.aspx?id=40784 |
Install MySQL using the downloaded .msi File
Install MySQL by running the .msi file you downloaded. The only components needed are MySQL Server and Workbench.
Use Legacy Authentication
MySQL 8.0 offers users two authentication methods: a newer method that uses strong password encryption and a legacy method to manage the password strength for user accounts. Retail Pro Prism only supports the legacy method for password strength encryption within MySQL. If a user wishes to utilize ‘strong passwords' for their user accounts within Retail Pro Prism, this can be done within the Prism application specifically.
Install MySQL Server and Workbench
Double-click the MySQL installer file (or right-click and select Install.
Accept the terms of the license agreement and click Next.
Select Custom as the Setup Type. Click Next.
In the Products To Be Installed list, add MySQL Server and Workbench. nothing else.
The next screen shows the products to be installed. First, the packages must be updated. Click Execute.
When the update is finished, each product will have a green check mark. Click Next.
Next, each of the main products needs to be configured. The next few screens go through the configuration process for each of the products listed.
Keep the Standalone MySQL Server option.
Click Next.
On the Type and Networking screen, set Config Type to Server Computer.
Click Next.
Select "Use Legacy Authentication Method." Retail Pro Prism only supports the legacy method for password strength encryption within in MySQL. If a user wishes to utilize ‘strong passwords' for their user accounts within Retail Pro Prism, this can be done within the Retail Pro Prism application specifically.
Set the root account password.
Leave the defaults for using MySQL Server as a service and click Next.
The Product Configuration screen is shown. Click Next.
When complete, click Finish. The MySQL Server is now configured.
To connect to the MySQL Server, enter the password you configured for the root user and click Check.
If the test is successful, click Next >.
Click Finish. The configuration is applied to the connection.
The Product Configuration screen is shown with an updated Status for the Samples and Examples product.
That just about finishes the installation. You have the option to automatically start SQL Workbench and the MySQL Shell command line interface. Click Finish.
The MySQL Workbench launches.
Click the "Local instance MySQL 80" panel.
Enter the root user's password and click OK.
MySQL Workbench launches.
At this point, MySQL does not yet have data. Next, install Prism for MySQL, join the enterprise and initialize Prism/MySQL with data.
Install Prism Server (MySQL version)
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.x). Later in this topic you will find information about how to navigate to the MySQL archive to download the correct version of these files.
Install Prism Server (MySQL version)
This section goes through the steps of installing the Prism Server (MySQL version).
- Run Windows Updates and reboot
- Install Apache
- 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.
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.
- 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.
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.
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.
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.
You can view progress status for the initialization. When initialization is finished, configure Permissions and Preferences.
Enforce Strong Passwords setting in Prism
MySQL 8.0 offers users two authentication methods: a newer method that uses strong password encryption and a legacy method to manage the password strength for user accounts. Retail Pro Prism only supports the legacy method for password strength encryption within MySQL. During the MySQL 8.0 installation process, users are required to select the "Legacy Authentication Method" instead of the "Strong Passwords" method. If a user wishes to utilize ‘strong passwords' for their user accounts within Retail Pro Prism, this can be done within the Retail Pro Prism application specifically.
To configure strong passwords and other password policy settings in Prism, navigate to Admin Console > Node Preferences > Employees > Policy. Click the checkbox for Enforce Strong Passwords. Define other policy settings as needed.
When strong passwords are enabled in Prism, user passwords must include the following elements:
• Uppercase and lowercase letters
• Numbers (0-9)
• Special characters (@, #, $, !)
• Length of 7 or more characters
Launch MySQL Workbench and View Prism Data
- Launch the MySQL Workbench.
- Click the MySQL link.
- Enter the password for the Root user that you defined during installation. Click OK. The MySQL WorkBench launches. The Prism data is stored in the rpsods database.
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.
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