| 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: | |
| 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 | ||
[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 |
+----------------------+
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.