mahara-contributors team mailing list archive

[Doris Tam <1906723@xxxxxxxxxxxxxxxxxx>]

After investigating this, it looks like artefacts are imported in do
keep their original ctime and do not update.

Steps:

At a fresh install, no artefacts seem to be made before user ctime
 artefact_id | artefact_ctime | user_ctime | user_id
-------------+----------------+------------+---------
(0 rows)


-> *investigating on a site* get some data where this is true

  a_id  | a_type  |  art_create          | usr_create         | user_id | username
--------+--------------------+---------------------------+---------------------+-----
 436768 | folder  | 2020-05-19 10:09:54 | 2020-05-20 10:04:15 |   56965 | testuser
 436745 | folder  | 2020-05-19 10:09:54 | 2020-05-20 10:04:15 |   56965 | testuser
 436760 | folder  | 2020-05-19 10:09:54 | 2020-05-20 10:04:15 |   56965 | testuser
 436753 | folder  | 2020-05-19 10:09:54 | 2020-05-20 10:04:15 |   56965 | testuser
 436761 | folder  | 2020-05-19 10:09:00 | 2020-05-20 10:04:15 |   56965 | testuser
 436754 | folder  | 2020-05-19 10:09:00 | 2020-05-20 10:04:15 |   56965 | testuser
 436769 | folder  | 2020-05-19 10:09:00 | 2020-05-20 10:04:15 |   56965 | testuser
 436746 | folder  | 2020-05-19 10:09:00 | 2020-05-20 10:04:15 |   56965 | testuser

```sql
 SELECT  DISTINCT ON (a.artefacttype) artefacttype,
       a.ctime              AS art_ctime,
       a.title              AS title,
       u.ctime              AS u_ctime,
       age(u.ctime,a.ctime) AS diff
  FROM artefact a JOIN usr u
    ON a.owner = u.id
 WHERE owner=0000 AND a.ctime < u.ctime;
 ```

This user was created in 2020 however the culprit artefact types that
seem to be created before the user are:

- folder
- blog
- address (contact nfo)
- archive
- blog
- blogaddress
- blogpost
- business num
- city
- comment
- contactinformation
- country
- email
- educationhistory
- email
- employment
- fax
- file
- firstname
- folder
- homenumber
- html
- image
- industry


-> *try creating content as for an institution and then giving that to someone else - Nope.

```sql
SELECT
  a.ctime as a_ctime,
  a.title,
  usr.ctime as u_ctime,
  age(usr.ctime, a.ctime) as diff
  FROM artefact a JOIN usr
  on a.owner = usr.id
 WHERE owner=56965
   AND artefacttype='folder';
```


looks like all the ctime early might be an imported thing...

-> *fill up the profile page*
-> *let's try exporting my recently create user*
-> *import them on a fresh db and then create artefacts* usr id=2

-> *make a new user, give them stuff already like contact info, then
import and overwrite*

Exactly what I thought. Imports!

-> *just looking at folders*

```sql
SELECT a.title       AS a_title,
       a.artefacttype AS a_type,
       a.ctime       AS a_ctime,
       usr.ctime     AS u_ctime,
       age(usr.ctime, a.ctime)  AS since_usr_born
  FROM artefact a JOIN usr
    ON a.owner = usr.id
 WHERE owner=2
   AND age(usr.ctime,
       a.ctime) < '1 second'::interval
  --  AND artefacttype='folder'
ORDER BY since_usr_born DESC;
 ```

-[ RECORD 1 ]--+------------------------------------------
a_title        | juge@xxxxxxx
a_type         | email
a_ctime        | 2021-06-29 17:17:38
u_ctime        | 2021-06-29 17:17:38
since_usr_born | 00:00:00
-[ RECORD 2 ]--+------------------------------------------
a_title        | Sybill Stevens's Journal
a_type         | blog
a_ctime        | 2021-06-29 17:17:38
u_ctime        | 2021-06-29 17:17:38
since_usr_born | 00:00:00
-[ RECORD 3 ]--+------------------------------------------
a_title        | Amelia
a_type         | firstname
a_ctime        | 2021-06-29 17:17:38
u_ctime        | 2021-06-29 17:17:38
since_usr_born | 00:00:00
-[ RECORD 4 ]--+------------------------------------------
a_title        | Glass
a_type         | lastname
a_ctime        | 2021-06-29 17:17:38
u_ctime        | 2021-06-29 17:17:38
since_usr_born | 00:00:00
-[ RECORD 5 ]--+------------------------------------------
a_title        | Leah Fulton
a_type         | preferredname
a_ctime        | 2021-06-29 17:19:28
u_ctime        | 2021-06-29 17:17:38
since_usr_born | -00:01:50
-[ RECORD 6 ]--+------------------------------------------
a_title        | introduction
a_type         | introduction
a_ctime        | 2021-06-29 17:19:28
u_ctime        | 2021-06-29 17:17:38
since_usr_born | -00:01:50
-[ RECORD 7 ]--+------------------------------------------
a_title        | 237985170873245
a_type         | studentid
a_ctime        | 2021-06-29 17:19:28
u_ctime        | 2021-06-29 17:17:38
since_usr_born | -00:01:50
-[ RECORD 8 ]--+------------------------------------------
a_title        | Et exercitationem qu
a_type         | city
a_ctime        | 2021-06-29 17:19:35
u_ctime        | 2021-06-29 17:17:38
since_usr_born | -00:01:57
-[ RECORD 9 ]--+------------------------------------------
a_title        | Dolorum reiciendis p
a_type         | town
a_ctime        | 2021-06-29 17:19:35
u_ctime        | 2021-06-29 17:17:38
since_usr_born | -00:01:57

Looks like it isn't necessarily a bug unless we expect the ctime to
change on import.

** Changed in: mahara
     Assignee: Robert Lyon (robertl-9) => Doris Tam (doristam)

** Changed in: mahara
       Status: Confirmed => Invalid

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/1906723

Title:
  Artefacts are created before the author is created according to their
  ctime

Status in Mahara:
  Invalid

Bug description:
  Some how it is possible for a usr.ctime value to be newer than an
  artefact.ctime value when the artefact.owner = usr.id

  To check this out do the following:

  SELECT a.id AS artefact_id, a.ctime AS artefact_ctime, u.ctime AS
  user_ctime, u.id AS user_id FROM artefact a JOIN usr u ON u.id =
  a.owner WHERE a.ctime < u.ctime ORDER BY a.ctime DESC;

  There doesn't seem to be any obvious reason for this, what I've checked so far on a big database:
  1. Is it only an old problem? No. The problem exists for old artefacts / users (made in 2008) and new artefacts / people (made in 2020) 
  2) Are the artefacts of only one type? No. The offending artefacts are of many different artefact types.
  3) Are the users all using the same auth method? No. The people have different auth methods, both internal and external.
  4) Are they in the same institution? No. They are in a variety of institutions
  5) Are the difference between artefact.ctime and usr.ctime small (race condition)? No. The gap noticed varies from 1sec to almost 1 decade (3561 days)

  So my conclusion is that there is something updating the usr.ctime
  field after a user has been created. That shouldn't be happening

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1906723/+subscriptions