Page MenuHomePhabricator
Create Task
Maniphest T269463

Drop cl_timestamp
Closed, InvalidPublic
Actions

Description

In migrating categorylinks to abstract schema I accidentally changed datatype of cl_timestamp from timestamp to binary(14). I was about to fix it but I realized this column is not used anywhere. Even its documentation says it's not used at the moment:

This isn't really used at present. Provided for an optional sorting method by approximate addition time.

The problem is that this documentation was added in 2005 (commit 5fdbef206f35adeb11ee0b97894f8fdb5c451a0e) the field probably exited even before that and not used until now and it's not even not fixed along other timestamp fields (T42626: Standardise type of timestamp database fields (MySQL)).

The idea of cl_timestamp is delicious but keeping an empty field (plus its index) on such a large table (in commons it's 200GB) for more than 15 years because it might get used one day(TM) seems a bit excessive to me. We can add it back again when resources to implement such feature is provided.

Related Objects

Event Timeline

Ladsgroup created this task.Dec 4 2020, 6:36 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 4 2020, 6:36 PM
Reedy added a comment.Dec 4 2020, 6:38 PM

I think codesearch is broken

Targets
    Occurrences of 'cl_timestamp' in Directory /Users/reedy/PhpstormProjects/mediawiki
Found Occurrences  (105 usages found)
    Unclassified  (56 usages found)
        mediawiki  (56 usages found)
            core  (42 usages found)
                includes  (4 usages found)
                    api  (4 usages found)
                        ApiQueryCategories.php  (1 usage found)
                            156 $vals['timestamp'] = wfTimestamp( TS_ISO_8601, $row->cl_timestamp );
                        ApiQueryCategoryMembers.php  (3 usages found)
                            235 $this->setContinueEnumParameter( 'continue', "$row->cl_timestamp|$row->cl_from" );
                            274 $vals['timestamp'] = wfTimestamp( TS_ISO_8601, $row->cl_timestamp );
                            280 $this->setContinueEnumParameter( 'continue', "$row->cl_timestamp|$row->cl_from" );
                maintenance  (18 usages found)
                    archives  (2 usages found)
                        patch-categorylinks.sql  (2 usages found)
                            26 cl_timestamp timestamp NOT NULL,
                            35 KEY cl_timestamp(cl_to,cl_timestamp)
                    postgres  (2 usages found)
                        tables-generated.sql  (2 usages found)
                            565 cl_timestamp TIMESTAMPTZ NOT NULL,
                            575 CREATE INDEX cl_timestamp ON categorylinks (cl_to, cl_timestamp);
                    sqlite  (11 usages found)
                        archives  (9 usages found)
                            initial-indexes.sql  (2 usages found)
                                136 cl_timestamp timestamp NOT NULL
                                140 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks_tmp (cl_to,cl_timestamp);
                            patch-categorylinks-cl_to-varbinary.sql  (3 usages found)
                                6 cl_timestamp BLOB NOT NULL,
                                14 SELECT cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type
                                24 CREATE INDEX cl_timestamp ON /*_*/categorylinks (cl_to, cl_timestamp);
                            patch-categorylinks-fix-pk.sql  (4 usages found)
                                26 cl_timestamp timestamp NOT NULL,
                                44 INSERT INTO /*_*/categorylinks_tmp(cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type)
                                45 SELECT cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type
                                58 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                        tables-generated.sql  (2 usages found)
                            539 cl_timestamp BLOB NOT NULL,
                            549 CREATE INDEX cl_timestamp ON /*_*/categorylinks (cl_to, cl_timestamp);
                    refreshLinks.php  (1 usage found)
                        473 $timestamp = $row->cl_timestamp;
                    tables-generated.sql  (2 usages found)
                        504 cl_timestamp BINARY(14) NOT NULL,
                        510 INDEX cl_timestamp (cl_to, cl_timestamp),
                tests  (19 usages found)
                    phpunit  (19 usages found)
                        data  (19 usages found)
                            db  (19 usages found)
                                sqlite  (19 usages found)
                                    tables-1.13.sql  (1 usage found)
                                        95 cl_timestamp timestamp ) /*$wgDBTableOptions*/;
                                    tables-1.15.sql  (2 usages found)
                                        119 cl_timestamp timestamp NOT NULL
                                        123 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.16.sql  (2 usages found)
                                        126 cl_timestamp timestamp NOT NULL
                                        130 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.17.sql  (2 usages found)
                                        128 cl_timestamp timestamp NOT NULL,
                                        134 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.18.sql  (2 usages found)
                                        134 cl_timestamp timestamp NOT NULL,
                                        140 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.19.sql  (2 usages found)
                                        136 cl_timestamp timestamp NOT NULL,
                                        142 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.20.sql  (2 usages found)
                                        137 cl_timestamp timestamp NOT NULL,
                                        143 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.21.sql  (2 usages found)
                                        142 cl_timestamp timestamp NOT NULL,
                                        148 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.22.sql  (2 usages found)
                                        143 cl_timestamp timestamp NOT NULL,
                                        149 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                                    tables-1.23.sql  (2 usages found)
                                        145 cl_timestamp timestamp NOT NULL,
                                        151 CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
                HISTORY  (1 usage found)
                    16287 * (bug 12584) Don't reset cl_timestamp when auto-updating sort key on move
            extensions  (14 usages found)
                DynamicPageList  (4 usages found)
                    DPLMain.php  (4 usages found)
                        1958 $sSqlCl_timestamp = '';
                        2486 $sSqlCl_timestamp = ", DATE_FORMAT(cl0.cl_timestamp, '%Y%m%d%H%i%s') AS cl_timestamp";
                        2524 $sSqlRev_timestamp . $sSqlRev_id . $sSqlCats . $sSqlCl_timestamp .
                        3055 $dplArticle->mDate = $row->cl_timestamp;
                DynamicPageListEngine  (3 usages found)
                    includes  (2 usages found)
                        DpleFeatureResults.php  (2 usages found)
                            165 if ( isset( $row->cl_timestamp ) ) {
                            166 $title->dpleCustom['categoryadd'] = $row->cl_timestamp;
                    HISTORY  (1 usage found)
                        32 first category is now aliased as <code>cl_timestamp</code>. This does not change anything unless you directly use <code>DynamicPageListEngineQuery::getResult
                GoogleNewsSitemap  (1 usage found)
                    includes  (1 usage found)
                        specials  (1 usage found)
                            GoogleNewsSitemap.php  (1 usage found)
                                176 $pubDate = $row->cl_timestamp ?? wfTimestampNow();
                intersection  (2 usages found)
                    includes  (2 usages found)
                        DynamicPageListHooks.php  (2 usages found)
                            546 $categoryDate = wfTimestamp( TS_ISO_8601, $row->cl_timestamp );
                            555 $categoryDate = $contLang->date( wfTimestamp( TS_MW, $row->cl_timestamp ) );
                Memento  (4 usages found)
                    tests  (4 usages found)
                        docker-image  (4 usages found)
                            backup.sql  (2 usages found)
                                163 `cl_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                                168 KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
                            skeleton.sql  (2 usages found)
                                163 `cl_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                                168 KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
    Usage in comments  (9 usages found)
        mediawiki  (9 usages found)
            core  (2 usages found)
                includes  (2 usages found)
                    specials  (1 usage found)
                        SpecialRandomInCategory.php  (1 usage found)
                            211 *   was a large gap in the distribution of cl_timestamp values. This way instead
                    MovePage.php  (1 usage found)
                        598 // cl_timestamp, which may disturb time-based lists on some sites.
            extensions  (7 usages found)
                DynamicPageList  (1 usage found)
                    DPLMain.php  (1 usage found)
                        2484 // format cl_timestamp field (type timestamp) to string in same format AS rev_timestamp field
                GoogleNewsSitemap  (5 usages found)
                    includes  (5 usages found)
                        specials  (5 usages found)
                            GoogleNewsSitemap.php  (5 usages found)
                                174 // @todo FIXME: Under what circumstance would cl_timestamp not be set?
                                191 * good. Does this by checking the cl_timestamp of the latest article
                                194 * The first check (cl_timestamp) is needed to protect against someone removing
                                252 // Part 2: cl_timestamp:
                                404 // Without an include category, cl_timestamp will be null.
                intersection  (1 usage found)
                    includes  (1 usage found)
                        DynamicPageListHooks.php  (1 usage found)
                            706 // TODO: Maybe could do something like check max(cl_timestamp) in
    Usage in string constants  (40 usages found)
        mediawiki  (40 usages found)
            core  (20 usages found)
                includes  (13 usages found)
                    api  (6 usages found)
                        ApiQueryCategories.php  (1 usage found)
                            68 $this->addFieldsIf( 'cl_timestamp', isset( $prop['timestamp'] ) );
                        ApiQueryCategoryMembers.php  (5 usages found)
                            88 $this->addFieldsIf( 'cl_timestamp', $fld_timestamp || $params['sort'] == 'timestamp' );
                            108 $this->addTimestampWhereRange( 'cl_timestamp',
                            123 $this->addWhere( "cl_timestamp $op $continueTimestamp OR " .
                            124 "(cl_timestamp = $continueTimestamp AND " .
                            129 $this->addOption( 'USE INDEX', 'cl_timestamp' );
                    deferred  (1 usage found)
                        LinksUpdate.php  (1 usage found)
                            646 'cl_timestamp' => $this->getDB()->timestamp(),
                    installer  (1 usage found)
                        PostgresUpdater.php  (1 usage found)
                            840 [ 'addPgIndex', 'categorylinks', 'cl_timestamp', '(cl_to, cl_timestamp)' ],
                    specials  (4 usages found)
                        SpecialRandomInCategory.php  (4 usages found)
                            228 'ORDER BY' => 'cl_timestamp ' . $dir,
                            240 $qi['conds'][] = 'cl_timestamp ' . $op . ' ' .
                            282 'low' => 'MIN( cl_timestamp )',
                            283 'high' => 'MAX( cl_timestamp )'
                    MovePage.php  (1 usage found)
                        617 'cl_timestamp=cl_timestamp' ],
                maintenance  (7 usages found)
                    refreshLinks.php  (3 usages found)
                        456 "(cl_timestamp > $timestamp OR (cl_timestamp = $timestamp AND cl_from > $lastId))";
                        458 [ 'page_id', 'cl_timestamp' ],
                        462 'ORDER BY' => [ 'cl_timestamp', 'cl_from' ],
                    tables.json  (3 usages found)
                        1604 "name": "cl_timestamp",
                        1634 "name": "cl_timestamp",
                        1636 "columns": [ "cl_to", "cl_timestamp" ],
                    updateCollation.php  (1 usage found)
                        206 'cl_timestamp = cl_timestamp',
            extensions  (20 usages found)
                BlueSpiceFoundation  (2 usages found)
                    tests  (2 usages found)
                        phpunit  (2 usages found)
                            Api  (2 usages found)
                                BSApiCategoryStoreTest.php  (1 usage found)
                                    63 'cl_timestamp' => $oDbw->timestamp()
                                BSApiCategoryTreeStoreTest.php  (1 usage found)
                                    51 'cl_timestamp' => $oDbw->timestamp()
                DynamicPageList  (2 usages found)
                    DPLMain.php  (2 usages found)
                        2486 $sSqlCl_timestamp = ", DATE_FORMAT(cl0.cl_timestamp, '%Y%m%d%H%i%s') AS cl_timestamp";
                        2775 $sSqlWhere .= 'cl0.cl_timestamp';
                DynamicPageListEngine  (4 usages found)
                    includes  (4 usages found)
                        DpleFeatureCategory.php  (1 usage found)
                            46 $query->addVars( [ 'cl_timestamp' => 'cl1.cl_timestamp' ] );
                        DpleFeatureContains.php  (1 usage found)
                            43 $query->addVars( [ 'clx_timestamp' => 'clx1.cl_timestamp' ] );
                        DpleFeatureOrder.php  (2 usages found)
                            226 $sqlSort = 'cl1.cl_timestamp';
                            230 $sqlSort = 'clx1.cl_timestamp';
                GoogleNewsSitemap  (5 usages found)
                    includes  (5 usages found)
                        specials  (5 usages found)
                            GoogleNewsSitemap.php  (5 usages found)
                                222 'MAX(cl_timestamp) AS ts',
                                232 'MAX(cl_timestamp) AS ts',
                                283 $fields = [ 'page_namespace', 'page_title', 'page_id', 'c1.cl_timestamp' ];
                                316 $conditions[] = 'c1.cl_timestamp > ' . $MWTimestamp;
                                353 $options['ORDER BY'] = 'c1.cl_timestamp ' . $sortOrder;
                intersection  (2 usages found)
                    includes  (2 usages found)
                        DynamicPageListHooks.php  (2 usages found)
                            383 $fields[] = 'c1.cl_timestamp';
                            491 $sqlSort = 'c1.cl_timestamp';
                Memento  (2 usages found)
                    tests  (2 usages found)
                        docker-image  (2 usages found)
                            backup.sql  (1 usage found)
                                2206 1,'cl_to'),('my_wiki','categorylinks','cl_timestamp','2019-05-20 16:31:41','n_diff_pfx02',0,1,'cl_to,cl_timestamp'),('my_wiki','categorylinks','cl_timestamp
                            skeleton.sql  (1 usage found)
                                2199 the index'),('my_wiki','categorylinks','cl_timestamp','2019-05-20 16:31:41','n_diff_pfx01',0,1,'cl_to'),('my_wiki','categorylinks','cl_timestamp','2019
                UploadWizard  (3 usages found)
                    includes  (3 usages found)
                        UploadWizardCampaign.php  (3 usages found)
                            149 'USE INDEX' => [ 'categorylinks' => 'cl_timestamp' ]
                            175 'ORDER BY' => 'cl_timestamp DESC',
                            177 'USE INDEX' => [ 'categorylinks' => 'cl_timestamp' ]
Ladsgroup closed this task as Invalid.Dec 4 2020, 6:39 PM

Codesearch isn't broken. I used quote mark but it took it literally. I can find it in https://codesearch.wmcloud.org/search/?q=cl_timestamp&i=nope&files=&repos=

Ladsgroup mentioned this in T230428: Migrate tables.sql to abstract schema.Dec 13 2020, 2:11 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits