If you run into error "Failed to execute SQL" when updating LiveAgent, and installer is stuck on version 5.9.5, then your DB tables most probably have incorrect storage engine and / or incorrect collation. LiveAgent requires that all database tables use InnoDB storage engine (except for qu_la_conversations_search which uses MyISAM), and database and all tables and columns use collation utf8_general_ci (except for some specific columns changed by application to utf8mb4_general_ci, utf8mb4_bin and ascii_bin).
Steps to convert database, tables and columns to correct format:
1. Back up your database
2. Check collation on your LiveAgent database. If collation is not utf8_general_ci, run the following (replace <db_name> with your database name)
ALTER DATABASE <db_name> CHARACTER SET utf8 COLLATE utf8_general_ci;
3. Run the following statement to check for tables that need to be converted to utf8. If the output is not empty, copy all the resulting ALTER TABLE statements and run them on database.
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci;")
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<db_name>"
AND TABLE_TYPE="BASE TABLE"
AND TABLE_COLLATION != 'utf8_general_ci'
4. Run the following to check for columns that need to be converted to utf8. If the output is not empty, copy all the resulting ALTER TABLE statements and run them on database.
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME, " CHANGE ", COLUMN_NAME , " ", COLUMN_NAME, " ", DATA_TYPE, CASE WHEN DATA_TYPE IN ('text','longtext','tinytext','mediumtext') THEN "" ELSE CONCAT ("(", CHARACTER_MAXIMUM_LENGTH, ")") END, " CHARACTER SET utf8 COLLATE utf8_general_ci;" )
FROM information_schema.columns
WHERE table_schema = '<db_name>'
AND COLLATION_NAME NOT IN ('utf8_general_ci', 'utf8_unicode_ci', 'utf8mb3_general_ci', 'utf8mb3_unicode_ci', 'utf8mb4_general_ci', 'utf8mb4_unicode_ci', 'utf8_bin', 'utf8mb3_bin', 'utf8mb4_bin', 'ascii_bin')
ORDER BY table_name,ordinal_position
5. Run the following to check for MyISAM tables that need to be converted to storage engine InnoDB. If the output is not empty, copy all the resulting ALTER TABLE statements and run them on database. Warning: converting large tables will take a long time - MySQL will be creating a copy of the whole table. You may want to run this outside of business hours.
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," ENGINE = InnoDB;")
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<db_name>"
AND TABLE_TYPE="BASE TABLE"
AND ENGINE = 'MyIsam'
AND TABLE_NAME != 'qu_la_conversations_search'
When steps 1-5 are done, you can run LiveAgent installer again - it should now continue without error.