Database Problems with upgrade script 6.1.23 -> 6.2.36 (table exclusion_table column family_exclusion; NULL and primary key)

[c-holtermann]

This issue came up when I did a migration and upgrade at the same time.

Version

6.1.23 to 6.2.36

Installation method

From Sympa debian .deb to compiled from source

Expected behavior

normale upgrade

Actual behavior

sympa.pl --upgrade stopped issuing the error:

notice Sympa::DatabaseManager::_check_primary_key() Primary key exclusion_table [list_exclusion,robot_exclusion,user_exclusion,family_exclusion] is missing. Adding it
err main::#842 > Sympa::Upgrade::upgrade#121 > Sympa::DatabaseManager::probe_db#214 > Sympa::DatabaseManager::_check_primary_key#453 > Sympa::DatabaseDriver::MySQL::set_primary_key#360 > Sympa::Database::do_query#280 Unable to execute SQL statement "ALTER TABLE exclusion_table ADD PRIMARY KEY (list_exclusion,robot_exclusion,user_exclusion,family_exclusion)": (01000) Data truncated for column 'family_exclusion' at row 1
err main::#842 > Sympa::Upgrade::upgrade#121 > Sympa::DatabaseManager::probe_db#214 > Sympa::DatabaseManager::_check_primary_key#453 > Sympa::DatabaseDriver::MySQL::set_primary_key#371 Could not set fields list_exclusion,robot_exclusion,user_exclusion,family_exclusion as primary key for table exclusion_table in database sympa
err main::#842 > Sympa::Upgrade::upgrade#121 > Sympa::DatabaseManager::probe_db#223 Unable to check the validity of primary key for table exclusion_table. Aborting err main::#842 > Sympa::Upgrade::upgrade#125 Database sympa defined in sympa.conf has not the right structure or is unreachable. verify db_xxx parameters in sympa.conf err main::#846 Migration from 6.1.23 to 6.2.36 failed

Additional information

Database before migration was MySQL. Used mysqldump. Migrate to MariaDB.

I couldn't run the upgrade script with mysql. But I ran the upgrade command

ALTER TABLE exclusion_table ADD PRIMARY KEY (list_exclusion,robot_exclusion,user_exclusion,family_exclusion)

on the original machine. MySQL accepts it. Even if I dump this and reimport to MariaDB I get the same error. I could only make it work when doing:

update exclusion_table SET family_exclusion=0 where family_exclusion is null;

then

alter table exclusion_table modify column family_exclusion varchar(50) not null;

now

ALTER TABLE exclusion_table ADD PRIMARY KEY (family_exclusion,user_exclusion,list_exclusion,robot_exclusion);

The problem seems to be setting family_exclusion as primary key when null values are in the database.

[ikedas]

Hi @c-holtermann,
Could you please tell us versions of (older) MySQL and (newer) MariaDB?

I found this information:

• MariaDB Knowledge Base: Primary Keys with Nullable Columns

[c-holtermann]

That looks like it.

old server:
mysql Ver 14.14 Distrib 5.5.60, for debian-linux-gnu (x86_64) using readline 6.3

new server:
mysql Ver 15.1 Distrib 10.3.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
from docker image mariadb

[ikedas]

@c-holtermann, thanks for confirming. The bug may be fixed by the PR above.

Note on your workaround: family_exclusion field is varchar and modification would be better to be:

update exclusion_table SET family_exclusion = '' where family_exclusion is null;

Thanks for reporting bug!

[c-holtermann]

Thank you for the correction.

[paulmenzel]

Distributions should backport this commit to their packages.

[guillaume-uH57J9]

I encountered the same issue with a postgresql database, when upgrading from sympa 6.1.23 to 6.2.36 on a Debian system.

The workaround worked, ie replacing null values by an empty string

[ghost]

For your information, this fix is included in the sympa version (checked in 6.2.40) available in Debian Buster.