I am currently debugging problems with PluggableAuth/SimpleSAMLphp, which behave differently between a Wiki instance created from scratch (has problems) and one that was upgraded from 1.35.8 to 1.39.2 (no problems). The general configuration (LocalSettings.php), extensions and everything else is identical.
So I decided to take a closer look at the database (PostgreSQL in my case) and found some differences, that were obviously not handled by the upgrade process. Some differences are benign, like indexes with different names, e.g. on user_newtalk:
fresh install:
\dS user_newtalk
Table "public.user_newtalk"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+----------
user_id | integer | | not null | 0
user_ip | text | | not null | ''::text
user_last_timestamp | timestamp with time zone | | |
Indexes:
"un_user_id" btree (user_id)
"un_user_ip" btree (user_ip)upgraded:
\dS user_newtalk
Table "public.user_newtalk"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+----------
user_id | integer | | not null | 0
user_ip | text | | not null | ''::text
user_last_timestamp | timestamp with time zone | | |
Indexes:
"user_newtalk_id_idx" btree (user_id)
"user_newtalk_ip_idx" btree (user_ip)In other cases, the semantic of the indexes is slightly different:
fresh install:
\dS objectcache
Table "public.objectcache"
Column | Type | Collation | Nullable | Default
----------+--------------------------+-----------+----------+----------------------------------------
keyname | text | | not null | ''::text
value | text | | |
exptime | timestamp with time zone | | not null |
modtoken | character varying(17) | | not null | '00000000000000000'::character varying
flags | integer | | |
Indexes:
"objectcache_pkey" PRIMARY KEY, btree (keyname)
"exptime" btree (exptime)upgraded:
\dS objectcache
Table "public.objectcache"
Column | Type | Collation | Nullable | Default
----------+--------------------------+-----------+----------+----------------------------------------
keyname | text | | |
value | text | | | '\x'::bytea
exptime | timestamp with time zone | | not null |
modtoken | character varying(17) | | not null | '00000000000000000'::character varying
flags | integer | | |
Indexes:
"objectcacache_exptime" btree (exptime)
"objectcache_keyname_key" UNIQUE CONSTRAINT, btree (keyname)and sometimes triggers were not removed (or not created on the new Wiki?):
fresh install:
\dS page
Table "public.page"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------------------------------------
page_id | integer | | not null | nextval('page_page_id_seq'::regclass)
page_namespace | integer | | not null |
page_title | text | | not null |
page_is_redirect | smallint | | not null | 0
page_is_new | smallint | | not null | 0
page_random | double precision | | not null |
page_touched | timestamp with time zone | | not null |
page_links_updated | timestamp with time zone | | |
page_latest | integer | | not null |
page_len | integer | | not null |
page_content_model | text | | |
page_lang | text | | |
titlevector | tsvector | | |
Indexes:
"page_pkey" PRIMARY KEY, btree (page_id)
"page_len" btree (page_len)
"page_name_title" UNIQUE, btree (page_namespace, page_title)
"page_random" btree (page_random)
"page_redirect_namespace_len" btree (page_is_redirect, page_namespace, page_len)
"ts2_page_title" gin (titlevector)
Triggers:
ts2_page_title BEFORE INSERT OR UPDATE ON page FOR EACH ROW EXECUTE FUNCTION ts2_page_title()upgraded:
\dS page
Table "public.page"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------------------------------------
page_id | integer | | not null | nextval('page_page_id_seq'::regclass)
page_namespace | integer | | not null |
page_title | text | | not null |
page_is_redirect | smallint | | not null | 0
page_is_new | smallint | | not null | 0
page_random | double precision | | not null | random()
page_touched | timestamp with time zone | | not null |
page_links_updated | timestamp with time zone | | |
page_latest | integer | | not null |
page_len | integer | | not null |
page_content_model | text | | |
page_lang | text | | |
titlevector | tsvector | | |
Indexes:
"page_pkey" PRIMARY KEY, btree (page_id)
"page_len" btree (page_len)
"page_main_title" btree (page_title text_pattern_ops) WHERE page_namespace = 0
"page_mediawiki_title" btree (page_title text_pattern_ops) WHERE page_namespace = 8
"page_name_title" UNIQUE, btree (page_namespace, page_title)
"page_project_title" btree (page_title text_pattern_ops) WHERE page_namespace = 4
"page_random" btree (page_random)
"page_redirect_namespace_len" btree (page_is_redirect, page_namespace, page_len)
"page_talk_title" btree (page_title text_pattern_ops) WHERE page_namespace = 1
"page_user_title" btree (page_title text_pattern_ops) WHERE page_namespace = 2
"page_utalk_title" btree (page_title text_pattern_ops) WHERE page_namespace = 3
"ts2_page_title" gist (titlevector)
Triggers:
page_deleted AFTER DELETE ON page FOR EACH ROW EXECUTE FUNCTION page_deleted()
ts2_page_title BEFORE INSERT OR UPDATE ON page FOR EACH ROW EXECUTE FUNCTION ts2_page_title()All in all I found differences in the following tables:
- change_tag
- logging
- objectcache
- page
- page_props
- page_restrictions
- protected_titles
- redirect
- revision
- sites
- text
- user
- user_newtalk
- watchlist
I can provide a complete diff on request.
Using these findings and taking a (very) quick glance at the sources of PluggableAuth and SimpleSAMLphp, I don't see any reason why the differences in e.g. user should cause the problems I have, but I thought I let someone know about it :) Maybe someone can explain if and why these differences are no problem, or maybe someone would like to fine tune the upgrade script.