Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Using the schema module, we've been seeing a mismatch on declared value for the user_agent field since it exceeds the standard 255 limit for a varchar index. I thought at first to remove the index on the varchar, but I'm guessing the index is there because the report allows to sort by that field.
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. shrop has more details.
Comment | File | Size | Author |
---|---|---|---|
#11 | login_history-1825020-11.patch | 1.52 KB | star-szr |
Comments
Comment #1
gregglesThat plan makes sense to me.
Comment #2
shrop CreditAttribution: shrop commentedI 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.
Comment #3
star-szrClosed #1869586: Schema declaration mismatch as a duplicate.
Comment #4
star-szrCommitted 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.
Comment #6
malc0mn CreditAttribution: malc0mn commentedWe just had this issue again, despite the 'fix' here. Do note this:
http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-...
Our sysadmin had to solve the problem on the fly when backup restoration tests were failing, he had to drop and recreate the table:
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.
Comment #7
star-szrThe solution proposed in the OP still seems pretty sensible to me:
Comment #8
star-szrMy 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.
Comment #9
greggles@Cottser - that makes sense to me. It seems unlikely someone would need to sort on user agents.
Comment #10
star-szrGreat, I'll move forward with that plan.
Comment #11
star-szrHow about this?
Comment #12
gregglesOn visual review it looks good.
Comment #13
star-szrCommitted to 7.x, moving to 8.x.
Comment #16
star-szrCommitted to 8.x without the hook_update_N().