mysql upgrade script 10_to_11 fails
40 views
Skip to first unread message
Martin Schamberger
Dec 28, 2021, 4:10:09 AM12/28/21
to Opencast Users
Hi,
I was trying to upgrade my opencast test installation, but I'm failing to apply the database changes (I'm on MariaDB 10.3.32).
When running
```
UPDATE oc_series
SET modified_date = TIMESTAMP '1970-01-01 00:00:01'
WHERE modified_date IS NULL;
```
MariaDB states:
```
SET modified_date = TIMESTAMP '1970-01-01 00:00:01'
WHERE modified_date IS NULL;
```
MariaDB states:
```
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:01' for column `opencast_prod`.`oc_series`.`modified_date` at row 1
```
When running the same script on a clean 10.8 installation (including wiping database, re-generating db scheme by Opencast and processing a small number of mediapackages), applying the script works without issues.
```
When running the same script on a clean 10.8 installation (including wiping database, re-generating db scheme by Opencast and processing a small number of mediapackages), applying the script works without issues.
Can anyone give advice?
Best regards,
Martin
Best regards,
Martin
Lars Kiesow
Dec 28, 2021, 1:42:44 PM12/28/21
to us...@opencast.org
Hi Martin,
that's a really good question. My first guess was that your local time
zone was dropping the TIMESTAMP value below the minimum date but I
cannot reproduce that locally. Here are a few ideas to start
investigating:
- What happens if you just run a select statement like this:
select TIMESTAMP '1970-01-01 00:00:01';
- What are your local time zone settings for your database
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
- Are there actually any values to modify?
select count(*) from oc_series where modified_date IS NULL;
–Lars
On Tue, 28 Dec 2021 01:10:09 -0800 (PST)
Martin Schamberger <majo...@gmail.com> wrote:
> Hi,
>
> I was trying to upgrade my opencast test installation, but I'm
> failing to apply the database changes (I'm on MariaDB 10.3.32).
>
> When running
> ```
> UPDATE oc_series
> SET modified_date = TIMESTAMP '1970-01-01 00:00:01'
> WHERE modified_date IS NULL;
> ```
> MariaDB states:
> ```
> ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:01'
> for column `opencast_prod`.`oc_series`.`modified_date` at row 1
> ```
> When running the same script on a clean 10.8 installation (including
> wiping database, re-generating db scheme by Opencast and processing a
> small number of mediapackages), applying the script works without
> issues.
>
> As the script only adds and modifies *new* columns to table
that's a really good question. My first guess was that your local time
zone was dropping the TIMESTAMP value below the minimum date but I
cannot reproduce that locally. Here are a few ideas to start
investigating:
- What happens if you just run a select statement like this:
select TIMESTAMP '1970-01-01 00:00:01';
- What are your local time zone settings for your database
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
- Are there actually any values to modify?
select count(*) from oc_series where modified_date IS NULL;
–Lars
On Tue, 28 Dec 2021 01:10:09 -0800 (PST)
Martin Schamberger <majo...@gmail.com> wrote:
> Hi,
>
> I was trying to upgrade my opencast test installation, but I'm
> failing to apply the database changes (I'm on MariaDB 10.3.32).
>
> When running
> ```
> UPDATE oc_series
> SET modified_date = TIMESTAMP '1970-01-01 00:00:01'
> WHERE modified_date IS NULL;
> ```
> MariaDB states:
> ```
> ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:01'
> for column `opencast_prod`.`oc_series`.`modified_date` at row 1
> ```
> When running the same script on a clean 10.8 installation (including
> wiping database, re-generating db scheme by Opencast and processing a
> small number of mediapackages), applying the script works without
> issues.
>
Martin Schamberger
Dec 29, 2021, 3:26:49 AM12/29/21
to Opencast Users, Lars Kiesow
Hi Lars,
thank you for your hints!
- What happens if you just run a select statement like this:
select TIMESTAMP '1970-01-01 00:00:01';
Returns always 1970-01-01 00:00:01 regardless of timezone settings (global, session, connection).
- What are your local time zone settings for your database
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
Both have the value SYSTEM. According to timedatectl my timezone settings are:
```
Local time: Mi 2021-12-29 08:08:18 CET
Universal time: Mi 2021-12-29 07:08:18 UTC
RTC time: Mi 2021-12-29 07:08:18
Time zone: Europe/Vienna (CET, +0100)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
```
Local time: Mi 2021-12-29 08:08:18 CET
Universal time: Mi 2021-12-29 07:08:18 UTC
RTC time: Mi 2021-12-29 07:08:18
Time zone: Europe/Vienna (CET, +0100)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
```
- Are there actually any values to modify?
select count(*) from oc_series where modified_date IS NULL;
Yes. After setting/copying modified_date from oc_search, there are still 671 (of 1641) series with modified_date IS NULL.
Interestingly, if setting @@GLOBAL.time_zone or @@SESSION.time_zone to "+00:00" before running the statement
UPDATE oc_series SET modified_date = TIMESTAMP '1970-01-01 00:00:01' WHERE modified_date IS NULL
it is executed without errors, but timestamp value is "1970-01-01 01:00:01".
So when using timezone settings SYSTEM/+01:00, the statement also works when adding an extra hour to the timestamp value:
UPDATE oc_series SET modified_date = TIMESTAMP '1970-01-01 01:00:01' WHERE modified_date IS NULL;
UPDATE oc_series SET modified_date = TIMESTAMP '1970-01-01 01:00:01' WHERE modified_date IS NULL;
Does Opencast expect exactly 1970-01-01 00:00:01 in modified_date - or is the purpose just to set a any (arbitrary) date in the past - and this workaround should do the job?
Kind regards,
Martin
Martin
Lukas Kalbertodt
Jan 5, 2022, 3:49:09 AM1/5/22
to us...@opencast.org
Dang, I was pretty sure I tested this with different timezones. Sorry
about that!
> Does Opencast expect exactly 1970-01-01 00:00:01 in modified_date -
> or is the purpose just to set a any (arbitrary) date in the past -
> and this workaround should do the job?
The purpose is just to set an arbitrary value. So 1970-01-01 01:00:01 is
also fine. One possible option to fix the script for everyone is to use
1970-01-02 as that should be a workaround for all possible timezones.
Will see about that.
> Yes. After setting/copying modified_date from oc_search, there are
> still 671 (of 1641) series with modified_date IS NULL.
Out of interest: does that mean you have 671 series that don't have any
events? I assumed the number of empty series in real systems should be
fairly small.
Lukas
On 29.12.21 09:26, Martin Schamberger wrote:
> Hi Lars, thank you for your hints!
>
> - What happens if you just run a select statement like this: select
> TIMESTAMP '1970-01-01 00:00:01';
>
>
> Returns always 1970-01-01 00:00:01 regardless of timezone settings
> (global, session, connection).
>
> - What are your local time zone settings for your database SELECT
> @@GLOBAL.time_zone, @@SESSION.time_zone;
>
>
> Both have the value SYSTEM. According to timedatectl my timezone
> settings are: ``` Local time: Mi 2021-12-29 08:08:18 CET Universal
> time: Mi 2021-12-29 07:08:18 UTC RTC time: Mi 2021-12-29 07:08:18
> Time zone: Europe/Vienna (CET, +0100) System clock synchronized: yes
> NTP service: active RTC in local TZ: no ```
>
> - Are there actually any values to modify? select count(*) from
> oc_series where modified_date IS NULL;
>
> Yes. After setting/copying modified_date from oc_search, there are
> still 671 (of 1641) series with modified_date IS NULL.
>
> Interestingly, if setting @@GLOBAL.time_zone or @@SESSION.time_zone
> to "+00:00" before running the statement UPDATE oc_series SET
> modified_date = TIMESTAMP '1970-01-01 *00:00:01*' WHERE
>
> -- To unsubscribe from this group and stop receiving emails from it,
> send an email to users+un...@opencast.org
> <mailto:users+un...@opencast.org>.
about that!
> Does Opencast expect exactly 1970-01-01 00:00:01 in modified_date -
> or is the purpose just to set a any (arbitrary) date in the past -
> and this workaround should do the job?
also fine. One possible option to fix the script for everyone is to use
1970-01-02 as that should be a workaround for all possible timezones.
Will see about that.
> Yes. After setting/copying modified_date from oc_search, there are
> still 671 (of 1641) series with modified_date IS NULL.
events? I assumed the number of empty series in real systems should be
fairly small.
Lukas
On 29.12.21 09:26, Martin Schamberger wrote:
> Hi Lars, thank you for your hints!
>
> - What happens if you just run a select statement like this: select
> TIMESTAMP '1970-01-01 00:00:01';
>
>
> Returns always 1970-01-01 00:00:01 regardless of timezone settings
> (global, session, connection).
>
> - What are your local time zone settings for your database SELECT
> @@GLOBAL.time_zone, @@SESSION.time_zone;
>
>
> Both have the value SYSTEM. According to timedatectl my timezone
> settings are: ``` Local time: Mi 2021-12-29 08:08:18 CET Universal
> time: Mi 2021-12-29 07:08:18 UTC RTC time: Mi 2021-12-29 07:08:18
> Time zone: Europe/Vienna (CET, +0100) System clock synchronized: yes
> NTP service: active RTC in local TZ: no ```
>
> - Are there actually any values to modify? select count(*) from
> oc_series where modified_date IS NULL;
>
> Yes. After setting/copying modified_date from oc_search, there are
> still 671 (of 1641) series with modified_date IS NULL.
>
> Interestingly, if setting @@GLOBAL.time_zone or @@SESSION.time_zone
> to "+00:00" before running the statement UPDATE oc_series SET
> modified_date IS NULL it is executed without errors, but timestamp
> value is "1970-01-01 *01:00:01*".
>
> So when using timezone settings SYSTEM/+01:00, the statement also
> works when adding an extra hour to the timestamp value: UPDATE
> oc_series SET modified_date = TIMESTAMP '1970-01-01 01:00:01' WHERE
> modified_date IS NULL;
>
>
> Kind regards, Martin
> So when using timezone settings SYSTEM/+01:00, the statement also
> works when adding an extra hour to the timestamp value: UPDATE
> oc_series SET modified_date = TIMESTAMP '1970-01-01 01:00:01' WHERE
> modified_date IS NULL;
>
>
>
> -- To unsubscribe from this group and stop receiving emails from it,
> send an email to users+un...@opencast.org
> <mailto:users+un...@opencast.org>.
Martin Schamberger
Jan 7, 2022, 3:42:26 AM1/7/22
to Opencast Users, Lukas Kalbertodt
Dang, I was pretty sure I tested this with different timezones. Sorry
about that!
No worries ;-)
The purpose is just to set an arbitrary value. So 1970-01-01 01:00:01 is
also fine. One possible option to fix the script for everyone is to use
1970-01-02 as that should be a workaround for all possible timezones.
Will see about that.
Out of interest: does that mean you have 671 series that don't have any
events? I assumed the number of empty series in real systems should be
fairly small.
Our users can register series according to the roles in our campus management system - we have a simple UI for registration. Don't ask me why, but many users register every available course, although practically using just one ;-)
Thanks for your help.
Kind regards, Martin
Reply all
Reply to author
Forward
0 new messages