Add last 'changed' property to user entity

The patch attached adds a 'changed' column to the user table and added also some views and token integration. I prefer the name 'changed' because other entities (eg. node) also use this name.

Drupal 8 user entity has 'changed' field.

Changing to D7

+1
It will be great to have such information for D7 too.

Drupal 8 has it as well and this seems like it could be useful, so I'm not opposed to adding this to a major D7 release (7.60/7.70)

hi,
i encountered following error after applying patch::
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.changed' in 'field list': SELECT u.uid AS uid, u.name AS name, u.status AS status, u.created AS created, u.changed AS changed, u.access AS access FROM {users} u WHERE (u.uid <> :db_condition_placeholder_0) ORDER BY u.created DESC LIMIT 50 OFFSET 0; Array ( [:db_condition_placeholder_0] => 0 ) in PagerDefault->execute() (line 79 of C:\xampp\htdocs\drupal-7\includes\pager.inc).
i can't acces my people menu now!!!

@vinayak nair did you run database update afte applying the patch?

hi,
i updated my database but still the error is not gone.
i could not access my people menu!!!

Thanks for path, works but it doesn't work for updating exciting users.
Fixed it & some typo 'Last changed'.

Latest one, path without 'www' to fix the tests

patch is probably ok
testbot is failing, see related issue

#2970950: D7 test runner not working since may 4th 2018 'Composer command failed'

This would be super useful, I've definitely had to hack created in the past to provide this functionality, a new column would be ideal.

works fine for me RTBC

Bumping to 7.70, this didn't make it into 7.60

I can't believe Drupal 7.70 release without it =.=

Rationale, D8 has this,
patch has tests
Patch rtbc over 2 years straight

Adding my results ... Patch from #20 applies fine to the latest 7.x branch. No problems with database update. UI on Admin > People works as expected. RTBC++

Once this gets in, we'll want to add patches to some of the most popular modules. I looked through the code for a handful of these of these top 100 modules and found the following (though I may have missed a few).

The following need the "changed" property to be added: views, entity (Entity API).

These modules don't appear to need updates: admin_views, ctools, metatag, token, views_bulk_operations, rules, i18n, variable, context, ds (Display Suite), search_api, strongarm, uuid.

This would be really handy to get in. :-)

RTBC + 1, approved

I don't think this is a risky change, it needs a change record though

Draft change record here:

https://www.drupal.org/node/3250745

For the CR, would help to edit this page: https://api.drupal.org/api/drupal/developer%21globals.php/global/user/7.x

I don't have access to edit this page to add the details? Is this a role that someone could grant me? Access to edit api documentation?

(via @longwave in slack...) looks like it's:

• https://www.drupal.org/project/documentation (see explanation on the project page)
• https://git.drupalcode.org/project/documentation/-/blob/7.x-1.x/develope... ...is where that page comes from, IIUC.

Thanks for the draft CR @joseph.olstad

Hmm, is that a real test fail with MySQL 8? Re-running to check.

Doesn't look like it was a real test fail; phew.

Thanks everyone that contributed!

Amazing, cheers.

Oh, sorry @joseph.olstad I was running through the CR's for the release on 1st Dec and created a new one for this change:

https://www.drupal.org/node/3251649

I somehow missed the draft you'd created. Apologies.

We should probably add a D9 issue to migrate changed column. d6|7_user.yml only migrates created, not changed.

@mcdruid, I'm on vacation at the moment on my android phone, feel free to scavange (pilfer/borrow/copy/paste) from the original CR https://drupal.org/node/3250745

Good suggestion @heddn; I filed #3252133: Add the new D7 user->changed property to the migration system for that.

We have run into a problem after upgrading a site to Drupal 7.83 where the site was previously using Drupal 7.82 with patches including the patch in comment #20. On executing "drush updb" it returns "No database updates required." However, subsequent login after entering username and password fails with error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'base.changed' in 'field list': SELECT base.uid AS uid, base.name AS name, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature, base.signature_format AS signature_format, base.created AS created, base.changed AS changed, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.language AS language, base.picture AS picture, base.init AS init, base.data AS data FROM {users} base WHERE (base.name = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => xxxx ) in DrupalDefaultEntityController->load() (line 198 of includes/entity.inc).

Could this be related to a missing database update and require using an sql command to reset the schema_version?

Any thoughts on how to resolve this are appreciated. Thanks.

I was able to work around this by adding the column manually, but without an index.

First I tried what the database updates was attempting, with no luck:

mysql> ALTER TABLE users ADD `changed` INT NOT NULL DEFAULT 0 COMMENT 'Timestamp for when user was changed.', ADD INDEX `changed` (`changed`);
ERROR 1061 (42000): Duplicate key name 'changed'

Then I tried it without adding the index:

mysql> ALTER TABLE users ADD `changed` INT NOT NULL DEFAULT 0 COMMENT 'Timestamp for when user was changed.';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Back up your database before doing this, and make sure you use a database table prefix if your site has them. This only got my site up and running, nothing more. I don't know if I should attempt to add an index, but that didn't work as a second step:

mysql> ALTER TABLE users ADD INDEX `changed` (`changed`);
ERROR 1061 (42000): Duplicate key name 'changed'

This may be due to my site having started as a Drupal site a long time ago, and it may have some a legacy database table structure. When I do a SHOW CREATE TABLE users; on my current database, this key already exists:

[...]
  KEY `changed` (`access`),
[...]

Just to be clear, it sounds like these are problems on sites that may have applied previous patches or otherwise had modified the users table as opposed to being a bug in the latest release. Obviously let me know if I've misunderstood.

After user_update_7020() has run (or in a clean install of D7.83 or later) I'd expect the users table to look something like this:

mysql> SHOW CREATE TABLE `users` \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique user ID.',
  `name` varchar(60) NOT NULL DEFAULT '' COMMENT 'Unique user name.',
  `pass` varchar(128) NOT NULL DEFAULT '' COMMENT 'User’s password (hashed).',
  `mail` varchar(254) DEFAULT '' COMMENT 'User’s e-mail address.',
  `theme` varchar(255) NOT NULL DEFAULT '' COMMENT 'User’s default theme.',
  `signature` varchar(255) NOT NULL DEFAULT '' COMMENT 'User’s signature.',
  `signature_format` varchar(255) DEFAULT NULL COMMENT 'The `filter_format`.format of the signature.',
  `created` int(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for when user was created.',
  `changed` int(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for when user was changed.',
  `access` int(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for previous time user accessed the site.',
  `login` int(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for user’s last login.',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Whether the user is active(1) or blocked(0).',
  `timezone` varchar(32) DEFAULT NULL COMMENT 'User’s time zone.',
  `language` varchar(12) NOT NULL DEFAULT '' COMMENT 'User’s default language.',
  `picture` int(11) NOT NULL DEFAULT '0' COMMENT 'Foreign key: `file_managed`.fid of user’s picture.',
  `init` varchar(254) DEFAULT '' COMMENT 'E-mail address used for initial account creation.',
  `data` longblob COMMENT 'A serialized array of name value pairs that are related to the user. Any form values posted during user edit are stored and are loaded into the $user object during user_load(). Use of this field is discouraged and it will likely disappear in a future...',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  KEY `picture` (`picture`),
  KEY `changed` (`changed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores user data.'

If the index doesn't look right (as in @sillygwailo's example) you should be able to drop and re-add it. (Be careful, take backups, the value of your investments may go down as well as up etc..):

mysql> DROP INDEX changed ON users;
Query OK..

mysql> CREATE INDEX changed ON users (changed);
Query OK..

@izmeez if you'd run an update as part of a previous patch and your schema's not correct you could tweak your system table to get the update to run (again take backups beforehand etc..) - something like:

mysql> UPDATE system SET schema_version = 7019 WHERE name = 'user' AND type = 'module';
Query OK..

...then run the db update.

However, I suspect that'd only go smoothly if you don't have the "changed" field and the index in the schema beforehand.

The data in that field shouldn't be too precious so if you had to drop the field and re-add it manually.. worse things happen. You've taken a backup or three already, right? That'd be something like:

mysql> ALTER TABLE users DROP COLUMN changed;
Query OK..

mysql> ALTER TABLE users ADD COLUMN changed int NOT NULL DEFAULT '0' COMMENT 'Timestamp for when user was changed.';
Query OK..

..after which you'd need to manually re-add the index (as above).

Assuming you can get the schema for the users table to look like the above, you should be okay I'd hope.

Did we mention that you should take backups before performing manual SQL surgery?

Thank you @sillygwailo and @mcdruid.

Yes, to be clear there is no indication of a bug in the drupal 7.83 release.

The detailed explanation was helpful.

Yes, of course, first a database backup was done.

Then using the suggestion to check if the user_update_7020() had run with the SQL command:

mysql> SHOW CREATE TABLE `users` \G

confirmed the field 'changed' was not present and using:

mysql> UPDATE system SET schema_version = 7019 WHERE name = 'user' AND type = 'module';

to reset the schema_version and running update worked to resolve the problem.

It would have been nice to check what the schema_version was at before resetting it but I was not able to figure out the correct sql command to do that.

Again, thanks for the help.

I'm getting the same problem as #49 on all my Drupal 7 sites. The update fails and I can no longer log in to any of them.

It's probable that some upgrades will run into this same issue -- i.e. the 'changed' column already exists -- either because someone has already applied a patch from this issue on their site (e.g. @izmeez and @sillygwailo), or they installed a module that does something similar.

Thank you @mcdruid for providing a manual solution. But here's a simple code change that checks db_field_exists('users', 'changed') and db_index_exists('users', 'changed'), and only adds the column or recreates the index as necessary.

IMO, this code should have been part of the original commit.

Just to be clear, even though we thought we had applied the patch before, on review it appears that the patch had not be fully applied (the update database had not run but the schema_version may have been incremented or corrupted), therefore there is some other reason why the update did not run in our case. We have not, as of yet, identified the underlying state of the site. Since we have that all so important backup, with some future new learning we may be able to go back and more clearly identify the state we were in. Thanks.

If you download the Drupal 4.6.0 tarball (lol..) you can see that at that time there was a column called `changed`, as well as an index called `changed`. This column was renamed to `access` in system_update_136() which you can find in the 4.7.0 tarball. Doesn't look like the mysql index was renamed, so maybe it makes sense that folks still have a `changed` index..?

@mfb This is a Drupal 7 issue. If you look at comment #52 you will see that with the new drupal 7.83 release the user table now includes both a `changed` field and an `access` field. While the `access` field is a timestamp of last access, the `changed` field is a timestamp of when the user info was last changed (or modified), such as their email address. I am not sure how this relates to the corresponding field names in Drupal 9 but I would be surprised if they were thought to be the same and just treated as a renaming.

See the child issues sidebar box for contrib modules that contain patches to support this new field. Please check them out and test if you get a chance.

@izmeez my response was to #52; I'm saying that it looks possible that very old drupal sites - which have been upgraded for many years - could have a pre-existing `changed` index in the users table dating from Drupal 4.6 days. That said, I didn't try to reproduce the upgrades to see if my theory is true.

@mfb Thanks for the clarification, my bad. It is getting so confusing these days with the drupal change to versions of modules using numbers like 4.x and 5.x etc. I inadvertently thought you were talking about something new not something old like drupal 4.6 and 4.7 my apologies.

I met similar issue from #49 with existing D7 site (7.82) and using entitycache module.

Updating core thanks to drush without making updatedb will revert because of this error :

$ drush upc drupal
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'base.changed' in 'field list': SELECT base.uid AS uid, base.name AS name, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature, base.signature_format AS signature_format, base.created AS created, base.changed AS changed, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.language AS language, base.picture AS picture, base.init AS init, base.data AS data, base.uuid AS uuid
FROM 
{users} base
WHERE  (base.uid IN  (:db_condition_placeholder_0)) ; Array
(
    [:db_condition_placeholder_0] => 0
)
 in EntityCacheControllerHelper::entityCacheLoad() (line 142 of /var/www/html/www/sites/all/modules/contrib/entitycache/includes/entitycache.entitycachecontrollerhelper.inc

In this case, do not use drush upc but drush pm:update to update code and database in the same time.

I ran into this problem, and our site is one that started on an old version of Drupal. Fix below.

After upgrading, when I ran update.php it failed with the error:

The following updates returned messages
user module
Update #7020
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'changed': ALTER TABLE {users} ADD `changed` INT NOT NULL DEFAULT 0 COMMENT 'Timestamp for when user was changed.', ADD INDEX `changed` (`changed`);

Using phpmyadmin to look at my users table, it doesn't have a 'changed' field, but I do see a 'changed' index:

Keyname: Changed
Type: BTREE
Unique: No
Packed: No
Column: access
Cardinality: 5659
Collation: A
Null: No

I dropped that index, then ran the update.php again. This time it completed without any errors.

Checking the structure in phpmyadmin shows the users table now has a 'changed' field, and a 'changed' index, and that the new 'changed' index is on the 'changed' column, as expected.

Patch to tweak the update function, given that it seems correct for very old MySQL databases to have a "changed" index on the "access" column. Presumably this means they are also missing the "access" index on "access" column, so I add that if needed. (On Postgres, the "changed" index was dropped and a new "access" index was created, so I think this only affects MySQL.) Note, I don't have access to re-open this issue.

p.s. Here is ye olde update function for those don't want to go poring thru tarballs: https://api.drupal.org/api/drupal/modules%21system%21system.install/func...

We had the same error as #49 on multiple sites when applying the 7.86 or 7.87 update to sites on the previous security update version, 7.82 . One or two were very old sites that started on Drupal <= 4.6, most were not. Applying #55 + #65 + https://www.drupal.org/project/drupal/issues/3107295#comment-13980549 (separate issue affecting multiple sites) resolved it.

Just ran into the same issue upgrading from 7.82 to 7.87. WSOD

This is a Drupal 7 multisite, started on Drupal 7, no changes ever made to the users table.

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'base.changed' in 'field list': SELECT base.uid AS uid, base.name AS name, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature, base.signature_format AS signature_format, base.created AS created, base.changed AS changed, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.language AS language, base.picture AS picture, base.init AS init, base.data AS data FROM {users} base WHERE (base.uid IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] =>1 ) DrupalDefaultEntityController->load() in includes/entity.inc

@Collins405 I've commented in the issue you filed that @cilefen linked.

That's slightly different to the recent discussion here, which is instead about the fact that some sites may have something in their schema (e.g. an index or column) that conflicts with the schema update in user_update_7020().

I am pleased to say I have got to the bottom of the problem which was preventing this update working on most of my Drupal 7 websites.
A while ago there was a patch to the user table which updated the schema_version number to 7020. I had applied this patch to most of my D7 sites. This new update in 7.83 checks the schema_version, (expecting it to be 7019) and thinks the update has already been done.
The solution is to put the schema_version back to 7019 and run the update again. This worked flawlessly on every one of my sites.

UPDATE system SET schema_version = 7019 WHERE name = 'user' AND type = 'module';

Thanks to mcdruid above for the code.

I have added the following comment to the Issue Summary:

*** Note: Anyone who experiences difficulty with user logins after an update of drupal 7 core from before 7.83 to 7.83 or above should look at comment #52 and other comments following that.***

Thanks for all the comments and investigation.

It looks like the most common problem scenario is an existing index called changed on the access field, and #65 looks like a good solution for that.

However, it seems that some sites will have an existing changed field that conflicts with the update.

I'm not certain we can cater for every such eventuality, but here's a patch that tries to alter an existing field to match the new schema.

We'd hope that in most situations this will preserve any data that's already in that column. However, I suppose it's possible there'd be a problem if the existing schema is significantly different.

I'll do some manual testing.

It may be a more robust option to drop the changed field if it's already there and create it again, discarding any existing data in the process (and replacing it with the default value that matches the access field).

Anyone have a strong preference for either option?

I've done some basic manual testing with MySQL and SQLite, putting an index and / or field "in the way" of this update and running it.

So far it's worked okay in every scenario I've tried.

I am considering changing the logic to drop and recreate instead of using db_change_field() when there's already a changed field though, as I suspect that's less likely to cause a problem, and the data in that column should not be precious.

@mcdruid While I appreciate the fix for this issue has caused more grief than expected I would take issue with the assertion that

the data in that column should not be precious.

This issue is in it's 10th year and the opening issue summary and other comments have explained the rationale and usefulness of the change to some platforms. It may be presumptuous to assume the existing data is not important, at least to some sites. An approach that preserves existing data would be preferable and if needed possibly an alert on update where a conflict cannot be resolved directing people to this issue and the various solution contained might be better.

I would urge for more input to this question by those who this might directly impact.

Thank you.

The patch in #73 appears to work great in my quick testing, and the code looks like it addresses the issues mentioned so far. I don't personally feel comfortable marking RTBC because I haven't done the rigorous testing that a core commit deserves.

I agree with @izmeez that it's better to make the change without destroying existing data because it's highly likely that anyone who has already applied this patch in the past would like to keep the data that's already there. Plus, imagine a website that is user-centric where the changed date is shown on a user's profile. If we were to delete the data in that column, the date would change, and that might be confusing/concerning to users.

Two questions, besides that RTBC.

1. +++ b/modules/user/user.install
   @@ -935,6 +935,15 @@ function user_update_7019() {
   +  if (db_index_exists('users', 'changed')) {
   +    if (!db_index_exists('users', 'access')) {
   +      db_add_index('users', 'access', array('access'));
   +    }
   +    db_drop_index('users', 'changed');
   +  }
   

   Question:

   Why is the inner if() dependent on the outer if.

   Don't we always want to add the index on 'access' when it does not exist?

2. +++ b/modules/user/user.install
   @@ -946,12 +955,19 @@ function user_update_7020() {
   +  if (db_field_exists('users', 'changed')) {
   +    db_change_field('users', 'changed', 'changed', $spec, $keys);
   +  }
   

   Will db_change_field() also add the index?

Good questions!

1) Yes I suppose we would want to create the index on access if it didn't exist. The nested if is based on the assumption that it only won't exist if it's there but called something else.

2) IIUC \DatabaseSchema::addField() will create the index e.g. by calling \DatabaseSchema_mysql::createKeysSql

https://git.drupalcode.org/project/drupal/-/blob/7.87/includes/database/...

I had to check, but yes db_change_field() will also do the same if there are keys in the schema:

https://git.drupalcode.org/project/drupal/-/blob/7.87/includes/database/...

Do you want a change based on 1 or shall we go ahead with #73? I think I'm happy as it is.

RTBC for #73 - in practice probably no one has the index on access missing that does not also have an index on changed ...

Thank you everyone that helped out with this follow up.

FWIW, we're running into the missing column (much like as reported in #62 and #68) because we are doing a drush vset maintenance_mode 1 at the start of our deployment process, which seems to be loading a user object somewhere along the bootstrap process. Not sure yet if this is because our particular setup or a more generic problem. One would think that should be a pretty common setup.

I am still having this issue on a Drupal 7 multisite.

My default_users table was updated and the default_system table had the updated module number. But the _users & _system tables for my two other sites were not updated.

I tried running the update statement above and reinstalling the 7020 update and still no luck.

I have some problems updating from 7.56 to 7.90 in a multisite instance. I resolved with this sequence:

• drush @sites -y variable-set maintenance_mode 1
• drush @sites -y cache-clear all
• drush default -y pm-update
• drush @sites -y updatedb
• drush @sites -y variable-set maintenance_mode 0
• drush @sites -y cache-clear all

@millisaeubanks Updates on a multi-site need to be run on each separate site, e.g. you'll need to run the update at example1.com/update.php as well as example2.com/update.php, etc.

Here is the commented scripted solution I have introduced inside my deployment process inside a small shell script. It will need to run once in every environment in order to be able to upgrade all my Drupal sites from 7.73 to 7.92. I will remove it once 7.92 is in production. Thanks to all for the input and inspiration.

#!/bin/bash

# - - - - - - - - - - - 
# September 2022 - update Drupal core to 7.92
#
# Fix the users table before running breaking updates in Drupal 7.83 updates
# @see https://www.drupal.org/project/drupal/issues/1835754#comment-14327942
# 
# We cannot bootstrap Drupal before user_update_7020 has run because of schema incompatibility.
# drush sql-query commands are used to avoid bootstraping Drupal
#
# Remove this block and redeploy sites after running in the production environment

echo "Running mantenance mode = 1 && update DB before all other steps for Drupal >= 7.83 update."

drush sql-query 'UPDATE variable SET value = "i:1;" WHERE name= "maintenance_mode";'

drush sql-query 'TRUNCATE TABLE cache;'
drush sql-query 'TRUNCATE TABLE cache_block;'
drush sql-query 'TRUNCATE TABLE cache_bootstrap;'
drush sql-query 'TRUNCATE TABLE cache_field;'
drush sql-query 'TRUNCATE TABLE cache_filter;'
drush sql-query 'TRUNCATE TABLE cache_form;'
drush sql-query 'TRUNCATE TABLE cache_image;'
drush sql-query 'TRUNCATE TABLE cache_menu;'
drush sql-query 'TRUNCATE TABLE cache_page;'
drush sql-query 'TRUNCATE TABLE cache_path;'
drush sql-query 'TRUNCATE TABLE cache_token;'
drush sql-query 'TRUNCATE TABLE cache_update;'
drush sql-query 'TRUNCATE TABLE cache_variable;'

drush updb -y

echo "Setting maintenance mode was sucessfull?"
drush vget 'maintenance_mode'

drush sql-query 'UPDATE variable SET value = "i:0;" WHERE name= "maintenance_mode";'

drush sql-query 'TRUNCATE TABLE cache;'
drush sql-query 'TRUNCATE TABLE cache_block;'
drush sql-query 'TRUNCATE TABLE cache_bootstrap;'
drush sql-query 'TRUNCATE TABLE cache_field;'
drush sql-query 'TRUNCATE TABLE cache_filter;'
drush sql-query 'TRUNCATE TABLE cache_form;'
drush sql-query 'TRUNCATE TABLE cache_image;'
drush sql-query 'TRUNCATE TABLE cache_menu;'
drush sql-query 'TRUNCATE TABLE cache_page;'
drush sql-query 'TRUNCATE TABLE cache_path;'
drush sql-query 'TRUNCATE TABLE cache_token;'
drush sql-query 'TRUNCATE TABLE cache_update;'
drush sql-query 'TRUNCATE TABLE cache_variable;'

echo "Removing maintenance mode was sucessfull?"
drush vget 'maintenance_mode'

# END September 2022 - update Drupal core to 7.92
# - - - - - - - - - - -