Upgrade 21.10 to 22.04

[metti-himself]

We are unable to upgrade our current Centreon, after upgrading Packages we see the following error:

2022-06-28 15:22|0|0|UPGRADE - 22.04.0-beta.1: Unable to migrate broker config to unified_sql - Code : 42000 - Error : SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INTERSECT SELECT DISTINCT(config_id) FROM cfg_centreonbroker_info
WH' at line 2 - Trace : #0 /usr/share/centreon/www/class/centreonDBStatement.class.php(129): PDOStatement->execute(NULL)
#1 /usr/share/centreon/www/install/php/Update-22.04.0-beta.1.php(506): CentreonDBStatement->execute()
#2 /usr/share/centreon/www/install/php/Update-22.04.0-beta.1.php(153): migrateBrokerConfigOutputsToUnifiedSql(Object(CentreonDB))
#3 /usr/share/centreon/www/install/step_upgrade/process/process_step4.php(72): include_once('/usr/share/cent...')
#4 {main}

System is running COS 8 Stream, and the Database is external mysql 5.7. Any idea what is going wrong here, sql_mode or something?

Regards

[vcoumailleau]

Hello,
I got the same error. It was from one of the php upgrade files (/usr/share/centreon/www/install/php/Update-22.04.0-beta.1.php )
This PHP file upgrade the database scheme, and at some point use the SQL operator 'INTERSECT' (line 502). This part of the code create the new broker output with unified SQL and delete the 2 others
MySQL can't use INTERSECT operator

To work around the problem , i commented the call of the function who use INTERSECT (migrateBrokerConfigOutputsToUnifiedSql), reloead the upgrade page to pass the upgrade

Then i created the broker output manually and the delete the deprecated ones, export the config on the Central Server to apply the new broker config
(Configuration>Broker Configuration>central-broker-master>Output)

Hope this helps