runtime 23.10.000.131 fails to create the database

[JuergenB]

Code: Select all

Root mode: On
Hostname: localhost.localdomain
Free diskspace: 78.9 % (of 27.19 GiB)
OpenEMM License: OpenEMM (ID: 0)
OpenEMM Runtime Version: 23.10.000.131
OpenEMM Version: 23.10.000.131
System-Url: http://192.168.0.1


Are you sure to drop all current data from database? ('DROP' => drop data, Blank => Back):
 > DROP
Please enter database root user password:
 > ********
Dropping database data (openemm) ...
+-----------------+
| VERSION()       |
+-----------------+
| 10.3.39-MariaDB |
+-----------------+
Database data was dropped
Creating database (openemm) and user (openemm) ...
+-----------------+
| VERSION()       |
+-----------------+
| 10.3.39-MariaDB |
+-----------------+
Database and user were created
Creating new database structure
Creating OpenEMM database structure ...
Database update started

DB already contains version 23.01.378
DB already contains version 23.01.379
DB already contains version 23.01.392
DB already contains version 23.01.515
DB already contains version 23.01.520
DB already contains version 23.01.557
DB already contains version 23.01.562
DB already contains version 23.04.381

Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-messages.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-messages-deleted.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-messages-openemm.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.01.402-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.001-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.062-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.188-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.194-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.198-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.222-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.242-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.255-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.256-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.347-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.360-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.369-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.386-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.418-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.434-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.04.451-basic.sql
Executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.07.097-basic.sql
ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLUMN default_value TO bk_default_value' at line 1
Error while executing /home/openemm/webapps/emm/WEB-INF/sql/mariadb/emm-mariadb-update-23.07.097-basic.sql
SQL database updates caused an error. Press any key to continue.

[aso]

You are using MariaDB version "10.3.39".

EMM DB update file "emm-mariadb-update-23.07.097-basic.sql" changes some db column names for later deletion like:
ALTER TABLE customer_field_tbl RENAME COLUMN default_value TO bk_default_value;

MariaDB changed its syntax for column renaming.
MariaDB 10.6.8 (recommended by AGNITAS AG) supports the syntax as given in that file.
MariaDB 10.3.39 does NOT.

So there are 2 solution to this problem:
- Upgrade to MariaDB 10.6.8
- Execute the rename sql statements manually by using this old MariaDB syntax:

Code: Select all

ALTER TABLE customer_field_tbl CHANGE COLUMN default_value bk_default_value VARCHAR(200);
ALTER TABLE customer_field_tbl CHANGE COLUMN field_group bk_field_group INT(11);
ALTER TABLE workflow_reaction_tbl CHANGE COLUMN is_legacy_mode bk_is_legacy_mode INT(1);
DELETE FROM job_queue_result_tbl WHERE job_id IN (SELECT id FROM job_queue_tbl WHERE runclass = 'com.agnitas.emm.core.workflow.service.jobs.ComWorkflowReportSendJobWorker');
DELETE FROM job_queue_parameter_tbl WHERE job_id IN (SELECT id FROM job_queue_tbl WHERE runclass = 'com.agnitas.emm.core.workflow.service.jobs.ComWorkflowReportSendJobWorker');
DELETE from job_queue_tbl where runclass = 'com.agnitas.emm.core.workflow.service.jobs.ComWorkflowReportSendJobWorker';
DELETE FROM job_queue_result_tbl WHERE job_id IN (SELECT id FROM job_queue_tbl WHERE runclass = 'com.agnitas.emm.grid.grid.service.DeleteGridTemplateWorkCopiesJobWorker');
DELETE FROM job_queue_parameter_tbl WHERE job_id IN (SELECT id FROM job_queue_tbl WHERE runclass = 'com.agnitas.emm.grid.grid.service.DeleteGridTemplateWorkCopiesJobWorker');
DELETE FROM job_queue_tbl where runclass = 'com.agnitas.emm.grid.grid.service.DeleteGridTemplateWorkCopiesJobWorker';
UPDATE job_queue_tbl SET description = REPLACE(description, 'WorkflowStopHandler', 'WorkflowStateHandler'), runclass = 'com.agnitas.emm.core.workflow.service.jobs.WorkflowStateTransitionJobWorker'
	WHERE runclass = 'com.agnitas.emm.core.workflow.service.jobs.ComWorkflowStopJobWorker';
INSERT INTO agn_dbversioninfo_tbl (version_number, updating_user, update_timestamp)
	VALUES ('23.07.097', CURRENT_USER, CURRENT_TIMESTAMP);
COMMIT;

But this may also happen on other EMM update sql files.
So updating your db version would be more useful.

[JuergenB]

Thanks,
i upgraded to MariaDB 10.6.8 and AlmaLinux 9.3 and got OpenEMM up and running.