551995 - Duplicate row in fpAgent table created when NicHelps migrated his account

Status: RESOLVED FIXED

(support.mozilla.org Graveyard :: Chat, defect)

Description

[Matthew Middleton (:zzxc)]

Fastpath created a duplicate row in the fpAgent table after NicHelps logged in and migrated his account, which caused all users to not be able to accept chats.  I fixed the problem in the Openfire administration panel by deleting his account from Fastpath, but we need to figure out why this happened to prevent it in the future.

Comment 1

[Matthew Middleton (:zzxc)]

2010.03.12 10:14:17 [org.jivesoftware.openfire.component.InternalComponentManager$2.error(InternalComponentManager.java:354)]
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry 'NicHelps' for key 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.sql.Statement$$EnhancerByProxool$$ff08ad83.executeUpdate(<generated>)
at org.jivesoftware.xmpp.workgroup.AgentManager.insertAgent(AgentManager.java:292)
at org.jivesoftware.xmpp.workgroup.AgentManager.createAgent(AgentManager.java:148)
at org.jivesoftware.xmpp.workgroup.AgentManager.getAgents(AgentManager.java:204)
at org.jivesoftware.xmpp.workgroup.Workgroup.getAgents(Workgroup.java:980)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.sendWorkgroups(WorkgroupManager.java:793)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.process(WorkgroupManager.java:757)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.processPacket(WorkgroupManager.java:651)
at org.jivesoftware.openfire.component.InternalComponentManager$RoutableComponents.process(InternalComponentManager.java:619)
at org.jivesoftware.openfire.spi.RoutingTableImpl.routePacket(RoutingTableImpl.java:260)
at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:303)
at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:101)
at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:68)
at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:319)
at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler.java:79)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:284)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:176)
at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandler.java:133)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived(AbstractIoFilterChain.java:570)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimpleProtocolDecoderOutput.java:58)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:185)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:239)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:283)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)
at java.lang.Thread.run(Thread.java:595)
2010.03.12 10:14:17 [org.jivesoftware.openfire.component.InternalComponentManager$2.error(InternalComponentManager.java:354)]
java.lang.IllegalArgumentException: Agent could not be created
at org.jivesoftware.xmpp.workgroup.AgentManager.createAgent(AgentManager.java:149)
at org.jivesoftware.xmpp.workgroup.AgentManager.getAgents(AgentManager.java:204)
at org.jivesoftware.xmpp.workgroup.Workgroup.getAgents(Workgroup.java:980)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.sendWorkgroups(WorkgroupManager.java:793)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.process(WorkgroupManager.java:757)
at org.jivesoftware.xmpp.workgroup.WorkgroupManager.processPacket(WorkgroupManager.java:651)
at org.jivesoftware.openfire.component.InternalComponentManager$RoutableComponents.process(InternalComponentManager.java:619)
at org.jivesoftware.openfire.spi.RoutingTableImpl.routePacket(RoutingTableImpl.java:260)
at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:303)
at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:101)
at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:68)
at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:319)
at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler.java:79)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:284)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:176)
at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandler.java:133)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived(AbstractIoFilterChain.java:570)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimpleProtocolDecoderOutput.java:58)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:185)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:239)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:283)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)
at java.lang.Thread.run(Thread.java:595)

Comment 2

[Matthew Middleton (:zzxc)]

Attachment: Convert all livechat_id's to lowercase

These collisions were being caused by livechat_id's that contained uppercase characters, resulting in case collisions after migrating a lowercase username to a livechat_id containing uppercase characters.  (All Openfire usernames are lower case)

This simply converts all livechat_id's to lowercase before processing.  (Case collisions are already forbidden in tiki-register.php and tiki-livechat_migration.php.)

Comment 3

[James Socol [:jsocol, :james]]

Comment on attachment 433364 [details] [diff] [review]
Convert all livechat_id's to lowercase

Looks OK to me.

Comment 4

[Matthew Middleton (:zzxc)]

r64387