Two filenames differing by a trailing space stops source browser updates

[JonF]

A user inadvertently committed two filenames that were identical, except that one had a trailing space. This resulted in an error message:

WARNING: Revision 135524 already cached: IntegrityError(1062, "Duplicate entry '1-0000135524-testing/trailingblank -A' for key 'PRIMARY'")

This commit and all subsequent commits were not viewable in Trac.

I confirmed that the commit had been successful, and that the changes were visible in the standard SVN source browser.

I reproduced this error by simply creating two files, 'trailingblank' and 'trailingblank ' and committing them to my Subversion repo.

Changing the log level to DEBUG produced these messages:

2016-03-17 14:23:36,140 Trac[cache] INFO: repos rev [135524] != cached rev [135523]
2016-03-17 14:23:36,142 Trac[cache] INFO: Trying to sync revision [135524]
2016-03-17 14:23:36,145 Trac[cache] DEBUG: Caching node change in [135524]: (u'testing/trailingblank', 'file', 'add', None, -1)
2016-03-17 14:23:36,145 Trac[cache] DEBUG: Caching node change in [135524]: (u'testing/trailingblank ', 'file', 'add', None, -1)
2016-03-17 14:23:36,146 Trac[cache] WARNING: Revision 135524 already cached: IntegrityError(1062, "Duplicate entry '1-0000135524-testing/trailingblank -A' for key 'PRIMARY'")

This changeset (135524) and all subsequent changesets were not cached in the MySQL database, and not viewable using Trac.

I tracked this down to the "node_change" table. In 1.0.1, its primary key includes 'path'. After verifying that the blanks were not being stripped by the Trac code, I discovered that MySQL (we're using 5.5), and maybe SQL in general, will ignore trailing spaces for certain types (CHAR, VARCHAR, TEXT) when doing string comparisons. This appears to be the root cause.

I have temporarily worked around the problem by patching Trac to substitute a non-printable character for spaces in path names that are being inserted into the node_change table. For the few cases where the path name is retrieved from a node_change table row, I added code to restore the spaces.

One notable problem with this solution is that it may not work well with UTF-8 filenames. This is not a problem for us at the moment.

This workaround appears to have resolved the problem, but it seems like a fix based on a schema change would be a better solution.

[Jun Omae]

After 1.1.4 (r13782, #3676), node_change table has id auto-increment primary key, instead.