Database changes when upgrading sympa?

[Michael D. Sofka]

I'm upgrading Sympa from version 6.1.4 to 6.1.17 by way of 6.1.7. This
is through a pair of Ubuntu upgrades: 11.10 -> 12.04 -> 14.04.

I'm testing the upgrade on a VM clone. During the 12.04 to 14.04
upgrade (Sympa 6.1.7. to Sympa 6.1.17), a series of PostgreSQL errors
appeared:

2014-10-08 15:37:35 EDT STATEMENT: INSERT INTO list_table (status_list,
name_list, robot_
list, subject_list, web_archive_list, topics_list, owners_list,
editors_list) VALUES ('ope
n', 'alumni-grad', 'cs.lists.rpi.edu', 'alumni-grad', 1, 'departmental',
'xxxxxx-***@public.gmane.org,
xxxxxx-***@public.gmane.org', 'xxxxxx-***@public.gmane.org,xxxxxx-***@public.gmane.org')
2014-10-08 15:37:35 EDT ERROR: relation "list_table" does not exist at
character 8
2014-10-08 15:37:35 EDT STATEMENT: UPDATE list_table SET status_list =
'open', name_list
= 'alumni-grad', robot_list = 'cs.lists.rpi.edu', subject_list =
'alumni-grad', web_archiv
e_list = 1, topics_list = 'departmental', owners_list =
'xxxxxx-***@public.gmane.org,xxxxxx-***@public.gmane.org',
editors_list = 'xxxxxx-***@public.gmane.org,xxxxxx-***@public.gmane.org' WHERE robot_list =
'cs.lists.rpi.edu' AND n
ame_list = 'alumni-grad'
2014-10-08 15:37:35 EDT ERROR: relation "list_table" does not exist at
character 13

and so on.


The upgrades server appears to function correctly, perhaps in a
compatibility mode? Or perhaps it is only partly correct.

I recall that at some point list configuration information was moved to
the database. Apparently this process is not automatic, or at least was
not done correctly during the upgrades.

I've been googling for any explanation of the upgrade process, but so
far all I've found are the DB schema descriptions/code on sympa.org. I
could recreate and reload the DB. Should this be done before the
upgrade? After? And if after does sympa.pl --sync_list_db load the
configs? Any guidance on where to look, or how to continue appreciated.

Mike

[Emmanuel Bouthenot]

On Fri, Oct 10, 2014 at 01:44:42PM -0400, Michael D. Sofka wrote:
[...]

Post by Michael D. Sofka
I've been googling for any explanation of the upgrade process, but so
far all I've found are the DB schema descriptions/code on sympa.org.
I could recreate and reload the DB. Should this be done before the
upgrade? After? And if after does sympa.pl --sync_list_db load the
configs? Any guidance on where to look, or how to continue
appreciated.

You should take a look at this thread:
https://listes.renater.fr/sympa/arc/sympa-users/2014-07/msg00004.html

Regards,

[Michael D. Sofka]

Post by Emmanuel Bouthenot
https://listes.renater.fr/sympa/arc/sympa-users/2014-07/msg00004.html
Regards,

Okay, that got me farther. I was able to upgrade the database with only
a little bit of difficulty.

Now when running:

sympa.pl --sync_list_db

I get errors about the list of owners being longer than 100 characters.
And indeed, in list_table defines owners_list, editors_list, and so on
as: varchar(100).

Two things:

First, given that email.addresses3413-***@public.gmane.org, it is not
difficult to exceed that limit. And in our case, student club lists,
for whatever reason, tend to have a lot of owners. Perhaps the field
should be larger.

Second, does it matter? It appears this is used as a cache. Is it
rebuilt on startup?

Mike

[IKEDA Soji]

On Tue, 14 Oct 2014 14:58:32 -0400

Post by Michael D. Sofka

Post by Emmanuel Bouthenot
https://listes.renater.fr/sympa/arc/sympa-users/2014-07/msg00004.html
Regards,

Okay, that got me farther. I was able to upgrade the database with only
a little bit of difficulty.
sympa.pl --sync_list_db
I get errors about the list of owners being longer than 100 characters.
And indeed, in list_table defines owners_list, editors_list, and so on
as: varchar(100).
difficult to exceed that limit. And in our case, student club lists,
for whatever reason, tend to have a lot of owners. Perhaps the field
should be larger.
Second, does it matter? It appears this is used as a cache. Is it
rebuilt on startup?

You may extend those fields as large as you want, _if_ database
engine will permit.

If you changed structure of list_table, you have to run
"sympa.pl --sync_list_db" manually: Table will not be rebuilt
automatically.


Some lists with democratic policy tend to delegate ownership to
many or (extremely) all of the subscribers. The limitation by
lengths of owners_list and editors_list fields are planned to be
removed by Sympa 6.2.


Regards,

--- Soji

Post by Michael D. Sofka
Mike
--
C&MT Sr. Systems Programmer, Email, TeX, Epistemology
Rensselaer Polytechnic Institute, Troy, NY. http://www.rpi.edu/~sofkam/

[Michael D. Sofka]

Post by IKEDA Soji
You may extend those fields as large as you want, _if_ database
engine will permit.

It will permit, and I tend to over-design database servers so I am not
concerned about space.

Post by IKEDA Soji
If you changed structure of list_table, you have to run
"sympa.pl --sync_list_db" manually: Table will not be rebuilt
automatically.

I get that, but my bigger question is: Is there any reason to expand
the fields? Because it doesn't seem to matter.

Adding owners until the list exceeds VARCHAR(100) generate an
error in the postgresql logs, but appears to have no other affects. The
owner is added to the lists config file, and they do display in the list
configuration page---even after Sympa and Apache are restarted.
db_list_cache is "on", in sympa.conf. But this test is not through,
and it is possible owners not on the list may have difficulties.
Regardless,
I'll at least double the field size in my upgrade, but I would like to know
if this error is something to look out for, and warn list owners about.

Post by IKEDA Soji
Some lists with democratic policy tend to delegate ownership to
many or (extremely) all of the subscribers. The limitation by
lengths of owners_list and editors_list fields are planned to be
removed by Sympa 6.2.

That's good to know, but I will need to finish this upgrade before then,
alas.

Mike