<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<font face="monospace">Hi,<br>
<br>
sorry for the late reply.<br>
Here a bit of info about the '</font><font face="monospace">ngcp-mysql-compare-dbs'
script so you can understand why we introduced it.</font><br>
<font face="monospace"><br>
<br>
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.<br>
<br>
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.<br>
<br>
How it works:<br>
We deploy in the NGCP system specific '.json' files containing the
expected schema of all the databases of NGCP.<br>
The ‘ngcp-mysql-compare-dbs’ script does the following:<br>
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".<br>
2. it loads the expected schema of the current deployed version.
It is called by the script "json file".<br>
3. it compares database per database the schema and report back
all the differences.<br>
<br>
<br>
Said that, we are able to replicate both the problems you
reported.<br>
The quick solution is to change the type of the column as you
suggested or re-add the prosody routine.<br>
Anyhow we don't suggest to change manually the schema, but to wait
for an official fix.<br>
<br>
<br>
In case any of you notice other differences in the database,
please let us know so we can investigate then one by one.<br>
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.<br>
<br>
<br>
Thank You<br>
<br>
Best Regards<br>
Marco<br>
<br>
</font><br>
<br>
<div class="moz-cite-prefix">On 09/11/23 19:43, Jiri Ptacnik wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAKEbq9Ee0OaNcJcX23PZ8AWXm3PjToA1fhVgj8jTwGw_8RYrqQ@mail.gmail.com">
<pre class="moz-quote-pre" wrap="">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 <a class="moz-txt-link-rfc2396E" href="mailto:ptakjura@gmail.com"><ptakjura@gmail.com></a> napsal:
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">
same problem with divergence on upgrade from latest LTS 10.5. to latest LTS 11
after suggested solution i have this:
root@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 <a class="moz-txt-link-rfc2396E" href="mailto:rickey58@gmail.com"><rickey58@gmail.com></a> napsal:
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">
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 <a class="moz-txt-link-rfc2396E" href="mailto:spce-user@lists.sipwise.com"><spce-user@lists.sipwise.com></a> napisał(a):
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">
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:
<a class="moz-txt-link-freetext" href="https://www.sipwise.com/doc/mr11.5.1/spce/ce/mr11.5.1/upgrade/upgrade.html">https://www.sipwise.com/doc/mr11.5.1/spce/ce/mr11.5.1/upgrade/upgrade.html</a>
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: <a class="moz-txt-link-abbreviated" href="mailto:mcapetta@sipwise.com">mcapetta@sipwise.com</a>
Website: <a class="moz-txt-link-abbreviated" href="http://www.sipwise.com">www.sipwise.com</a>
--
Spce-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Spce-user@lists.sipwise.com">Spce-user@lists.sipwise.com</a>
<a class="moz-txt-link-freetext" href="http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com">http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com</a>
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
--
Spce-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Spce-user@lists.sipwise.com">Spce-user@lists.sipwise.com</a>
<a class="moz-txt-link-freetext" href="http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com">http://lists.sipwise.com/mailman/listinfo/spce-user_lists.sipwise.com</a>
</pre>
</blockquote>
</blockquote>
<pre class="moz-quote-pre" wrap="">
</pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<div
style="line-height:16px;
margin:6px 0;
padding:8px 8px 8px 8px;
border-top:1px #aeb1a6 dotted;
border-bottom:1px #aeb1a6 dotted;
font-family: 'Lucida Sans', Lucida Grande, Verdana, Arial, Sans-Serif;
font-size:11px;
color:#555555;"> <strong
style="color:#333333;
text-transform:uppercase;
font-size:10px;"> Marco Capetta </strong> <br>
R&D Manager
<p> <a href="http://www.sipwise.com"
style="color:rgb(0, 136, 204) !important;
text-decoration:none !important;
border-bottom:1px dotted #AAA;"> Sipwise GmbH | an ALE
Company </a><br>
c/o ALE International – Austria Branch<br>
Saturn Tower<br>
Leonard-Bernstein-Straße 10<br>
1220 Vienna, Austria </p>
<p> Email: <a href="mailto:mcapetta@sipwise.com"
style="color:rgb(0, 136, 204) !important;
text-decoration:none !important;
border-bottom:1px dotted #AAA;"
class="moz-txt-link-freetext"> mcapetta@sipwise.com </a> <br>
Website: <a href="http://www.sipwise.com"
style="color:rgb(0, 136, 204) !important;
text-decoration:none !important;
border-bottom:1px dotted #AAA;"> www.sipwise.com </a>
</p>
</div>
</div>
</body>
</html>