[DBQuery] `SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata` with SQLite when uploading over existing file
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	Czech.Fox
	Feb 21 2018, 2:10 PM

Description

Problem
When uploading a new version of ZIP file to an existing File: page, DB crashes with exception [DBQuery] SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata

Log

[DBConnection] Wikimedia\Rdbms\LoadBalancer::openConnection: calling initLB() before first connection.
[DBConnection] Connected to database 0 at 'localhost'.
UploadBase::createFromRequest: class name: UploadFromFile
[Mime] MimeAnalyzer::loadFiles: loading mime types from D:\Wiki\includes/libs/mime/mime.types

[Mime] MimeAnalyzer::loadFiles: loading mime info from D:\Wiki\includes/libs/mime/mime.info

[Mime] MimeAnalyzer::doGuessMimeType: analyzing head and tail of C:\Windows\Temp\phpF9CC.tmp for magic numbers.

[Mime] MimeAnalyzer::doGuessMimeType: ZIP header present in C:\Windows\Temp\phpF9CC.tmp

[Mime] MimeAnalyzer::detectZipType: unable to identify type of ZIP archive

[Mime] MimeAnalyzer::guessMimeType: guessed mime type of C:\Windows\Temp\phpF9CC.tmp: application/zip

[Mime] MimeAnalyzer::improveTypeFromExtension: improved mime type for .zip: application/zip

MediaHandlerFactory::getHandler: no handler found for application/zip.
mime: <application/zip> extension: <zip>
UploadBase::detectScript: checking for embedded scripts and HTML stuff
UploadBase::detectScript: no scripts found
UploadBase::detectVirus: virus scanner disabled
[Mime] MimeAnalyzer::doGuessMimeType: analyzing head and tail of C:\Windows\Temp\phpF9CC.tmp for magic numbers.

[Mime] MimeAnalyzer::doGuessMimeType: ZIP header present in C:\Windows\Temp\phpF9CC.tmp

[Mime] MimeAnalyzer::detectZipType: unable to identify type of ZIP archive

[Mime] MimeAnalyzer::guessMimeType: guessed mime type of C:\Windows\Temp\phpF9CC.tmp: application/zip

[Mime] MimeAnalyzer::improveTypeFromExtension: improved mime type for .zip: application/zip

UploadBase::verifyExtension: mime type application/zip matches extension zip, passing file
UploadBase::verifyFile: all clear; passing.
Title::getRestrictionTypes: applicable restrictions to [[File:Logo Package.zip]] are {edit,move,upload}
[FileOperation] FileBackendStore::getFileStat: File mwstore://local-backend/local-public/archive/b/b9/20180221132749!Logo_Package.zip does not exist.

[DBQuery] SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata

[exception] [915463c1acb68b4778307394] /wiki/index.php?title=Special%3AUpload   Wikimedia\Rdbms\DBQueryError from line 1149 of D:\Wiki\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: INSERT  INTO oldimage (oi_name,oi_archive_name,oi_size,oi_width,oi_height,oi_bits,oi_timestamp,oi_user,oi_user_text,oi_metadata,oi_media_type,oi_major_mime,oi_minor_mime,oi_sha1,oi_description) VALUES ('Logo_Package.zip','20180221132749!Logo_Package.zip','2871684','0','0','0','20180117081611','1','Master',NULL,'ARCHIVE','application','zip','drwbrj3mkv1ak6zmyqksw0j377ak81p','All variants of logo')
Function: LocalFile::recordUpload2/multi-row
Error: 19 NOT NULL constraint failed: oldimage.oi_metadata

#0 D:\Wiki\includes\libs\rdbms\database\Database.php(979): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#1 D:\Wiki\includes\libs\rdbms\database\Database.php(1589): Wikimedia\Rdbms\Database->query(string, string)
#2 D:\Wiki\includes\libs\rdbms\database\DatabaseSqlite.php(621): Wikimedia\Rdbms\Database->insert(string, array, string, string)
#3 D:\Wiki\includes\libs\rdbms\database\Database.php(2412): Wikimedia\Rdbms\DatabaseSqlite->insert(string, array, string, array)
#4 D:\Wiki\includes\filerepo\file\LocalFile.php(1441): Wikimedia\Rdbms\Database->insertSelect(string, array, array, array, string, array, array, array)
#5 D:\Wiki\includes\filerepo\file\LocalFile.php(1246): LocalFile->recordUpload2(string, string, boolean, array, string, User, array)
#6 D:\Wiki\includes\upload\UploadBase.php(861): LocalFile->upload(string, string, boolean, integer, array, boolean, User, array)
#7 D:\Wiki\includes\specials\SpecialUpload.php(567): UploadBase->performUpload(string, boolean, boolean, User, array)
#8 D:\Wiki\includes\specials\SpecialUpload.php(207): SpecialUpload->processUpload()
#9 D:\Wiki\includes\specialpage\SpecialPage.php(522): SpecialUpload->execute(NULL)
#10 D:\Wiki\includes\specialpage\SpecialPageFactory.php(578): SpecialPage->run(NULL)
#11 D:\Wiki\includes\MediaWiki.php(287): SpecialPageFactory::executePath(Title, RequestContext)
#12 D:\Wiki\includes\MediaWiki.php(851): MediaWiki->performRequest()
#13 D:\Wiki\includes\MediaWiki.php(523): MediaWiki->main()
#14 D:\Wiki\index.php(43): MediaWiki->run()
#15 {main}
User: loading options for user 1 from database.
OutputPage::sendCacheControl: no caching **
Request ended normally
[session] Saving all sessions on shutdown
[DBConnection] Closing connection to database ''.

DB structure

Environment
MW: 1.30 stable
DB: SQLite
PHP: 7.1.14 (windows binaries)
OS: Windows Server 2008 R2

Event Timeline

Czech.Fox created this task.Feb 21 2018, 2:10 PM

Restricted Application added projects: Commons, Multimedia. · View Herald TranscriptFeb 21 2018, 2:10 PM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Krinkle moved this task from Untriaged to Usage problem on the MediaWiki-libs-Rdbms board.Jun 27 2019, 9:13 PM

Krinkle edited projects, added Schema-change; removed MediaWiki-libs-Rdbms.Jul 18 2019, 7:58 PM

Mwjoso subscribed.Jul 10 2021, 3:19 PM

I face the same problem. Updating a file gives me the same log message. I'm using 1.35.2 from Debian.

As far as I've tracked down the problem is in the PDO driver of SQLite:

% php <<\__EOF
<?php
$db = new PDO('sqlite:/tmp/db.sqlite');

foreach($db->query("select x'' as col") as $row) {
    print_r($row);
    echo "col = " . gettype($row[0]) . "\n";
}

foreach($db->query("select x'DATA' as col") as $row) {
    print_r($row);
    echo "col = " . gettype($row[0]) . "\n";
}
__EOF
Array
(
    [col] => 
    [0] => 
)
col = NULL
PHP Warning:  Invalid argument supplied for foreach() in Standard input code on line 9

An empty BLOB becomes NULL and iterating over, and non-empty blob fails completely. Hence, there has to be a completely different solution for SQLite.

My quick'n'dirty workaround is this:

diff
--- /tmp/Database.php   2021-07-10 18:08:39.965797199 +0200
+++ /usr/share/mediawiki/includes/libs/rdbms/database/Database.php      2021-07-10 18:09:52.248651839 +0200
@@ -3468,7 +3468,8 @@
                $insertOptions = $this->normalizeOptions( $insertOptions );
                $selectOptions = $this->normalizeOptions( $selectOptions );
 
-               if ( $this->cliMode && $this->isInsertSelectSafe( $insertOptions, $selectOptions ) ) {
+               // workaround for https://phabricator.wikimedia.org/T187883
+               if ( /* $this->cliMode && */ $this->isInsertSelectSafe( $insertOptions, $selectOptions ) ) {
                        // For massive migrations with downtime, we don't want to select everything
                        // into memory and OOM, so do all this native on the server side if possible.
                        $this->doInsertSelectNative(

This enables the native insert … select also in web-mode, which circumvents the problem, because the copying happens inside SQLite.

SacredSum renamed this task from [DBQuery] SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata to [DBQuery] `SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata` with SQLite when uploading over existing file.Feb 13 2023, 5:19 AM

I also have this problem, with MediaWiki 1.39.1 and an SQLite database. PHP 7.4.33-1+deb11, Debian 11.6.

Whether the error occurs depends on the file type. I get the DBQueryError when I try to overwrite an existing .pdf file with a different .pdf file, but not when I try to overwrite an existing .png file with a different .png file.

Instructions to reproduce the error, starting from a fresh installation of Debian 11.6:

Install packages.

# wget https://releases.wikimedia.org/mediawiki/1.39/mediawiki-1.39.1.zip
# mkdir -p /var/www/mediawiki
# unzip -q mediawiki-1.39.1.zip
# mv mediawiki-1.39.1 /var/www/mediawiki/w
# chown www-data /var/www/mediawiki/w/images
# chmod 755 /var/www/mediawiki/w/images
# mkdir -p /var/www/data
# chgrp www-data /var/www/data
# chmod g+rwx /var/www/data
# apt install nginx php-fpm php-intl php-mbstring php-sqlite3 php-xml

Edit /etc/nginx/sites-available/default:

	root /var/www/mediawiki;
	index index.php;
	location ~ \.php$ {
		include fastcgi_params;
		fastcgi_pass unix:/run/php/php-fpm.sock;
		fastcgi_param SCRIPT_FILENAME $request_filename;
	}

Browse to http://localhost/w/.
- Connect to database
  - Database type: SQLite
  - SQLite data directory: /var/www/data
  - Database name: my_wiki
- Options
  - ✓ Enable file uploads
Download LocalSettings.php and copy to /var/www/mediawiki/w.
Edit /var/www/mediawiki/w/LocalSettings.php to allow .pdf uploads and show a stack trace.

$wgFileExtensions = array_merge($wgFileExtensions, array("pdf"));
$wgShowExceptionDetails = true;

Browse to http://localhost/w/index.php?title=Main_Page.
Log in (the admin user created during installation will work).
Browse to http://localhost/w/index.php?title=Special:Upload.
Upload a .png file; I used /usr/share/doc/xorg/xsf.png.
"Upload a new version of this file".
Upload a different .png file; I used /usr/share/doc/debian/FAQ/images/tip.png.
See that there's no error.
Browse to http://localhost/w/index.php?title=Special:Upload.
Upload a .pdf file; I used /usr/share/cups/data/standard.pdf.
"Upload a new version of this file".
Upload a different .pdf file; I used /usr/share/cups/data/default.pdf.
See the database error:

[aa83b7c01e00f11e6654a759] /w/index.php?title=Special:Upload Wikimedia\Rdbms\DBQueryError: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading or after adding a new extension?

Please see https://www.mediawiki.org/wiki/Special:MyLanguage/Manual:Upgrading and https://www.mediawiki.org/wiki/Special:MyLanguage/Manual:How_to_debug for more information.

Error 19: NOT NULL constraint failed: oldimage.oi_metadata
Function: LocalFile::recordUpload3
Query: INSERT INTO "oldimage" (oi_name,oi_archive_name,oi_size,oi_width,oi_height,oi_bits,oi_description_id,oi_timestamp,oi_metadata,oi_media_type,oi_major_mime,oi_minor_mime,oi_sha1,oi_actor) VALUES ('Standard.pdf','20230213050802!Standard.pdf','979','0','0','0','1','20230213050009',NULL,'OFFICE','application','pdf','iuy8zeg15dqhksjq8j9fdadjztql0pz','1')

Backtrace:

from /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(1618)
#0 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(1602): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(1576): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(952): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(1835): Wikimedia\Rdbms\Database->query()
#4 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(2206): Wikimedia\Rdbms\Database->insert()
#5 /var/www/mediawiki/w/includes/libs/rdbms/database/Database.php(2128): Wikimedia\Rdbms\Database->doInsertSelectGeneric()
#6 /var/www/mediawiki/w/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->insertSelect()
#7 /var/www/mediawiki/w/includes/libs/rdbms/database/DBConnRef.php(558): Wikimedia\Rdbms\DBConnRef->__call()
#8 /var/www/mediawiki/w/includes/filerepo/file/LocalFile.php(1900): Wikimedia\Rdbms\DBConnRef->insertSelect()
#9 /var/www/mediawiki/w/includes/filerepo/file/LocalFile.php(1742): LocalFile->recordUpload3()
#10 /var/www/mediawiki/w/includes/upload/UploadBase.php(941): LocalFile->upload()
#11 /var/www/mediawiki/w/includes/specials/SpecialUpload.php(614): UploadBase->performUpload()
#12 /var/www/mediawiki/w/includes/specials/SpecialUpload.php(246): SpecialUpload->processUpload()
#13 /var/www/mediawiki/w/includes/specialpage/SpecialPage.php(701): SpecialUpload->execute()
#14 /var/www/mediawiki/w/includes/specialpage/SpecialPageFactory.php(1428): SpecialPage->run()
#15 /var/www/mediawiki/w/includes/MediaWiki.php(316): MediaWiki\SpecialPage\SpecialPageFactory->executePath()
#16 /var/www/mediawiki/w/includes/MediaWiki.php(904): MediaWiki->performRequest()
#17 /var/www/mediawiki/w/includes/MediaWiki.php(562): MediaWiki->main()
#18 /var/www/mediawiki/w/index.php(50): MediaWiki->run()
#19 /var/www/mediawiki/w/index.php(46): wfIndexMain()
#20 {main}

[DBQuery] `SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata` with SQLite when uploading over existing fileOpen, Needs TriagePublicActions

Description

Event Timeline

[DBQuery] `SQL ERROR: NOT NULL constraint failed: oldimage.oi_metadata` with SQLite when uploading over existing file
Open, Needs TriagePublic
Actions