title: Getting a 'Duplicate entry' error even though I'm using ON DUPLICATE KEY UPDATE
body: <p>I'm currently doing a project where I'm going through thousands of data packets. Now, I log each of those packets IP and MAC address along with some other information. To store all this I use MySQL and my script is written in Node.js. Currently I'm dealing with well over 40k packets per second, so the database traffic is pretty intense. Now, the tricky part is that IP and MAC addresses should be linked together so they only exist once in the table. Therefore I've set both of them to UNIQUE. However, when I query the database I get an error:</p>
  
  <pre><code>Error: ER_DUP_ENTRY: Duplicate entry 'ff:ff:ff:ff:ff:ff' for key 'mac_UNIQUE'
  </code></pre>
  
  <p>And even though I'm using <code>ON DUPLICATE KEY UPDATE</code>, it spits the error.
  My database is set up like this:</p>
  
  <pre><code>delimiter $$
  
  CREATE TABLE `hosts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `ip` varchar(45) NOT NULL DEFAULT '',
    `mac` varchar(45) NOT NULL DEFAULT '',
    `method` varchar(45) DEFAULT NULL,
    `netbiosName` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ip_UNIQUE` (`ip`),
    UNIQUE KEY `mac_UNIQUE` (`mac`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=latin1$$
  </code></pre>
  
  <p>And this is my query in Node.js with the node_mysql library:</p>
  
  <pre><code>mysqlConnection.query('INSERT INTO ' + mysqlTable + ' (ip, mac, method) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE ip=?, mac=?, method=?',
                      [ipAddress, macAddress, method, ipAddress, macAddress, method]);
  </code></pre>
  
  <p>I really can't figure out a way of getting this to work.</p>
  
  <p><strong>UPDATE:</strong> I think I might be better of using the IP address as the UNIQUE key for a number of reasons. The question remains though, is this possible?</p>