Stop storing IP of contributors in the logs

[jiru]

I tried to access a local tatoeba installation via IPv6. While going on the login page, the debug showed the following error. The IPv6 network Tatoeba is running on is fd01:2345:6789:abcd::/64.

Warning (512): SQL Error: 1062: Duplicate entry 'fd01:2345:6789:' for key 'ip' [CORE/cake/libs/model/datasources/dbo_source.php, line 526]
Query: INSERT INTO `visitors` (`timestamp`, `ip`) VALUES (1397479495, 'fd01:2345:6789:abcd:XXXX:XXXX:XXXX:XXXX') 

Analysis : The length of the field visitors.ip in the database is too short to hold an IPv6, resulting in truncation while insterting values. The length of the fields holding IPs should be extended to at least 39 characters to fit the maximum size of an IPv6. This include : visitors.ip, contributions.ip and last_contributions.ip

[alanfgh]

According to Google statistics, the number of visitors to Google using IPv6 is still less than 3.5% at the moment, but rising steadily.

[jiru]

Just to clarify: this issue is currently harmless since tatoeba.org does not have IPv6 connectivity. It however has to be solved if we decide to support IPv6 (which I think would be a good thing, but this should be discussed in another ticket), or if someone wants to run its own Tatoeba instance with IPv6 connectivity.

[jiru]

I asked our hosting company Firstheberg about IPv6. They said they expect to provide IPv6 connectivity by the end of the year.

[jiru]

Note: HTTP_X_FORWARDED_FOR may contain a comma-separated list of IPs. In this case, because we use it as-is to fill the field ip, we end up having the same duplicate error.

[halfdan]

What's the status of this? As it stands today tatoeba.org is not reachable via IPv6

✗ host -t AAAA tatoeba.org
tatoeba.org has no AAAA record

Most of German ISPs have switched to native IPv6. I'm happy to help with this.

[jiru]

Hello halfdan, nice to meet you and welcome to the project. Note that I opened this ticket to make Tatoeba work with IPv6, while enabling IPv6 on the machine that currently hosts tatoeba.org is another problem. Since you’re mentioning it, it seems that our host is providing IPv6 on their VPSes while I don’t know about dedicated servers like us. I asked them and I’m waiting for their reply.

Another option is to get an IPv6 connectivity using a tunnel.

In the meantime, you can work on removing the warning I mentioned, making Tatoeba working on a local IPv6 network.

[jiru]

We got IPv6 connectivity from our host and the production server now has IPv6 connectivity. Once this issue is fixed (among with other similar ones), we can enable IPv6 on tatoeba.org.

[halfdan]

Just revisiting this: IP addresses should not be stored in a VARCHAR field anyway. They should be converted with inet_pton and stored in a VARBINARY field (see also http://stackoverflow.com/questions/6964143/storing-ipv6-addresses-in-mysql).

That said, I can't figure out how, if at all, this table is still used? There is a stray controller returning the visitors in the last 5 minutes, but nothing seems to ever write to the table or actually call that Controller/Model.

IP addresses are also stored in the contributions table where what I said above definitely applies.

[trang]

No indeed, the visitors table is no longer used.

[Ppjet6]

Out of curiosity, why do we have ip fields on these tables?

I understand we might need logs of people coming and going, required per the french law, but these we already have in the webserver logs. If for some other reasons we need them, I would argue that a hash is sufficient.