0

I need to load a text file of IP addresses into a MySQL table.

The IP addresses are stored as datatype INT(4) in the table as that is how I created the table. The IP addresses in the text file are in regular IP address format (8.8.8.8). Below is the syntax that I've tried for Loading the data into my table but it throws this error:

Data truncated for column 'ip_address' at row 1

I'm not exactly sure what Data truncated means or why it's throwing this error. For reference I have two Columns in my table: Id, ip_address. Also my txt file of IP addreses is just a list of ip's on a new line. There's like 15,000 lines in that file so I'd really like to get this working. 

LOAD DATA INFILE '/var/lib/mysql-files/default.blacklist'
INTO TABLE blacklist
(ip_address)
SET ip_address = INET_ATON(ip_address);
Michael
  • 37
  • 1
  • 7
  • 1
    since you are using INET_ATON you'd need a 32-bit unsigned integer. INT(4) is not enough to cut it. Change your data type to INT UNSIGNED (do not specify length) and you'll be alright – Javier Larroulet Apr 02 '19 at 03:19
  • Possible duplicate of [What type should I store IP addresses for MySQL?](https://stackoverflow.com/questions/4590192/what-type-should-i-store-ip-addresses-for-mysql) – Javier Larroulet Apr 02 '19 at 03:20
  • That makes sense. I modified the table to VARCHAR(15) as recommended and re ran the Load Data query and it worked perfectly. Thanks so much! – Michael Apr 02 '19 at 03:21
  • You should not use VARCHAR if using INET_ATON. Use INT as suggested instead. You can use VARCHAR and store it in original IPv4 format, but this would require more space. – fifonik Apr 02 '19 at 03:54
  • or `VARBINARY(16)` to include IPv6 like [this answer](https://stackoverflow.com/questions/55347251/cannot-select-where-ip-inet-ptonip/55460076#55460076) – danblack Apr 02 '19 at 03:58

0 Answers0