1.30.2 cannot login

[codabiz]

Hi. I just upgraded to 1.30.2 (from 1.30.0) running on Ubuntu server 16.04 LTS. Upgrade went fine and zoneminder was working well, except for creating a new user (errors same as below).

I had previously added another user, so I removed the default admin user. When I restarted zoneminder again I got the new login layout, but I could not login with the new user. The error shown is:

SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='', statement was 'select * from Users where Enabled = 1 AND Username=? AND Password=password(?)'

As I am now locked out, any ideas how to fix it

[jkemp]

I also cannot login on a test system.

SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='', statement was 'select * from Users where Enabled = 1 AND Username=? AND Password=password(?)'

[jkemp]

I had this happen and few weeks ago when this update creped into then main repository. I had to fall back to previous version.

"sudo apt install zoneminder=1.30.0-xenial1" if you are using ubuntu 16.04.1

I noticed it switched the skin to flat on the login screen that I could not log into. Once I fail back to 1.30.0 the classic dark login returned.

James

[codabiz]

Ok. I went into zm database checked that user was correct. I removed the password from the db - put blank password. I attempted to log in again, I was prompted for a new password. Got the same error message.

SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='', statement was 'select * from Users where Enabled = 1 AND Username=? AND Password=password(?)'

Also removed language setting en-gb. Still the same SQL mixed collations message. I also had the skin change to blue.
I also noticed server/zm/api is no longer authenticated. Everything was OK with version 1.30.0

I am at a loss what to try next?

[jkemp]

you can use script I listed to go back to the previous version in my message. Between the quotes.

[bbunge]

Did not have any issues upgrading Ubuntu 16.04/ZM 1.29.0 with Mariadb server to ZM 1.30.2.

If you are using MySQL 5.7 make sure this is in your my.cnf
In the [mysqld] section add the following

sql_mode = NO_ENGINE_SUBSTITUTION

I should add that I did a fresh install of ZM 1.30.2 on Ubuntu 16.04/MySQL 5.7 with no issues. See: https://wiki.zoneminder.com/Ubuntu_Serv ... e_easy_way

[codabiz]

After a bit of research: logged into mysql shell zm database and created a new password with:

> update Users set Password = password( "newpassword" ) where Username="user";

Again I got the SQL-ERR 1267 when trying to login?

My database showed +--------------------+---------+--------------------+
| zm | charset | collation |
+--------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| mysql | utf8mb4 | utf8mb4_general_ci |
| performance_schema | utf8 | utf8_general_ci |
| zm | latin1 | latin1_swedish_ci |
+--------------------+---------+--------------------+

But I am using MariaDB version 10.0.29 it shows charset = utf8mb4 , and collation = utf8_general_ci

So I decided to change MariaDB settings to match the ZM. (sudo nano /etc/mysql/my.cnf ) I added the new charset and collation and commented out the existing set, as follows.

character-set-server = latin1
collation-server = latin1_swedish_ci
#character-set-server = utf8mb4
#collation-server = utf8mb4_general_ci

Result - it worked. I can now log in as normal. I think this problem went back a couple of versions when I upgraded to MariaDB.

ZM version 1.30.2 is working great, except for the API issue and 401 error that has been reported elsewhere. I hope this fix helps others with the SQL-ERR 'SQLSTATE[HY000]: General error: 1267

If anyone has more knowledge on this issue, please post.

[bbunge]

You may have something new! Why am I not surprised?

May I assume the language you use in Zoneminder is not English? Or is your OS not English?

If this is the case, I should place a note in the Mariadb instructions that "character-set-server = utf8mb4" and "collation-server = utf8mb4_general_ci" may need to be changed to match the language used?
Should this also apply to MySQL?

bb

[codabiz]

From MariaDB website:

"In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci (however this may differ in some distros, see for example Differences in MariaDB in Debian)"

I used Mysql (latin1 default) on Ubuntu 16.04.01 LTS until a couple of versions ago, then I upgraded to MariaDB on Ubuntu 16.04.01 LTS . Currently my MariaDB version is 10.0.29-0ubuntu0.16.04.1. So presumably on install the character set/collation was set to non-default mode for Debian/Ubuntu, as follows:

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

Or, should the zm character/collation have been changed to match the Ubuntu OS?

I know no more!

[bbunge]

Well, I just upgraded my production server, Ubuntu 16.04, ZM 1.30.2 with Mariadb and ran into the same error when I tried to log in.

Fix was to edit my.cnf as follows:

character-set-server = latin1
collation-server = latin1_swedish_ci
#character-set-server = utf8mb4
#collation-server = utf8mb4_general_ci

Guess some more testing is in order... Stay tuned

[bbunge]

Here are the changes I made to the WIKI Ubuntu instructions:

Change Mariadb settings:

nano /etc/mysql/my.cnf

Make the following changes:

character-set-server = latin1
collation-server = latin1_swedish_ci

Note: The above settings are actually the Mariadb default. Changing back to default is necessary to avoid errors when logging into Zoneminder. This was added on 01APR17 but is not an April Fool! It may be necessary to align these settings with your regional language.