FUNCTION pp_conv2smallInt does not exist while importing database for MYOB and Acumatica

Hello everybody,

today I want to share with you one rake, which stolen from me few days of my life. 

Recently I imported SQL backup of MySQL database, and got error like this:

17:20:58 Restoring D:\Backups\Wire\rev.sql
Running: mysql.exe --defaults-file="c:\users\zalju\appdata\local\temp\tmp5dndjm.cnf" --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=rev < "D:\\Backups\\Wire\\rev.sql"
ERROR 1305 (42000) at line 70858: FUNCTION rev.pp_conv2smallInt does not exist

Operation failed with exitcode 1
17:28:28 Import of D:\Backups\\Wire\rev.sql has finished with 1 errors

Error message looked similar to what you can see below:

After plenty of googling and applying different advice I was disappointed as nothing worked for me. 

Then I've decided to take a look on clean database of installed Acumatica and discovered the following:

Then I've decided to create such a function manually and tried to execute import one more time. 

Execution lasted for a bit longer period of time, but now I got another error message, but now related to function pp_conv2smallInt, which you should be aware of how to fix.

Summary

If you make import of MySQL database, then prior to it create functions pp_conf2int, binaryMaskTest and other standard Acumatica functions, otherwise you'll get error messages similar to mine, and anyway will need to create them properly. Not very much convenient but working approach. In other words, cheap comes with it's price.

 

 

 

 

How to catch all MySQL queries generated by Acumatica

Hello everybody,

finally I found out how to catch all queries to MySQL server, generated by Acumatica. Well, in context of My SQL as usually people work more with MYOB, but under the hood MYOB is Acumatica.

Typical schema of Acumatica <-> MySQL connection looks like this:

In order to get generated MySQL queries, you may need some proxy service, which will intercept queries. You can use MySQL proxy, but instead of MySQL proxy I suggest to use Neor Profile SQL as it has much more convenient UI:

 

In order to achieve such catching of all My SQL queries, you'll need following steps:

  1. Install Neor Profile SQL.
  2. In your Acumatica web.config make following change:
  <connectionStrings>
    <remove name="ProjectX_MySql" />
    <remove name="ProjectX" />
    <add name="ProjectX" providerName="System.Data.SqlClient" connectionString="Server=localhost;Port=4040;Database=PXProjecti

pay especial attention to this part: Port=4040

3. Next goes configuration of Neor Profile SQL. Create connection to MySQL server in a way similar to what you see on screenshot:

4. You are all set. Now Acumatica will send SQL queries to Neor Profile SQL, while Neor Profile SQL will re-translate them to My SQL:

Summary

If you need to catch generated My SQL queries, you can go with My SQL query proxy and logging all files to file. Or with help of Neor Profile SQL you may get nice tool for tracking all generated queries. 

And also with such steps you can track everything that MYOB generated!