Websevices crashing after upgrade to OpenEMM Version: 19.10.000.169

[MSelke]

No, i installed in an empty database, the error i get come from the latest update. I installed mid dezember and updated one week ago the forst time after the installation.

And i found the one possible explanation. I looked in the tables and the update script and found that one table name didnt match.

Name in database: cust1_ban_tbl
Name in script: lock$cust_ban_tbl

[maschoff]

Good catch! This would be the correct code for the update script:

Code: Select all

DROP PROCEDURE IF EXISTS TEMP_PROCEDURE;

DELIMITER ;;
CREATE PROCEDURE TEMP_PROCEDURE()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE companyid INT(11);
  DECLARE company_cursor CURSOR FOR SELECT company_id FROM company_tbl WHERE status IN ('active', 'inactive') ORDER BY company_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  ALTER TABLE prevent_table_drop DROP FOREIGN KEY IF EXISTS lock$cust_ban_tbl;
  ALTER TABLE prevent_table_drop DROP KEY IF EXISTS lock$cust_ban_tbl;
    
  ALTER TABLE prevent_table_drop ADD email_ban VARCHAR(150) COLLATE utf8mb4_bin COMMENT 'Referenced email';
  ALTER TABLE cust_ban_tbl MODIFY email VARCHAR(150) COLLATE utf8mb4_bin NOT NULL;
  ALTER TABLE prevent_table_drop ADD CONSTRAINT lock$cust_ban_tbl FOREIGN KEY (email_ban) REFERENCES cust_ban_tbl (email);
  
  OPEN company_cursor;
  
  read_loop: LOOP
    FETCH company_cursor INTO companyid;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @ddl2 = CONCAT('ALTER TABLE prevent_table_drop drop constraint lock$cust', companyid, '_ban_tbl');
    PREPARE stmt2 FROM @ddl2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    SET @ddl1 = CONCAT('ALTER TABLE cust', companyid, '_ban_tbl MODIFY email VARCHAR(150) COLLATE utf8mb4_bin NOT NULL');
    PREPARE stmt1 FROM @ddl1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    SET @ddl2 = CONCAT('ALTER TABLE prevent_table_drop ADD CONSTRAINT lock$cust', companyid, '_ban_tbl FOREIGN KEY (email_ban) REFERENCES cust', companyid, '_ban_tbl (email)');
    PREPARE stmt2 FROM @ddl2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
  END LOOP;
  
  CLOSE company_cursor;
END;;
DELIMITER ;

CALL TEMP_PROCEDURE;
DROP PROCEDURE TEMP_PROCEDURE;

INSERT INTO agn_dbversioninfo_tbl (version_number, updating_user, update_timestamp)
	VALUES ('20.10.122', CURRENT_USER, CURRENT_TIMESTAMP);

We will provide an update soon.

[MSelke]

Hi, i run the script with the following command and result.

mysql> source /home/openemm/webapps/emm/WEB-INF/sql/mysql/emm-mysql-update-20.10.122-basic.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS lock$cust_ban_tbl;
ALTER TABLE prevent_table_drop DROP KEY IF EXISTS' at line 9
ERROR 1305 (42000): PROCEDURE openemm.TEMP_PROCEDURE does not exist
ERROR 1305 (42000): PROCEDURE openemm.TEMP_PROCEDURE does not exist
ERROR 1062 (23000): Duplicate entry '20.10.122' for key 'PRIMARY'

Did i something wrong. Do i need to rename the table first?

[aso]

What version of MySQL do you use?

Seems like your database does not support the syntax:
ALTER TABLE prevent_table_drop DROP KEY IF EXISTS lock$cust_ban_tbl;

If so, just execute the statements of this script "one by one" manually"

[MSelke]

I use the following Mysql Version.

mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper

I try to run the script line by line.

[MSelke]

I tried the script statements from line 9.

Line 9 and 10 both didnt work because there is no cust_ban_tbl, only cust1_ban_tbl, Line 12 runs fine and 13 tries also to alter cust_ban_tbl, which don't exists, because, why?

What are the changes in the script? Or do i have to rename the table?