Page MenuHomePhabricator

make pagetriage_tags.ptrp_tags_updated nullable in database
Closed, ResolvedPublicBUG REPORT


The page triage extension began treating the ptrp_tags_updated field as nullable in rEPTRf30ba5abb4f7: QueueManager: Add insert method and use it from PageTriage class and the schema should be updated to reflect that change.

Original issue:

Steps to replicate the issue (include links if applicable):

There are two ways to replicate this error:

  • Create a new page as a non-patroller user or as an anonymous user.


  • Manually add the page with the Add to the New Pages Feed link.

What happens?:
Nothing gets added. The INSERT statement silently fails. On further examination, it seems like the error is the following:

Error: stepping, NOT NULL constraint failed: pagetriage_page.ptrp_tags_updated (19).

What should have happened instead?:

The page should have been added to the New Pages Feed and the row insertion should have no errors.

Software version (skip for WMF-hosted wikis like Wikipedia):

Other information (browser name/version, screenshots, etc.):

Event Timeline

Thanks for filing this, I was just looking to see if there was a task :)

One thing I don't understand is why the error is silent, I'd expect more fireworks for a failure like this.

From what I can see in the logs, the query is an INSERT OR IGNORE query. That's why it fails silently.

From what I can see in the logs, the query is an INSERT OR IGNORE query. That's why it fails silently.

Oh right. That is 100% my fault.

public function insert( QueueRecord $queueRecord ): Status {
		$status = new Status();
		$queueRecordData = $queueRecord->jsonSerialize();
			// 'IGNORE' is needed for fields like ptrp_tags_updated which will not be
			// present on a new record.
			[ 'IGNORE' ]
		$status->setOK( $this->dbw->affectedRows() === 1 );
		return $status;

added in rEPTRf30ba5abb4f7: QueueManager: Add insert method and use it from PageTriage class

// 'IGNORE' is needed for fields like ptrp_tags_updated which will not be
// present on a new record.

I hadn't seen this comment. ptrp_tags_updated is the field that is supposedly NOT NULL and the reason why the insertion is failing. That's why it's failing. Should we change the SQLite schema to reflect it can be NULL?

Scardenasmolinar changed the task status from Open to In Progress.Mar 29 2023, 11:27 PM
Scardenasmolinar claimed this task.
Scardenasmolinar moved this task from Ready to In Progress on the Moderator-Tools-Team (Kanban) board.

I have a few questions on how to proceed with this ticket:

  1. Should we change the database schema and make ptrp_tags_updated to a nullable field?


  1. Should we add a now() timestamp to ptrp_tags_updated and then PageTriage::bulkSetTagsUpdated corrects the timestamp. I don't like this solution because prtp_tags_updated will have incorrect data until PageTriage::bulkSetTagsUpdated runs. It looks like it is only called when a user's block status is updated (PageTriageUtil::updateMetadataOnBlockChange).

Additional questions:

  • Is PageTriage silently breaking in production because of this error?
  • Why is MySQL not breaking and can add pages to Special:NewPagesFeed if it also has this field constraint?
  • Should we call PageTriage::bulkSetTagsUpdated on other parts of the code?

Change 904582 had a related patch set uploaded (by Jsn.sherman; author: Jsn.sherman):

[mediawiki/extensions/PageTriage@master] WIP: make pagetriage_page.ptrp_reviewd_updated nullable

While looking at this, I initially was not seeing new pages going into the queue automatically even with mysql, but I seem to have that working today, so that makes me think that things would be fine in production.

Looking through the code and the schema, it looks like prtp_tags_updated didn't get a schema update when it became nullable.

I went ahead and updated the schema from my mariadb-backed docker environment:
@Scardenasmolinar could you try this patch in the sqllite-based environment?

note that you'll need to run the update script to do the migration

I commented on the Gerrit patch. It seems like you modified ptrp_reviewed_updated instead of prtp_tags_updated.

I got a little turned around with the tools for the abstract schema changes, but believe I have it sorted now. The sqlite patch handles the change by creating a temporary table.

It's working now! I missed the SQLite schema change file in my initial review.

jsn.sherman renamed this task from Fix SQLite PageTriage error to make pagetriage_tags.ptrp_tags_updated nullable in database.Apr 11 2023, 7:49 PM
jsn.sherman added a project: Schema-change.
jsn.sherman updated the task description. (Show Details)
jsn.sherman updated Other Assignee, added: jsn.sherman.

@jcrespo we think we're looking at a schema change for the PageTriage extension. Looks like it's enabled on testwiki, testwiki2, and enwiki. Per I thought it looked like the right time to tag a DBA. Does this look reasonable to you?

jcrespo added a subscriber: Ladsgroup.

Hi, sorry for the confusion (maybe there is an old reference to my account somewhere)? or maybe or you just looked at an older task. The right person to tag for help with a schema changes is at the moment my workmate @Ladsgroup , as he is the person that will know the high level of the mw db status, review and deploy changes, and he will make sure the right person helps you move forward/support you on your database needs. :-)

I can help if needed, but he should be for now the right person to contact first, as that help us coordinate better! (using the DBA tag is how you can get our attention, which I now just added)

Hi, yeah, you need a separate ticket for this. For example: T333332. Most importantly and before doing so, you need to make a patch inside the extension to reflect the new schema (add me to review the patch). See for that. See patches connected to T188180 as examples.

Thank you both! I wasn't quite sure which step of the process to place us in since the application code treating this field as nullable is already in production and this schema change would be catching the db up with that. So I was treating this as step 2.

Make sure to involve as many relevant people as possible before merging. [...] Please include at least a DBA also, even for changes that may look trivial, as they can provide constructive feedback if warned in time. Sometimes simple schema changes require a lot of preparation for deployment.

I'll get that new ticket started. I do have a patch for the schema change in page triage, which I'll add you to!

Change 904582 merged by jenkins-bot:

[mediawiki/extensions/PageTriage@master] make pagetriage_page.ptrp_tags_updated nullable

after this migration happens, we should be able to stop relying on insert or ignore behavior in the queue manager insert function.