plugin migration: index creation fails

[jelmd]

When applying focalboad and playbook migration, the following errors occure:

# boards
2025-02-10T18:44:46.716019Z ERROR PostgreSQL Database error 42P07: relation "idx_preferences_name" already exists
QUERY: CREATE INDEX idx_preferences_name ON mattermost.focalboard_preferences (name);
2025-02-10T18:44:46.724020Z ERROR PostgreSQL Database error 42P07: relation "idx_preferences_category" already exists
QUERY: CREATE INDEX idx_preferences_category ON mattermost.focalboard_preferences (category);

# playbooks
2025-02-10T19:14:00.384010Z ERROR PostgreSQL Database error 42P07: relation "ir_playbookmember_playbookid" already exists
QUERY: CREATE INDEX IR_PlaybookMember_PlaybookID ON mattermost.ir_playbook (id);

Checks indeed reveal, that there seems to be some name collisions:

mattermost=> SELECT tablename, indexname, indexdef FROM pg_indexes where indexname similar to 'idx_preferences_(name|category)' ORDER BY tablename, indexname;
  tablename  |        indexname         |                                      indexdef                                      
-------------+--------------------------+------------------------------------------------------------------------------------
 preferences | idx_preferences_category | CREATE INDEX idx_preferences_category ON public.preferences USING btree (category)
 preferences | idx_preferences_name     | CREATE INDEX idx_preferences_name ON public.preferences USING btree (name)
(2 rows)

mattermost=> SELECT tablename, indexname, indexdef FROM pg_indexes where indexname='ir_playbookmember_playbookid' ORDER BY tablename, indexname;
     tablename     |          indexname           |                                            indexdef                                            
-------------------+------------------------------+------------------------------------------------------------------------------------------------
 ir_playbookmember | ir_playbookmember_playbookid | CREATE INDEX ir_playbookmember_playbookid ON public.ir_playbookmember USING btree (playbookid)
(1 row)

So what should be done: Create INDEX with different names like idx_board_preferences_name. idx_board_preferences_category, and idx_ir_playbook_id manually?

[t1nux]

I have the same issues for playbooks. I have the feeling this is new since 10.5, I'm not sure though.

[jelmd]

FWIIW: Analyzed the MySQL schema of my production server (MM 8.1.2 + focalboard 7.11.4) with the PG schema of a vanilla instance with the same versions: see results.txt .

So because the focalboard_preferences table is empty (at least in our instance), the index name collision errors wrt. focalboard can be ignored (hopefully the index is not explicitly used in certain statements hard coded somewhere).

The index name collision error for playbooks can be ignored too because a primary key with the same signature already exists (perhaps it gets generated because of the FK constraint in ir_playbookmember).

Furthermore (optional) the index ir_playbookmember_playbookid_memberid_key as well as ir_statusposts_incidentid_postid_key can be dropped too, because a primary key with the same signature already exists.

Last but not least (for completeness): The MM migration warnings wrt. fk_retentionpolicieschannels_retentionpolicies and fk_retentionpoliciesteams_retentionpolicies (see output in #37 (comment)) can be ignored, too - they exist after migration so IMHO not something to worry about.

[t1nux]

@jelmd Thanks for the info!

To be clear, I only had the issue with playbooks. I had other issues with boards, but I fixed them in a different way.

Regarding the playbooks issue, I went ahead ignoring the error and tried to see what was going to happen, but I am facing issues. While I did not test anything extensively in operation, I am getting this error in the server logs:

{
  "caller": "app/plugin_api.go:1011",
  "error": "sql: Scan error on column index 2, name \"digestnotificationsettingsjson\": unsupported Scan, storing driver.Value type string into type *json.RawMessage\nfailed to get userInfo by userId '5t6bp17ehifijj6ph9q8aa1hzr'\ngithub.com/mattermost/mattermost-plugin-playbooks/server/sqlstore.(*userInfoStore).Get\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/sqlstore/user_info.go:51\ngithub.com/mattermost/mattermost-plugin-playbooks/server/api.(*BotHandler).connect\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/api/bot.go:175\ngithub.com/mattermost/mattermost-plugin-playbooks/server/api.NewBotHandler.withContext.func3\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/api/context.go:36\nnet/http.HandlerFunc.ServeHTTP\n\tnet/http/server.go:2136\ngithub.com/mattermost/mattermost-plugin-playbooks/server/api.MattermostAuthorizationRequired.func1\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/api/api.go:109\nnet/http.HandlerFunc.ServeHTTP\n\tnet/http/server.go:2136\ngithub.com/mattermost/mattermost-plugin-playbooks/server/api.LogRequest.func1\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/api/logger.go:46\nnet/http.HandlerFunc.ServeHTTP\n\tnet/http/server.go:2136\ngithub.com/gorilla/mux.(*Router).ServeHTTP\n\tgithub.com/gorilla/[email protected]/mux.go:210\ngithub.com/mattermost/mattermost-plugin-playbooks/server/api.(*Handler).ServeHTTP\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/api/api.go:60\nmain.(*Plugin).ServeHTTP\n\tgithub.com/mattermost/mattermost-plugin-playbooks/server/plugin.go:96\ngithub.com/mattermost/mattermost-server/v6/plugin.(*hooksRPCServer).ServeHTTP\n\tgithub.com/mattermost/mattermost-server/[email protected]/plugin/client_rpc.go:453\nreflect.Value.call\n\treflect/value.go:596\nreflect.Value.Call\n\treflect/value.go:380\nnet/rpc.(*service).call\n\tnet/rpc/server.go:382\nruntime.goexit\n\truntime/asm_amd64.s:1650",
  "level": "error",
  "msg": "An internal error has occurred. Check app server logs for details.",
  "plugin_caller": "github.com/mattermost/mattermost-plugin-playbooks/server/api/api.go:85",
  "plugin_id": "playbooks",
  "request_id": "tosyqatmdjgbtckk6aur6xtnar",
  "timestamp": "2025-02-12 12:30:24.353 +01:00"
}

I do not know if this is related, but, for now, this is what is keeping me from making the migration final.

[t1nux]

@jelmd Any idea what the above issue might be related to?

[t1nux]

@jelmd I solved my issue with

ALTER TABLE ir_userinfo ALTER COLUMN digestnotificationsettingsjson SET DATA TYPE json USING digestnotificationsettingsjson::json;

[jelmd]

@t1nux Thanx for sharing. Haven't seen your error on my instance logs so far, neither after PG migration (v8.1.2) nor after upgrade of MM to v9.11.9 - schema looks like this:

CREATE TABLE public.ir_userinfo (
    id character varying(26) NOT NULL,
    lastdailytododmat bigint,
    digestnotificationsettingsjson json
);