No events listed - ZM_1.35.15, Ubuntu 20.04

[Rigor]

Hey All,
I recently "upgraded" my server to Ubuntu 20.04, knowing full well that there would be things to fix afterwards, but I'm kind of stuck.

I had to reinstall MySQL to get the database to connect, but after that it seems to be working, except for one thing: my main console doesn't show any information:


I see errors in the log with SQL errors:

Code: Select all

SQL-ERR dbFetchAll no result, statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?'params: WAR,0,50
SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'', statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?' params:WAR,0,50
SQL-ERR dbFetchOne no result, statement was 'SELECT count(*) AS Total FROM Logs WHERE (Code LIKE ?)'params: WAR
SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'', statement was 'SELECT count(*) AS Total FROM Logs WHERE (Code LIKE ?)' params:WAR
SQL-ERR dbFetchAll no result, statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?'params: WAR,0,50
SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'', statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?' params:WAR,0,50
SQL-ERR dbFetchOne no result, statement was 'SELECT count(*) AS Total FROM Logs WHERE (Code LIKE ?)'params: WAR
SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'', statement was 'SELECT count(*) AS Total FROM Logs WHERE (Code LIKE ?)' params:WAR
SQL-ERR dbFetchAll no result, statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?'params: WAR,0,50
SQL-ERR 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'', statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs`  WHERE (Code LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?' params:WAR,0,50
SQL-ERR dbFetchOne no result, statement was 'SELECT count(*) AS Total FROM Logs WHERE (Code LIKE ?)'params: WAR

Not sure if this is something I did or an issue related to the new updates. Anybody run into similar issues?

[Magic919]

I had the same when I transferred to an Ubuntu 20.04 server. It's this bit https://stackoverflow.com/questions/100 ... ysql-error

Illegal collations. No, I'd never heard of them either.

I converted table by table, but there might be a better route for the more clever

ALTER TABLE [table-name} CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

[iconnor]

I suspect that backing up the db, dropping it and re-creating it might be the easiest way around this.

mysql8 changed the collation stuff and broke a lot.

You can go through and change each table but I'm thinking a dump and reload might be faster.

[Rigor]

Thanks for the info - I'll give it a try this weekend and let you know.

[Rigor]

It seems that I did have an issue with the collations being incorrect in mySQL, but apparently that wasn't my only issue.

For anyone who needs to fix the collations (or just view them to check), I wrote a python3 script. You just need to copy/paste below into a file and make it executable (except the top line).

First execution does the check so you can see collations, then you can add any argument & it will try to fix.

Code: Select all

#!/usr/bin/python3
print("This program is supposed to fix collation of ZM.")

import sys
import pymysql

#tab_schema = "SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,"
#tab_schema += " information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA"
#tab_schema += " WHERE CCSA.collation_name = T.table_collation"
#tab_schema += ' AND T.table_schema = "schemaname"'
#tab_schema += ' AND T.table_name = "'

tab_try2 = 'SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "'



def show_table_collations():
  # Note that you need to have the user "zmuser" and password "zmpass" for this to work!
  db = pymysql.connect("localhost","zmuser","zmpass","zm")

  cursor = db.cursor()

  cursor.execute("SELECT VERSION()")
  data = cursor.fetchone()
  print("The next line should show your database version. If it fails, then there's a problem connecting to mySQL.")
  print("Database version : %s " % data)

  # Get list of tables for executing commands and write to 'table_list'
  # The returned format is a list of lists, so I'm simplifying to just a list of table names.
  cursor.execute("SHOW TABLES")
  data = cursor.fetchall()
  table_list = []
  print("Creating table list: ", end='')
  for tables in data:
    table_list.append(tables[0])
    print(tables[0] + " ", end='')
  print("\n")

  # First check info for schema (database):
  task = "SELECT @@character_set_database, @@collation_database;"
  #task =  "SELECT default_character_set_name FROM information_schema.SCHEMATA"
  #task += ' WHERE schema_name = "zm";'
  cursor.execute(task)
  data = cursor.fetchone()
  print("zm table: " + data[0] + " , " + data[1] + "\n")
    
  # I want to add spaces to line things up nicely for the tables.
  # First need longest item in table list:
  longest = 0
  for table in table_list:
    if longest < len(table):
      longest = len(table)

  # And now we can get the info for each table:
  for table in table_list:
    print("For '" + table + "': ", end='')
    # Add spaces to make each line the max length.
    for number in range(1, longest - len(table) + 1):
      print(" ", end='')
    task = tab_try2 + table + '";'
    try:
      cursor.execute(task)
    except MySQLError as e:
      print('Got error {!r}, errno is {}'.format(e, e.args[0]))
    data = cursor.fetchall()
    print(data[0][2])

  db.close()

def fix_table_collations():
  # Note that you need to have the user "zmuser" and password "zmpass" for this to work!
  db = pymysql.connect("localhost","zmuser","zmpass","zm")

  cursor = db.cursor()

  cursor.execute("SELECT VERSION()")
  data = cursor.fetchone()
  print("The next line should show your database version. If it fails, then there's a problem connecting to mySQL.")
  print("Database version : %s " % data)

  # Next lines
  cursor.execute("SHOW TABLES")
  data = cursor.fetchall()
  print("Tables listing:")
  for tables in data:
    task = "ALTER TABLE `" + tables[0] + "` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"
    print("Executing task: " + task)
    try:
      cursor.execute(task)
    except MySQLError as e:
      print('Got error {!r}, errno is {}'.format(e, e.args[0]))

  db.close()

if __name__ == '__main__':
  if len(sys.argv) < 2:
    show_table_collations()
    print("****************************** NOTE ***********************************************************************************")
    print("On your first run of this, it just shows you info. You should see that all tables have collation type 'utf8_general_ci'")
    print("If that's not the case, you can run this script again with an argument - for example:")
    print("    fix_zm_db_collations.py fix")
    print("I provide no warranty or support for this script - sorry.")
    print("I just used it to fix my issues and shared so others wouldn't have to make it again.")
  else:
    print("Attempting to fix collations.")
    fix_table_collations()

[Magic919]

That’s a lot more impressive than my method. Hopefully that’ll be useful for anyone in the future.

[Rigor]

Thanks Magic -- I know very little about mySQL and probably less about how ZM works, so I would have never figured it out without your help.

Funny part is, I did all the tables manually before writing the script, but I figure it may come in handy in the future... and I like writing scripts

[Rigor]

So, I tried running 'zmaudit.pl' since I had messed with the event storage area (deleted about 1 TB of videos), and something interesting happened. Now it seems to know that I have events on each monitor, but it doesn't know when they happened.



Any chance there's another magic auditing script that fixes this one?

[Magic919]

Hmmm. Fixed means they all appear in their correct columns I presume.

There are a number of Events tables in the DB and I'm presuming that Events_Day etc are simply empty on yours. The main Events one is heavy on details and Events_Day etc seem to carry minimal information (EventId | MonitorId | StartDateTime | DiskSpace).

I think the triggers might do that job, just an educated guess.

They can be installed if absent -

mysql -u zmuser -p zm < /usr/share/zoneminder/db/triggers.sql

[Rigor]

Thanks again Magic, that did the trick!

I had to use -uroot b/c zmuser didn't have permissions for some reason, but otherwise it's exactly what I needed.

[Magic919]

Great. Lucky guess.

I've had that DB permissions thing too. I think the database permissions are not granted as fully as needed. This seems to depend on the exact route taken through the ZM versions.

Glad it all worked out.

[tsp84]

I have 1.35.16 and have the same issue, I did the zmaudit.pl and at least now I can see my total events. My other columns are empty and show 0.00 B. Any ideas on what would fix this issue for me?

Screenshot from 2020-12-23 21-07-44.png (157.91 KiB) Viewed 4747 times

[tsp84]

Removing and readding the camera worked after running the zmaudit.pl.