[ZBX-23146] TSDB upgrade doesn't work Created: 2023 Jul 25  Updated: 2024 Apr 10  Resolved: 2024 Jan 08

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 7.0.0alpha1
Fix Version/s: 7.0.0alpha8, 7.0 (plan)

Type: Problem report Priority: Trivial
Reporter: Andrejs Kozlovs Assignee: Vladislavs Sokurenko
Resolution: Fixed Votes: 3
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2023-12-12-12-53-42-648.png     PNG File image-2023-12-12-12-55-43-972.png     PNG File image-2023-12-12-12-56-26-152.png     PNG File image-2023-12-12-14-16-58-671.png    
Issue Links:
Causes
caused by ZBXNEXT-8324 Remove old style numerics Closed
Duplicate
is duplicated by ZBX-23482 Upgrade to 7.0.0alpha5 fails on datab... Closed
Team: Team A
Sprint: Sprint 106 (Nov 2023), Sprint 107 (Dec 2023), S2401
Story Points: 1

 Description   

Upgrade patch for TimescaleDB starting from 6050008 fails

[Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  operation not supported on hypertables that have compression enabled
 [alter table history alter value type double precision;
alter table history alter value set default '0.0000']
 19717:20230725:093511.711 database upgrade failed on patch 06050008, exiting in 10 seconds

 

 Comments   
Comment by Rodrigo P [ 2023 Jul 27 ]

One more here. Tried to upgrade from 6.0.7 to 7.0.0alpha3 and got message:

[Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  operation not supported on hypertables that have compression enabled
zabbix-server  |  [alter table history alter value type double precision;
zabbix-server  | alter table history alter value set default '0.0000']
zabbix-server  |      7:20230726:184820.667 database upgrade failed on patch 06050008, exiting in 10 seconds
zabbix-server  |      7:20230726:184830.667 Zabbix Server stopped. Zabbix 7.0.0alpha3 (revision 20b6104).
Comment by Rodrigo P [ 2023 Aug 07 ]

Hi. Any news on this issue? 

Upgrade from 6.0.7 to 7.0.0alpha3 is not OK, as reported above. Upgrade from 6.0.7 to 6.4.4 is OK.

Comment by Rodrigo P [ 2023 Sep 05 ]

Hi Zabbix Teams.

Problem still persists when trying to upgrade to version 7.0.0alpha5.

zabbix-server     | 8:20230905:063031.759 starting automatic database upgrade
postgres-server   | 2023-09-05 06:30:31.762 -03 [402] ERROR:  operation not supported on hypertables that have compression enabled
postgres-server   | 2023-09-05 06:30:31.762 -03 [402] STATEMENT:  alter table history alter value type double precision;
postgres-server   |   alter table history alter value set default '0.0000'
zabbix-server     | 8:20230905:063031.762 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  operation not supported on hypertables that have compression enabled
zabbix-server     |   [alter table history alter value type double precision;
zabbix-server     | alter table history alter value set default '0.0000']
zabbix-server     | 8:20230905:063031.763 database upgrade failed on patch 06050008, exiting in 10 seconds
zabbix-server     | 8:20230905:063041.763 Zabbix Server stopped. Zabbix 7.0.0alpha5 (revision 79b085c).

 

Comment by Adrian Cunnelly [ 2023 Sep 30 ]

Need a function adding to check the field type if database is PostgreSQL

In libs/zbxdbhigh/db.c

 

#if defined(HAVE_POSTGRESQL)
static int    validate_db_type_name(const int expected_type, const char *type_name)
{
    switch(expected_type)
    {
        case ZBX_TYPE_ID:
            if (0 == strcmp(ZBX_TYPE_ID_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_INT:
            if (0 == strcmp(ZBX_TYPE_INT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_CHAR:
            if (0 == strcmp(ZBX_TYPE_CHAR_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_FLOAT:
            if (0 == strcmp(ZBX_TYPE_FLOAT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_UINT:
            if (0 == strcmp(ZBX_TYPE_UINT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_LONGTEXT:
            if (0 == strcmp(ZBX_TYPE_LONGTEXT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_SHORTTEXT:
            if (0 == strcmp(ZBX_TYPE_SHORTTEXT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_TEXT:
            if (0 == strcmp(ZBX_TYPE_TEXT_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_BLOB:
            if (0 == strcmp(ZBX_TYPE_BLOB_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_CUID:
            if (0 == strcmp(ZBX_TYPE_CHAR_STR, type_name))
                return SUCCEED;
            break;
        case ZBX_TYPE_SERIAL:
            if (0 == strcmp(ZBX_TYPE_SERIAL_STR, type_name))
                return SUCCEED;
            break;
        default:
            return FAIL;
    }    return FAIL;
}

/******************************************************************************
 *                                                                            *
 * Purpose: checks if the column of specific table in the database has        *
 *              correct type                                                  *
 *                                                                            *
 * Return value: SUCCEED - Column has correct type                            *
 *               FAIL    - Otherwise                                          *
 ******************************************************************************/
int zbx_db_check_postgresql_column_type(const char *table_name, const char *column_name, int expected_type)
{
    zbx_db_result_t  result = NULL;
    zbx_db_row_t     row;
    int              ret = FAIL;

    if (NULL == (result = zbx_db_select(
            "select pg_typeof(%s) from %s LIMIT 1",
            column_name, table_name)))
    {
        goto clean;
    }
    if (NULL != (row = zbx_db_fetch(result)))
    {
        ret = validate_db_type_name(expected_type, row[0]);
    }
clean:
    zbx_db_free_result(result);
    
    return ret;
}
#endif

Then the following db patches need to check the field type by calling the function zbx_db_check_postgresql_column_type, if postgresql, e.g.

#if defined(HAVE_POSTGRESQL)
if (SUCCEED == zbx_db_check_postgresql_column_type("history","value",ZBX_TYPE_FLOAT))
    return SUCCEED;
#endif
  • DBpatch_6050008
  • DBpatch_6050009
  • DBpatch_6050010
  • DBpatch_6050011

The above code is untested.

 

 

Comment by Thiago Murilo Diniz [ 2023 Oct 01 ]

Same thing here. I tried updating to version 7.0.0alpha6 (revision fdc49f9).

Both from the version 6.0.21 and from 6.4.6 I had the same problem. The only difference is that when I tried to update from 6.4.6 I received the message below a few times before it was able to continue with the update process.

 cannot perform database upgrade: node "<standalone server>" is still running, if node is unreachable it will be skipped in 2s
 Zabbix Server stopped. Zabbix 7.0.0alpha6 (revision fdc49f9).

Thanks

Comment by Rodrigo P [ 2023 Nov 05 ]

Hi Zabbix team.

Any news on this issue? We are unable to upgrade installations running Zabbix + Postgres Timescale at this moment and test/validate new features.

Thank you!

Comment by dimir [ 2023 Nov 06 ]

Will try to get some attention to this.

Comment by Vladislavs Sokurenko [ 2023 Nov 13 ]

Possible workaround is mentioned here:
https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_primary_keys

Comment by Vladislavs Sokurenko [ 2023 Nov 13 ]

Also please see https://github.com/timescale/timescaledb/issues/2145

Comment by Adrian Cunnelly [ 2023 Nov 13 ]

The issue is that the database table doesn't need updating as the fields are already the correct type, so the upgrade process should check the column type before attempting the table update.  A similar thing is already done if the database is Oracle.

Comment by Vladislavs Sokurenko [ 2023 Nov 13 ]

Thank you for clarification, this can be done.

Comment by Vladislavs Sokurenko [ 2023 Nov 16 ]

Fixed in:

Comment by Thiago Murilo Diniz [ 2023 Nov 28 ]

Hi Zabbix team, thanks for the great work!

I tested the update here, from version 6.0 and 6.4 (running timescaledb.sql before starting the updated zabbix-server to convert the auditlog table), and everything seems to work fine.
The only log I noticed was the one below, but I believe it is not a problem.

NOTICE:  function items_name_upper_upper() does not exist, skipping
Comment by Eugene Mihaylovskiy [ 2023 Dec 12 ]

Hello
I get same error

Comment by Eugene Mihaylovskiy [ 2023 Dec 12 ]

I was rollback my VM and install correct version timescaledb, but problem continued. I'm upgrading zabbix from 5.4.12 to 7.0.0alpha8 

Comment by Arturs Dancis [ 2023 Dec 12 ]

Documentation (7.0) updated:

Generated at Tue Apr 15 09:32:05 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.