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.

[Christian Boos]

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  

[anonymous]

Replying to 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  

This did not work for me:

How to Reproduce

While doing a GET operation on /, Trac issued an internal error.

(please provide additional details here)

System Information

Trac	0.11.7
Python	2.6.5 (r265:79063, Apr 16 2010, 14:15:55) [GCC 4.4.3]
setuptools	0.6
MySQL	server: "5.1.41-3ubuntu12.10", client: "5.1.41", thread-safe: 1
MySQLdb	1.2.2
Genshi	0.5.1
mod_python	3.3.1
Pygments	1.2.2
Subversion	1.6.6 (r40053)

Python Traceback

Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/trac/web/main.py", line 450, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.6/dist-packages/trac/web/main.py", line 176, in dispatch
    chosen_handler)
  File "/usr/lib/python2.6/dist-packages/trac/web/main.py", line 296, in _pre_process_request
    chosen_handler = filter_.pre_process_request(req, chosen_handler)
  File "/usr/lib/python2.6/dist-packages/trac/versioncontrol/api.py", line 86, in pre_process_request
    self.get_repository(req.authname).sync()
  File "/usr/lib/python2.6/dist-packages/trac/versioncontrol/cache.py", line 215, in sync
    path, kind, action, bpath, brev))
  File "/usr/lib/python2.6/dist-packages/trac/db/util.py", line 64, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
IntegrityError: (1062, "Duplicate entry '1-depts/cadek/help.gif -A' for key 'PRIMARY'")

[Christian Boos]

Was the change effective?

i.e. what's the output of:

mysql> SHOW FULL COLUMNS FROM 'node_change';

[anonymous]

Replying to cboos:

Was the change effective?

i.e. what's the output of:

mysql> SHOW FULL COLUMNS FROM 'node_change';

mysql> SHOW FULL COLUMNS FROM node_change;
+-------------+------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+------+-----------+------+-----+---------+-------+---------------------------------+---------+
| rev         | text | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| path        | text | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| node_type   | text | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
| change_type | text | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         |
| base_path   | text | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
| base_rev    | text | utf8_bin  | YES  |     | NULL    |       | select,insert,update,references |         |
+-------------+------+-----------+------+-----+---------+-------+---------------------------------+---------+
6 rows in set (0.02 sec)

[Christian Boos]

Ok, looks good. What is this problematic changeset about? (the Changed paths part of svn log -v -r xyz, where xyz is the first changeset not yet synced?)

[anonymous]

Replying to cboos:

Ok, looks good. What is this problematic changeset about? (the Changed paths part of svn log -v -r xyz, where xyz is the first changeset not yet synced?)

I believe this may be due to transaction corruption somewhere. svnadmin rmtxns /path/to/repo svnadmin lstxns /path/to/repo would not work yesterday, so I manually rm -rf'd them and ran the initial import again. This error was upon checking the Trac page ( / ) to see if the data was available via "browse source" this morning. After verifying the mysql settings suggested here I ran a svnadmin verify and it seems to have cleared it up.