Unexpected behaviour of the query

Question

This particular query is supposed to enter the user into the database.

This query does not always insert the values of the firstname and the lastname fields along with others. firstname and lastname are empty for a few insertions and for the others its working as expected.

INSERT INTO `users` (mobile, passwordHash, firstname, lastname, ent_id, email )
VALUES ('913800341127', '678a1491514b7f1006d605e9161946b1', 'nat', 'sam', '108', NULL)
ON DUPLICATE KEY UPDATE `firstname` = VALUES(firstname),`lastname` = VALUES(lastname)

Related Info:

CREATE TABLE `users` (
 `id` int(11) NOT NULL auto_increment,
 `tag` varchar(5) NOT NULL default 'ind',
 `username` varchar(50) default NULL,
 `firstname` varchar(100) default NULL,
 `lastname` varchar(100) default NULL,
 `passwordhash` varchar(255) NOT NULL,
 `secretq` varchar(255) default NULL,
 `secreta` varchar(100) default NULL,
 `email` varchar(50) default NULL,
 `mobile` varchar(13) default NULL,
 `last_login` datetime default NULL,
 `ent_id` bigint(20) NOT NULL default '1',
 `is_inactive` tinyint(1) NOT NULL COMMENT 'Whether the user is active or not',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `mobile_2` (`mobile`,`ent_id`),
 UNIQUE KEY `email_2` (`email`,`ent_id`),
 KEY `username` (`username`),
 KEY `ent_id` (`ent_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

My bet would be that this happens when the user name contains non-ASCII characters, which are coming in some encoding other than latin1 (like UTF-8) and break the string altogether. You'll need to add more info about where your data comes from. — Pekka
What can you tell us about the cases where it fails? Does the same data always fail in the same place? Does it fail under high load? Any errors or warnings in the logs? — Tim Martin
Can you answer your own question in a way that would help others? If you do, you can select yours as the correct answer. It may seem strange, but it is preferred to deleting in situations like this. — user1228

diEcho diEcho · Accepted Answer · 2011-04-06T13:28:31

1

votes

syntax error it should be :

ON DUPLICATE KEY UPDATE firstname = 'nat',lastname = 'sam'

Unexpected behaviour of the query

1 Answers