I have a PHP-based process (running PHP 8.1.2 in this case) that regularly queries the primary router in our network to obtain a list of current network devices, and stores them into a table for later use in other processes. (It is also useful for those instances where the IP address of some host has not made it to DNS for whatever reason).

One of the attributes stored in this table, is the hostname as returned by the router. In (only) one instance, this hostname is apparently a value outside the range of the UTF8MB4 column character set allocated for that column, and MySQL throws an error when an UPDATE comes along for that device:

General error: 1366 Incorrect string value: 'xE5x01x10pxB4xAF...' for column 'hostname' at row 1"

In an effort to identify the proper character set, I added

$lov->writeLogLine( ['Encoding for hostname is %s', mb_detect_encoding($data['hostname']) ] ) ;

to the process when the SQL error is detected, and the result that comes back is ‘UTF-8’

I know there are flavors of UTF-8, and I don’t know how to determine what precise type this column should be (router documentation doesn’t identify) (and we won’t [yet] get into why the router is returning nonsensical hostnames – that is outside my support realm). Is there a magic value or magic method I might use to satisfy both PHP and MySQL – make them play nicely together?