* update_sound_set_handle_parents() procedure is redesigned
to avoid "insert into select from" statements as they cause
gap locks when run concurrently and access the same index
rows.
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6594 page no 6 n bits 56 index parent_id_idx
of table `provisioning`.`voip_sound_sets` trx id 400852
lock mode S locks rec but not gap waiting
the "insert into select from" statements are reworked:
the "select from/with recursive" parts are now declared cursors
and the data is inserted inside the opened cursor loop (if any).
this way the selects are not causing exclusive gap locks.
(cherry picked from commit 4017d3519c)
Change-Id: I35ec28a33ecca42de898e67a2709eda4364e5f95
There are 2 .up scripts that modify voip_allowed_ip_groups.ipnet
15702.up - modifies it to varchar(43)
15710.up - modifies it to varchar(46)
Because in 15710.up was backported into mr9.5 but is missing in mr10.5,
existing upgrades from mr9.5 -> mr10.5 apply 15702 after 15710.up,
effectively downgrading the column to varchar(46).
This .up script fixes the scenario and also the .down script modifies
the column also to varchar(46) to respect the previous 15710.up the
sets it to varchar(46), so to achieve the consistent state.
Change-Id: If14917ef437de3abf46b1900f68c9cc3a9a4b947
(cherry picked from commit 9c6ef86372)
* convert unique key set_handle_id_idx (set_id, handle_id)
to non-unique to avoid deadlocks because of forced gap locks
in INSERT and DELETE concurrent operations (when such occur)
* "update_sound_set_handle_parents" procedure:
- avoid IODKU and instead calculate entries to skip on the fly.
- WITH and WITHOUT u_handle_id is now split into 2 separate
IF blocks as otherwise "AND handle_id = u_handle_id OR 1:1"
caused full handles update even if u_handle_id was there
* fix a typo for "TRIGGER voip_sound_files_create_trig AFTER INSERT"
which wrongly had "AFTER UPDATE"
* fix warnings of 'Truncated DOUBLE' coming from using IF evaluation
variable as boolean, IF(LENGTH(cte.parent_chain) > 1 addresses
it.
Change-Id: Id6b6ab66de53c2ccd95f28d23e5e2b97ece89923
(cherry picked from commit 38051cfc67)
* the ngcp.timezone table is not replicated and hence
the script must be also not replicated.
- the script is renamed to _not_replicated
- the ALTER TABLE ADD COLUMN is enhanced with
"IF NOT EXISTS" to mitigate scenarios where
somebody manually added the column on the other node,
and to ensure that the upgrades are safe
Change-Id: Ic44f76b5f3f43ae4124cebad30d559224fa90b31
(cherry picked from commit ff81df911a)
* on update_sound_set_handle_parents() call delete
orphaned entries from voip_sound_set_handle_parents
do not have entries in the voip_sound_sets
* update_sound_set_handle_parents() improvements:
- when u_sound_set_id is not null:
= delete only child entries (before, also the parent entries
were removed)
= on insert, on duplicate update parent_set_id and parent_chain
to effectively skip entries that already exist and only
insert new entries
Change-Id: Id0881d9f79ddd05bc5be67d1f181ea809e43e180
(cherry picked from commit 4fd9399a2b)
* this column stores the currently used olson database timezone
version
Change-Id: Ib26c0d731d7f378717ea399d3a9f06d5f033a596
(cherry picked from commit 55477004f8)
* use char based aggregation instead of JSON_ARRAY accumulation +
REPLACE
Change-Id: I5deb748637d2becc6d5aa6f598ba5a858b09b74b
(cherry picked from commit e17353c794829c5357edbd5994519e44babaa9ca)
* new table provisioning.voip_sound_set_handle_parents
that is a helper table and contains current effective parents
for all sound set handles
* new stored procedure update_sound_set_handle_parents that accepts
sound_set_id or NULL, handle_id or NULL to update the helper
table (e.g. call update_sound_set_handle_parents(NULL, NULL)
to rebuild the whole helper table)
* new triggers around voip_sound_sets and voip_sound_files
that trigger the stored procedure when parent_id is changed
or a file changed/use_parent changed
* v_sound_set_files view uses the helper table now instead of
calculating all the nested sound sets and their handles'
parents
Change-Id: Ic775c077227b22b48f7129bf002e36ce353de82f
(cherry picked from commit 763541f12d)
In the process of improvement and optimization of the whole
soundset structure, it is fundamental to make the contract
soundset behaving exactly like the system soundsets.
Due to that now all the announcements are exposed to customers
Change-Id: I5da9162ed544e69336e954d9257a180898157014
* add provisioning.voip_sound_sets parent field, that is null by default
and can be set to another sound_set id to indicate it as own parent.
* add provisioning.voip_sound_files use_parent field, boolean not null
default 0. this field defines wether parent sound sets should be
used for this sound (default yes).
* add v_sound_set_files view that returns an accumulated
representation of sound files available for sets for all the
available sound handles.
Therefore "select * from v_sound_set_files where set_id = 3"
returns all available sound handles and the filename and data columns
containing the according content either from the current sound set
or from first parent that contains a non empty filename and data,
or until the sound set (current or parent) with the use_parent field = 0.
* parent_chain virtual column contains a ':' separated chain of parents
used for the sound set.
* data_set_id virtual column contains the sound_set that was used for
the sound data or null if there is no sound data.
* voip_sound_files update index from handle_id_ref (handle_id) to
handle_set_id_idx (handle_id, set_id) for optimised behaviour
of the v_sound_set_files view.
* add provisioning.voip_sound_sets expose_to_customer field that
enables the sets to be used by customers.
* update provisioning.voip_preferences 'sound_set' attibute with
expose_to_customer = 1
Change-Id: I06fb384d911cfe1d2ffe7be8fd2ba8d80468662f
If any user depends on the previous format for mono Opus, this makes it
possible to preserve the old behaviour.
Change-Id: Ib0587585278cf143046b1210e93c6f8230b81749
These files were built on Pro trunk with:
/usr/share/ngcp-db-schema/helper/ngcp-build-schema-files
Change-Id: I688146703b32c0a00b477f0edea9d2418a72ca4d
The main tool ngcp-build-schema-files:
1. initializes and starts a dedicated mysql instance in tmpfs.
2. prepares schemes.
3. runs ngcp-dump-json.pl and mysqldump to get sql and json files.
4. stops and deletes mysql instance.
The helper ngcp-dump-json.pl creates json dump of the schema.
Change-Id: I65968d0f9136b619f9adc5ed735daca0bd129e6e
For preparation of dumps (sql and json) it's useful to use separate
mysql instance which is run and handled manually.
So add support of such instance.
Add variable MYSQL_SOCKET which shows that we want to connect to custom
mysql instance via defined socket.
In such a case don't try to start a service mysql.
Change-Id: I3f40a3857125fc72158aabf277f892c7cd541bf6
The 'enable_t38' preference was added long time ago to
provision of phones that support fax services with t38.
We don't support those devices since long time.
Additionally the preference creates confusion with real
subscriber feature of enable t38 for mailtofax/faxtomail
service.
Due to those reason the preference can be removed.
Change-Id: I05b0f353eff9dcc6d57f51c6a32a6756f692d29d
This preference was created long time ago to support
the automated provision of a desktop jitsi client.
The feature is not used and most probably not working
at all since we don't support the jitsi client anymore.
Change-Id: I4592b8130a26adec04352af00bdbea939f3d2aa0
The feature was never really used and no customer is using
it at the moment.
Removed from the DB the following
voip_preferences:
* dialogic_mode
* dialogic_ip_rtp
* dialogic_ip_config
* dialogic_out_codecs
* dialogic_ss7_opc
* dialogic_ss7_apc
* dialogic_ss7_dpc
* dialogic_nfs_server
* dialogic_nfs_path
* dialogic_snmp_system_name
* dialogic_snmp_system_location
* dialogic_snmp_system_contact
* dialogic_snmp_community_name
* dialogic_ip_snmp_manager
* dialogic_use_optical_spans
* dialogic_is_isdn_userside
voip_preferences_enum:
* dialogic_mode
* dialogic_out_codecs
voip_preference_groups:
* Dialogic Settings
Change-Id: I5c483ffc61728fc2de7134d4ebd84593955ba184
The preference is used only for device provisioning
to give priority to user changes over the automated
provisioning coming from the server.
Change-Id: I65bb0b90b80e11aa34f7b56019f0b57319678cc0
Activate profile preferences to all the options already
available for both subscriber and domain.
Extend 'music_on_hold' and 'clir_intrapbx' to be
domain preferences.
Add 'ignore_cf_when_hunting' preference to subscriber
role.
Fix and update 'expose_to_customer' and 'expose_to_subscriber'
columns to properly expose what is necessary for each
role and hide features that should be activated only
by system administrator.
Change-Id: Ia0aa8f9f10dc1bab47ff8b9ccfc90c2741e988e0
* ringtimeout is now internal = 1 as this preference is dynamically
changed by CFT call forwards and is not meant to be usable from the
UI/API
Change-Id: I6c2abf94e455cf50cdbc6cf7122844582ba6bbb4
* expose_to_customer is now set to 1 for 'cli' preference so that
subscriberadmin users can access it
Change-Id: Ia661a7095457bd0fbf6eab5c74cd17eb224d7c3b
* expose_to_subscriber is a new preferences flag that
indicates that the preference can be read/modified by
'subscriber' role
* expose_to_customer now only does the same as expose_to_subscriber
but for 'subscriberadmin' role
Change-Id: I4b38ac5653e7132510354de4fd8b72d010d0ff68
* ncos_sets table contains a list of ncos_sets
* ncos_set_levels table contains "many to many" mappings
for ncos_sets and ncos_levels
* add ncos_levels.time_set_id column (default null)
Change-Id: Ib46fe46bd75ef13b8375961c93f586281f28866d
This reverts commit 1631573453.
The script is actually a one-time only script, no need to re-execute
it again, instead it fails to run due to:
| Applying revision script /usr/share/ngcp-db-schema/db_scripts/diff/15743.up: ERROR 1050 (42S01) at line 4: Table 'autoprov_firmwares_data' already exists
| failed. :(
Change-Id: I75ea1a0ce86064113032844a9ff4fc8a2b0af7cd
In git rev a9c1cf4cb we had to take care of adjusting the offset
variable to support MariaDB >=10.6 in the existing diff/15301.up script.
So that also existing installations receive that change during upgrade,
let's provide the according script which re-creates the
split_autoprov_firmware_data stored procedure.
Change-Id: Iae9634b39bda9f1ad63b2b6093f2e6095f68fc45
Thanks: Kirill Solomko <ksolomko@sipwise.com>
Fixes:
| Applying revision script /usr/share/ngcp-db-schema/db_scripts/diff/15301.up: ERROR 1064 (42000) at line 14: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset int;
| DECLARE chunk longblob;
| DECLARE done int DEFAULT FALSE;
|
| ...' at line 4
| failed. :(
This is caused by MariaDB having "offset" as reserved word, quoting from
https://mariadb.com/kb/en/upgrading-from-mariadb-10-5-to-mariadb-10-6/:
| New reserved word: OFFSET. This can no longer be used as an identifier without being quoted.
Now that Debian/bookworm includes mariadb-server v10.6.10-1
we need to rename the variable.
Change-Id: I9db699ef60259411b8b79305f08179d4989edcd2
* the new table is to contain data for customer speed dials
(that affect all subscribers under the customer)
* triggers that replicate the data into kamailio.speed_dial
Change-Id: I0bc175e0ede7fa79b296c83a7f0d512b03c74aab
rtcengine product has been discontinued already since mr10.5.1
so it is useless to keep the database in the system
Change-Id: Icdf06c8d9de9a0236a5fc87b645d698952db3b44
This reverts commit 5f34f84b04.
Reason for revert: upstream provide us with a solution that doesn't need a kamailio change
Change-Id: Ifdb7f8c55f27498a880cc35a104e20472e6e69f7
Kirill Solomko
Sipwise Jenkins Builder
Mykola Malkov
Marco Capetta
Richard Fuchs
Daniel Grotti
Michael Prokop
Víctor Seva