[bug]: commit ddb5c37 makes database id columns nullable

[fm81]

Severity

Minor

Versions

20.7.0

Components/Modules

pjsip, pbx_realtime

Operating Environment

Almalinux 8.x

Frequency of Occurrence

Constant

Issue Description

Commit ddb5c37 is missing default values for the database column nullable settings.

Running the database migration with

/usr/local/bin/alembic -c config.ini upgrade head

Before migration, the columns

• ps_aors. id
• ps_auths.id
• ps_domain_aliases.id
• ps_endpoint_id_ips.id
• ps_endpoints.id
• ps_inbound_publications.id
• ps_outbound_publishes.id
• ps_registrations .id

are 'not null', after the migration they are 'nullable'.

With enabled query logging in MariaDB 10.11 the script https://github.com/asterisk/asterisk/blob/ddb5c377fd54723a3a08b279b178d320eeae9319/contrib/ast-db-manage/config/versions/dac2b4c328b8_incease_pjsip_id_length.py generates the following SQL queries:

ALTER TABLE ps_aors MODIFY id VARCHAR(255) NULL ALTER TABLE ps_auths MODIFY id VARCHAR(255) NULL ALTER TABLE ps_domain_aliases MODIFY id VARCHAR(255) NULL ALTER TABLE ps_endpoint_id_ips MODIFY id VARCHAR(255) NULL ALTER TABLE ps_endpoints MODIFY id VARCHAR(255) NULL ALTER TABLE ps_inbound_publications MODIFY id VARCHAR(255) NULL ALTER TABLE ps_outbound_publishes MODIFY id VARCHAR(255) NULL ALTER TABLE ps_registrations MODIFY id VARCHAR(255) NULL

From https://alembic.sqlalchemy.org/en/latest/ops.html:

MySQL has special requirements here, since MySQL cannot ALTER a column without a full specification. When producing MySQL-compatible migration files, it is recommended that the existing_type, existing_server_default, and existing_nullable parameters be present, if not being altered.

Parameters: [...] existing_nullable – Optional; the existing nullability of the column. Required on MySQL if the existing nullability is not being changed; else MySQL sets this to NULL.

An alembic downgrade with /usr/local/bin/alembic -c config.ini downgrade 465f47f880be is possible, but the column remains nullable.

Relevant log output

No response

Asterisk Issue Guidelines

• [X] Yes, I have read the Asterisk Issue Guidelines

[github-actions[bot]]

This issue was closed by #701.