We have recently attempted to upgrade our Xibo installation from version 3.3.8 to 4.0.2. During this process, we encountered a significant issue that is preventing us from completing the update. The specific error we are encountering is shown in the system logs:
2023-11-29T15:17:03.586725187Z PDOException: SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size in /var/www/cms/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:192
It appears we are facing an issue with the lock table size during the database migration. Has anyone else experienced a similar problem when upgrading to version 4.0.2? Are there recommended steps or configurations that we should consider to resolve this error?
Any guidance or suggestions would be greatly appreciated. We are eager to complete this upgrade and continue to use Xibo effectively.
You need to look in the database and see what large tables you have. At a guess they could be a very large log table or a very large stat table:
select count(*) from log; select count(*) from stat; select count(*) from stat_archive;
If it is logs, then you would just delete those:
truncate log;
and then run again.
If it is a lot of proof of play data that is not needed you can delete that too:
truncate stat; truncate stat_archive;
You will then need to either turn off the collection for proof of play stats OR set up the stats archiver to delete/export old records going forward
However, if that data is needed then you will need to increase the MySQL buffer pool size to allow it to complete ensuring that you have sufficient ram for that. (We can help you with this if you do need to go this route).