MySQL Erro 1062 (Duplicate Entry) when doing "resync" with filenames that differ in capitalization

[popen2@…]

Trac creates tables in MySQL (during initenv) with the field "node_change.path" having the type of TEXT. Also, Trac creates a primary index of the three fields (rev, path, change_type). So far so good. However, when trying to resync with perforce, which allows two files to be of names like "john.txt" and "john.TXT", MySQL (through Trac) claims that

Traceback (most recent call last):
  File "/usr/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11rc1', 'console_scripts', 'trac-admin')()
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/admin/console.py", line 1249, in run
    return admin.onecmd(command)
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/admin/console.py", line 104, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/admin/console.py", line 632, in do_resync
    repos = env.get_repository().sync(self._resync_feedback)
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/versioncontrol/cache.py", line 206, in sync
    path, kind, action, bpath, brev))
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11rc1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'XXX' for key 1")
(where XXX is the name of the first file that was inserted into the table)

It seems that replacing the type of path from TEXT to BLOB solves the problem, probably because it forces MySQL to compare binary values rather than text values. I didn't try to reproduce this with SVN.

These might help: Both trac and mysql are running on ubuntu 8.04 (but on separate machines) Trac version: 0.11rc1, running on Apache 2.2.8 with mod_python. Python version: 2.5.2 MySQL version string: MySQL 5.0.45-Debian_1ubuntu3.3-log Perforce plugins: PyPerforce?-0.4-py2.5-linux-i686.egg, TracPerforce?-0.5dev-py2.5.egg

[anonymous]

I'm not certain, but you may be able to fix this by changing the MySQL collation to something that isn't case insensitive (utf8_bin for example).

- Mike

[popen2@…]

You're right. I changed the "path" column to be with character set "UTF-8 Unicode" and collation of "utf8_bin" and the resync was successful. This is much more elegant than changing the field type to BLOB.

[mcarpent]

Glad I could be helpful! I've run into similar issues myself.

[cboos]

Setting the collation to "utf8_bin" will work as long as you don't have to deal with non-ascii characters, see #4378 (which has now a patch to cope with utf8_bin and unicode).

[mstankie]

I can confirm this worked for me also.

 ALTER TABLE `node_change` CHANGE `path` `path` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL