My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
Issue 851: [MySQL] hash collision due to wrong datatype
5 people starred this issue and may be notified of changes. Back to list
Status:  Submitted
Owner:  ----
Closed:  May 2013


Sign in to add a comment
 
Reported by Ian.Kuml...@gmail.com, Feb 22, 2011
Affected Version: All? this was in 2.1.6

What is the expected output? What do you see instead?
change_messages hash id collision

Please provide any additional information below.
Using mysql change_messages uuid is stored as a VARCHAR but that is a case insensitive datatype, if you want to store it case sensitive you have to store it as VARBINARY

See:
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

We just had a collision happening where "16635-AAAAPX//+Es=" was inserted and collided with "16635-AAAAPX//+es=".


[2011-02-22 12:53:28,741] ERROR com.google.gerrit.server.git.ReceiveCommits : Error storing replacement patch for change [CHANGE NR], commit [SHA1]
com.google.gwtorm.client.OrmException: insert failure on change_messages
        at com.google.gwtorm.schema.sql.SqlDialect.convertError(SqlDialect.java:122)
        at com.google.gwtorm.jdbc.JdbcAccess.convertError(JdbcAccess.java:331)
        at com.google.gwtorm.jdbc.JdbcAccess.doInsert(JdbcAccess.java:178)
        at com.google.gwtorm.jdbc.JdbcAccess.doInsert(JdbcAccess.java:35)
        at com.google.gwtorm.client.impl.AbstractAccess.insert(AbstractAccess.java:56)
        at com.google.gerrit.server.git.ReceiveCommits.doReplace(ReceiveCommits.java:1224)
        at com.google.gerrit.server.git.ReceiveCommits.doReplaces(ReceiveCommits.java:1004)
        at com.google.gerrit.server.git.ReceiveCommits.onPreReceive(ReceiveCommits.java:281)
        at org.eclipse.jgit.transport.ReceivePack.executeCommands(ReceivePack.java:985)
        at org.eclipse.jgit.transport.ReceivePack.service(ReceivePack.java:646)
        at org.eclipse.jgit.transport.ReceivePack.receive(ReceivePack.java:577)
        at com.google.gerrit.sshd.commands.Receive.runImpl(Receive.java:89)
        at com.google.gerrit.sshd.AbstractGitCommand.service(AbstractGitCommand.java:104)
        at com.google.gerrit.sshd.AbstractGitCommand.access$000(AbstractGitCommand.java:34)
        at com.google.gerrit.sshd.AbstractGitCommand$1.run(AbstractGitCommand.java:69)
        at com.google.gerrit.sshd.BaseCommand$TaskThunk.run(BaseCommand.java:395)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
        at java.util.concurrent.FutureTask.run(FutureTask.java:166)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:165)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:266)
        at com.google.gerrit.server.git.WorkQueue$Task.run(WorkQueue.java:324)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
        at java.lang.Thread.run(Thread.java:636)

Caused by: java.sql.BatchUpdateException: Duplicate entry '16635-AAAAPX//+Es=' for key 1
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2007)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1443)
        at com.google.gwtorm.jdbc.JdbcAccess.execute(JdbcAccess.java:293)
        at com.google.gwtorm.jdbc.JdbcAccess.doInsert(JdbcAccess.java:171)
        ... 22 more


Feb 22, 2011
#1 Ian.Kuml...@gmail.com
Would something like this help:
ALTER TABLE table_name
MODIFY column_name data_type CHARACTER SET latin1 COLLATE latin1_general_cs;

Is there other tables that has the same assumption? All VARCHAR tables?
Feb 22, 2011
#2 sop@google.com
Yup, all VARCHAR tables assume what every other SQL server out there assumes, which is VARCHAR is case sensitive. So they all should have their collation changed.
Feb 22, 2011
#3 Ian.Kuml...@gmail.com
Heh, outch - time for a schema update =P

(Changing all collate on our db manually now)
Feb 22, 2011
#4 Ian.Kuml...@gmail.com
If someone else who needs to do the conversion quickly

Dump the tables you need in to a tables.txt file and then, something like this:
for x in `cat tables.txt` ; do for y in $(echo show full COLUMNS FROM reviewdb.$x | mysql | grep _ci | awk '{ print $1 ":" $2 '}) ; do  z=${y//:/ } && echo ALTER TABLE reviewdb.$x MODIFY $z CHARACTER SET latin1 COLLATE latin1_general_cs |not_mysql_yet ;done ;done

remove "|not_mysql_yet" and verify the output, then change it to "|mysql" when you are certain that the output is correct.

No warranty, it's ugly bash hacking, but.... ;)
Feb 22, 2011
#5 Ian.Kuml...@gmail.com
Theoretically it modifies too many fields, really really verify
Feb 24, 2011
#6 Ian.Kuml...@gmail.com
NOTE: user accounts will become case sensitive which might confuse your users

(Damn, typing on a mobile phone while being out in the cold sure as hell confuses me)
Nov 23, 2011
#7 altm...@gmail.com
Is anybody working on this issue? 
May 17, 2013
#8 sop@google.com
 Issue 1700  has been merged into this issue.
May 17, 2013
#9 sop@google.com
https://gerrit-review.googlesource.com/45960
Status: ChangeUnderReview
May 18, 2013
#10 sop@google.com
(No comment was entered for this change.)
Status: Submitted
Labels: FixedIn-2.6
Sign in to add a comment

Powered by Google Project Hosting