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 or lastname fields in the user table contained special characters.
Method 1: If the gateway has only a few users with special characters
Step 1.a: 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.b: 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 : Set the character set to utf8mb4 to support most special characters in first names and last names.
Step 2.a: Check which character set is currently being used.
root@gateway:# mysql
mysql> SHOW VARIABLES LIKE '%char%';
eg: SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
Step 2.b: Edit the MySQL configuration file and set the character set to utf8mb4.
root@gateway:# vim /etc/mysql/mysql.conf.d/mysqld.cnf #for Debian-based servers
root@gateway:# vim /etc/my.cnf #for RHEL-based servers
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
Step 2.c: Restart the MySQL server and try upgrading Ezeelogin again.
root@gateway:# systemctl restart mariadb #for RHEL-based servers
root@gateway:# systemctl restart mysql #for Debian-based servers
Method 3 : If the gateway has many users with special characters
Step 3.a: 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 3.b: Clear the firstname and lastname field for all users.
Step 3.b.i: If the issue is with lastname run below command.
root@gateway:# /usr/local/ezlogin/ez_queryrunner.php "update prefix_users set lastname=''"
Step 3.b.ii: If the issue is with firstname run below command.
root@gateway:# /usr/local/ezlogin/ez_queryrunner.php "update prefix_users set firstname=''"
Step 3.c: Upgrade Ezeelogin to latest version.
Step 3.d: 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 3.e: 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 3.f: 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 3.g: Post-upgrade validation steps:
Step 3.h: Log in to the Ezeelogin GUI and verify that the firstname and lastname fields are populated correctly.
Step 3.i: 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?