PostgreSQL: Unable to edit owners/subscribers with 6.2.26 and 6.2.28

[lspagnol]

Theses versions introduce an attempt to solve sync problems with database, but the following code is a regression (impossible to update Owner / subscriber):
Note: solution was given by Jean-Luc Oms on list sympa-fr AT listes DOT renater DOT fr

# diff src/cgi/wwsympa.fcgi /home/sympa/bin/wwsympa.fcgi > wwsympa_remove-tentative-fix-owner.patch

# cat wwsympa_remove-tentative-fix-owner.patch
10736,10757c10736,10757
<     # Sync owner/editor in list config with database.
<     # FIXME: TENTATIVE FIX.
<     my @static_admins =
<         grep {$_ and $_->{'subscribed'}} @{$list->_get_admins || []};
<     my @subparams;
<     # Current static owners.
<     my $static_owner =
<         [grep { $_->{'role'} and $_->{'role'} eq 'owner' } @static_admins];
<     @subparams = qw(email gecos info profile reception visibility);
<     foreach my $u (@$static_owner) {
<         $u = {map { $_ => $u->{$_} } @subparams};
<     }
<     $list->{'admin'}{'owner'} = $static_owner;
<     # Current static editors.
<     my $static_editor =
<         [grep { $_->{'role'} and $_->{'role'} eq 'editor' } @static_admins];
<     @subparams = qw(email gecos info reception visibility);
<     foreach my $u (@$static_editor) {
<         $u = {map { $_ => $u->{$_} } @subparams};
<     }
<     $list->{'admin'}{'editor'} = $static_editor;
<     # FIXME: END OF TENTATIVE FIX.
---
> #    # Sync owner/editor in list config with database.
> #    # FIXME: TENTATIVE FIX.
> #    my @static_admins =
> #        grep {$_ and $_->{'subscribed'}} @{$list->_get_admins || []};
> #    my @subparams;
> #    # Current static owners.
> #    my $static_owner =
> #        [grep { $_->{'role'} and $_->{'role'} eq 'owner' } @static_admins];
> #    @subparams = qw(email gecos info profile reception visibility);
> #    foreach my $u (@$static_owner) {
> #        $u = {map { $_ => $u->{$_} } @subparams};
> #    }
> #    $list->{'admin'}{'owner'} = $static_owner;
> #    # Current static editors.
> #    my $static_editor =
> #        [grep { $_->{'role'} and $_->{'role'} eq 'editor' } @static_admins];
> #    @subparams = qw(email gecos info reception visibility);
> #    foreach my $u (@$static_editor) {
> #        $u = {map { $_ => $u->{$_} } @subparams};
> #    }
> #    $list->{'admin'}{'editor'} = $static_editor;
> #    # FIXME: END OF TENTATIVE FIX.

[ikedas]

Hi @lspagnol,
Could you please show us how you reproduce this bug step by step? (Show page “xxxx”, enter address to “xxxx”, click “xxxx” button and so on). Thanks.

[lspagnol]

https://listes.univ-reims.fr/sympa/create_list_request
name of list "test3"
check an fill "List type", "Object" , "Public", and "Description"
Send
Result in WebUI => "Erreur de configuration", Owner is empty
The list isn't accessible via WebUI, but was created. Config file looks OK (/home/sympa/list_data/univ-reims.fr/test2/config): I'm owner, there is nothing in DB.
I found something interesting in LOGs:

Mar 22 11:30:48 listes wwsympa[20096]: err main::#1575 > main::do_edit_list#10541 > Sympa::List::sync_include_admin#7156 > Sympa::List::add_list_admin#3971 > Sympa::DatabaseDriver::PostgreSQL::do_prepared_query#101 > Sympa::Database::do_prepared_query#382 Unable to execute SQL statement "INSERT INTO admin_table (user_admin, comment_admin, list_admin, robot_admin, date_epoch_admin, update_epoch_admin, reception_admin, visibility_admin, subscribed_admin, included_admin, include_sources_admin, role_admin, info_admin, profile_admin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)": (23502) ERROR:  null value in column "date_admin" violates not-null constraint#012DETAIL:  Failing row contains (test2, laurent.spagnol@univ-reims.fr, univ-reims.fr, owner, null, null, mail, null, 1, 0, null, null, normal, noconceal, 1521714648, 1521714648).
Mar 22 11:30:48 listes wwsympa[20096]: err main::#1575 > main::do_edit_list#10541 > Sympa::List::sync_include_admin#7156 > Sympa::List::add_list_admin#3998 Unable to add admin laurent.spagnol@univ-reims.fr to table admin_table for list Sympa::List <test2@univ-reims.fr>:
Mar 22 11:30:48 listes wwsympa[20096]: err main::#1575 > main::do_edit_list#10541 > Sympa::List::sync_include_admin#7158 Failed to add new owner(s) to list Sympa::List <test2@univ-reims.fr>
Mar 22 11:30:48 listes wwsympa[20096]: debug2 Sympa::send_notify_to_listmaster(Sympa::List <test2@univ-reims.fr>, web_intern_error, HASH)

=> ERROR:  null value in column "date_admin" violates not-null constraint#012DETAIL:  Failing row contains (test2, laurent.spagnol@univ-reims.fr, univ-reims.fr, owner, null, null, mail, null, 1, 0, null, null, normal, noconceal, 1521714648, 1521714648).
Mar 22 11:30:48 listes wwsympa[20096]: err main::#1575 > main::do_edit_list#10541 > Sympa::List::sync_include_admin#7156 > Sympa::List::add_list_admin#3998 Unable to add admin laurent.spagnol@univ-reims.fr to table admin_table for list Sympa::List <test2@univ-reims.fr>:

=> "ERROR: null value in column "date_admin" violates not-null constraint"

[lspagnol]

Please note that I have another critical problem: removing subscriber in list is OK, but it is impossible to add subscriber.
I have updated from 6.2.22 to 6.2.26 tuesday and from 6.2.26 to 6.2.28 today

[ikedas]

Hi @lspagnol,

Can you use psql? If you can, please check if:

• date_admin column of admin_table has NOT NULL constraint, and
• date_subscriber column of subscriber_table has NOT NULL constraint.

If they are, drop NOT NULL constraint issuing these queries:

> ALTER TABLE admin_table ALTER COLUMN date_admin DROP NOT NULL;
> ALTER TABLE subscriber_table ALTER COLUMN date_subscriber DROP NOT NULL;

Then please check if you can add owner and subscriber.

---

Note for readers

Currently, it turns out that:

• This bug occurs with PostgreSQL.
• This bug doesn't occur with MySQL.

[lspagnol]

I confirm:

• date_admin column of admin_table has NOT NULL constraint
• date_subscriber column of subscriber_table has NOT NULL constraint
  I've done modification:

ALTER TABLE admin_table ALTER COLUMN date_admin DROP NOT NULL;
ALTER TABLE subscriber_table ALTER COLUMN date_subscriber DROP NOT NULL;
I've do some tests:
ADD subscriber to a list => OK
REMOVE subscriber from a list => OK
Create new list => OK
ADD and REMOVE owner => OK
ADD and REMOVE moderator => OK
The problems seems to be solved:

• unable to create new list via WebUI (config file is OK but no data in DB)
• add owner
• add subscriber
  Thanks !!

[ikedas]

@lspagnol, thanks for quickly confirming!
(I changed title)

We may announce this bug to users in some ways, then I will close this issue.

[lspagnol]

And thanks for quickly fix ! :)
I've done some additional test: I have created "test2" and "test3" lists. These lists where created under my domain and have coherent "config" file but they where not available in WebUI and have not entry in my aliases file.
So i have done "sympa --sync_list_db", "sympa --reload_list_config" and restart WebUI CGI => these lists are still not available in WebUI.
So i have to remove "test2" and "test3" manually.

[ikedas]

Fixed. A patch release 6.2.30 will be released soon.

[jloms]

What about the TENTATIVE FIX in wwsympa.fcgi.in ?
This part of code is still present in 6.30 and was a problem in 6.26 (no owner/editor when you edit list conf on the wen interface).

[ikedas]

Hi @jloms,

6.2.26 was withdrawn by ugry typo in "tentative fix" (see fix on 6.2.28). This issue is not related to it directly.

[jloms]

Sorry I missed this modification.
Upgraded to 6.2.30 ... all is ok
Thanks