Last modified: 2012-08-04 20:49:01 UTC
I found a problem with MediaWiki 1.11.0 and PostgreSQL 8.2.5. I overwrite existing image and see database error as follows. (I can do it in formerly when I use MediaWiki 1.9.0 and PostgreSQL 8.2.1.) ---------------------------------------------------------------------- A database error has occurred Query: INSERT INTO oldimage (oi_name,oi_archive_name,oi_size,oi_width,oi_height,oi_bits,oi_timestamp,oi_description,oi_user,oi_user_text,oi_metadata,oi_media_type,oi_major_mime,oi_minor_mime,oi_sha1) SELECT img_name,'20071220081847!Logo.png',img_size,img_width,img_height,img_bits,img_timestamp,img_description,img_user,img_user_text,img_metadata,img_media_type,img_major_mime,img_minor_mime,img_sha1 FROM image WHERE img_name = 'Logo.png' Function: LocalFile::recordUpload2 Error: 1 ERROR: column "oi_metadata" is of type bytea but expression is of type text LINE 1: ...,oi_timestamp,oi_description,oi_user,oi_user_text,oi_metadat... ^ HINT: You will need to rewrite or cast the expression. Backtrace: #0 /home/cory/mediawiki-1.11.0/includes/Database.php(795): DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'INSERT INTO ol...', 'LocalFile::reco...', false) #1 /home/cory/mediawiki-1.11.0/includes/Database.php(1861): Database->query('INSERT INTO ol...', 'LocalFile::reco...') #2 /home/cory/mediawiki-1.11.0/includes/filerepo/LocalFile.php(746): Database->insertSelect('oldimage', 'image', Array, Array, 'LocalFile::reco...') #3 /home/cory/mediawiki-1.11.0/includes/filerepo/LocalFile.php(642): LocalFile->recordUpload2('20071220081847!...', '(for test updat...', '(for test updat...', Array, false) #4 /home/cory/mediawiki-1.11.0/includes/SpecialUpload.php(436): LocalFile->upload('mwrepo://local/...', '(for test updat...', '(for test updat...', 1, Array) #5 /home/cory/mediawiki-1.11.0/includes/SpecialUpload.php(248): UploadForm->processUpload() #6 /home/cory/mediawiki-1.11.0/includes/SpecialUpload.php(14): UploadForm->execute() #7 /home/cory/mediawiki-1.11.0/includes/SpecialPage.php(653): wfSpecialUpload(NULL, Object(SpecialPage)) #8 /home/cory/mediawiki-1.11.0/includes/SpecialPage.php(459): SpecialPage->execute(NULL) #9 /home/cory/mediawiki-1.11.0/includes/Wiki.php(201): SpecialPage::executePath(Object(Title)) #10 /home/cory/mediawiki-1.11.0/includes/Wiki.php(45): MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest)) #11 /home/cory/mediawiki-1.11.0/index.php(89): MediaWiki->initialize(Object(Title), Object(OutputPage), Object(User), Object(WebRequest)) #12 {main} ---------------------------------------------------------------------- Then I find illegally type of column such as different definition "oi_metadata" (= bytea) and "img_metadata" (= text). ---------------------------------------------------------------------- % /usr/local/pgsql/bin/psql -d wikidb -U wikiuser -W ... Welcome to psql 8.2.5, the PostgreSQL interactive terminal. wikidb=> ¥d oldimage Table "mediawiki.oldimage" Column | Type | Modifiers -----------------+--------------------------+---------------------------------- oi_name | text | not null oi_archive_name | text | not null oi_size | integer | not null oi_width | integer | not null oi_height | integer | not null oi_bits | smallint | not null oi_description | text | oi_user | integer | oi_user_text | text | not null oi_timestamp | timestamp with time zone | not null oi_deleted | character(1) | not null default '0'::bpchar oi_metadata | bytea | not null default ''::bytea oi_media_type | text | oi_major_mime | text | not null default 'unknown'::text oi_minor_mime | text | not null default 'unknown'::text oi_sha1 | text | not null default ''::text Indexes: "oi_name" btree (oi_name) "oi_sha1" btree (oi_sha1) Foreign-key constraints: "oldimage_oi_name_fkey" FOREIGN KEY (oi_name) REFERENCES image(img_name) "oldimage_oi_user_fkey" FOREIGN KEY (oi_user) REFERENCES mwuser(user_id) ON DELETE SET NULL wikidb=> ¥d image Table "mediawiki.image" Column | Type | Modifiers -----------------+--------------------------+--------------------------- img_name | text | not null img_size | integer | not null img_width | integer | not null img_height | integer | not null img_metadata | text | img_bits | smallint | img_media_type | text | img_major_mime | text | default 'unknown'::text img_minor_mime | text | default 'unknown'::text img_description | text | not null img_user | integer | img_user_text | text | not null img_timestamp | timestamp with time zone | img_sha1 | text | not null default ''::text Indexes: "image_pkey" PRIMARY KEY, btree (img_name) "img_sha1" btree (img_sha1) "img_size_idx" btree (img_size) "img_timestamp_idx" btree (img_timestamp) Foreign-key constraints: "image_img_user_fkey" FOREIGN KEY (img_user) REFERENCES mwuser(user_id) ON DELETE SET NULL ---------------------------------------------------------------------- In MySQL, these column are defined same type (= mediumblob), but in PostgreSQL there are defined different type. I think column "oi_metadata" to define as "text" what is a solution. Right?
Created attachment 4455 [details] patch for maintenance/postgres/tables.sql, rev.25546. I think datatype of "oldimage.oi_metadata" is different. This patch is to correct datatype (BYTEA -> TEXT). But this patch will be not solution for system already updated (tables.sql rev.22597 or over).
If you already upgraded MediaWiki 1.11.0, you must rebuild "oldimage" table to correct column type. A solution as follows: 日本語の記事がこちらにあります (Japanese documentation here): http://www.cory.to/kn/kn071220/MediaWiki_bug12365_solution << Please note! >>- - - - - - - - - - - - - - - - - - - - - - - - - - If your wiki has important data, you must take a complete backup of your wiki database at first. See: http://www.mediawiki.org/wiki/Manual:Backing_up_a_wik - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1. Stop your MediaWiki. 2. Patch to maintanance/postgres/tables.sql. 3. Get "oldimage" table by SQL format. Ex: ---------- % /usr/local/pgsql/bin/pg_dump -U (wikiuser) -t oldimage (wikidb) > oldimage_old.sql % cp oldimage_old.sql oldimage_new.sql ---------- Note: (wikiuser) = $wgDBuser (wikidb) = $wgDBname /usr/local/pgsql : Replace your PostgreSQL base directory 4. edit "oldimage_new.sql" file as follows: - oi_metadata bytea DEFAULT ''::bytea NOT NULL, + oi_metadata text DEFAULT ''::text NOT NULL, 5. Write correct "oldimage" table. Ex: ---------- % /usr/local/pgsql/bin/psql -d (wikidb) -U (wikiuser) -W wikidb=> DROP TABLE oldimage; wikidb=> \i oldimage_new.sql wikidb=> \d oldimage Table "mediawiki.oldimage" Column | Type | Modifiers -----------------+--------------------------+---------------------------------- oi_name | text | not null oi_archive_name | text | not null oi_size | integer | not null oi_width | integer | not null oi_height | integer | not null oi_bits | smallint | not null oi_description | text | oi_user | integer | oi_user_text | text | not null oi_timestamp | timestamp with time zone | not null oi_deleted | character(1) | not null default '0'::bpchar oi_metadata | text | not null default ''::text oi_media_type | text | oi_major_mime | text | not null default 'unknown'::text oi_minor_mime | text | not null default 'unknown'::text oi_sha1 | text | not null default ''::text Indexes: "oi_name" btree (oi_name) "oi_sha1" btree (oi_sha1) Foreign-key constraints: "oldimage_oi_name_fkey" FOREIGN KEY (oi_name) REFERENCES image(img_name) "oldimage_oi_user_fkey" FOREIGN KEY (oi_user) REFERENCES mwuser(user_id) ON DELETE SET NULL wikidb=> \q ---------- 6. Start the MediaWiki service and test to overwrite image.
bug #11363 suggests these should all be bytea instead of all text *** This bug has been marked as a duplicate of bug 11363 ***