ZoneMinder Forums

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

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()