KB Article # 17574

Topic/Category:  Duplicate Batch Instance numbers with MariaDB

Applies to: v4.1.2.0

Issue:

You will see batch instance IDs for batch instances already processed. It can also start off back at BI1 if all batch instances have been cleaned from the batch_instance table by the reporting functionality in Ephesoft Transact.

Root Cause:

This can be due to a known issue with the version of MariaDB packaged with Ephesoft Transact 4.1.2.0. The version of MariaDB affected is 10.0.0.9. Specifically this issue occurs when Batch instances have been cleaned from the database by Transact, then MariaDB is restarted. Once MariaDB is started back up, it will check the batch_instance table in the database and it will start numbering at the next number. So if your last BI has gone to finished status and was cleaned fro the batch_instance table, if you restart MariaDB, it will start the numbering again at BI1.


Solution: 

You will need to install a newer version of MariaDB on a new server. Each image below can be zoomed in by clicking on them.

Steps to resolve the issue:

  1. Download the latest STABLE version of MariaDB from the following site. (msi file is the preferred method of installation. The current version at the time of this wiki is 10.2.7) https://downloads.mariadb.org/
  2. Install MariaDB:
    1. Install MariaDB by right clicking on the msi and clicking install.
      Screenshot from 2017-07-12 16-40-22

    2. Click Next
      Screenshot from 2017-07-12 16-41-04
    3. Accept the license terms.
      Screenshot from 2017-07-12 16-41-25
    4. Use the default settings and click next.
      Screenshot from 2017-07-12 16-41-41
    5. Enter the password that you would like for the root account. Should match what you used on your previous install of Ephesoft. It is imperative that you Select the Enable root from remote machines and the Use UTF8 as default checkboxes!
      Screenshot from 2017-07-12 16-42-11
    6. Set the service name and TCP port.
      Screenshot from 2017-07-12 16-42-24
    7. Click next when asked to provide feedback.
      Screenshot from 2017-07-12 16-42-47
    8. Click install and it will install MariaDB.
      Screenshot from 2017-07-12 16-43-05 Screenshot from 2017-07-12 16-43-23
    9. When the install finishes, click Finish.
      Screenshot from 2017-07-12 16-44-06
  3. Once the install finishes, you will have a new icon for HeidiSQL on your desktop and some new applications listed in your start menu.
    Screenshot from 2017-07-12 16-46-31 Screenshot from 2017-07-12 16-46-49
  4. On your Ephesoft server, use the information from the server.xml in order to connect to your old MariaDB instance. Ephesoft Transact service should be stopped before you continue. Screenshot from 2017-07-14 11-23-26
  5. Once you log in, your will see all of the databases that Ephesoft Transact uses(ephesoft, report, report_archive)
    Screenshot from 2017-07-14 11-23-55
  6. Right click on the ephesoft database and click Export database as SQL
    Screenshot from 2017-07-14 11-24-15
  7. A new window will open listing your databases. Check the boxes for Create under Database(s) and Table(s). Select INSERT in the Data field, Select Single .sql file under Output, and navigate to a folder and provide a filename for the .sql file. Click Export when finished.
    Screenshot from 2017-07-14 11-24-57
  8. Follow thhe same instructions for the report and report_archive databases each with their own unique names. You will have three .sql files when finished.
    Screenshot from 2017-07-14 11-25-18 Screenshot from 2017-07-14 11-25-40 Screenshot from 2017-07-14 11-25-58
  9. Copy these files to the new MariaDB server.
  10. Also copy the file ephesoft-mysql-config.sql from the C:\Ephesoft\Dependencies\MySqlSetup folder to the new server.
    Screenshot from 2017-07-14 11-26-21
  11. On the new server open HeidiSQL and connect to the localhost with the root password that you chose when you installed MariaDB.
    Screenshot from 2017-07-14 15-42-05
  12. Go to File and select Load SQL File… or press CTRL+O
    Screenshot from 2017-07-14 15-40-51
  13. Select the ephesoft database .sql file you saved on the previous server and click open
    Screenshot from 2017-07-14 11-29-49
  14. Your database creation script will appear. Click the drop down next to the run query button and select Run or press F9. If you get errors, it is ok, as long as the database was created.
    Screenshot from 2017-07-14 11-30-56
  15. Follow the same instructions to deploy the other two databases (report and report_archive)
  16. Now open the ephesoft-mysql-config.sql file, but do not run it. Delete everything from the script except for the GRANT USAGE and CREATE USER  lines which are at the bottom and I have a screenshot of all of the lines here. If you pulled this file form your Ephesoft Transact server, these should be filled in from the installation on that server. If not the samples I used here are user ephesoft and password P@ssw0rd) Then click Run or F9
    Screenshot from 2017-07-14 11-32-59
  17. Once that finishes, the databases should be ready to go. Now you need to go back to the Ephesoft Transact server and alter the server.xml to point to the new server’s IP in the Resource definitions.
    Screenshot from 2017-07-14 11-34-31
  18. You may now start Ephesoft Transact and the server will now number the Batch Instances correctly even after you restart MariaDB with an empty batch_instance table.

 

Notes: These instructions have been tested with the 4.1.2.0 instance of Ephesoft and will preserve your historical reporting data.

 

Was this article helpful to you?

J.D. Abbey

Comments are closed.