Wikitools settings inaccessible on large site

Hi,

thanks for your reply! At least it explains what I was experiencing, even if I don't understand what this "O(n²)" query does; as a total amateur, I'd probably try to make a temporary table, copy the page titles and nids over, make the title field distinct and collect what's falling out in a second table with references to the nids. However, for sure there are reasons why this won't work.

My next thought from the "idiot user point of view" would be to have a look at MediaWiki's page table; even if the MediaWiki database schema is much simpler than Drupal's, their concepts might be worth a look (at least it is proven that they can handle millions of wiki pages, including page revisions, templates/includes, and backlinks).

As far as I understand it, they prevent the problem from the beginning because they don't allow duplicate pages at all with MediaWiki::checkInitialQueries(), Manual:index.phpand Manual:Title.php (however, only page_id is unique, like Drupal's $nid; MediaWiki's page_title is a simple varchar(255)); so this problem would only occur if a) two MediaWiki databases were to be merged, and indeed Google doesn't know much about such attempts, or b) if one article would be moved between two namespaces (Manual:Using custom namespaces, section "Dealing with existing pages").

Their approach to resolve such cases is aa) to manually fix the affected pages/page titles or bb) to use the "Namespace title conflict cleanup script" namespaceDupes.php (couldn't find a documentation for this, the code is in their SVN repository). The duplicate finding part is, I believe:

    /**
     * Find pages in mainspace that have a prefix of the new namespace
     * so we know titles that will need migrating
     * @param $ns int Namespace id (id for new namespace?)
     * @param $name String Prefix that is being made a namespace
     */
    private function getConflicts( $ns, $name ) {
        $page  = 'page';
        $table = $this->db->tableName( $page );

        $prefix     = $this->db->strencode( $name );
        $encNamespace = $this->db->addQuotes( $ns );

        $titleSql = "TRIM(LEADING '$prefix:' FROM {$page}_title)";
        if( $ns == 0 ) {
            // An interwiki; try an alternate encoding with '-' for ':'
            $titleSql = $this->db->buildConcat( array( "'$prefix-'", $titleSql ) );
        }
                                     
        $sql = "SELECT {$page}_id    AS id,
                       {$page}_title AS oldtitle,
                       $encNamespace AS namespace,
                       $titleSql     AS title
                  FROM {$table}
                 WHERE {$page}_namespace=0
                   AND {$page}_title " . $this->db->buildLike( $name . ':', $this->db->anyString() );

        $result = $this->db->query( $sql, __METHOD__ );

        $set = array();
        foreach( $result as $row ) {
            $set[] = $row;
        }
        $this->db->freeResult( $result );

        return $set;
    }

If this "O(n²)" query is a one time operation, an offline (like MediaWiki's maintenance) script might be as well suited as a Batch API operation. Maybe at leasts parts of namespaceDupes.php can be adapted for this?

Greetings, -asb

I just was bitten again by this issue on another site. After importing a few hundred images into image nodes, I reached 33,633 nodes and ./admin/settings/wikitools became inaccessible again. That's the query it seems to be running:

EXPLAIN SELECT COUNT(DISTINCT(n1.nid)) FROM node n1, node n2 WHERE LOWER(REPLACE(REPLACE(n1.title, "_", " "), "-", " ")) = LOWER(REPLACE(REPLACE(n2.title, "_", " "), "-", " ")) AND n1.nid != n2.nid AND n1.type IN('artikel','blog','etappe','image','nachricht','poi','produkt','region','reisebeschreibung','ressource','rezension','seite','poll') AND n2.type IN('artikel','blog','etappe','image','nachricht','poi','produkt','region','reisebeschreibung','ressource','rezension','seite','poll'):

Is there any progress on this issue, or maybe there is a workaround? Since I can't access the 'wikitools' configuration page anymore, I can't disable the duplicate checking; would it be safe to just disable the module?

Thanks & greetings, -asb