After upgrading from Elgg v2.x.x to v3.x.x unserialize error with the config table

Hi everybody,

I have been away for a while but when I checked the blog of elgg, I see tons of activity with releases full of improvements. So excited with all this, I have decided to finally upgrade my elgg site from Elgg 2 to all the way to version 6.

I started with upgrading from 2.3.12 to 3.3.25 (I still have Debian 10 with PHP version PHP version7.3.31-1~deb10u7. So I shall upgrade also Debian before I upgrade to Elgg 4, 5 and 6). Followed the general upgrade instructions as well as specific instructions for upgrading from Elgg 2 to 3.

After upgrading to 3.3.25, I initially did not convert the database encoding to utf8mb4. Hence I did not set the commented out the dbencoding variable setting in settings.php.

I ran the site. It worked fine. Then I started adding the plugins one by one. After adding the account_removal plugin (V3.0), I tried to remove the account from a test account. I have got the white error screen with the message ""...The page isn’t redirecting properly.....". When I checked the Apache error log, I saw that there was a de-serialization error  (unserialize(): Error at offset) while reading from the config table.

Thinking this was because of not having converted encoding to utf8mb4, one by one, I have altered all tables to utf8mb4 via the SQL command:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After doing this for all tables, I have also updated the character set of the database with the SQL command:

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then I went to ...web/elgg-config/settings.php and uncommented the line which sets the db encoding to utf8mb4

Then to be sure (probably not required), I have restarted the Apache2, flushed the caches of the site and tried again. But I got the same error.

Then I temporarily modified ConfigTable.php (was failing at line 160) in the Elgg database engine to print out the config table key and value to Apache error log. When I checked it, I see that it is failing while trying to de-serialize the value of the key 'site_custom_menu_items'. Both the key and the value are both readable and seem OK.

Thanks in advance for any ideas what I did wrong!

Cheers!

  • It sounds like you're encountering a tricky issue with serialized data in your Elgg database after upgrading.

    Understanding the Issue

    • Serialization: Elgg, like many PHP applications, uses serialization to store complex data structures (like arrays and objects) in the database as strings. This allows it to retrieve and use this data later.
    • De-serialization Errors: When Elgg tries to read serialized data and encounters an error ("unserialize(): Error at offset"), it means the serialized string is corrupted or incompatible with the current PHP version. This often happens during upgrades, especially when database encoding changes.

    Possible Causes and Solutions

    1. Incomplete Data Conversion:

    • Even though you converted tables to utf8mb4, there might be remnants of old, incompatible encoding in the serialized data itself.
    • Solution: Try a more thorough data cleanup:
      • Backup your database! This is crucial before any major data manipulation.
      • Use a tool like phpMyAdmin to export your database as SQL.
      • Open the SQL file in a text editor and search for any instances of characters that might indicate encoding issues (e.g., strange symbols, non-standard characters).
      • Manually fix any problematic characters or consider using a dedicated database encoding conversion tool.
      • Import the cleaned SQL file back into your database.

    2. Database Integrity Check:

    • Full Backup: Before making any further changes, create a full backup of your database. This will allow you to restore your site if needed.
    • Data Integrity Check: Use a tool like phpMyAdmin or a similar database management tool to run a full data integrity check. This will help identify any inconsistencies or corrupted data.

    3. Corrupted Database:

    • In rare cases, database corruption can lead to serialization errors.
    • Solution:
      • Backup your database!
      • Use a database repair tool (often available within database management systems like phpMyAdmin) to attempt to repair any corrupted tables.

    Debugging Tips

    • Enable PHP Error Logging: Configure your PHP settings to log detailed error messages. This can provide more specific clues about the serialization error.
    • Inspect the Serialized Data: Use a tool like var_dump() or print_r() to examine the contents of the serialized data in the site_custom_menu_items key. Look for any unusual characters or structures.
  • Note: Please use this tutorial only if you didn't use composer when installing Elgg.

    How to upgrade Elgg 2 to 3

    Upgrade 2.3 > 3.0
    - backup DB
    - backup production
    - backup /mod
    - backup elgg-config/settings.php
    - login as Administrator
    - turn off all plugins
    - turn off all simple caches' options (Dashboard -> Advanced settings)
    - turn on Logging: errors and warning
    - turn on New users can register
    - go to Dashboard and stay on there
    - format your DB, tables and columns to utf8mb4 and InnoDB set 'innodb_large_prefix' to 'ON'
    - change a new .htaccess \ nginx  // remove  fastcgi_param QUERY_STRING    __elgg_uri=$uri&$args;
    - delete old /vendor and files
    - copy the new 3.0 /vendor and files
    - add new changes in elgg-config/settings.php
    - delete old /mod
    - delete in /data folders: /views_simplecache and /system_cache
    - clean opCache
    - increase PHP memory as well (for highload servers)
    - run upgrade.php -> go to admin/upgrades and run Upgrades -> return on Dashboard
    - copy new /mod 
    - activate only bundled plugins
    - reset all plugins settings
    - run upgrade.php \ flush the caches
    - enable and set your 3rd party plugins
    - run upgrade.php \ flush the caches

    If you have plugins that require PHP 7.4, then first update Elgg, then update PHP and install the new plugins.

    ln -s  /var/www/elgg/data/caches/views_simplecache/ cache

    Delete old crontab rules

    #Elgg 3
    crontab -u www-data -e

    * * * * * /usr/bin/php /var/www/demo/www/vendor/bin/elgg-cli cron -q

    NOW
    Upgrade 3.0 > 3.1 > 3.2 > 3.3

    How to upgrade Elgg 3 to 4, 4 to 5

  • Set utf8mb4 database

    my.cnf:

    
    [client]
    default-character-set = utf8mb4
    [mysqld]
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci
    character-set-client-handshake = FALSE
    [mysql]
    default-character-set = utf8mb4

    1. Database: Change Database default collation as utf8mb4.

    2. Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.  ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

    3. Set utf8mb4 in database connection: 

    $database_connection = new mysqli($server, $user, $password, $database_name); 
    
    $database_connection->set_charset('utf8mb4');

    Note: steps 1-3 can be done successfully with phpMyAdmin tool and I recommend it instead of mySQL console.

    4. Check your database:

    mysqlcheck -u root -pPASSWORD --auto-repair --optimize --all-databases
    
  • @Nikolai Shcherbin THANK YOU for your answers. I shall go over them in detail.

    Just a feedback:

    - When I first ran my site (upgrade.php,  login,  viewing a few things etc), I had NOT yet converted the encoding to utf8mb4 neither in data nor in settings.

    Maybe not very likely but this might have corrupted something in DB.

    - When I went to Administration screen, I was surprised to see that there was an upgrade awaiting (among the others) which would convert database to utf8mb4. This was surprising because it was written elsewhere that it is somethings which should be done manually. I checked all release notes but could not see any automatic upgrade. Anyways, being lazy, I took the offer and ran the upgrade. Later when I was investigating the problem, before I manually converted the encoding in DB, when I checked the data in DB via phpMyAdmin, I realized that the conversion was not done for all or some string columns of tables where the collation was not utf8mb4_unicode_ci.

    Maybe not very likely but during this automatic upgrade, I might have corrupted something in DB.

    After manual conversion, I have checked the collation of all string columns in all tables and they are all utf8mb4_unicode_ci.

    - Open the SQL file in a text editor and search for any instances of characters that might indicate encoding issues (e.g., strange symbols, non-standard characters).

    This is a difficult one because I do not know what entails as strange, non-standart characters. For example, would Latin Turkish extensions like ü,ş,ı,ğ etc would qualify as one, then not much I can do about them.

    - I have done all PhpMyAdmin offers as Table Maintenance. Some (repair) would not be applicable de to InnoDB engine I believe.

    - With the exception of disabling simple cache, I have done everything in your list during the upgrade. Afterwards I have flushed the caches so many times...

    cheers.

  • This is one thing I did not do:

    Upgrade 3.0 > 3.1 > 3.2 > 3.3

    I have directly upgraded from 2.3.12 to 3.3.25 with the intention of later upgrading from 3.3.25 to 4.3.10 and so forth. As allowed by upgrade instructions.

    By the way, I have earlier seen your feedback about this here. The problem I am having is with a 250 char string in config table with only 43 rows (unless it is a symptom of a bigger problem). If all fails I might go that way, one minor version at a time.

    Coming from ancient 2.3.12 and in between having to do all that Debian OS upgrades, it is a messy upgrade path I have.

    Therefore I am not doing any of this in live system (even if not a busy one but with only a few users every day). I have cloned everything to a VirtualBox virtual machine. I am doing the dry run in VM. With VM snapshots etc, it is very easy for me to roll back and try again from a problematic upgrade.

    - I shall locate and check the my.cnf file.

    -  Your collating sequence ALTER command seems to be different from mine:

    Table: Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.  ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

    I have converted to utf8mb4_unicode_c and not to utf8mb4_bin. Could it be a issue?

    - I shall check this and find the way to do in phpMyAdmin

    $database_connection = new mysqli($server, $user, $password, $database_name); 
    
    $database_connection->set_charset('utf8mb4');

    Thanks a lot again!

    Cheers.

  • I have directly upgraded from 2.3.12 to 3.3.25

    It's not a problem if everything works for you. As I already wrote in the mentioned reply, we used to update exactly like this according to the guides. But we started to encounter some issues if the DB is very large.

    Through various experiments, we came to the conclusion that upgrading 3.0 > 3.1 > 3.2 > 3.3 works.

    Since Elgg v3.3. there are no such issues anymore, and you can follow the instructions.

    I have converted to utf8mb4_unicode_c and not to utf8mb4_bin. Could it be a issue?

    This should not affect. The commands for the MySQL console are given as an example. I recommend doing everything through phpMyAdmin.

     

  • Thank you Nikolai Shcherbin . The links really helped.

    I realized that it could be related to a length mismatch with a string. And indeed. After all those presumptions I made, the real reason for de-serialization was a mistake I had introduced to database while trying to get the system work. In the elgg_config table, for the 'site_custom_menu_items' parameter, I had got rid 'https' in site URL string making its length incompatible with the real situation....  Now I am one step further by getting fatal with account_removal which seems to complain about passing null for the 5th parameter of elgg/engine/lib/notification.php/notify_user which seems to expect an array. Anyways, for the time being, I'll continue my upgrade without it.

    Thanks a lot for your help woth the deserialization problem!

    cheers.