0

so im using ON DUPLICATE KEY UPDATE when logging in - i am using it at every login because im getting the data from an external page and the users can only update their settings there. thats because the lack of an api by the software on that page.

actually im using this query to update their settings. if the account isnt listed in my database, its getting created with their credentials on success.

my problem is, that if the user isnt listed in my database and they are inserted into it, their id (auto increament) is not 1, 2, 3, 4 and so on. its starting at 32, then it goes to 54, after that 185 and so on. the ID raises so fast. is this an issue in my query or is this actually a bug?

http://puu.sh/8iXv7.png

heres my query

mysqli_query($database, "   INSERT INTO `benutzer` (`id`, `login`, `vorname`, `nachname`, `gruppen`, `email`, `adresse`, `telefon`, `telefon2`, `telefon3`, `bemerkungen`)
            VALUES (NULL, '".$userdata[0]."', '".$userdata[1]."', '".$userdata[2]."', '".implode(";", $gruppen)."', '".$userdata[3]."', '".$userdata[4]."', '".$userdata[5]."', '".$userdata[6]."', '".$userdata[7]."', '".$userdata[8]."')
            ON DUPLICATE KEY UPDATE `vorname` = '".$userdata[1]."', `nachname` = '".$userdata[2]."', `gruppen` = '".implode(";", $gruppen)."', `email` = '".$userdata[3]."', `adresse` = '".$userdata[4]."', `telefon` = '".$userdata[5]."', `telefon2` = '".$userdata[6]."', `telefon3` = '".$userdata[7]."', `bemerkungen` = '".$userdata[8]."'") or die(mysql_error());

aand this is the structure of the table

CREATE TABLE IF NOT EXISTS `benutzer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(32) NOT NULL,
`vorname` text NOT NULL,
`nachname` text NOT NULL,
`gruppen` text NOT NULL,
`email` text NOT NULL,
`adresse` text NOT NULL,
`telefon` text NOT NULL,
`telefon2` text NOT NULL,
`telefon3` text NOT NULL,
`bemerkungen` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `login` (`login`),
KEY `login_2` (`login`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

thanks in advance

Littlericket
  • 7
  • 3
  • Do you observe the same behaviour if you don't use ON DUPLICATE KEY UPDATE? Might be useful to know whether you've definitely narrowed it down to that feature. – Hammerite Apr 22 '14 at 15:02
  • 1
    Also, can you issue `SHOW VARIABLES LIKE 'auto_increment_increment'` and check that it reports that the value is 1 – Hammerite Apr 22 '14 at 15:05
  • @Hammerite, no, if i first check if theres a login with that username, and then doing a single query if not, it works perfect. so when im using two queries it works. "auto_increment_increment" is 1 – Littlericket Apr 22 '14 at 15:09
  • You have two keys on `login` column. You could remove login_2 index. – Marcus Adams Apr 22 '14 at 15:10
  • possible duplicate of [Too many auto increments with ON DUPLICATE KEY UPDATE](http://stackoverflow.com/questions/9189102/too-many-auto-increments-with-on-duplicate-key-update) – Marcus Adams Apr 22 '14 at 15:21

1 Answers1

-1

It's expected and documented behavior. If you don't like it - then don't use the tool on the wrong purpose.

However, I wouldn't call it a problem at all. Id is an abstract identifier and lasts up to four billion, which ought to be enough for everyone

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345