Open dragonwocky opened 3 months ago
Hi,
You might like to compare person_photo
table contents before and after, to see if the bytes were corrupted:
select personid, md5(photodata) from person_photo order by personid;
Also, plain mysqldump
won't capture the getCurrentUserID
function (see show create function getCurrentUserID;
). To back up everything, I use:
mysqldump --single-transaction --add-drop-table --routines --events --opt jethro > jethro.sql
You should be able to just pipe that SQL to mysql
on the new server. If not, something's broken. If you can paste the mysql errors here we can help.
I had a dreadful time migrating Jethro once, and it was due to encodings. IIRC, old MySQL 5.x defaults to latin1
encoding but still silently accepts utf8 bytes - and things work until you attempt to restore a SQL dump into a newer version of MySQL, which rejects the broken characters. There are some details at https://github.com/tbar0970/jethro-pmm/pull/754#issuecomment-1505244718. A good way to catch encoding problems is to add a 😊 into a text field in your old Jethro, and it ensure it renders in your new Jethro.
If you're sure it wasn't the SQL dump/restore that broke things, then I'd look for error logs relating to the broken image. I don't know about Caddy, but I'd expect to find them in Apache's error.log
.
I've not been brave enough to try Jethro with PHP versions above 7.4. I'd say stick to what worked on the old instance.
Thanks @jefft
You might like to compare person_photo table contents before and after, to see if the bytes were corrupted:
Yep, looks like they were.
mysqldump --single-transaction --add-drop-table --routines --events --opt jethro > jethro.sql
Tried this - took the dump, did a find-and-replace of `jethro`@`localhost`
to`root`@`%`
and added USE `jethro`;
to the top. When piping it into the database I get:
ERROR 1064 (42000) at line 991: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''�\��\�\0JFIF\0\0`\0`\0\0��\0;CREATOR: gd-jpeg v1.0 (using IJG J' at line 1
Context:
�'); -- end of line 990
INSERT INTO `person_photo` VALUES (361,'�\��\�\0JFIF\0\0`\0`\0\0��\0;CREATOR: gd-jpeg v1.0 (using IJG JPEG v62), quality = 90\n�\�\0C\ -- start of line 991
A good way to catch encoding problems is to add a 😊 into a text field in your old Jethro, and it ensure it renders in your new Jethro.
Adding a 😊 to a text field on the old Jethro (MariaDB 10.3.39 with PHP 8.0.30) gives me this:
2024/07/11 17:29:56 [error] 27966#27966: *43129 FastCGI sent in stderr: "PHP message: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\x8A a...' for column `jethro`.`_abstract_note`.`details` at row 1 - Line 240 of /var/www/html/jethro/include/db_object.class.php" while reading response header from upstream, client: ..., server: ..., request: "POST /jethro/?view=_add_note_to_person&personid=133 HTTP/1.1", upstream: "fastcgi://unix:/run/php/php8.0-fpm.sock:", host: "...", referrer: ".../jethro/?view=_add_note_to_person&personid=133"
Adding a 😊 to a text field on the new Jethro (freshly setup, without an import) gives me this:
NOTICE: PHP message: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x8A' for column 'first_name' at row 1 - Line 419 of /var/www/html/jethro/include/db_object.class.php
Seems like there may be something wrong with the encodings on both servers...
Edit: same error on the new server after downgrading to PHP 7.4 and trying again.
So you're exporting SQL from MariaDB 10.3.39 and importing into MySQL (not mariadb) 8.4.1?
I wonder if person_photo
rows are being truncated due to MySQL's max_allowed_packet
being too small, per serverfault. You could check with show variables like 'max_allowed_packet';
- mine is 16Mb. Also try the suggested --hex-blob
option.
If it's not truncation, I'd try - very carefully - restoring the SQL into another database (e.g. jethro2
) in the old MariaDB. That will show whether the SQL is corrupt everywhere, or only in the opinion of MySQL 8.4.1.
You've also got encoding problems, but that shouldn't be breaking person_photo
, which is just a mediumblob
without any encoding. But if you want to dive into encoding problems later, this will show your database-level default encoding:
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA where SCHEMA_NAME=DATABASE();
and this will show encodings of individual tables:
SELECT T.table_name, CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = DATABASE();
Ideally they should all be utf8mb4
but that's unlikely - probably, if emojis don't work - it's latin1
or utf8
.
Hi there,
I'm in the process of moving our Jethro install onto a new server and run into a couple of challenges:
With PHP 8.3.9, I get
"An error occurred. Please contact your system administrator for help."
and this in the logs whenever I perform a search:After downgrading to PHP 8.0.30, search works fine again. I can live with that, but figured it might be a bug worth reporting.
Profile images don't seem to have come across along with the rest of the data. When heading to any profile that has a picture set on the old server, it fails to load on the new server: I can confirm that there's data in the
person_photo
table, and if I upload a new image as the profile it will display. This I'm stumped on, and would appreciate some help with.Potentially relevant infomation:
I have
define('SQL_MODE', 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION');
in myconf.php
.I did the data import by doing a
mysqldump
on both installs, then combining the table initialisations from the new server with the insert statements from the old server and then running that on the new server. I didn't have any success running the dump from the old server directly/unmodified on the new server.The old server is running on a Raspberry Pi. The new server is running in a container with the configuration below. Both are using Jethro 2.35.1.
Dockerfile
docker-compose.yaml
Caddyfile
Thanks!