Page MenuHomePhabricator

wikitech database has almost all of its varbinary fields wrong
Closed, ResolvedPublic

Description

This drifts unique only to s10 came up in the report after we run it on data type:

{
    "category cat_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "imagelinks il_to field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "interwiki iw_prefix field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "interwiki iw_wikiid field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "iwlinks iwl_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "job job_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "l10n_cache lc_key field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "langlinks ll_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "log_search ls_value field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "pagelinks pl_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "protected_titles pt_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "querycache qc_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "querycachetwo qcc_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "querycachetwo qcc_titletwo field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "redirect rd_fragment field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "redirect rd_interwiki field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "redirect rd_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "templatelinks tl_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "updatelog ul_key field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    },
    "watchlist wl_title field-type-mismatch": {
        "s10": [
            "db1128:labswiki"
        ]
    }
}

This needs a clean up.

Related Objects

Event Timeline

Marostegui triaged this task as Medium priority.Dec 4 2020, 6:18 AM
Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui added a subscriber: Andrew.

Mentioned in SAL (#wikimedia-operations) [2020-12-11T21:46:10Z] <Amir1> Running schema changes on wikitech database for T269348

It seems most of issues is because its charset is set to latin1:

[email protected](labswiki)> SHOW CREATE TABLE updatelog;
 ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Table     | Create Table                                                                                                                                                 |
 ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| updatelog | CREATE TABLE `updatelog` (
  `ul_key` varchar(255) NOT NULL,
  `ul_value` blob DEFAULT NULL,
  PRIMARY KEY (`ul_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.01 sec)

Can I just convert everything to utf-8?

I would suggest we try first on the labstestwiki - @Andrew?

Another ping? One month since my last ping has passed.

I still think it needs to be tested frist on labtestwiki: T269348#6737173

Mentioned in SAL (#wikimedia-operations) [2021-03-13T18:53:19Z] <Amir1> run schema changes for varbinary on wikitech (T269348)

Mentioned in SAL (#wikimedia-operations) [2021-03-13T19:01:59Z] <Amir1> change default charset of all core tables in labstestwiki to binary (T269348)

All changed now to binary in labstestwiki, I let it wait for a couple of days to see if things break and if not, I'll change labswiki.

If we change charsets to binary, this would be fixed automatically. Shall I do it?

@Ladsgroup if you want to take care of it, that's good!.

Mentioned in SAL (#wikimedia-operations) [2021-06-02T22:19:53Z] <Amir1> setting charset of all tables in wikitech to binary (T284108 T269348)