[Spce-user] Upgrade CE mr9.5.4 -> CE mr11.5.1: detected divergence in mysql schemes, run ngcp-mysql-compare-dbs to check details
Marco Capetta
mcapetta at sipwise.com
Fri Nov 10 08:14:25 EST 2023
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> 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> 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> 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
>>>> Website:www.sipwise.com
>>>>
>>>> --
>>>> Spce-user mailing list
>>>> 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
>>> http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com
--
*Marco Capetta *
R&D Manager
Sipwise GmbH | an ALE Company <http://www.sipwise.com>
c/o ALE International – Austria Branch
Saturn Tower
Leonard-Bernstein-Straße 10
1220 Vienna, Austria
Email: mcapetta at sipwise.com
Website: www.sipwise.com <http://www.sipwise.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20231110/a75300f6/attachment-0001.html>
More information about the Spce-user
mailing list