Add a new MySQL service for Profits Plus

Table of Contents

Grant privileges and set passwords:
Set MYSQL_VERSION environment variable.
Setup MySql.ini
Start Profits Plus.
Initialize the memo devices file.
Modify night processing to refresh shadow tables.
Modify mysql.bat
Create a DSN for MySQL so Windows applications can access the Profits Plus database.

As of November 2014 we install MySQL assuming that there will always be the possibility of adding multiple services of the mysqld deamon later because Maxene Reporter will also use a separate service of MySQL. We install just one version of MySql and create more than one instance (service) of the mysqld deamon, each service will have it's own datadir and port number.

This page assumes the original installation of MySQL was completed earlier. It did not support multiple services.

If this machine has never supported multiple services of MySQL then you'll probably have to convert the installed "C:\Program files\MySQL\MySQL SERVER 5.5\my.ini" file to support multiple services now. Here's how.

You do not have to stop any of the other old MySQL services when adding a new one. But make sure the new one you are adding now is stopped.

If you didn't just convert the my.ini file to support multiple services for the first time above then go to the bottom of the "C:\Program files\MySQL\MySQL SERVER 5.5\my.ini" file and create a new service by copying/pasting the last live service section and changing the new service name, data path to match your customer's info. Use the next available port # for the new service.
If you DID just convert my.ini file to support multiple services for the first time above then you don't have to copy/paste anything below because you created the new xxx service section when you converted the my.ini file. The first default port number should be 3306.

The datadir path for our hosted cloud servers will be arranged differently than a single customer server.

Hosted Profits Plus	datadir = C:\MVS\xxx\mysql\data
Hosted Maxene Reporter	datadir = C:\maxene_reporter\MaxeneReporter\mysql\data
Single Profits Plus	datadir = C:\u\mvs\mysql\data
Single Maxene Reporter	datadir = C:\maxene_reporter\MaxeneReporter\mysql\data

#------------------------------------------------------------------
# options for mysql55_exc service (use forward slashes in path)
#------------------------------------------------------------------
[mysql55_exc]
datadir = C:/MVS/exc/mysql/data
port = 3306

#------------------------------------------------------------------
# options for mysql55_xxx service (use forward slashes in path)
#------------------------------------------------------------------
[mysql55_xxx]
datadir = C:/MVS/xxx/mysql/data
port = 3307

WARNING! You have to pay close attention to the actual location of the "live" data template folder that was installed. All new services must start with a copy of this template folder. Different versions of Windows will put it in different folders and sometimes multiple folders. Windows 2003, 2008, 2012 use the "C:\Program files\MySQL\MySQL SERVER 5.5\data" folder but Windows 7 installs two data folders; one in "C:\Program files\ and another identical copy in C:\ProgramData but only the C:\ProgramData one is the live data template folder. So always refer to the datadir= path in the [mysqld] section of the "C:\Program files\MySQL\MySQL SERVER 5.5\my.ini" file for the correct live data template folder.
You have to copy the originally installed data template folder to the new path in C:\MVS\xxx\mysql. If the C:\MVS\xxx\mysql\data folder exists now, delete it so you can copy in the new one. Enter this command in a Dos prompt window. (You might have to use a different Drive letter and MVS_ROOT path). You can also do this using Windows Explorer (copy/paste). Do not delete the original C:\Program files\MySQL\MySQL SERVER 5.5\Data folder! You will need it later when creating new mysql services.

del c:\MVS\xxx\mysql\data
copy "C:\Program files\MySQL\MySQL SERVER 5.5\Data" C:\MVS\xxx\mysql

cd c:\MVS\xxx\mysql\data
mkdir 100001, 200001, 210001, 220001, 230001, 240001, 250001, 260001, 270001
mkdir 100002, 200002, 210002, 220002, 230002, 240002, 250002, 260002, 270002
mkdir softwork

Note: On the 2008 mvsalliancehosting.com server the orginal files are in C:\Program data\MySQL\MySQL SERVER 5.5\Data. You don't have to create the softwork folder because it was already done while the original database was in the Program Data folder.

del c:\MVS\xxx\mysql\data
copy "C:\Program data\MySQL\MySQL SERVER 5.5\Data" C:\MVS\xxx\mysql

cd c:\MVS\xxx\mysql\data
mkdir 100001, 200001, 210001, 220001, 230001, 240001, 250001, 260001, 270001
mkdir 100002, 200002, 210002, 220002, 230002, 240002, 250002, 260002, 270002

If you are installing MySQL for the first time then the original MySQL installation created the mysql55_xxx service, so you don't have to do this next step. But if you are adding a new service to an existing installation you need to create an additional service mysql55_xxx as a Windows service manually. Your login should probably have administrative privileges. Execute this in a command line shell (You MUST specify the --defaults-file parameter even though it is the default defaults file). You should be able to just copy/paste the command below directly into notepad first, edit the --install mysql55_xxx service name and then copy/paste it to a DOS prompt.

"C:\Program files\MySQL\MySQL SERVER 5.5\bin\mysqld" --install mysql55_xxx --defaults-file="C:\Program files\MySQL\MySQL SERVER 5.5\my.ini"

If you ever have to un-install this service use the dos command: sc delete mysql55_xxx
Go into Windows Services and start the MySQL55_xxx service and make it automatically launch at startup. If the service fails to start double check the path you gave for the datadir.

If you ever have to un-install this service use the dos command: sc delete mysql55_xxx

Grant privileges and set passwords:

Note: On the 2008 mvsalliancehosting.com server you don't have to do this step to set privileges and passwords because it was already done on the original database in C:\Program data\ folder. So you can skip to "Set MYSQL_VERSION environment variable" below.

The GRANT commands below will create 7 users:

mvuser is for command line client.
mvuser_odas is for MVSWEB/ODAS client when the ODAS database is in the same datadir as Profits Plus database.
mvuser_pp is for Softworks RUN client for Profits Plus database and uses old_password format.
mvuser_mr is for Maxene Reporter client (read-only) for the Profits Plus database.
mvuser_web is for MVSWEB/ODAS ar any read-only client to read the Profits Plus database tables.
guest is for read-only users to access the Profits Plus database
root is for admin

Only mvuser_pp user is configured to use the old_password format because that is what RUN requires. All other users are expected to use the mysql.exe client which will use the new_password format.

In Windows 2012 you'll have to right click on the PP_DOS_Prompt.bat shortcut and choose "Run as administrator" to be able to run the mysql command below.

Logon to the MySql client as root in a dos prompt window by entering this command:

mysql.exe -uroot -pmysql1 -Pxxxx (where -Pxxxx is the port # you assigned to this instance of MySQL)

If you're re-installing MySQL then you might get a password error with the above command. If so, then don't give the -pmysql1 argument in the above command.

If client prompts for a password use the password mysql1. It will give you a MySQL command line so you can copy/paste the following commands directly into the mysql client prompt:

GRANT ALL PRIVILEGES ON *.* TO 'mvuser'@'localhost' IDENTIFIED BY 'mvuser1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser'@'%' IDENTIFIED BY 'mvuser1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_odas'@'localhost' IDENTIFIED BY 'mvuser_odas1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_odas'@'%' IDENTIFIED BY 'mvuser_odas1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_pp'@'localhost' IDENTIFIED BY 'mvuser_pp1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_pp'@'%' IDENTIFIED BY 'mvuser_pp1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_mr'@'localhost' IDENTIFIED BY 'mvuser_mr1';

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_mr'@'%' IDENTIFIED BY 'mvuser_mr1';

REVOKE ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, SHUTDOWN, SUPER ON *.* FROM 'mvuser_mr'@'localhost','mvuser_mr'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_web'@'localhost' IDENTIFIED BY 'mvuser_web1';

GRANT ALL PRIVILEGES ON *.* TO 'mvuser_web'@'%' IDENTIFIED BY 'mvuser_web1';

REVOKE ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, SHUTDOWN, SUPER ON *.* FROM 'mvuser_web'@'localhost','mvuser_web'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'guest'@'localhost' IDENTIFIED BY 'guest1';

GRANT ALL PRIVILEGES ON *.* TO 'guest'@'%' IDENTIFIED BY 'guest1';

REVOKE ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, SHUTDOWN, SUPER ON *.* FROM 'guest'@'localhost','guest'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mysql1' WITH GRANT OPTION;

SET PASSWORD FOR 'mvuser_pp'@'localhost' = OLD_PASSWORD('mvuser_pp1');

SET PASSWORD FOR 'mvuser_pp'@'%' = OLD_PASSWORD('mvuser_pp1');

CREATE TABLE `SoftWork`.`inmemo_00000000` (jn BIGINT UNSIGNED not NULL,db VARCHAR(255) not NULL,tbl VARCHAR(255) not NULL,rn INT UNSIGNED not NULL,E INT not NULL,lw INT UNSIGNED not NULL,PRIMARY KEY(jn,db,tbl,rn,E,lw),INDEX(db,tbl,rn,E));

If the CREATE TABLE for 'SoftWork' fails for some reason you might be able to just copy the tables from the Google Drive MyDrive\mysql\softwork folder.

Set MYSQL_VERSION environment variable.

Make sure the following batch files have the following command: SET MYSQL_VERSION=5.5

win32\env\setup_env.bat

Setup MySql.ini

The MySQL.ini file is read only by Softworks RUN
Create the directory %MVS_ROOT%\dsk0\001004
Copy the following text into a text file %MVS_ROOT%\dsk0\001004\mysql.ini
Change the DEVICE=SQL0: line so it has the correct domain name. Instead of localhost it could be an IP address or name of the machine. The 3306 is the port number. The default is 3306 but you might have to change it for your situation. Also change the drive/path on the mysql.sock argument if your's is different :

# %MVS_ROOT%\dsk0\001004\mysql.ini
#
# This file can be placed in DSK0:[1,4], current directory,
# or in the users home directory.
# Search order is as listed above, all files are read, allowing for system
# wide, local, and user settings to be combined.
# ------------------------------------------------------------
# A device is the machine where MySql is running.
# You can define up to 9 devices for different MySql machines
# RUN.SQL opens a MySql session for each defined device.
# ------------------------------------------------------------------
# Map SQL0 and SQL1 to MySQL server on localhost
DEVICE=SQL0:,localhost,mvuser_pp,mvuser_pp1,3306,c:\mvs\xxx\mysql\mysql.sock
#
#BULKREAD'LOGIN=mvuser,mvuser1 ; new for 2015
#
# DEVICE=SQL1:,
#-------------------------------------------------------------------
# disable DSK0 and DSK1 as SQL devices for speed
device=DSK0:,
device=DSK1:,
device=DSK2:,
device=TEST:,
device=MEM:,
device=RES:,
# ------------------------------------------------------------------
# Turn on MySQL in RUN
use_mysql=true
# Use the latest inmemo locking method
INMEMO_SQL=SQL0:
#
# How to handle eof() when used on random, or ISAM file
eof2=1
# make record #1 a special control record in random files.
record_1_special=1
# ---------------------------------------------------------------------------
#
fixed_buffers=true
#
# These have no effect for on random files, but may in the future.
read_speed=true
limit=100
#
# ISAM settings
multiple_user_add=false
use_get_free=false
use_add_key=false
use_rtn_free=true

Start Profits Plus.

If it doesn't work then you'll have to solve the problem before going any further. If you can't get it to work you can rename the /u/mvs/dsk0/001004/mysql.ini file to mysql.ini.renamed so Profits Plus won't try to use MySql. At least Profits Plus will work while you research how to fix the MySql problem.

Initialize the memo devices file.

Go into Software Maintenance for company 1 task 32/29. "Initialize data files." and Initialize "Memo file devices" for 100 records.

Modify night processing to refresh shadow tables.

Copy/paste these lines to the %MVS_ROOT%\dsk0\002002\doit.bat and doit.nor.bat files. Place them near the end after all the modules have been processed for the night. If these lines are already there then you can skip this step.

echo doit:880:

:: --------------------------------------------------------------------

:: Refresh shadow tables in MySQL.

:: This will refresh all shadow tables in all modules in all companies

:: --------------------------------------------------------------------

IF NOT EXIST %MVS_ROOT%\dsk0\001004\mysql.ini GOTO SQLFIL_END

cd %MVS_ROOT_ND%\dsk0\100001

SET au_INI_FILENAME=trash\sqlfil.job999

SET FNAME_SQLFIL=%MVS_ROOT_ND%\dsk0\100001\shadow.job999.prt

SET FNAME_OK=%MVS_ROOT_ND%\dsk0\100001\sqlfil_ok.prt

echo INI'FUNCTION=SHADOW > %au_INI_FILENAME%

echo INI'SQLFIL'FNAME=%FNAME_SQLFIL% >> %au_INI_FILENAME%

echo INI'SUCCESS'NAME=%FNAME_OK% >> %au_INI_FILENAME%

IF EXIST %FNAME_OK% DEL %FNAME_OK%

IF EXIST sqlfil.log DEL sqlfil.log

CALL %MVS_RUN% "DSK2:SQLFIL.RUN[100,000]"

echo doit:881:

IF NOT EXIST %FNAME_OK% GOTO SQLFIL_FAIL

echo SQLFIL ended successfully

GOTO SQLFIL_END

:SQLFIL_FAIL

echo SQLFIL failed and logged the following messages.

type sqlfil.log

:SQLFIL_END

:: --------------------------

:: End of shadow table logic

:: --------------------------

Modify mysql.bat

Edit the mysql client startup bat files in %MVS_ROOT%\win32\bin\mysql.bat so that they specify the correct port number for the mysql service. So -P33xx is the unique port number you assigned above.

   @echo off
   mysql.exe -P33xx -umvuser -pmvuser1
   pause

Create a short-cut of win32\mysql.bat for the au\mvs_tools folder and name it mysql_client_xxx.bat where xxx is the 3 character abbreviation for the customer's software. Example: mysql_client_jre.bat

Create a DSN for MySQL so Windows applications can access the Profits Plus database.

As of 3/18/2015 Vivek says MVSWEB can't use the 64-bit ODBC connectors so you'll have to install 32-bit versions. See neil-pc: OneDrive\SoftwareTools\mysql for these choices:

mysql-connector-odbc-5.3.4-win32.msi
mysql-connector-odbc-5.3.4-winx64.msi
mysql-connector-odbc-3.51.30-win32.msi (if the 5.3.4 version won't install)

These instructions were taken from the following web page.

http://fdo.osgeo.org/files/fdo/docs/FET_TheEssentialFDO/files/WS1a9193826455f5ffd30557110ffe778f8-7c7c.htm

Do the following:

If you are installing a 32-bit connector on 64-bit windows then you can't use the Data Source Administrator you get from the start menu because it won't show any of the 32-bit connectors you installed to choose from. Instead you need to use the 32-bit [ODBC Data Source Administrator] which is in C:\Windows\SysWOW64\odbcad32.exe. Just double click on the file to start the Administrator. Windows 2012 and above have integrated both 32-bit and 64-bit in the same Data Source app, so you don't have to use the odbcad32.exe separately. You can choose 32-bit or 64-bit inside the Data Source app.
But if you are installing a 64-bit connector on 64-bit Windows then you can use the Windows Start menu, click Settings, click Control Panel, click Administrative Tools, click Data Sources (ODBC). The ODBC Data Source Administrator dialog box is displayed.
In the ODBC Data Source Administrator dialog box, click the User DNS tab if you want the data source to be visible only to you on this machine, or click the System DSN tab if you want the data source to be visible to others on this machine and on the network. Normally we click the System DSN tab. Click Add. The Create New Data Source dialog box is displayed.

NoteYou may not use the File DSN tab, because Fdo does not support its use.
In the Create New Data Source dialog box, if "MySQL ODBC x.xx Driver" isn't listed you can download one from http://dev.mysql.com/downloads/connector/odbc/ and choose the typical install option. We also keep downloads in the Google Drive MyDrive\mysql folder.
In the Create New Data Source dialog box, click MySQL ODBC 3.51 Driver, click Finish. The Connector/ODBC 3.51.12 dialog box is displayed.
In this dialog box, in the text entry box labeled Data Source Name , type the name that you want to use as the value of the DataSourceName connection property. For Profits Plus we use "ProfitsPlus_xxx" where xxx is the 3 character abbreviation of the customer. For Maxene Reporter we use "MaxeneReporter". We don't have to designate the customer in the Maxene Reporter name because Maxene Reporter tables keeps all customers in one database.
In this dialog in the text entry box labeled Server, type the hostname/IP address of the machine where the MySQL database server is installed. The default is localhost.
Enter user: mvuser_web and password mvuser_web1. Pick database 100001 using the drop-down combination box labeled Database and click the TEST button. You should get a "Connection Successful" message in a popup window.
Click OK to close the Configuration dialog.
In the ODBC Data Source Administrator dialog box, you see the DSN that you just added listed.
Click OK.