Page MenuHomePhabricator

rebuildrecentchanges broken on PostgreSQL
Closed, ResolvedPublic

Description

Author: overlordq

Description:

Loading from page and revision tables...
$wgRCMaxAge=604800 (7 days)
Updating links and size differences...
Warning: pg_query(): Query failed: ERROR: invalid input syntax for integer: "NULL" in /home/wiki/includes/DatabasePostgr es.php on line 553
A database error has occurred
Query: UPDATE recentchanges SET rc_last_oldid=0,rc_new=1,rc_type=1,rc_old_len='NULL',rc_new_len='490' WHERE rc_cur_id=60 AND rc_this_oldid=102
Function:
Error: 1 ERROR: invalid input syntax for integer: "NULL"

Backtrace:
#0 /home/wiki/includes/Database.php(799): DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'UPDATE recentch... ', '', false)
#1 /home/wiki/maintenance/rebuildrecentchanges.inc(108): Database->query('UPDATE recentch...')
#2 /home/wiki/maintenance/rebuildrecentchanges.inc(12): rebuildRecentChangesTablePass2()
#3 /home/wiki/maintenance/rebuildrecentchanges.php(18): rebuildRecentChangesTable()

#4 {main}

Problem is ~103-106 of maintenance/rebuildrecentchanges.inc

$size = $size ? $size : 'NULL';
....

rc_old_len='$lastSize',rc_new_len='$size' "

NULL shouldn't be in quotes.


Version: 1.16.x
Severity: minor

Details

Reference
bz13453

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 10:07 PM
bzimport added a project: Wikimedia-Rdbms.
bzimport set Reference to bz13453.
bzimport added a subscriber: Unknown Object (MLST).

Fixed in r32226, thanks for the report.

overlordq wrote:

"rc_old_len='$lastSize',rc_new_len=$size "

quotes around $lastSize will still throw the error as shown the SQL in comment 0, call it line 93 of same file if the page is new.

overlordq wrote:

Working now :)

overlordq wrote:

This seems to have regressed to being broken again.

I'll attach the output

overlordq wrote:

Backtract + output

Attached:

overlordq wrote:

Seems to be r49112 that causes this.

'rc_cur_id' => 'COALESCE(page_id, 0)'

was there a problem with that field being NULL vs 0?

(In reply to comment #6)

Seems to be r49112 that causes this.

'rc_cur_id' => 'COALESCE(page_id, 0)'

was there a problem with that field being NULL vs 0?

Has to be I guess, but it seems weird to me because we use 0 in this role in a lot of other places as well (e.g. rev_user, which is either a valid user_id or 0). Is there even any benefit from adding these relations between tables in the postgres schema? The MySQL schema doesn't have them.

overlordq wrote:

The benefit is getting warnings like this that your software is trying to insert data that doesn't make sense. Without strict mode MySQL is known to do some really silly things when inserting data and foreign keys would catch that.

The hackish way would be to create a stub page like is done for anonymous users and user_id 0 but that would likely do odd things elsewhere.

I guess without doing something strange like splitting up the log table into actions against pages and actions against users, would be to drop the constraint.

(In reply to comment #8)

The benefit is getting warnings like this that your software is trying to
insert data that doesn't make sense.

That's not true. rc_cur_id=0 DOES make sense, it has a well-defined meaning. PostgreSQL should not try to enforce its conception of what makes sense over MW's. For that reason, the constraint should be dropped IMO, as you suggest.

overlordq wrote:

Fixed in r58269

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.