Database concerns

This error may appear while upgrading from a version of Sympa < 5.2b to a more recent version. This error is related to the changes in the admin_table structure : a new robot_admin field has been added (this was required to provide a full virtual hosting support in Sympa, thus allowing to manage 2 lists with the same name within different virtual hosts) and it is part of the primary key. The upgrade process will populate the new robot_admin field with the appropriate value. The problem appears if an update of the admin_table was triggered before the upgrade procedures were run, resulting in a duplicate entry error.

The fix consists in emptying the admin_table to prevent such conflicts.

DELETE FROM admin_table;

You can safely run this SQL command because the admin_table in just a cache for data primarily defined in the list configuration files.

You might need to perform a bulk update of your list members email addresses because your top level domain has changed. Let's consider you wish to change every xx@old.tld to xx@new.tld.

Sympa does not provide and API to perform such bulk operations, therefore, we just provide the set of actions that you should carry :

1. update the subscriber_table : note that this table includes both subscribed members and members included from a third-party datasource. You should take care of updating the datasource (LDAP, SQL, flat file, other mailing list) for this second category of users

or the @old.tld will be back soon in the Sympa database. The SQL query will look like this : UPDATE subscriber_table SET user_subscriber= concat(substring(user_subscriber, 1, instr(user_subscriber, '@')),'new.tld') WHERE user_subscriber LIKE '%@new.tld';

1. update the user_table : UPDATE user_table SET email_user= concat(substring(email_user, 1, instr(email_user, '@')),'new.tld') WHERE email_user LIKE '%@new.tld';
2. the admin_table does not need to be changed because it is just a cache of data defined in list config files
3. update the list configuration files : grep old.tld /home/sympa/expl*/config

Il you have problems with the list members cache (subscribed members that cannot be unsubscribed, members that never get expired), you probably have an inconsistency in your subscriber_table SQL table. This is due to an incorrect upgrade process of Sympa that leeds to entries of subscriber_table they have neither the subscribed_subscriber field nor the included_subscriber field set.

This problem can be fixed, by running the following SQL query on your sympa database :

UPDATE subscriber_table SET subscribed_subscriber=1 WHERE ((included_subscriber IS NULL OR included_subscriber!=1) AND (subscribed_subscriber IS NULL OR subscribed_subscriber!=1))

You might get this error in Sympa log file if you are using Sympa 5.3 or later with an old version of the Mysql server.

Sympa 5.3 requires mysql version 4.1.