Page MenuHomePhabricator

Special:NewFiles query is broken (missing img_size on Mysql, fatal 2x img_timestamp on Mssql)
Closed, ResolvedPublic

Description

The following error occurred when viewing the new files list (Special:NewFiles):

[63b5c718d3bd9ab843f1c879] /index.php?title=Special%3ANewFiles Wikimedia\Rdbms\DBQueryError from line 1506 of includes\libs\rdbms\database\Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: SELECT TOP 51 img_name,img_timestamp,img_width,img_height,img_metadata,img_bits,img_media_type,img_major_mime,img_minor_mime,img_timestamp,img_sha1,img_description AS [img_description_text],NULL AS [img_description_data],NULL AS [img_description_cid],img_user,img_user_text,NULL AS [img_actor],img_metadata FROM [dbo].[image] LEFT JOIN [dbo].[user_groups] ON (ug_group = 'bot' AND (ug_user = img_user) AND (ug_expiry IS NULL OR ug_expiry >= '20190709184530')) WHERE (ug_group IS NULL) ORDER BY img_timestamp DESC 
Function: IndexPager::buildQueryInfo (NewFilesPager)
Error: 209 [SQLSTATE 42000][Error Code 209][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ambiguous column name 'img_timestamp'.
[SQLSTATE 42000][Error Code 8180][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

Like I have been doing with other MSSQL errors - I created a temporary hack to get around it in includes\libs\rdbms\database\DatabaseMssql.php at the bottom of the selectSQLText method:

else if ( strpos( $sql, 'img_timestamp,img_width' ) !== false 
			&& strpos( $sql, 'img_timestamp,img_sha1' ) !== false ) {
			$replace = [
				"img_timestamp,img_sha1" => "img_sha1",
			];
			$sql = str_replace( array_keys( $replace ), array_values( $replace ), $sql );
		}

Thanks,
Eric

Event Timeline

Ejucf created this task.Jul 9 2019, 7:16 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 9 2019, 7:16 PM
Aklapper renamed this task from Error When Viewing New Files to Error When Viewing New Files: "Ambiguous column name 'img_timestamp'".Jul 9 2019, 7:19 PM
Reedy added a subscriber: Reedy.Jul 10 2019, 12:46 PM

What version of MediaWiki?

I'm presuming it doesn't like that there is an index called img_timestamp too

1.32.1

I'm presuming it doesn't like that there is an index called img_timestamp too

I'm not sure - just removing the ambiguous column name from the query solved the problem.

Krinkle added a subscriber: Krinkle.EditedJul 11 2019, 3:35 PM

I think the issue is that it's selecting img_timestamp twice.

The code is also "trying" to select img_name twice, but thanks to completely broken use of + for arrays and a happy accident, that is working fine. It does mean that img_size is missing from the query, which might also be causing issues for Wikipedia in production.

The code:

NewFilesPages.php
function getQueryInfo() {
	$imgQuery = LocalFile::getQueryInfo();
	$fields = [ 'img_name', 'img_timestamp' ] + $imgQuery['fields']; # A
LocalFile.php
	public static function getQueryInfo( array $options = [] ) {
		$ret = [
			'fields' => [
				'img_name', # *poof* => A0 'img_name' (no-op)
				'img_size', # *poof* => A1 'img_timestamp'
				'img_width',
				'img_height',
				'img_metadata',
				'img_bits',
				'img_media_type',
				'img_major_mime',
				'img_minor_mime',
				'img_timestamp', # *oops*
				'img_sha1',
Krinkle renamed this task from Error When Viewing New Files: "Ambiguous column name 'img_timestamp'" to Special:NewFiles query is broken (missing img_size on Mysql, fatal 2x img_timestamp on Mssql).Jul 11 2019, 3:35 PM
Krinkle moved this task from Untriaged to Usage problem on the Wikimedia-Rdbms board.
Krinkle moved this task from To triage to Multimedia-related on the MediaWiki-Special-pages board.
Krinkle edited projects, added Multimedia; removed Wikimedia-Rdbms.Jul 11 2019, 3:46 PM

Change 530952 had a related patch set uploaded (by Umherirrender; owner: Umherirrender):
[mediawiki/core@master] Simplifiy NewFilesPager::getQueryInfo

https://gerrit.wikimedia.org/r/530952

It does mean that img_size is missing from the query, which might also be causing issues for Wikipedia in production.

No, because the column is not used from this query.

Change 530952 merged by jenkins-bot:
[mediawiki/core@master] Simplify NewFilesPager::getQueryInfo

https://gerrit.wikimedia.org/r/530952