ERROR db users: Upgrade failed
How to fix "ERROR db users - Incorrect string value" while upgrading Ezeelogin?
Overview: This article describes how to address the "ERROR db users: Incorrect string value" error encountered during an Ezeelogin upgrade.
The error was triggered because the firstname and lastname fields in the user table contained special characters.
Method 1: If the gateway has only a few users with special characters
Step 1: Login to the Ezeelogin GUI, edit the user, and update the firstname and lastname to an ASCII-friendly spelling or clear the fields.
Step 2: After updating the user with special characters, proceed with the upgrade. Once the upgrade is completed, revert the username back to its original spelling with special characters.
Method 2 : If the gateway has many users with special characters
Step 1: Before the upgrade, take a full mysqldump of the database and a dump of the user table.
root@gateway:# mysqldump -u root -p $(awk '/^db_name/ {print $2}' /usr/local/etc/ezlogin/ez.conf) > $(awk '/^db_name/ {print $2}' /usr/local/etc/ezlogin/ez.conf).sql.old
root@gateway:# mysqldump -u root -p $(awk '/^db_name/ {print $2}' /usr/local/etc/ezlogin/ez.conf) $(awk '/^db_prefix/ {print $2}' /usr/local/etc/ezlogin/ez.conf)users > $(awk '/^db_prefix/ {print $2}' /usr/local/etc/ezlogin/ez.conf)users.sql.old
Step 2: Clear the firstname and lastname field for all users.
- Log in to your MySQL console and clear the firstname and lastname field values on table "users" for all users.
Note: Replace " dbprefix_" with the value of dbprefix_ in /usr/local/etc/ezlogin/ez.conf of your Ezeelogin jump server.
Step 3: Upgrade Ezeelogin to latest version.
Step 4: Take a full mysqldump of the current database.
root@gateway:# mysqldump -u root -p $(awk '/^db_name/ {print $2}' /usr/local/etc/ezlogin/ez.conf) > $(awk '/^db_name/ {print $2}' /usr/local/etc/ezlogin/ez.conf).sql.new
Step 5: Restore users table with new database prefix. Make sure to take a copy of old database dump before proceeding with the sed command.
root@gateway:# cp prefix_users.sql.old prefix_users.sql.old.backup
eg: cp vuunl_users.sql.old vuunl_users.sql.old.backup
root@gateway:# sed -i 's/old_dbprefix/new_dbprefix/g' prefix_users.sql.old.backup
eg: sed -i 's/vuunl_/ixvq_/g' vuunl_users.sql.old.backup
Step 6: Restore updated user table to current Ezeelogin database.
root@gateway:# mysql -u root -p db_name < prefix_users.sql.old.backup
eg: mysql -u root -p ezlogin_ugetdm < vuunl_users.sql.old.backup
Step 7: Post-upgrade validation steps:
Step 7.a: Log in to the Ezeelogin GUI and verify that the firstname and lastname fields are populated correctly.
Step 7.b: Log in to the backend and SSH into a few random servers to confirm that authentication and access are working as expected after the upgrade.
Related Articles:
Upgrade Ezeelogin Jump server to the latest version
How to install and switch different versions of PHP in Ubuntu?
Upgrade PHP from 7.x to 8.x in RHEL8