Upgrade from 1.8.2 to 1.8.3 (and from 1.8.0 rc1 and on and on...)

[Dario_P]

Dario here.

Short version
maintenance never ends on my environment, had to send it to a background process to be able to start Xibo.
How can I debug it?

Long version
I had a problem, which i was able to solve, but i’ll just share it here just in case i should do something

I followed the regular guide to update using containers, which already did to update from 1.8.1.

After all the steps, I couldn’t start xibo CMS,

After some troubleshooting:

• container inspection with
  docker exec -it xibodockermaster_cms-web_1 bash
docker exec -it xibodockermaster_cms-db_1 bash
  peeking on shared directory,
  running queries against database

everything led to the container not invoking the web server (entrypoint.sh should have a log to the shared directories by the way), it seems than the line that invokes maintenance on entrypoint.sh is expected to finish.

    cd /var/www/cms
    su -s /bin/bash -c 'cd /var/www/cms && /usr/bin/php bin/run.php 1' www-data

It didn’t so I just added an & to all lines from there to the web server startup line.

I’m not sure if this should be done, or there is something wrong on my environment.

[alex]

@Dario_P

entrypoint.sh should have a log to the shared directories by the way

It logs to Docker. docker-compose logs -f will show you the current rolling log from all the containers. docker-compose logs -f cms-web will show just the web container.

Maintenance should take a few seconds to maybe a minute or two at the very top end to run on a healthy installation. If it’s taking longer, you have a problem and should resolve it, rather than making the change you have.

If you connect to the database, run the following SQL. What do you get?

SELECT count(*) from log;
SELECT count(*) from stat;

You can find instructions for connecting to the container and running SQL here:

How can I run a SQL command when using a Docker Install?

While troubleshooting an issue you maybe asked to run a SQL statement on your database Docker container, or you may want to run a statement yourself for another purpose. To do either of these things there are two options. Using the command line Using the GUI called PhpMyAdmin Command Line Before you can run a statement inside the docker container you will need to find the container name. This can be done using the docker ps command. The resulting output will show a list of running docker con…

[Dario_P]

Thank you for your answer.

Those are the results.

mysql> select count(*) from stat;

±---------+
| count(*) |
±---------+
| 21024 |
±---------+
1 row in set (0.01 sec)

mysql> select count(*) from log;

+----------+
| count(*) |
+----------+
|     2655 |
+----------+
1 row in set (0.00 sec)

[Dario_P]

I have reduced log and stat period retention and both numbers went to 17000 and 101

[alex]

OK, but it’s still taking a long time to run the maintenance tasks? Or is it now working as expected?

[Dario_P]

It keeps running.

www-data    51    49  0 14:26 ?        00:00:00 /usr/bin/php bin/run.php 1
root       126   102  0 14:36 pts/0    00:00:00 grep --color=auto run
root@843e52fffbdb:/# date
Mon Dec 11 14:36:28 UTC 2017

In an attempt to clean the environment, tried to run the tidy media process, but an error ocurred. I’ll post it later on another post if it isn’t related.

I understand that Xibo is freezed while maintenance is running. I already left it running several hours on weekend.

[alex]

In that case I think there’s a deeper issue in the database if you’re getting errors running the library tidy as well. You’ll need to collect logs from the CMS and see what is logged when that routine is run.

[Dario_P]

Thank you.

I have this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AS mediaId FROM dataset_9 UNION ALL SELECT Logos AS mediaId FROM dataset_16’ at line 13#0 /var/www/cms/lib/Storage/PdoStorageService.php(232): PDOStatement;execute(Array)

[alex]

I don’t think you’re running the latest 1.8.3 containers? If you are running 1.8.3 already, then there are updated containers that fix some issues discovered shortly after release.

You need to run a docker-compose pull and then docker-compose up command, with any -f parameter you normally use.

That should resolve that issue.

[Dario_P]

Thank you again.

I just updated the containers.

It did not solved the tidy media problem.

There seems to be a problem with dataset with columns with spaces.

I just modified a column and the error “moved” to another dataset.

I bet this column on line 259

xibosignage/xibo-cms/blob/41b52ef49d1ec2233aa16de550f13e62c7083aff/lib/Controller/Maintenance.php#L259

Should have… “backticks”?

[alex]

Thank you. It does look like a problem. I’ve logged the issue here:

Error running Library Tidy with datasets with spaces in column names

[Dario_P]

We were testing an application that consumes xibo web services.

I patched this on my environment.

The tidy media process is now trying to query a column that seems to be a calculated column

From log:
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘DatoFondo’ in ‘field list’ Exception Type: PDOException

Findings:

 mysql> select * from datasetcolumn where Heading like 'Da%';
 +-----------------+-----------+-------------+------------+---------------------+-------------+-------------+------------------------------------------------------------------------------+
 | DataSetColumnID | DataSetID | Heading     | DataTypeID | DataSetColumnTypeID | ListContent | ColumnOrder | Formula                                                                      |
 +-----------------+-----------+-------------+------------+---------------------+-------------+-------------+------------------------------------------------------------------------------+
 ...
 |             141 |        38 | DatoFondo   |          5 |                   2 | NULL        |           8 | CASE WHEN month(curdate()) = month(Fechadenacimiento) THEN 558  ELSE 565 END |
 ....
 +-----------------+-----------+-------------+------------+---------------------+-------------+-------------+------------------------------------------------------------------------------+
 7 rows in set (0.00 sec)

 mysql> select * from dataset_38;
 +----+---------+----------+------+---------------------+---------------------+--------------+------+
 | id | Nombre  | Apellido | Foto | Fechadenacimiento   | Fechadeingreso      | Puesto/Cargo | msj1 |
 +----+---------+----------+------+---------------------+---------------------+--------------+------+
 |  1 | Dario   | **       |   20 | 1977-01-01 00:00:00 | 2014-02-01 00:00:00 | Jefe BO      |    0 |
 |  2 | Martin  | **       |   24 | 1972-01-01 00:00:00 | 2011-01-01 00:00:00 | Director     |    0 |
 |  3 | Gabriel | **       |   21 | 1977-01-01 00:00:00 | 2007-01-01 00:00:00 | Director     |    0 |
 +----+---------+----------+------+---------------------+---------------------+--------------+------+
 3 rows in set (0.00 sec)

Let me know if I can be helpful.

Greetings.

[alex]

Thanks for your report. I’ve added a note to check your second part post on the original report.