Remove sorting on user agent column and remove the index from the login_history table

That plan makes sense to me.

I have attached a screenshot of the schema inconsistencies we have been seeing via the Schema module for reference.

The attached patch resolves the issue for new installs and upgrades by specifying the user_agent index length in login_history_schema(). I think keeping a 255 index length will be helpful since sorting by the user_agent column will still be possible with good performance. Also, I checked the Browser Capabilities Project for user agent lengths. The longest one on record was 188 characters. While the HTTP specifications do not limit the UA length, it looks like most UA lengths are currently well below 255.

Closed #1869586: Schema declaration mismatch as a duplicate.

Committed to 7.x-1.x in 6e7337d and 6.x-1.x in 0edef47. I also removed some whitespace before the hook_schema() implementation in the 7.x-1.x commit.

We just had this issue again, despite the 'fix' here. Do note this:

http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-...

MySQL has different limits on the amount of space you can use to define indexes on column(s) - for MyISAM it's 1,000 bytes; it's 767 for InnoDB.

Our sysadmin had to solve the problem on the fly when backup restoration tests were failing, he had to drop and recreate the table:

CREATE TABLE `login_history` (
  `uid` int(11) NOT NULL COMMENT 'uid of user.',
  `login` int(11) NOT NULL COMMENT 'Timestamp for user?s login.',
  `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The user?s host name.',
  `one_time` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Indicates whether the login was from a one-time login link (e.g. password reset).',
  `user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT 'User agent (i.e. browser) of the device used during the login.',
  KEY `login_history_uid` (`uid`),
  KEY `login_history_onetime` (`one_time`),
  KEY `login_history_uid_host_ua` (`uid`,`hostname`,`user_agent`(200))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Note the user_agent(200) part!

So IMHO the user_agent length should be limited even further, or the index should be defined entirely different...? It's very likely that this problem will pop up again with other Drupal installs, as the user_agent length can vary heavily...

Cheers,

mlc.

The solution proposed in the OP still seems pretty sensible to me:

If it were me, I'd consider increasing the field to something larger than 256 characters, removing the index, and not allowing to stort by user_agent on the report.

My thoughts now are that we should just remove the index and remove the sorting capability rather than increasing the length. I think that combined with #2110249: Long user agent strings result in a fatal PDOException should stabilize the user agent storage/functionality quite a bit.

@Cottser - that makes sense to me. It seems unlikely someone would need to sort on user agents.

Great, I'll move forward with that plan.

How about this?

On visual review it looks good.

Committed to 7.x, moving to 8.x.

Committed to 8.x without the hook_update_N().