Errors after inplace upgrade from 19.10 to 20.04

Use this forum for questions regarding installation, upgrading and configuration of OpenEMM

Moderator: moderator

rodig03
Posts: 44
Joined: Thu Sep 18, 2014 1:27 pm

Errors after inplace upgrade from 19.10 to 20.04

Post by rodig03 » Mon Aug 03, 2020 11:38 am

Hi,
I did an inplace upgrade from 19.10 to 20.04.
I was using the command line installer and the status in the installer seems ok

Code: Select all

================================
= OpenEMM Installer v20.04.003 =
================================
Host: nl.gesext.de
License: OpenEMM (ID: 0)
Runtime Version: 20.04.000.043
Manual Version: 20.04.011
OpenEMM Version: 20.04.000.155
System-Url: https://nl.mydomain.tld
And versions in the GUI

Code: Select all

License	"OpenEMM" (ID: 0, Type: Inhouse)
Hostname	nl.mydomain.tld
Java Version	1.8.0_252 (Oracle Corporation)
EMM Version	20.04.000.155
Temp Directory	/home/openemm/temp
Install Path	/home/openemm/release/emm/openemm-gui-20.04.000.155/
System Time	03.08.2020 12:37:03
Build Time	25.06.2020 09:29:24
Start Up Time	03.08.2020 12:36:06
Up Time	57 Sekunden
Configuration
Expiration Time	03.08.2020 12:41:28
DB Type	MariaDB
DB URL	jdbc:mariadb://127.0.0.1/openemm?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8
I can login, but I have different issues.
1)
The system status in the gui shows a general error because of a database error.
database seems to work - e.g. I can review old mailings. But the status screen shows this:

Code: Select all

Datenbank
Version	Status
19.07.420	is Missing
19.10.270	is Missing
19.10.295	is Missing
20.01.008	OK
20.01.018	is Missing
20.01.023	OK
20.01.062	OK
20.01.225	OK
20.01.257	OK
20.01.300	OK
20.01.315	OK
20.01.460	is Missing
20.01.504	OK
2)
I can look at the statistics of a single mailing, but if I want to compare 2 mailings, I do get the following. Is this connected or a different issue?

Code: Select all

The following items have errors:

ReportDesign (id = 1):
+ Invalid javascript expression: Wrapped org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT COALESCE(bounces_hard, 0) bounces, mailing_id FROM benchmark_mailing_stat_tbl WHERE company_id = ? and mailing_id in (126,127) ORDER BY days_between DESC]; nested exception is java.sql.SQLSyntaxErrorException: (conn=26) Table 'openemm.benchmark_mailing_stat_tbl' doesn't exist (/report/method[@name="initialize"]#16)
ReportDesign (id = 1):
+ There are errors evaluating script "importPackage(Packages.com.agnitas.reporting.birt.external.dataset);
mailingCompareDataSet = new Packages.com.agnitas.reporting.birt.external.dataset.MailingCompareDataSet();
mailingCompareData = mailingCompareDataSet.getSummaryData(reportContext.getPersistentGlobalVariable("tempTableID"), reportContext.getLocale());

currentrow = 0;":
Fail to execute script in function __bm_OPEN(). Source:
------
" + importPackage(Packages.com.agnitas.reporting.birt.external.dataset);
mailingCompareDataSet = new Packages.com.agnitas.reporting.birt.external.dataset.MailingCompareDataSet();
mailingCompareData = mailingCompareDataSet.getSummaryData(reportContext.getPersistentGlobalVariable("tempTableID"), reportContext.getLocale());

currentrow = 0; + "
-----
A BIRT exception occurred. See next exception for more information.
Can't find method com.agnitas.reporting.birt.external.dataset.MailingCompareDataSet.getSummaryData(null,java.util.Locale). (/report/data-sets/script-data-set[@id="754"]/method[@name="open"]#3).
+ There are errors evaluating script "if(currentrow >= mailingCompareData.size()) {
 return false;
}

row["category"] = Packages.com.agnitas.messages.I18nString.getLocaleString(mailingCompareData.get(currentrow).getCategory(), reportContext.getLocale());
row["category_index"] = mailingCompareData.get(currentrow).getCategoryindex();
row["mailing_id"] = mailingCompareData.get(currentrow).getMailingId();
row["mailing_name"] = mailingCompareData.get(currentrow).getMailingName();
row["mailing_name_full"] = mailingCompareData.get(currentrow).getMailingNameFull();
row["value"] = mailingCompareData.get(currentrow).getCount();
row["rate"] = mailingCompareData.get(currentrow).getRate();
row["target_id"] = mailingCompareData.get(currentrow).getTargetGroupId();
row["target_name"] = "fdsfd";// mailingCompareData.get(currentrow).getTargetGroupName();
row["target_index"] = mailingCompareData.get(currentrow).getTargetGroupIndex();
if (row["target_index"] == 1) {
    row["target_name_short"] = Packages.com.agnitas.messages.I18nString.getLocaleString(mailingCompareData.get(currentrow).getTargetShortName(), reportContext.getLocale());
}
else {
    row["target_name_short"] = mailingCompareData.get(currentrow).getTargetShortName();
}

currentrow++;
return true;":
Fail to execute script in function __bm_FETCH(). Source:
------
" + if(currentrow >= mailingCompareData.size()) {
 return false;
}

row["category"] = Packages.com.agnitas.messages.I18nString.getLocaleString(mailingCompareData.get(currentrow).getCategory(), reportContext.getLocale());
row["category_index"] = mailingCompareData.get(currentrow).getCategoryindex();
row["mailing_id"] = mailingCompareData.get(currentrow).getMailingId();
row["mailing_name"] = mailingCompareData.get(currentrow).getMailingName();
row["mailing_name_full"] = mailingCompareData.get(currentrow).getMailingNameFull();
row["value"] = mailingCompareData.get(currentrow).getCount();
row["rate"] = mailingCompareData.get(currentrow).getRate();
row["target_id"] = mailingCompareData.get(currentrow).getTargetGroupId();
row["target_name"] = "fdsfd";// mailingCompareData.get(currentrow).getTargetGroupName();
row["target_index"] = mailingCompareData.get(currentrow).getTargetGroupIndex();
if (row["target_index"] == 1) {
    row["target_name_short"] = Packages.com.agnitas.messages.I18nString.getLocaleString(mailingCompareData.get(currentrow).getTargetShortName(), reportContext.getLocale());
}
else {
    row["target_name_short"] = mailingCompareData.get(currentrow).getTargetShortName();
}

currentrow++;
return true; + "
-----
A BIRT exception occurred. See next exception for more information.
ReferenceError: "mailingCompareData" is not defined. (/report/data-sets/script-data-set[@id="754"]/method[@name="fetch"]#1).
+ Data Set script method "Fetch" returned null; expected a Boolean value.
3) webservices stopped working, I can open the wsdl, but from the program I do get a "could not connect to host".

Any idea where to look for the error? The 19 version was ok in these fields, but something was not upgraded correctly.
python3 is 3.8.5

After upgrading, however, I had to add some URLs to the config as these were undefined.

Code: Select all

birt.url.intern = https://nl.mydomain.tld:8080/birt
webservices.url = https://nl.mydomain.tld/2.0/
Thanks a lot!

Robert

maschoff
Site Admin
Posts: 2512
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by maschoff » Tue Aug 04, 2020 10:22 am

If DB updates are missing you may execute the corresponding SQL update files "emm-mysql-update-<version>-basic.sql" manually.
OpenEMM Maintainer

rodig03
Posts: 44
Joined: Thu Sep 18, 2014 1:27 pm

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by rodig03 » Tue Aug 04, 2020 11:48 am

Thanks - but this seems not to be the solution.
I searched for *.sql files in the openemm folder and found some unter release/emm/...

Code: Select all

./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-create-database.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-create-user.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-fulldb-basic.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-messages-deleted.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-messages-openemm.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-messages.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-18.10.490.sql
./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-19.01.634-basic.sql
...

I tried to use the ones marked as missing by these commands

Code: Select all

[openemm@nl ~]$ mysql -u dbopenemm -pSecretPasswd openemm < ./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-19.07.420-basic.sql
ERROR 1061 (42000) at line 11: Duplicate key name 'wfrot$datecustrea$idx'
[openemm@nl ~]$ mysql -u dbopenemm -pSecretPasswd  openemm < ./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-19.10.270-basic.sql
ERROR 1050 (42S01) at line 11: Table 'ws_login_track_tbl' already exists
[openemm@nl ~]$ mysql -u dbopenemm -pSecretPasswd  openemm < ./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-19.10.295-basic.sql
ERROR 1060 (42S21) at line 11: Duplicate column name 'intervalpattern'
[openemm@nl ~]$ mysql -u dbopenemm -pSecretPasswd  openemm < ./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-20.01.018-basic.sql
ERROR 1050 (42S01) at line 11: Table 'startup_job_tbl' already exists
[openemm@nl ~]$ mysql -u dbopenemm -pSecretPasswd  openemm < ./release/emm/openemm-gui-20.04.000.155/WEB-INF/sql/mysql/emm-mysql-update-20.01.460-basic.sql
ERROR 1050 (42S01) at line 11: Table 'layout_tbl' already exists
So it seems the update was done but is not recognized?
Any idea?
Best regards,

robert


Thanks
Robert

maschoff
Site Admin
Posts: 2512
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by maschoff » Tue Aug 04, 2020 5:14 pm

The error messages from executing the SQL files you posted show that these files seems to have been executed already. However, as you can see from the content of these files, when they are executed they should also write an entry into the table logging all DB updates. We have no idea why this has not happened.
OpenEMM Maintainer

rodig03
Posts: 44
Joined: Thu Sep 18, 2014 1:27 pm

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by rodig03 » Wed Aug 05, 2020 3:50 pm

So I just execute the part that is setting the flags to fix the status?


But still, I have the issue number 2) - the errors if I compare 2 mailings and number 3) webservices.

If this is not related to the DB upgrade process - where to look?
Thanks,
Robert

maschoff
Site Admin
Posts: 2512
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by maschoff » Wed Aug 05, 2020 4:05 pm

Issue 2 is a bug, we will fix it (O-447).
OpenEMM Maintainer

rodig03
Posts: 44
Joined: Thu Sep 18, 2014 1:27 pm

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by rodig03 » Wed Aug 05, 2020 6:44 pm

Thank you!

Issue 3 is now gone - I started from the backup of the old version and did the upgrade again.
This time I did not install java 11 but kept 1.8 - either this or I did an undetected error in the upgrade sequence.

EDIT:
Sorry, I stil have to check the status after the upgrade of den backend.
It is still OpenEMMBackend Version: 19.10.000.128

maschoff
Site Admin
Posts: 2512
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by maschoff » Wed Aug 05, 2020 8:09 pm

What happend when you tried to update the backend version?
OpenEMM Maintainer

maschoff
Site Admin
Posts: 2512
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by maschoff » Thu Aug 06, 2020 7:10 am

Regarding property "webservices.url": It's destination is the WSDL definition, like "https://<domain>/2.0/emmservices.wsdl".
OpenEMM Maintainer

rodig03
Posts: 44
Joined: Thu Sep 18, 2014 1:27 pm

Re: Errors after inplace upgrade from 19.10 to 20.04

Post by rodig03 » Thu Aug 06, 2020 10:53 am

With all updates installed (but "old" java) it is working. I am not sure if I should test the java upgrade as there is no need.

The webservices still work after upgrade - the URL is used for more than 5 years and not changed.

I did get an issue with devunct python3 processes, but I'll open an extra issue for that.

For clarity, I am listing the versions I am on after doing the upgrades

Code: Select all

================================
= OpenEMM Installer v20.04.003 =
================================
Host: nl.xxxxx.xx
License: OpenEMM (ID: 0)
Runtime Version: 20.04.000.043
Manual Version: 20.04.011
OpenEMM Version: 20.04.000.155
System-Url: https://nl.xxxxx.xx
OpenEMMBackend Version: 20.04.000.142

Current menu: Show OpenEMM status

OpenEMM status:
Operating System (OS): CentOS 7
Python version: 3.8.5 (default, Aug  5 2020, 17:45:13)  [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]
Postfix Version: 2.10.1
DB client version: /usr/bin/mysql  Ver 15.1 Distrib 10.2.22-MariaDB, for Linux (x86_64) using  EditLine wrapper
Database Connection: OK
Database version: MariaDB 10.2.22-MariaDB
OpenEMM database structure exists (Version 20.01.504)
Currently running database threads ratio (Current: 1, Maximum: 7): 0.143
Database table emm_db_errorlog_tbl: OK
Jobqueue status: OK
No DKIM keys
Java version: 1.8.0_252 (Oracle)
Tomcat version: 9.0.33.0
Tomcat-Native version: 1.2.23
Wkhtml version: wkhtmltopdf 0.12.5 (with patched qt)
OpenEMMApplication is running (Tomcat ProcessID: 12713, started at: 11:43 1:01)
OpenEMMBackend ok: once
OpenEMMBackend running: mailout, update, trigger, generate, mta, pickdist, slrtscn, direct-path, bav-update, bavd, bav, mlcontrol

Post Reply