Database Schema update fails from Zuhkov/observium

[charlescng]

The old image from Zuhkov/observium is running Observium CE 0.14.11.6000 with MariaDB 5.5.42 The new image is running Observium CE 18.9.1 with MariaDB 10.3

Trying to reuse the database will fail with the following schema update error:

[2018/10/06 23:15:01 -0400] discovery.php(298): ====== Schema update 183 -> 184 ============== [2018/10/06 23:15:01 -0400] discovery.php(298): Query: UPDATE eventlog SET severity = 5 WHERE message REGEXP ' [.(.]by (user|cron|console)'; [2018/10/06 23:15:01 -0400] discovery.php(298): Error: (1139) Got error 'POSIX collating elements are not supported at offset1' from regexp

[charlescng]

If the problem is similar to https://github.com/phpDocumentor/phpDocumentor2/issues/551 then the problem is a problem with the regular expression and maybe it can be tweaked.

[charlescng]

So the regex does work in MariaDB 5.5.42

MariaDB [observium]> select message from eventlog WHERE message REGEXP '[.(.]by (user|cron|console)' LIMIT 1;
+---------------------------------------------+
| message                                     |
+---------------------------------------------+
| Device added: 10.0.1.1 (by user: observium) |
+---------------------------------------------+
1 row in set (0.00 sec)

[charlescng]

Confirmed that the regex doesn't work in MariaDB 10.3.10

MariaDB [observium]> select message from eventlog WHERE message REGEXP '[.(.]by (user|cron|console)' LIMIT 1;
ERROR 1139 (42000): Got error 'POSIX collating elements are not supported at offset 0' from regexp

[charlescng]

Changing the regex to [(]by (user|cron|console) works

MariaDB [observium]> select message from eventlog WHERE message REGEXP '[(]by (user|cron|console)' LIMIT 1;
+---------------------------------------------+
| message                                     |
+---------------------------------------------+
| Device added: 10.0.1.1 (by user: observium) |
+---------------------------------------------+
1 row in set (0.000 sec)

[charlescng]

There is only one update script with that particular pattern

root@efd87440cdfe:/opt/observium/update# grep "\[\.(\.\]" *
184.sql:UPDATE `eventlog` SET `severity` = 5 WHERE `message` REGEXP ' [.(.]by (user|cron|console)';

[charlescng]

All of the schema updates complete successfully.

[charlescng]

Learned about collating sequences in regex: https://www.regular-expressions.info/posixbrackets.html. This makes sense now.