Database migration to new server

[dragonwocky]

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:

  NOTICE: PHP message: Creation of dynamic property View__Mixed_Search::$_search_params is deprecated - Line 12 of /var/www/html/jethro/views/view_0_mixed_search.class.php
  127.0.0.1 -  11/Jul/2024:03:37:23 +0000 "GET /jethro/index.php" 200
  NOTICE: PHP message: Cannot modify header information - headers already sent by (output started at /var/www/html/jethro/include/system_controller.class.php:313) - Line 168 of /var/www/html/jethro/include/general.php
  

  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 my conf.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

  FROM caddy:2.8.4-builder AS caddy
  RUN xcaddy build \
    --with github.com/baldinof/caddy-supervisor \
    --with github.com/caddyserver/replace-response
    
  FROM php:8.0.30-fpm
  COPY --from=caddy /usr/bin/caddy /usr/bin/caddy
  
  RUN apt-get update \
    && apt-get install -y curl unzip libcurl4-openssl-dev libfreetype-dev libjpeg62-turbo-dev libpng-dev \
    && docker-php-ext-configure gd --with-freetype=/usr/include/ --with-jpeg=/usr/include/ \
    && docker-php-ext-install gd pdo pdo_mysql mysqli gettext exif
  
  RUN mkdir -p /var/www/html/jethro && cd /var/www/html/jethro \
    && curl -LO https://github.com/tbar0970/jethro-pmm/releases/download/v2.35.1/jethro-2.35.1.zip \
    && unzip jethro-*.zip && chown -R www-data:www-data .
  
  CMD ["caddy", "run", "--config", "/etc/caddy/Caddyfile"]
  

  docker-compose.yaml

  services:
    jethro:
      build: ./jethro
      restart: always
      volumes:
        - ./jethro/files:/var/www/html/jethro/files
        - ./jethro/conf.php:/var/www/html/jethro/conf.php
        - ./jethro/jethro-white.png:/var/www/html/jethro/resources/img/jethro-white.png
        - ./jethro/iphone-icon.png:/var/www/html/jethro/resources/img/iphone-icon.png
        - ./jethro/terms.html:/var/www/html/jethro/terms/index.html
        - ./jethro/Caddyfile:/etc/caddy/Caddyfile
      environment:
        - DB_TYPE=mysql
        - DB_HOST=jethro_db
        - DB_USERNAME=root
        - DB_PASSWORD=realpasswordhere
        - DB_DATABASE=jethro
        - BASE_URL=https://example.com/jethro/
      depends_on: [jethro_db]
    jethro_db:
      image: mysql:8.4.1
      restart: always
      volumes: [jethro_db:/var/lib/mysql]
      environment:
        - MYSQL_ROOT_PASSWORD=realpasswordhere
        - MYSQL_DATABASE=jethro
  volumes:
    jethro_db:
  

  Caddyfile

  {
      supervisor {
    	  php-fpm
      }
  }
  
  root * /var/www/html
  php_fastcgi 127.0.0.1:9000
  file_server
  

Thanks!

[jefft]

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 #754 (comment). 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.

[dragonwocky]

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,'�\��\�\0�JFIF\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.

[jefft]

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.

[dragonwocky]

Alright, it took me a little while to work through this between other things. I've got everything working reliably now, upgrading all encodings to utf8mb4 and handling images/blobs correctly.

Export:

mysqldump -u <user> --password=<pwd> --single-transaction --add-drop-table --routines --opt --hex-blob jethro > jethro.sql
sed -i -e 's/CHARSET=utf8 /CHARSET=utf8mb4 /g' \
       -e 's/CHARSET=utf8mb3/CHARSET=utf8mb4/g' \
       -e 's/COLLATE=utf8_general_ci/COLLATE=utf8mb4_general_ci/g' jethro.sql

Import:

cat jethro.sql | mysql -u <user> --password=<pwd> --max_allowed_packet=16M jethro

For Jethro itself to handle emojis correctly, I had to patch https://github.com/tbar0970/jethro-pmm/blob/master/include/jethrodb.php#L53 to connect with charset=utf8mb4 instead of charset=utf8. Can open a PR for that if needed, seems to be stable, but not sure if that might break existing installations.

[jefft]

Thanks for recording the fix here.

Yes, the default charset should definitely be utf8mb4, but changing it is going to break some instances. We should at least have some advice for fixing those instances before changing to utf8mb4.