Edgewall Software
Modify

Opened 16 years ago

Closed 16 years ago

Last modified 13 years ago

#7305 closed defect (duplicate)

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

Reported by: popen2@… Owned by: Christian Boos
Priority: normal Milestone:
Component: version control Version: 0.11rc1
Severity: normal Keywords: mysql perforce duplicate blob
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description (last modified by Christian Boos)

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

Attachments (0)

Change History (10)

comment:1 by anonymous, 16 years ago

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

comment:2 by popen2@…, 16 years ago

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.

comment:3 by mcarpent, 16 years ago

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

comment:4 by Christian Boos, 16 years ago

Description: modified (diff)
Milestone: 0.11
Resolution: duplicate
Status: newclosed

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).

comment:5 by mstankie, 16 years ago

I can confirm this worked for me also.

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

in reply to:  5 ; comment:6 by anonymous, 13 years ago

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'")

in reply to:  6 ; comment:7 by Christian Boos, 13 years ago

Was the change effective?

i.e. what's the output of:

mysql> SHOW FULL COLUMNS FROM 'node_change';

in reply to:  7 ; comment:8 by anonymous, 13 years ago

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)
Last edited 13 years ago by Christian Boos (previous) (diff)

in reply to:  8 ; comment:9 by Christian Boos, 13 years ago

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?)

in reply to:  9 comment:10 by anonymous, 13 years ago

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.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.