Bug #1365 inet_aton not working in update query
Submitted: 21 Sep 2003 13:55 Modified: 23 Sep 2003 4:32
Reporter: Ron Offerman Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.15a OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[21 Sep 2003 13:55] Ron Offerman 
Description:
The inet_aton function does not seem to work in update query, 
the IP adress always converts to 127.255.255.255 
although using inet_aton in a select will convert correctly.

How to repeat:
DROP TABLE IF EXISTS `nodesATON`;
CREATE TABLE `nodesATON` (
  `id` int(10) NOT NULL auto_increment,
  `nodeINET` varchar(15) NOT NULL default '',
  `nodeATON` int(14) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM COMMENT='test inet_aton' AUTO_INCREMENT=4 ;

INSERT INTO `nodesATON` (`id`, `nodeINET`, `nodeATON`) VALUES (1, '192.168.1.1', 0);
INSERT INTO `nodesATON` (`id`, `nodeINET`, `nodeATON`) VALUES (2, '192.168.1.2', 0);
INSERT INTO `nodesATON` (`id`, `nodeINET`, `nodeATON`) VALUES (3, '192.168.1.3', 0);

SELECT `nodeINET`, inet_aton(`nodeINET`) from `nodesATON`;

UPDATE `nodesATON` SET `nodeATON` = inet_aton(`nodeINET`);

SELECT `nodeINET`, `nodeATON` from `nodesATON`;
[22 Sep 2003 2:11] Peter Brodersen 
This is not a bug. In your example nodeATON is a signed int, only leaving 31 bits for the positive value. Change it to an unsigned int:

ALTER TABLE `nodesATON` CHANGE `nodeATON` `nodeATON` int unsigned;

UPDATE `nodesATON` SET `nodeATON` = inet_aton(`nodeINET`);

SELECT * FROM `nodesATON`;
+----+-------------+------------+
| id | nodeINET    | nodeATON   |
+----+-------------+------------+
|  1 | 192.168.1.1 | 3232235777 |
|  2 | 192.168.1.2 | 3232235778 |
|  3 | 192.168.1.3 | 3232235779 |
+----+-------------+------------+
3 rows in set (0.01 sec)
[23 Sep 2003 4:32] Alexander Keremidarski 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Thanks to Peter Brodersen who first answered.