MySQL: Upgrading fails due to stricter SQL mode

[titou3as]

Version

From 6.1.24 TO Sympa 6.2.40
Migration method

Transfer .incl files , expl lists , topics , scenari
Stop services sympa , apache2 ,postfix
/usr/lib/sympa/bin/sympa.pl --upgrade
Restart services sympa, apache2, postfix

Expected behavior

Use of uninitialized value $effective_size in numeric ge (>=) at /usr/share/sympa/lib/Sympa/DatabaseManager.pm line 712.
Use of uninitialized value $effective_size in numeric ge (>=) at /usr/share/sympa/lib/Sympa/DatabaseManager.pm line 712.
Database sympa defined in sympa.conf has not the right structure or is unreachable. verify db_xxx parameters in sympa.conf

Actual behavior

Erreur interne du serveur

Sympa a été confronté à une erreur interne

Veuillez contacter le listmaster

Erreurs : Can't use an undefined value as an ARRAY reference at /usr/share/sympa/lib/Sympa/List.pm line 7055.
Pile d'appel

DIED: Can't use an undefined value as an ARRAY reference at /usr/share/sympa/lib/Sympa/List.pm line 7055.
at /usr/share/sympa/lib/Sympa/List.pm line 7055.
Sympa::List::sync_include_admin(Sympa::List [email protected]) called at /usr/share/sympa/lib/Sympa/List.pm line 418
Sympa::List::new('Sympa::List', 'listexample2', 'xxxx.yyyy.fr', HASH(0x55e0ab115f40)) called at /usr/share/sympa/lib/Sympa/List.pm line 7855
Sympa::List::get_lists('xxxx.yyyy.fr') called at /usr/lib/cgi-bin/sympa/wwsympa.fcgi line 9559
main::do_get_latest_lists() called at /usr/lib/cgi-bin/sympa/wwsympa.fcgi line 1566

Additional information

[ikedas]

Hi @khaoula-abaidi ,

Transfer .incl files , expl lists , topics , scenari

I think you should also transfer the database.

[titou3as]

Hi @khaoula-abaidi ,

Transfer .incl files , expl lists , topics , scenari

I think you should also transfer the database.

I've transfered the database also

[racke]

So you either missing the database configuration in sympa.conf or the information is not accurate.

[titou3as]

/usr/lib/sympa/bin/sympa.pl --upgrade --from=6.1.24 --to=6.2.40

notice main:: Sympa 6.2.40 Started
notice main:: Upgrade process...
notice main:: Upgrading from 6.1.24 to 6.2.40...
Use of uninitialized value $effective_size in numeric ge (>=) at /usr/share/sympa/lib/Sympa/DatabaseManager.pm line 712.
notice Sympa::DatabaseManager::_check_fields() Field "update_epoch_inclusion" (table "inclusion_table"; database "sympa") does NOT have awaited type (int(11)) where type in database seems to be (int). Attempting to change it...
notice Sympa::DatabaseDriver::MySQL::update_field() ALTER TABLE inclusion_table CHANGE update_epoch_inclusion update_epoch_inclusion int(11)
notice Sympa::DatabaseManager::_check_fields() Field "date_epoch_admin" (table "admin_table"; database "sympa") was NOT found. Attempting to add it...
err main::#890 > Sympa::Upgrade::upgrade#120 > Sympa::DatabaseManager::probe_db#186 > Sympa::DatabaseManager::_check_fields#337 > Sympa::DatabaseDriver::MySQL::add_field#261 > Sympa::Database::do_query#280 Unable to execute SQL statement "ALTER TABLE admin_table ADD date_epoch_admin int(11) ": (42000) Invalid default value for 'date_admin'
err main::#890 > Sympa::Upgrade::upgrade#120 > Sympa::DatabaseManager::probe_db#186 > Sympa::DatabaseManager::_check_fields#337 > Sympa::DatabaseDriver::MySQL::add_field#270 Could not add field date_epoch_admin to table admin_table in database sympa
err main::#890 > Sympa::Upgrade::upgrade#120 > Sympa::DatabaseManager::probe_db#186 > Sympa::DatabaseManager::_check_fields#354 Addition of fields in database failed. Aborting
err main::#890 > Sympa::Upgrade::upgrade#120 > Sympa::DatabaseManager::probe_db#196 Unable to check the validity of fields definition for table admin_table. Aborting
err main::#890 > Sympa::Upgrade::upgrade#124 Database sympa defined in sympa.conf has not the right structure or is unreachable. verify db_xxx parameters in sympa.conf
err main::#894 Migration from 6.1.24 to 6.2.40 failed

[ikedas]

@khaoula-abaidi , if you know, could you please tell us what is the version of MySQL (or MariaDB) server you are using?

[ikedas]

@khaoula-abaidi , could you please apply this patch and try again?

[titou3as]

MYSQL version is :
mysql Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64

[ikedas]

Thanks for information. Could you please apply this patch and try again?

[tkchen-github]

I would not get sympa.pl --health_check works with MySQL 8 database. This patch helps to fix the problem.

[ikedas]

Thanks for reporting bug and confirming fix! This fix will be included in the next release.

[titou3as]

Using : /usr/lib/sympa/bin/sympa.pl --health_check i've got this message :Database sympa defined in sympa.conf has not the right structure or is unreachable. verify db_xxx parameters in sympa.conf

[titou3as]

To migrate from 6.1.24 to 6.2.24 , will you tell me What files do I need to transfer from a list directory, espacially lists that editors , owners and subscribers are from data_sources ?
Is subscribers.* ,include_admin_user.lock and include.lock are important to transfer ?

[titou3as]

I've restart migration with transfering lists directories without subscribers.* ,include_admin_user.lock and include.lock files and archives directory.
And i got this error before restoring the 6.1.24 database
Thank you

[titou3as]

[ikedas]

@khaoula-abaidi , could you please apply this patch and try again?

[titou3as]

i've didn't apply the patch but i've the migration is well done.
i have apply the upgrade command (sympa.pl --from=6.1.24 --to=6.2.40) without restoring my old database(6.1.24 database which is a mysql 5).
I have just one problem : the list of lists into a family doesn't appear into the web interface.
I've transfer the family directory into (/etc/sympa/mydomain/families and the lists repertories(/var/lib/list_data/mydomain)
Since And the lists are attached to the family.
Thank you for your response.

[titou3as]

@khaoula-abaidi , could you please apply this patch and try again?

Hello , i've tried the patch and the problem is resolved , thank you

[titou3as]

Currently the problem is resolved.
But The isn't an other solution so that I don't change the MySQL.pm file with every upgrade?

[racke]

The fix will be included in the next release of Sympa (6.2.60), which is proposed to be released on 21st December this year.

[titou3as]

The fix will be included in the next release of Sympa (6.2.60), which is proposed to be released on 21st December this year.

Will the bug fix for version 6.2.60 only be limited to the MySQL.pm program or the database?
in the meantime can you give me what I can do in relation to the database for example to avoid the modifications to the program.
Thank you for your response.

[ikedas]

The fix will be included in the next release of Sympa (6.2.60), which is proposed to be released on 21st December this year.

Will the bug fix for version 6.2.60 only be limited to the MySQL.pm program or the database?
in the meantime can you give me what I can do in relation to the database for example to avoid the modifications to the program.
Thank you for your response.

I have not confirmed yet, but problem you reported may be avoided by adding

sql_mode =

in the [mysqld] section of my.cnf, then restart mysqld.

Or, if you have root user privilege on mysql server, execute this expression

SET GLOBAL sql_mode = '';

Either of those operations has the same effect as the fix included in 6.2.60.

However, if you share the MySQL database with the other software, they might be affected by the operation above. Fixes on 6.2.60 won't affect the other software.

[titou3as]

When the new release Sympa 6.2.60 will be deployed into the repositories Ubuntu 20.04 ?

[titou3as]

When the new release Sympa 6.2.60 will be deployed into the repositories Ubuntu 20.04 ?

The fix will be included in the next release of Sympa (6.2.60), which is proposed to be released on 21st December this year.

Will the bug fix for version 6.2.60 only be limited to the MySQL.pm program or the database?
in the meantime can you give me what I can do in relation to the database for example to avoid the modifications to the program.
Thank you for your response.

I have not confirmed yet, but problem you reported may be avoided by adding

sql_mode =

in the [mysqld] section of my.cnf, then restart mysqld.

Or, if you have root user privilege on mysql server, execute this expression

SET GLOBAL sql_mode = '';

Either of those operations has the same effect as the fix included in 6.2.60.

However, if you share the MySQL database with the other software, they might be affected by the operation above. Fixes on 6.2.60 won't affect the other software.

When the new release Sympa 6.2.60 will be deployed into the repositories Ubuntu 20.04 ?

[ikedas]

When the new release Sympa 6.2.60 will be deployed into the repositories Ubuntu 20.04 ?

I suppose never. Ubuntu 20.04 (focal) adopts Sympa 6.2.40 as upstream, and I think this may not be changed.
Ubuntu 21.04 (hirsute) adopts Sympa 6.2.58 as upstream. Thus, I think the next release will adopt Sympa 6.2.60 or better.

An alternative is to apply the patch fixing this bug to Sympa 6.2.40 you installed (However, everytime you upgrade your Sympa, you have to apply this patch again).

[titou3as]

When the new release Sympa 6.2.60 will be deployed into the repositories Ubuntu 20.04 ?

I suppose never. Ubuntu 20.04 (focal) adopts Sympa 6.2.40 as upstream, and I think this may not be changed.
Ubuntu 21.04 (hirsute) adopts Sympa 6.2.58 as upstream. Thus, I think the next release will adopt Sympa 6.2.60 or better.

An alternative is to apply the patch fixing this bug to Sympa 6.2.40 you installed (However, everytime you upgrade your Sympa, you have to apply this patch again).

So there will never be a new version of Sympa into ubuntu 20.04 and it will still the lastest version of sympa the 6.2.40?

[racke]

That's how these distributions work.