Bug #39386 INET_ATON() does not work properly inside a trigger
Submitted: 11 Sep 2008 9:21 Modified: 12 Sep 2008 5:59
Reporter: Ernie Souhrada Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.67, 5.1 OS:Linux (CentOS 5.2, FreeBSD)
Assigned to: CPU Architecture:Any
Tags: inet_aton, triggers
Triage: Triaged: D2 (Serious)

[11 Sep 2008 9:21] Ernie Souhrada
Description:
When using the INET_ATON() function inside a trigger (before_insert and before_update have both been tested) to convert the dotted-quad representation of an IP address into an unsigned integer, the value returned by the function is either a truncated string or a NULL rather than the proper value.  Here's what I would expect to see inserted into the table as a result of the trigger:

mysql> select inet_aton('1.2.3.4');
+----------------------+
| inet_aton('1.2.3.4') |
+----------------------+
|             16909060 | 
+----------------------+
1 row in set (0.00 sec)

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 | 
+--------------------------+
1 row in set (0.00 sec)

And here's what actually happens:

mysql> insert into test_it('1.2.3.4');
mysql> select ip_address from test_it;
+------------+
| ip_address |
+------------+
|         12 | 
+------------+
1 row in set (0.00 sec)

mysql> insert into test_it('192.168.1.1');
ERROR 1048 (23000): Column 'ip_address' cannot be null

How to repeat:
mysql> create table test_it ( ip_address INT UNSIGNED NOT NULL );
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter |
mysql> create trigger test_bi BEFORE INSERT on test_it
    -> FOR EACH ROW BEGIN
    ->   SET NEW.ip_address = INET_ATON(NEW.ip_address);
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)
     
mysql> insert into test_it (ip_address) values ("192.168.1.1");
ERROR 1048 (23000): Column 'ip_address' cannot be null

mysql> insert into test_it (ip_address) values ("1.2.3.4");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'ip_address' at row 1 | 
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select ip_address from test_it;
+------------+
| ip_address |
+------------+
|         12 | 
+------------+
1 row in set (0.00 sec)

---

Engine = InnoDB, character encoding is utf8

Suggested fix:
I tried a couple of variations on the trigger to see if I could come up with a workaround (use a temporary variable and either do a select into it or just set it directly) but neither of those worked, either, so I'm not sure what the fix is here.  INET_ATON() works just fine when it's called as a stand-alone function.
[11 Sep 2008 10:32] Susanne Ebrecht
Verfied by using 5.1 bzr tree.

This has nothing to do with the storage engine.

Here my test:

create table test_it ( ip_address INT UNSIGNED NOT NULL );
delimiter |
create trigger test_bi BEFORE INSERT on test_it FOR EACH ROW BEGIN SET NEW.ip_address = INET_ATON(NEW.ip_address); END;|
delimiter ;

select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 | 
+--------------------------+

insert into test_it (ip_address) values ('192.168.1.1');
ERROR 1048 (23000): Column 'ip_address' cannot be null

insert into test_it(ip_address) values('1.2.3.4');
Query OK, 1 row affected, 1 warning (0.01 sec)

show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'ip_address' at row 1 | 
+---------+------+-------------------------------------------------+

select * from test_it;
+------------+
| ip_address |
+------------+
|         12 | 
+------------+

select inet_aton('1.2.3.4');
+----------------------+
| inet_aton('1.2.3.4') |
+----------------------+
|             16909060 | 
+----------------------+