SQL error 1406 Data too long for column ip address at row 29
Query:
ALTER TABLE mybb_moderatorlog MODIFY varbinary(16) NOT NULL default
Elsewhere there was a post that showed same problem, but no process for resolution.
http://community.mybb.com/thread-186334-...97365.html
Obviously the upgrade is scripted, but I'd like to know where the script is located.
My plan is to modify the table update script so it goes through completely, then manually update problems later in phpmyadmin.
Is there a better plan?
It does not appear there is a way to note the error, skip and move on. Upgrade stalls completely.
Suggestions? This is being done on off-line server as test of upgrade process before performing on live forum, so I have time to resolve peacefully. I don't have enough hair to pull out over live upgrade problems.
Hm, the upgrade files will be in ./install/resources/, one of the files named like upgrade15.php (not sure which one specifically for this query without looking), you could modify there, however you'd end up with the same error if trying to manually update with phpMyAdmin later if you try to make the same change. I guess you could still edit as a temporary fix to get the upgrade done though but will need to look into why it's errored for you.
In the referenced post, the reply was error 3 should not have occurred, that it was tested without error.
If that is so, are the IPv6 addresses logged truncated when converted?
Do new IPv6 entries logged fit within varbinary(16)?
Looking ahead to database update through script, there are lots of IPv6 addresses in my system, particularly mybb_users, and other places as well.
If I want to preserve the addresses without truncation, does it matter to the running system whether they are longer if needed? Or is it only a matter of keeping the db smaller that truncation occurs?
Looking very briefly through scripts in resources, I see that user ip addresses are converted. Are all ipaddresses in the db supposed to be handled the same way?
I can't say for sure without going over it, we do have users on these community forums with IPv6 addresses though, so as far as I'm aware they should be supported. I don't have access to an IPv6 enabled network at the moment to test though.
For what it's worth, the 1406 error occurred at each field in tables where the IP address was converted. All existing IPv6 addresses were deleted. IPv4 addresses were not modified. At each instance where the error occurred and halted, I clicked OK to clear the error message, then started the upgrade process again. It appears to be a sucessful upgrade, with the one exception of the IP address conversion.
I do not have IPv6 addressing available on my test system, so can't do much more than note the problem.
In searching for answers to questions in this community, I see many self-answered posts "never mind, I fixed it." With no real answer for someone else trying to solve their problem. With that in mind....
I fixed it.
The problem was a difference between the MySQL version running on the live forum vs. what was running on the test server. Live was 5.5.52, Test was 5.7.16 which is not backward compatible with the default install.
The visible clue was in phpmyadmin showing server connection collation.
Test server was utf8mb4_general_ci, while live server was utf8_general_ci
While the database would import and run, with small problems, it was a killer when converting during the upgrade process. I could have limped along but would have had problems later on, in addition to losing legacy IPv6 data.
I was not able to reconfigure 5.7.16 to default utf8_general_ci easily. It was faster to wipe the server and install LAMP from Ubuntu 14.04 LTS.