Creating this task to specifically discuss how we should proceed to be able to merge: https://gerrit.wikimedia.org/r/#/c/370190/1 (we are coming from: T172485#3500757 and T17441#3500739
To sum up, we, DBAs, have finished converting UNIQUE into PRIMARY keys and we would like this to be reflected on tables.sql so future wikis are created with the same PKs that we have deployed in core.
I have been chatting with @Reedy and we thought it was better to discuss here rather than hijack other tasks as this is specifically for mediawiki core.
The statements we have used to alter the affected tables are:
ALTER TABLE /*_*/categorylinks DROP KEY /*i*/cl_from, ADD PRIMARY KEY (cl_from,cl_to); ALTER TABLE /*_*/templatelinks DROP INDEX /*i*/tl_from, ADD PRIMARY KEY (tl_from,tl_namespace,tl_title); ALTER TABLE /*_*/pagelinks DROP INDEX /*i*/pl_from, ADD PRIMARY KEY (pl_from,pl_namespace,pl_title); ALTER TABLE /*_*/text DROP KEY /*i*/old_id, ADD PRIMARY KEY (old_id); ALTER TABLE /*_*/imagelinks DROP KEY /*i*/il_from, ADD PRIMARY KEY (il_from,il_to); ALTER TABLE /*_*/iwlinks DROP KEY /*i*/iwl_from, ADD PRIMARY KEY (iwl_from,iwl_prefix,iwl_title); ALTER TABLE /*_*/langlinks DROP KEY /*i*/ll_from, ADD PRIMARY KEY (ll_from,ll_lang); ALTER TABLE /*_*/log_search DROP KEY /*i*/ls_field_val, ADD PRIMARY KEY (ls_field,ls_value,ls_log_id); ALTER TABLE /*_*/module_deps DROP KEY /*i*/md_module_skin, ADD PRIMARY KEY (md_module,md_skin); ALTER TABLE /*_*/objectcache DROP KEY /*i*/keyname, ADD PRIMARY KEY (keyname); ALTER TABLE /*_*/querycache_info DROP KEY /*i*/qci_type, ADD PRIMARY KEY (qci_type); ALTER TABLE /*_*/site_stats DROP KEY /*i*/ss_row_id, ADD PRIMARY KEY (ss_row_id); ALTER TABLE /*_*/transcache DROP KEY /*i*/tc_url_idx, ADD PRIMARY KEY (tc_url); ALTER TABLE /*_*/user_former_groups DROP KEY /*i*/ufg_user_group, ADD PRIMARY KEY (ufg_user,ufg_group); ALTER TABLE /*_*/user_properties DROP KEY /*i*/user_properties_user_property, ADD PRIMARY KEY (up_user,up_property);
If someone more experienced with mediawiki core and its patches and deployment can help here, it would be great.
To be done at a later date/after (USE|FORCE|IGNORE) INDEX statements that are using the tl_from or pl_from indexes...
ALTER TABLE /*_*/templatelinks DROP INDEX /*i*/tl_from; ALTER TABLE /*_*/pagelinks DROP INDEX /*i*/pl_from;