Description of problem:
The DWH is unable to insert new records to the ovirt-history-db as some of the sequences next value contain higher number than the primary key can store.
4944 2014-07-16 08:37:06|MO7N73|QHsJuZ|QtaPvp|OVIRT_ENGINE_DWH|SampleTimeKeepingJob|Default|6|Java Exception|tRunJob_1|java.lang.RuntimeException:Child job running failed|1
4945 Exception in component tJDBCOutput_4
4946 org.postgresql.util.PSQLException: ERROR: integer out of range
4947 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
4948 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
4949 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
4950 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
4951 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
4952 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321)
4953 at ovirt_engine_dwh.statisticssync_3_4.StatisticsSync.tJDBCInput_8Process(StatisticsSync.java:7788)
4954 at ovirt_engine_dwh.statisticssync_3_4.StatisticsSync$5.run(StatisticsSync.java:17391)
4955 2014-07-16 08:37:09|FB0naJ|QHsJuZ|fkCOaB|OVIRT_ENGINE_DWH|StatisticsSync|Default|6|Java Exception|tJDBCOutput_4|org.postgresql.util.PSQLException:ERROR: integer out of range|1
4956 Exception in component tJDBCOutput_5
4957 org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
4958 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
ERROR: integer out of range
STATEMENT: INSERT INTO host_interface_samples_history (history_datetime,host_interface_id,receive_rate_percent,transmit_rate_percent,host_interface_configuration_version) VALUES ($1,$2,$3,$4,$5)
ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: INSERT INTO vm_interface_samples_history (history_datetime,vm_interface_id,receive_rate_percent,transmit_rate_percent,vm_interface_configuration_version) VALUES ($1,$2,$3,$4,$5)
Version-Release number of selected component (if applicable):
100% if the service runs for logn time.
Steps to Reproduce:
This is not easy to reproduce as it need really long time to insert enough
records to the DB.
It can be triggered by the following command:
SELECT pg_catalog.setval('host_interface_history_seq1', 2147491275, true);
The data are not collected any more
The collection continues
The sequences are created in the following way
CREATE SEQUENCE host_interface_history_seq1
START WITH 1
INCREMENT BY 1
There is not max value set. The primary key of the related table is an integer
(-2147483648 to +2147483647)
It would be more suitable to use bigserial for autoincrementing values.
We may also set the max value and set CYCLE option for tables where we
periodically remove the old content.
We should change all columns to bigint.
Barak, what do you think we should do regarding the legacy views?
If we change the column type to bigint it can brake their api and if we dont they will not they may hit the int limit.
This needs to be addressed by moving to big int.
The might only influence customers who upgrade to 3.5 and still use older viewes.
I don't thing there are any such customers
This fix should go in with appropriate release note message (for 3.5)
I totally agree with Barak. Releasing this fix with a proper release not seems like the solution we need here.
With regards to customers using legacy views, I don't really think we have any customer using these on never versions.
Why did this fail qe?
Behaviour is the same as in description. using vt2.2
Can you please attach the logs?
This bug need to be checked on upgrade from 3.4 to 3.5 and not between 3.5.0 versions.
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.
For information on the advisory, and where to find the updated
files, follow the link below.
If the solution does not work for you, open a new bug report.