Problem between frontend and database

[RicOkteo]

Hello, I'm trying to migrate a zabbix 3.0 to a 6.0.
I'll tell you what I'm trying to do:

I installed a rocky linux 8 properly, installed a MariaDB 10.6.7 as well as the zabbix 6.0 packages.
An installation from scratch.
I then recover my old database which was in UTF8.
I modify my zabbix_server.conf and zabbix_conf.php files
Then I start my zabbix-server to upgrade the database

I check that the update is going correctly with this command :
cat /var/log/zabbix/zabbix_server.log | grep database

Once the database is updated, I turn off my zabbix server in order to switch my tables to utf8mb4 :

mysql -u'zabbix' -p'passwordDB' 'zabbix'
alter database zabbix character set utf8mb4 collate utf8mb4_bin;
SELECT @@character_set_database, @@collation_database;
exit
mysql -u'zabbix' -p'passwordDB' 'zabbix' < /utf8mb4_convert.sql
mysql -u'zabbix' -p'passwordDB' 'zabbix'
SET @ZABBIX_DATABASE = 'zabbix';
set innodb_strict_mode = OFF;
CALL zbx_convert_utf8();
set innodb_strict_mode = ON;
drop procedure zbx_convert_utf8;
exit

I then do the history tables :

mysql -u'zabbix' -p'passwordDB' zabbix -e "show create table history;"
wget https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/database/mysql/double.sql
mysql -u'zabbix' -p'passwordDB' zabbix < double.sql
mysql -u'zabbix' -p'passwordDB' zabbix -e "show create table history;"

Now that I have finished modifying everything etc.. I start my zabbix server.


When I try to log in with the Admin account, it shows me this error message:
Incorrect username or password or the account is temporarily blocked.

After checking, in the database logs, I see my query pass which does a SELECT on the Admin user (normal thing)
When I do the select, it doesn't retrieve anything. I understand that there is a problem with the database.
If I modify the variable username of Admin" in "aaaa" for example then, modify it again in "Admin", my select command works (I do not understand why because it is exactly the same name) and I manage to login to frontend.

Does anyone have a solution to this problem or has encountered it before?

[tim.mooney]

I'm not familiar with 'utf8mb4_convert.sql', is that something the Zabbix developers have provided to switch to true UTF-8?

At Zabbix 5.2, the encryption for internal passwords changed from MD5 to bcrypt, see: https://blog.zabbix.com/keep-all-sec...-secure/13076/

If you examine the contents of the users.passwd field, does it appear to still be encrypted in MD5?

[RicOkteo]

I'm not familiar with 'utf8mb4_convert.sql', is that something the Zabbix developers have provided to switch to true UTF-8?

At Zabbix 5.2, the encryption for internal passwords changed from MD5 to bcrypt, see: https://blog.zabbix.com/keep-all-sec...-secure/13076/

If you examine the contents of the users.passwd field, does it appear to still be encrypted in MD5?

Hello,

UTF8 no longer exists, at least it is no longer compatible
My database is basically in UTF8 and I converted it to UTF8mb4 using the script provided by zabbix

https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_charset_coll

So, indeed, my passwords are always encrypted in MD5, but when I connect with the admin account for example (after seeing manipulate the username field to repair the connection), my password changes from md5 to bcrypt
I therefore assume that still having passwords in MD5 format is not binding.

[RicOkteo]

In the article you sent me, it says:

• If Zabbix is upgraded, password rehashes automatically to use bcrypt on password change or on the first login.

[eDonkey]

I'm currently encountering a similar problem.

After 2 days of trying to fix it, i've now created a completely new VM and installed everything from scratch.
I've exported and copied the hosts and templates from the Zabbix Server 3.0 to the new VM with Zabbix 6.0 and tried to import them; this doesn't work either though.

I'm currently stuck with the following error on the template import:

• File is too big, max upload size is 2097152 bytes.

even though, the file size is around 4'000 KB.

[tim.mooney]

In the article you sent me, it says:

• If Zabbix is upgraded, password rehashes automatically to use bcrypt on password change or on the first login.

That's correct, that should happen. I was just wondering if there might be some reason that it wasn't happening, perhaps related to some problem with the conversion script you were running.

Since you've confirmed that the password field has been converted from MD5 to the newer bcrypt format for 'Admin', it appears that the conversion process is working, and the problem you're having is something else.

[RicOkteo]

I'm currently encountering a similar problem.

After 2 days of trying to fix it, i've now created a completely new VM and installed everything from scratch.
I've exported and copied the hosts and templates from the Zabbix Server 3.0 to the new VM with Zabbix 6.0 and tried to import them; this doesn't work either though.

I'm currently stuck with the following error on the template import:

• File is too big, max upload size is 2097152 bytes.

even though, the file size is around 4'000 KB.

If you are transferring a database, you must do this before exporting:
mysql -u'zabbix' -p'password' 'zabbix' -e "set global innodb_strict_mode='OFF';"
import database
Once the import is finished
mysql -u'zabbix' -p'password' 'zabbix' -e "set global innodb_strict_mode='ON';"

On the other hand if you import / export data such as hosts, templates between the two zabbix..
you will have to be patient and recover the elements very little by little because it screams very quickly at files that are too large

[RicOkteo]

That's correct, that should happen. I was just wondering if there might be some reason that it wasn't happening, perhaps related to some problem with the conversion script you were running.

Since you've confirmed that the password field has been converted from MD5 to the newer bcrypt format for 'Admin', it appears that the conversion process is working, and the problem you're having is something else.

Indeed,
When I use the SQL query that the frontend does to log in with the admin account, I see the query fine
But this one does not get any value, hence the fact that it refuses me the connection
If I run the same query, I get the same thing, no value

By modifying the username value from "Admin" to "abc" for example and then putting back "Admin" afterwards, if i run sql command i get values this time ok.. and connection works fine on frontend.
So I understand that there is indeed a problem with the "username" field but I don't understand. I see the value which is equal to "admin" by modifying it with anything and then putting it back to "admin", why will it manage to read it this time?

Anyway, I do indeed have a problem with the conversion of the database, but I can't really say when, since to connect to the frontend, my database must be migrated to utf8mb4..

[RicOkteo]

I'm currently encountering a similar problem.

After 2 days of trying to fix it, i've now created a completely new VM and installed everything from scratch.
I've exported and copied the hosts and templates from the Zabbix Server 3.0 to the new VM with Zabbix 6.0 and tried to import them; this doesn't work either though.

I'm currently stuck with the following error on the template import:

• File is too big, max upload size is 2097152 bytes.

even though, the file size is around 4'000 KB.

for information, 2097152 byte is equivalent to 2048 kB
hence the fact of going little by little when recovering the different elements

[tim.mooney]

By modifying the username value from "Admin" to "abc" for example and then putting back "Admin" afterwards, if i run sql command i get values this time ok.. and connection works fine on frontend.
So I understand that there is indeed a problem with the "username" field but I don't understand. I see the value which is equal to "admin" by modifying it with anything and then putting it back to "admin", why will it manage to read it this time?

Anyway, I do indeed have a problem with the conversion of the database, but I can't really say when, since to connect to the frontend, my database must be migrated to utf8mb4..

My recollection was that the conversion from the MySQL/MariaDB "utf8" 3-byte BMP subset to true UTF-8 didn't need to happen at 6.0 and could be put off for some future version, but I see now that the Zabbix developers are pretty strongly recommending it happen at 6.0.x: https://www.zabbix.com/documentation...b_charset_coll

Regarding your login issue, are you actually using 'Admin' as the login name? Remember that once you change the collation so that it ends in _bin, that collates every codepoint separately, so it works somewhat like one of the "cs" (case sensitive) collations. So whether you're using utf8_bin or utf8mb4_bin as the collation for the username column in that table, the matching is now (effectively) case sensitive. You need to use 'Admin' as the login name, not 'admin'.

Also, the same case sensitivity now applies to the password field too, if the collation for that is <something>_bin.

[RicOkteo]

My recollection was that the conversion from the MySQL/MariaDB "utf8" 3-byte BMP subset to true UTF-8 didn't need to happen at 6.0 and could be put off for some future version, but I see now that the Zabbix developers are pretty strongly recommending it happen at 6.0.x: https://www.zabbix.com/documentation...b_charset_coll

Regarding your login issue, are you actually using 'Admin' as the login name? Remember that once you change the collation so that it ends in _bin, that collates every codepoint separately, so it works somewhat like one of the "cs" (case sensitive) collations. So whether you're using utf8_bin or utf8mb4_bin as the collation for the username column in that table, the matching is now (effectively) case sensitive. You need to use 'Admin' as the login name, not 'admin'.

Also, the same case sensitivity now applies to the password field too, if the collation for that is <something>_bin.

I just checked, my old database located on my zabbix 3.0 is in utf8 / utf8_general_ci
when importing it to my MariaDB 10.6.7, it turns into utf8mb3 / utf8mn3_general_ci (which is normal because utf8 has disappeared in favor of utf8mb3)
And then I do my table conversion from utf8mb3/utf8mb3_general_ci to utf8mb4/utf8mb4_bin

Do you think that my problems come from the fact that my old database has a collation in utf8_general_ci and not utf8_bin?

I will try to deploy a copy of this off-network server, in order to move my db from general_ci to _bin, recover the clean dump in utf8_bin, and retest my migration

[tim.mooney]

Do you think that my problems come from the fact that my old database has a collation in utf8_general_ci and not utf8_bin?

The "_ci" in the utf8_general_ci collation means "case insensitive", so "admin", "ADMIN", "Admin", and "AdMiN" would all be treated the same. The actual username in the database probably was "Admin", though.

Now that the collation has switched to _bin (binary), case does matter, so you must type the username (and the password) exactly as it's stored in the database.

It's not a problem that the collation used to be utf8_general_ci. It just means that now that the collation is binary you need to know whether a username was originally entered as "ric" or "Ric", and you need to type it exactly now. The same goes for the password field (it too was previously case-insensitive).

When I upgraded my site from 3.x to 4.2 and fixed up our collation throughout the database, tables, and columns, I had to get used to typing "Admin" rather than "admin" as the administrative username when logging in as the Zabbix administrative user. But I had always been typing the Admin password using the mixed case, so I didn't need to adjust my habits there.

[RicOkteo]

The "_ci" in the utf8_general_ci collation means "case insensitive", so "admin", "ADMIN", "Admin", and "AdMiN" would all be treated the same. The actual username in the database probably was "Admin", though.

Now that the collation has switched to _bin (binary), case does matter, so you must type the username (and the password) exactly as it's stored in the database.

It's not a problem that the collation used to be utf8_general_ci. It just means that now that the collation is binary you need to know whether a username was originally entered as "ric" or "Ric", and you need to type it exactly now. The same goes for the password field (it too was previously case-insensitive).

When I upgraded my site from 3.x to 4.2 and fixed up our collation throughout the database, tables, and columns, I had to get used to typing "Admin" rather than "admin" as the administrative username when logging in as the Zabbix administrative user. But I had always been typing the Admin password using the mixed case, so I didn't need to adjust my habits there.

Okay I see. But precisely the problems come from there, in my examples I use "Admin", but if I use an account such as "rri" (all lowercase), impossible to connect.
I am however sure that the user is written in lower case, and sure of my password
Actually, my problem is that when I try to login through the fronttend, I see the request go through to do the authentication but, the command output is empty.
And the only way I've found to fix it is to modify my user fields, for example "rri" to change it to "abc" then back to "rri"
This way, the same request that didn't work before now works and the connection to the frontend is ok
So I think it's a problem with my database. Transcription problem? bad encoding?
These are just guesses...
I really don't know where my problem is coming from.

[RicOkteo]

UPDATE

I deployed my zabbix 3.0 server locally (nolan) and tried to migrate all my tables from utf8 utf8_general_ci to utf8 utf8_bin (it took me 22h..)

After having freshly recovered my dump in utf8_bin, I re proceeded with my tests and they are conclusive this time!
Everything works, so I had a snack problem at first sight..

But before closing this ticket, I have one last question.
My zabbix server is a production server with on-call behind.
So you understand that manipulating the server directly is quite complicated and therefore...

Is it possible to hot migrate all the tables of my database in utf8_bin without the zabbix-server crashing / without the database crashing.
While ensuring the proper collection of data from the different hosts.

Thanks in advance for your answers and thanks to tim.mooney!
I hadn't understood what exactly the "general-ci" was and your explanations helped me a lot