CODEWITHSUNDEEP
mysqlsqlnode.js
Fredefl
Fredefl

Reputation: 1381

Getting a 'Duplicate entry' error even though I'm using ON DUPLICATE KEY UPDATE

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:

Error: ER_DUP_ENTRY: Duplicate entry 'ff:ff:ff:ff:ff:ff' for key 'mac_UNIQUE'

And even though I'm using ON DUPLICATE KEY UPDATE, it spits the error. My database is set up like this:

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$$

And this is my query in Node.js with the node_mysql library:

mysqlConnection.query('INSERT INTO ' + mysqlTable + ' (ip, mac, method) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE ip=?, mac=?, method=?',
                    [ipAddress, macAddress, method, ipAddress, macAddress, method]);

I really can't figure out a way of getting this to work.

UPDATE: 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?

Upvotes: 2

Views: 7055

Answers (2)

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

Based on this article http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

The insert tries to replace the exsisting IP with your current IP but then fails to put the MAC as you already have it in your DB. An example is you have a record with IP 127.0.0.1. And you have a record with MAC address 'ff:ff:ff:ff:ff:ff' You insert a new record with this IP and MAC address 'ff:ff:ff:ff:ff:ff'. MySQL updates the record with the IP to the same IP - 127.0.0.1, but fails to finish the update as this MAC address already exsist in the DB.

Hope I managed to explain it well :)

If IP address to MAC address relationship is 1:1 they should use a composite key and then you can do ON DUPLICATE KEY UPDATE.

Upvotes: 7

jagra
jagra

Reputation: 553

Not sure if I understood your problem correctly:

If you want each pair of ip/mac to be unique (it makes sense) you must have a composed unique key and not two unique keys, as you have.

In a DHCP env, each MAC addr can/will have multiple IPs over time.

Upvotes: 2

Related Questions