MYSQL ON DUPLICATE KEY UPDATE failing for unique index

[jstfsklh211]

my insert statement seems to be ignoring my unique index and adding duplicates
what'd i mess up?

thanks in advance

Table

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `address_street1` varchar(100) NOT NULL,
  `address_street2` varchar(100) DEFAULT NULL,
  `address_street3` varchar(100) DEFAULT NULL,
  `address_city` varchar(45) NOT NULL,
  `address_state` char(2) DEFAULT NULL,
  `address_zip` varchar(10) NOT NULL,
  `address_country` varchar(100) NOT NULL,
  `address_latitude` double DEFAULT NULL,
  `address_longitude` double DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  UNIQUE KEY `a_dupe` (`address_street1`,`address_street2`,`address_street3`,`address_city`,`address_state`,`address_zip`,`address_country`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;

insert query

set @address_street1 = '1 street name';
set @address_street2 = null;
set @address_street3 = null;
set @address_city = 'some city';
set @address_state = 'SS';
set @address_zip = '00000';
set @address_country = 'US';
INSERT INTO address (address_street1, address_street2, address_street3, address_city, address_state, address_zip, address_country)
VALUES (@address_street1, @address_street2, @address_street3, @address_city, @address_state, @address_zip, @address_country)
ON DUPLICATE KEY UPDATE address_id = LAST_INSERT_ID(address_id);

[cereal]

It happens because you're using NULL values, for the series it's not a bug, it's a feature:

• https://bugs.mysql.com/bug.php?id=8173

[jstfsklh211]

yep that would do it thanks

[Teddy112]

cereal is right ...it is happening due to null values which are being assigned.