[Spce-user] Upgrade CE mr9.5.4 -> CE mr11.5.1: detected divergence in mysql schemes, run ngcp-mysql-compare-dbs to check details

Thu Nov 16 07:23:53 EST 2023

Hello,

is this still an open problem?

As we are planning to upgrade c5 PRO from 9.5.2 to 11.5.1 in next few weeks, so want just to be sure, that there will be no show stoppers.

thanks

Von: Jiri Ptacnik <ptakjura at gmail.com> 
Gesendet: Sonntag, 12. November 2023 18:01
An: Marco Capetta <mcapetta at sipwise.com>
Cc: spce-user at lists.sipwise.com
Betreff: Re: [Spce-user] Upgrade CE mr9.5.4 -> CE mr11.5.1: detected divergence in mysql schemes, run ngcp-mysql-compare-dbs to check details

Thanks Marco for explanation.

I have started my testing setup with version 9.5.3. Then some upgrades
in 9.5.x line, then upgrade to actual 10.5 and then to actual 11.5.

Jiri

pá 10. 11. 2023 v 14:15 odesílatel Marco Capetta via Spce-user
<spce-user at lists.sipwise.com <mailto:spce-user at lists.sipwise.com> > napsal:
>
> Hi,
>
> sorry for the late reply.
> Here a bit of info about the 'ngcp-mysql-compare-dbs' script so you can understand why we introduced it.
>
>
> Starting from mr11.5.1 a new script called ‘ngcp-mysql-compare-dbs’ has been introduced to compare the current mysql scheme on the NGCP system with the expected one for that specific version. It is an excellent tool because it helps detecting schema issues and preventing many problems, but it also brings up a lot of issues sleeping in customer systems for a long time. For example, the ngcp-status detects and reports a problem on a table that we were supposed to update in version 7.5 only now because we have this vital tool.
>
> Please don't blame the upgrade to mr11.5.1 because is creates issues in the DB during the upgrade, but let’s use this output to fix bugs that are in the system since long time ago.
>
> How it works:
> We deploy in the NGCP system specific '.json' files containing the expected schema of all the databases of NGCP.
> The ‘ngcp-mysql-compare-dbs’ script does the following:
> 1. it creates a runtime version of the schema files using as source the current schema of the local mariadb instance. It is called by the script "local db".
> 2. it loads the expected schema of the current deployed version. It is called by the script "json file".
> 3. it compares database per database the schema and report back all the differences.
>
>
> Said that, we are able to replicate both the problems you reported.
> The quick solution is to change the type of the column as you suggested or re-add the prosody routine.
> Anyhow we don't suggest to change manually the schema, but to wait for an official fix.
>
>
> In case any of you notice other differences in the database, please let us know so we can investigate then one by one.
> It would be also nice to know which was the version of the system you originally deployed and through which version you upgraded till mr11.5.1. This could help understanding when a particular issue was originated.
>
>
> Thank You
>
> Best Regards
> Marco
>
>
>
> On 09/11/23 19:43, Jiri Ptacnik wrote:
>
> Any news about hasslefree upgrade to 11?
>
> Solution is probably easy, just change column type to the same as in
> json, but...
>
> JP
>
> út 24. 10. 2023 v 16:42 odesílatel Jiri Ptacnik <ptakjura at gmail.com <mailto:ptakjura at gmail.com> > napsal:
>
> same problem with divergence on upgrade from latest LTS 10.5. to latest LTS 11
>
> after suggested solution i have this:
>
> root at spce:~# ngcp-mysql-compare-dbs
> Schema fileshare is equal to json file
> Schema sipstats is equal to json file
> Schema billing is equal to json file
> Schema syslog is equal to json file
> Schema stats is equal to json file
> Schema ldap is equal to json file
> Schema accounting is equal to json file
> Schema ngcp is equal to json file
> Schema kamailio is equal to json file
> Schema prosody is equal to json file
> Schema carrier is equal to json file
> The following errors were found:
>
> Element: columns/provisioning/voip_allowed_ip_groups/ipnet/column_type
> are not equal:
> ---
> local db: varchar(43)
> json file: varchar(46)
>
>
> JiriP
>
> po 23. 10. 2023 v 9:52 odesílatel Rickey <rickey58 at gmail.com <mailto:rickey58 at gmail.com> > napsal:
>
> Hi Marco,
>
> Thank You for the solution.
> Unfortunately, after executing the indicated commands, the problem still occurs:
> # apt-get update
> # apt-get upgrade
> # ngcp-update-db-schema
> # ngcpcfg apply 'updates packages'
> # ngcp-status
> DB: detected divergence in mysql schemes, run ngcp-mysql-compare-dbs to check details
>
> # ngcp-mysql-compare-dbs
> Schema stats is equal to json file
> Schema carrier is equal to json file
> Schema ldap is equal to json file
> Schema fileshare is equal to json file
> Schema syslog is equal to json file
> Schema accounting is equal to json file
> The following errors were found:
>
> Element: columns/kamailio/reseller_preferences/last_modified/extra are not equal:
> ---
> local db: on update current_timestamp()
> json file:
> Element: columns/kamailio/usr_preferences/last_modified/extra are not equal:
> ---
> local db: on update current_timestamp()
> json file:
> The following errors were found:
>
> Element: columns/provisioning/voip_allowed_ip_groups/ipnet/column_type are not equal:
> ---
> local db: varchar(43)
> json file: varchar(46)
> Element: columns/provisioning/voip_usr_preferences/creation_timestamp is missing in json file
> Schema sipstats is equal to json file
> Schema ngcp is equal to json file
> The following errors were found:
>
> Element: routines/prosody/uuidtobin/routine_definition are not equal:
> ---
> local db: RETURN
> UNHEX(CONCAT(
> SUBSTR(_uuid, 15, 4),
> SUBSTR(_uuid, 10, 4),
> SUBSTR(_uuid, 1, 8),
> SUBSTR(_uuid, 20, 4),
> SUBSTR(_uuid, 25) ));
> json file: RETURN
> UNHEX(CONCAT(
> SUBSTR(_uuid, 15, 4),
> SUBSTR(_uuid, 10, 4),
> SUBSTR(_uuid, 1, 8),
> SUBSTR(_uuid, 20, 4),
> SUBSTR(_uuid, 25) ))
> Schema billing is equal to json file
>
> Best Regards,
> Rickey
>
> pon., 23 paź 2023 o 09:34 Marco Capetta via Spce-user <spce-user at lists.sipwise.com <mailto:spce-user at lists.sipwise.com> > napisał(a):
>
> Hi Rickey,
>
> please try to update all the packages in your system, in particular 'ngcp-db-schema', and execute the commands
> ngcp-update-db-schema
> ngcpcfg apply 'updates packages'
>
> After that please check again if you have divergences in the mysql schemes
>
>
> Thank you
>
> Best Regards
> Marco
>
>
> On 18/10/23 17:09, Michael Prokop via Spce-user wrote:
>
> Hi,
>
> * Rickey [Wed Oct 18, 2023 at 03:41:53PM +0200]:
>
> Has the message reached the group?
>
> Yes, your mails arrived fine, we can reproduce the issue and are
> working on it (known as MT#58530 internally, FTR).
>
> regards
> -mika-
>
> After upgrade from CE mr9.5.4 to CE mr11.5.1 following the instructions:
> https://www.sipwise.com/doc/mr11.5.1/spce/ce/mr11.5.1/upgrade/upgrade.html
>
> and running:
> ngcp-status
>
> I've got red alert on DB section:
> detected divergence in mysql schemes, run ngcp-mysql-compare-dbs to check
> details
>
> After running:
> ngcp-mysql-compare-dbs
>
> [...]
>
>
> --
> Marco Capetta
> R&D Manager
>
> Sipwise GmbH | an ALE Company
> c/o ALE International – Austria Branch
> Saturn Tower
> Leonard-Bernstein-Straße 10
> 1220 Vienna, Austria
>
> Email: mcapetta at sipwise.com <mailto:mcapetta at sipwise.com> 
> Website: www.sipwise.com <http://www.sipwise.com> 
>
> --
> Spce-user mailing list
> Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com
>
> --
> Spce-user mailing list
> Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com
>
>
> --
> Marco Capetta
> R&D Manager
>
> Sipwise GmbH | an ALE Company
> c/o ALE International – Austria Branch
> Saturn Tower
> Leonard-Bernstein-Straße 10
> 1220 Vienna, Austria
>
> Email: mcapetta at sipwise.com <mailto:mcapetta at sipwise.com> 
> Website: www.sipwise.com <http://www.sipwise.com> 
>
> --
> Spce-user mailing list
> Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com

-- 
Spce-user mailing list
Spce-user at lists.sipwise.com <mailto:Spce-user at lists.sipwise.com> 
http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20231116/d1084f98/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5022 bytes
Desc: not available
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20231116/d1084f98/attachment-0001.p7s>