Problem displaying page when two or more pages have the same clean url

Bug #1412950 reported by Robert Lyon
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Expired
Low
Unassigned
1.10
Expired
Low
Unassigned
1.9
Expired
Medium
Unassigned
15.04
Expired
Medium
Unassigned
15.10
Expired
Low
Unassigned

Bug Description

This occurred for a site using clean urls

A user had two pages containing the same cleanurl so that neither could be displayed.

Got this error:
[WAR] c9 (lib/errors.php:747) get_record_sql found more than one row. If you meant to retrieve more than one record, use get_records_*, otherwise check your code or database for inconsistencies

I've been unable to replicate this problem via the system (only editing the db directly).
I've tried:
- copying a page
- copying a page from a collection
- exporting and then importing pages
- editing page 'title & description'

But none of these allow me to end up with a duplicate.

Revision history for this message
Aaron Wells (u-aaronw) wrote :

There's supposed to be a unique index on the VIEW table for the columns (URLID, OWNER, GROUP, INSTITUTION). This should make it impossible for a user to have two pages with the same URLID.

Was the site missing this index?

Changed in mahara:
status: Confirmed → Incomplete
Revision history for this message
Robert Lyon (robertl-9) wrote :

I did a test on my local instance where I set up two pages:

A) page1 with urlid=page1
B) page2 with urlid=page2

I go to the db and check that the unique index is present
"view_urlowngroins_uix" UNIQUE, btree (urlid, owner, "group", institution)

I then update, via db, the urlid of [B] to 'page1' and the database lets me.

This also works on mysql db

I note here: http://www.postgresql.org/docs/9.3/static/indexes-unique.html

"Null values are not considered equal."

So I suspect that unique index is not working how we expect it to due to 2 out of the four columns needing to be null.

I'm wondering if it would be better in mahara instead of having 'owner', 'group', 'institution' as three fields and requiring two of them to be null at any one time we change it to 'owner', 'ownertype' where ownertype can be mapped to an ownertype table containing 1=user,2=group,3=institution that way we wouldn't need to fuss with nulls and that we can follow the ownership of group/institution items better - eg what user made/edited a group or institution page.

Revision history for this message
Aaron Wells (u-aaronw) wrote :

"Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows."

I think you've got it. The problem is due to both these sentences together. It means that ('page1', 1, NULL, NULL) does not "equal" ('page1', 1, NULL, NULL), and so the uniqueness constraint allows it.

Apparently the problem we're facing is what some call "polymorphic associations". See http://stackoverflow.com/questions/7947218/polymorphic-association-foreign-key-constraints-is-this-a-good-solution and http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32

The problem with the (owner, ownertype) approach, is that it doesn't work with Postgres foreign keys. And it would require a rather substantial refactoring of a large table, during the next database upgrade.

I think a smaller solution that might also work, is to just split it into three indexes:

1. (urlid, author)
2. (urlid, group)
3. (urlid, institution)

Although in practice, dropping and recreating indexes also tends to take a while...

Changed in mahara:
assignee: nobody → Aaron Wells (u-aaronw)
status: Incomplete → In Progress
importance: Low → Medium
assignee: Aaron Wells (u-aaronw) → nobody
status: In Progress → Confirmed
Revision history for this message
Aaron Wells (u-aaronw) wrote :

Upping the severity to "Medium" because this probably would be a pretty annoying and noticeable problem if you're using clean URLs.

This problem will affect all versions of Mahara. I don't anticipate fixing it in 1.8_STABLE and 1.9_STABLE, though, because it's not a high severity bug, and the fix will probably require a database upgrade.

Revision history for this message
Aaron Wells (u-aaronw) wrote :

Although actually, we could just add a validation check on the PHP side, to see whether the urlid is already in use for that user/group/institution. Add it to the Pieform validation function.

Robert Lyon (robertl-9)
Changed in mahara:
milestone: 15.04.0 → 15.04.1
Son Nguyen (ngson2000)
no longer affects: mahara/1.8
no longer affects: mahara/1.9
Revision history for this message
Aaron Wells (u-aaronw) wrote :

To clarify:

- Any database changes should only be implemented on master.
- Any non-database fix (like the PHP-side validation check I mentioned a few comments back) can be backported.

Revision history for this message
Robert Lyon (robertl-9) wrote :

There seems to already be checks in the mahara code to stop one having a duplicate urlid

I suspect the only way to get the duplication is via adding data to the database directly - possibly via some migration script.

Until someone can make a test case of how to get this to happen via the front end I'll make this low priority.

If someone can prove it happening via front end please bump up to high

Revision history for this message
Launchpad Janitor (janitor) wrote :

[Expired for Mahara 15.10 because there has been no activity for 60 days.]

Revision history for this message
Launchpad Janitor (janitor) wrote :

[Expired for Mahara 15.04 because there has been no activity for 60 days.]

Revision history for this message
Launchpad Janitor (janitor) wrote :

[Expired for Mahara 1.9 because there has been no activity for 60 days.]

Revision history for this message
Launchpad Janitor (janitor) wrote :

[Expired for Mahara 1.10 because there has been no activity for 60 days.]

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.