adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

[history] IPv6 cannot be stored #840

New Issue
Closed
opened 2026-10-05 00:20:05 +02:00 by adam · 2 comments
adam commented 2026-10-05 00:20:05 +02:00
Owner
Copy link
Copy source

The current definition of IP field in history table is
IP VARCHAR(15)

(from upgrade_1.6.2.php if i am correct)

which is fine for IPv4 but IPv6 are 39 characters longs and cannot be stored

The current definition of IP field in history table is `IP` VARCHAR(15) (from upgrade_1.6.2.php if i am correct) which is fine for IPv4 but IPv6 are 39 characters longs and cannot be stored <!-- github-sync-id:376374003 -->
adam closed this issue 2026-10-05 00:20:05 +02:00
adam commented 2026-10-05 00:20:06 +02:00
Author
Owner
Copy link
Copy source

Extending the size of the IP column probably fixes the problem.
The following query changes the size of the IP field to 39 characters:

ALTER TABLE `piwigo_history`
	CHANGE COLUMN `IP` `IP` VARCHAR(39) NOT NULL DEFAULT '' AFTER `user_id`;

However for more efficient storage it may be better to change the type of the column to VARBINARY(16), so the storage requirement is at most 16 bytes (instead of 39 bytes with the previous solution).
Then use INET6_ATON() when storing an IP in the table, and use INET6_NTOA() when retrieving an IP from the table.

Note: INET6_ATON and INET6_NTOA functions were introduced in MySQL 5.6
What is the lowest MySQL version supported by Piwigo?

More information:
https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton
https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa

Extending the size of the `IP` column probably fixes the problem. The following query changes the size of the `IP` field to 39 characters: ``` ALTER TABLE `piwigo_history` CHANGE COLUMN `IP` `IP` VARCHAR(39) NOT NULL DEFAULT '' AFTER `user_id`; ``` However for more efficient storage it may be better to change the type of the column to `VARBINARY(16)`, so the storage requirement is at most **16** bytes (instead of **39** bytes with the previous solution). Then use `INET6_ATON()` when storing an IP in the table, and use `INET6_NTOA()` when retrieving an IP from the table. Note: `INET6_ATON` and `INET6_NTOA` functions were introduced in MySQL 5.6 What is the lowest MySQL version supported by Piwigo? More information: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa <!-- github-comment-id:468921404 -->
adam commented 2026-10-05 00:20:07 +02:00
Author
Owner
Copy link
Copy source

In old versions of MySQL/MariaDB, when inserting "abcd" into a varchar(3) field, it would silently insert "abc" (auto-truncate). In recent version of MySQL/MariaDB, you get an error. I've experienced it a lot lately. Not with IPv6 in piwigo_history but that is the same problem.

Maybe CHAR(39) would be better than VARCHAR(39). To be decided.

In old versions of MySQL/MariaDB, when inserting "abcd" into a varchar(3) field, it would silently insert "abc" (auto-truncate). In recent version of MySQL/MariaDB, you get an error. I've experienced it a lot lately. Not with IPv6 in `piwigo_history` but that is the same problem. Maybe `CHAR(39)` would be better than `VARCHAR(39)`. To be decided. <!-- github-comment-id:2205958719 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#840