Hi there, when updating from xibo 2.3.15 to the latest xibo 3.x.x the logs show:
We will upgrade it, take a backup
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
So no upgrade… Could use a little help here.
The message you have there is normal. It’s not an error.
You need to wait and allow time for a backup of the database to be taken. If it takes a long time, it’s a good indication that you don’t have the proof of play stats archiver configured and have a very large number of proof of play records in the database. That will ultimately bog the CMS down and give you performance issues.
@alex thank you for your reply. After long waiting the update does not give the desired outcome.
On the webpage i get the message “An upgrade is in progress”.
We will upgrade it, take a backup
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Running database migrations
Phinx by CakePHP - https://phinx.org. 0.9.2
using config file .varwwwcmsphinx.php
using config parser php
using migration paths
- /var/www/cms/db/migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database cms
== 20221024082400 AdCampaignMigration: migrating
In PdoAdapter.php line 134:
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'maxPlay
sPerHour'
migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]
Importing ca-certs
cp: can't stat '/var/www/cms/ca-certs/*.pem': No such file or directory
cp: can't stat '/var/www/cms/ca-certs/*.crt': No such file or directory
WARNING: ca-certificates.crt does not contain exactly one certificate or CRL: skipping
Setting up Quickchart
Configuring Maintenance
Removing web/install/index.php from production container
Starting cron
Starting webserver
AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 172.18.0.7. Set the 'ServerName' directive globally to suppress this message
172.18.0.8 - - [07/Dec/2022:18:33:52 +0000] "GET /xmds.php?what HTTP/1.1" 200 1 "-" "okhttp/3.12.1"
That’s the cause. I expect you had the upgrade running and killed the container part way through, and now when the migration tries to run it’s already partly complete.
You could try dropping that column off of the campaign table, but you may then hit another error after that.
Alternatively roll back to the backup you took prior to starting and run the upgrade again from scratch.
@alex Thnx, I will try as soon as possible and will let you know the results.
Did everything from scratch. It was looking good. Unfortunately the following error stopped the migration from 2.3.16 to 3.3.0:
== 20221024082400 AdCampaignMigration: migrating
In PdoAdapter.php line 134:
SQLSTATE[HY000]: General error: 1878 Temporary file write failure.
Perhaps you’re out of disk space? Some of the migrations may copy tables, and if you have a very large number of proof of play records for example that could easily double the size of your database and more.
Yes I also thought that could be the issue. So what i did now is re-did everything until the point i am at 2.3.16 (did some nighttime work here). Tonight i will make a snapshot of that version so when migration fails again i don’t have to redo everything from scratch.
Hope it will work then, I will let you know. Thank you so far for your kind support, really appreciate it!
If you don’t need the proof of play data, then just drop it before doing the upgrade
truncate `stat`;
optimize table `stat`;
will clear it and free the disk space. You’ll loose that proof of play data though, so only do that if you don’t need it.
Thank you very much for all your help. Finally (with your help) I managed to upgrade to the newest version of Xibo. What a relieve. I will shortly share my steps how I did this to provide the community with information. After that I will mark this as closed.
Hi there,
Took some time, but hereby my steps:
In this scenario I assume you are migrating an older version of Xibo from an old server to a new server (another location).
Also I assume that you are using docker compose on both locations. I have no experience with local installations.
Server A ----xibo migrate----> Server B
shared | - backup - cms | - library
wget -O xibo-docker.tar.gz https://xibo.org.uk/api/downloads/cms
tar --strip-components=1 -zxvf xibo-docker.tar.gz
cp config.env.template config.env
vim config.env
change the mysql password to the same mysql password you are using on the old environment.
you can find the password in the config.env file on the old environment.
Edit the docker-compose.yml
Here you have to change the image version numbers to the exact version numbers you are using on the old environment. Do this for both the CMS and MySQL Containers/Images.
Optional: To shrink the database you can clear the proof of play statistics. This speeds up the migration enormous.
Run the following commands. Also notice that maybe your DB containers name is different. So use the commands with your environment settings.
docker exec -ti xibo-cms-db-1 bash
mysql -u cms -h localhost -p cms
truncate `stat`;
optimize table `stat`;
exit
exit
Manual backup can be made with:
docker exec xibo-cms-db-1 /usr/bin/mysqldump -uusername --password=yourpassword cms> import.sql
On the old server there should be a backup of the database that should be generated every day. shared\backup should be the location. You should migrate this backup to the new environment. Also notice that it is a gzip format so you will have to extract it. When extracted, rename the database to import.sql and move it to
shared\backup\
so that the exact location will be
shared/backup/import.sql
shared\cms\library
.
It could help to compress the folder first so that a transfer should be fast.
compress with:
tar -zcvf myfolder.tar.gz myfolder
extract with:
tar -tf myfolder.tar.gz
If everything is in place you are ready to spin up the environment with docker compose up -d
This could take a little time. You can monitor it by using HTOP (look at mysqld process). Also with docker logs xibo-cms-web-1 you can check the container logs. It should say “importing database”. Just have patience.
Now when everything is working you can perform the upgrade(s). As a reference I used: Xibo CMS 2.3.10 Released - #2 by alex
If you’re upgrading from an earlier version to 2.3.10, the version of MySQL bundled changes from 5.6 to 5.7 as 5.6 is now end of life.
docker compose down
docker run --rm -ti -v /opt/docker/xibo/shared/db:/var/lib/mysql mysql:5.7 bash
mysqld --skip-grant-tables &
mysql_upgrade (just type the command, you wont see a cursor)
exit
docker compose up -d
When upgrading do not interrupt the process, be patience and always make a backup before performing an upgrade.
When looking at docker logs you could see the following error:
We will upgrade it, take a backup
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
Don’t panic, just wait! After a while you should see:
Running database migrations
Just wait, monitor docker logs and processes. Finally you should be able to access with webbrowser.
Hope this helps!
We found out that after an upgrade not all displays where showed/visible:
You should make an DB connection for that:
docker exec -ti xibo-cms-db-1 bash (name is db docker container)
mysql -u cms -h localhost -p cms
UPDATE `displaygroup` SET userId = (SELECT userId FROM `user` WHERE usertypeid = 1 LIMIT 1) WHERE userId NOT IN (SELECT userId FROM `user`);
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.