Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-15220

DB2. not last value displayed on "latest data" page for values received in the same second

    Details

    • Team:
      Team B
    • Sprint:
      Sprint 47, Dec 2018, Sprint 48, Jan 2019
    • Story Points:
      1

      Description

      Condition: use DB2 dba backend.

      Send 2 values to a zabbix trapper items in the same second.
      These values correctly saved to database with increasing nanoseconds.

      But on latest data page, an SQL:

      SELECT * FROM (SELECT * FROM history h WHERE h.itemid='91702' AND h.clock>1543221807 ORDER BY h.clock DESC) WHERE rownum BETWEEN 0 AND 2
      

      Gives incorrectly sorted values.

      A base of the query with better formatting for readability:

      SELECT
       ITEMID,
       CLOCK,
       timestamp('1970-01-01-02.00.00') + (clock) seconds as clock_converted,
       cast (VALUE as Integer) as VALUE,
       NS
      FROM history h WHERE h.itemid='91702' AND h.clock>1543221807 ORDER BY h.clock DESC
      

      gives this result:

      ITEMID CLOCK CLOCK_CONVERTED VALUE NS
      91702 1543316406 2018-11-27 13:00:06.0 0 49716928
      91702 1543316400 2018-11-27 13:00:00.0 -1 518098657
      91702 1543315501 2018-11-27 12:45:01.0 0 306570805
      91702 1543315500 2018-11-27 12:45:00.0 -1 877559319
      91702 1543314601 2018-11-27 12:30:01.0 -1 137989694
      91702 1543314601 2018-11-27 12:30:01.0 0 542263130
      91702 1543313701 2018-11-27 12:15:01.0 0 619180272
      91702 1543313701 2018-11-27 12:15:01.0 -1 179899176
      91702 1543312801 2018-11-27 12:00:01.0 0 62977420
      91702 1543312800 2018-11-27 12:00:00.0 -1 476845716
      91702 1543311901 2018-11-27 11:45:01.0 0 59055749
      91702 1543311900 2018-11-27 11:45:00.0 -1 572813242
      91702 1543311001 2018-11-27 11:30:01.0 0 99963819
      91702 1543311000 2018-11-27 11:30:00.0 -1 573168019
      91702 1543310101 2018-11-27 11:15:01.0 -1 158478434
      91702 1543310101 2018-11-27 11:15:01.0 0 612926004
      91702 1543309201 2018-11-27 11:00:01.0 0 500864830
      91702 1543309200 2018-11-27 11:00:00.0 -1 971772850
      91702 1543308301 2018-11-27 10:45:01.0 0 250319891
      91702 1543308300 2018-11-27 10:45:00.0 -1 786422790
      91702 1543307401 2018-11-27 10:30:01.0 -1 322586092
      91702 1543307401 2018-11-27 10:30:01.0 0 744526148
      91702 1543306501 2018-11-27 10:15:01.0 -1 70795220
      91702 1543306501 2018-11-27 10:15:01.0 0 522244003
      91702 1543305601 2018-11-27 10:00:01.0 0 535815892
      91702 1543305600 2018-11-27 10:00:00.0 -1 751398435
      91702 1543304700 2018-11-27 09:45:00.0 -1 473536705
      91702 1543304700 2018-11-27 09:45:00.0 0 980123686
      91702 1543303801 2018-11-27 09:30:01.0 0 388139580
      91702 1543303800 2018-11-27 09:30:00.0 -1 894647950
      91702 1543302900 2018-11-27 09:15:00.0 -1 512084790
      91702 1543302900 2018-11-27 09:15:00.0 0 962628699
      91702 1543302002 2018-11-27 09:00:02.0 0 252073993
      91702 1543302001 2018-11-27 09:00:01.0 -1 116854415
      91702 1543301102 2018-11-27 08:45:02.0 0 132886542
      91702 1543301100 2018-11-27 08:45:00.0 -1 832285467
      91702 1543300202 2018-11-27 08:30:02.0 0 395116534
      91702 1543300201 2018-11-27 08:30:01.0 -1 222990553
      91702 1543299301 2018-11-27 08:15:01.0 0 965572602
      91702 1543299300 2018-11-27 08:15:00.0 -1 797882315
      91702 1543298401 2018-11-27 08:00:01.0 -1 408101788
      91702 1543298401 2018-11-27 08:00:01.0 0 867734257
      91702 1543297501 2018-11-27 07:45:01.0 0 356652887
      91702 1543297500 2018-11-27 07:45:00.0 -1 951245656
      91702 1543296601 2018-11-27 07:30:01.0 0 188889274
      91702 1543296600 2018-11-27 07:30:00.0 -1 786262142
      91702 1543295700 2018-11-27 07:15:00.0 -1 487497914
      91702 1543295700 2018-11-27 07:15:00.0 0 876591116
      91702 1543294801 2018-11-27 07:00:01.0 -1 141648081
      91702 1543294801 2018-11-27 07:00:01.0 0 561122489
      91702 1543293901 2018-11-27 06:45:01.0 0 442211281
      91702 1543293900 2018-11-27 06:45:00.0 -1 996500273
      91702 1543293001 2018-11-27 06:30:01.0 0 252557497
      91702 1543293000 2018-11-27 06:30:00.0 -1 828542131
      91702 1543292100 2018-11-27 06:15:00.0 -1 521219616
      91702 1543292100 2018-11-27 06:15:00.0 0 922880230
      91702 1543291201 2018-11-27 06:00:01.0 0 335926336
      91702 1543291200 2018-11-27 06:00:00.0 -1 878680224
      91702 1543290301 2018-11-27 05:45:01.0 0 144783269
      91702 1543290300 2018-11-27 05:45:00.0 -1 750749051
      91702 1543289400 2018-11-27 05:30:00.0 -1 530470220
      91702 1543289400 2018-11-27 05:30:00.0 0 943024748
      91702 1543288500 2018-11-27 05:15:00.0 -1 238426848
      91702 1543288500 2018-11-27 05:15:00.0 0 627522803
      91702 1543287601 2018-11-27 05:00:01.0 0 379002853
      91702 1543287600 2018-11-27 05:00:00.0 -1 979021121
      91702 1543286701 2018-11-27 04:45:01.0 0 331773576
      91702 1543286700 2018-11-27 04:45:00.0 -1 920792017
      91702 1543285801 2018-11-27 04:30:01.0 0 130366916
      91702 1543285800 2018-11-27 04:30:00.0 -1 752896853
      91702 1543284900 2018-11-27 04:15:00.0 -1 574529927
      91702 1543284900 2018-11-27 04:15:00.0 0 958170375
      91702 1543284001 2018-11-27 04:00:01.0 -1 107106370
      91702 1543284001 2018-11-27 04:00:01.0 0 748101697
      91702 1543283101 2018-11-27 03:45:01.0 0 293315766
      91702 1543283100 2018-11-27 03:45:00.0 -1 910485356
      91702 1543282201 2018-11-27 03:30:01.0 0 23480920
      91702 1543282200 2018-11-27 03:30:00.0 -1 631885459
      91702 1543281301 2018-11-27 03:15:01.0 0 698530576
      91702 1543281300 2018-11-27 03:15:00.0 -1 496281824
      91702 1543280401 2018-11-27 03:00:01.0 -1 147830463
      91702 1543280401 2018-11-27 03:00:01.0 0 697430715
      91702 1543279501 2018-11-27 02:45:01.0 0 180849870
      91702 1543279500 2018-11-27 02:45:00.0 -1 788505896
      91702 1543278600 2018-11-27 02:30:00.0 -1 393654854
      91702 1543278600 2018-11-27 02:30:00.0 0 812131463
      91702 1543277700 2018-11-27 02:15:00.0 -1 271179772
      91702 1543277700 2018-11-27 02:15:00.0 0 682740973
      91702 1543276800 2018-11-27 02:00:00.0 -1 487881612
      91702 1543276800 2018-11-27 02:00:00.0 0 913783389
      91702 1543275901 2018-11-27 01:45:01.0 -1 115353930
      91702 1543275901 2018-11-27 01:45:01.0 0 522683998
      91702 1543275001 2018-11-27 01:30:01.0 0 22352141
      91702 1543275000 2018-11-27 01:30:00.0 -1 632489066
      91702 1543274100 2018-11-27 01:15:00.0 -1 330797789
      91702 1543274100 2018-11-27 01:15:00.0 0 751846975
      91702 1543273201 2018-11-27 01:00:01.0 0 387214282
      91702 1543273200 2018-11-27 01:00:00.0 -1 765444377
      91702 1543272300 2018-11-27 00:45:00.0 -1 250691462
      91702 1543272300 2018-11-27 00:45:00.0 0 655103662
      91702 1543271401 2018-11-27 00:30:01.0 -1 79796966
      91702 1543271401 2018-11-27 00:30:01.0 0 490462614
      91702 1543270501 2018-11-27 00:15:01.0 0 385471270
      91702 1543270500 2018-11-27 00:15:00.0 -1 975341991
      91702 1543269601 2018-11-27 00:00:01.0 0 282338577
      91702 1543269600 2018-11-27 00:00:00.0 -1 771556799
      91702 1543268701 2018-11-26 23:45:01.0 -1 217142365
      91702 1543268701 2018-11-26 23:45:01.0 0 740441257
      91702 1543267801 2018-11-26 23:30:01.0 -1 206626718
      91702 1543267801 2018-11-26 23:30:01.0 0 589179261
      91702 1543266901 2018-11-26 23:15:01.0 -1 184361439
      91702 1543266901 2018-11-26 23:15:01.0 0 591638876
      91702 1543266001 2018-11-26 23:00:01.0 0 295867250
      91702 1543266000 2018-11-26 23:00:00.0 -1 899993910
      91702 1543265101 2018-11-26 22:45:01.0 0 255691800
      91702 1543265100 2018-11-26 22:45:00.0 -1 882870243
      91702 1543264201 2018-11-26 22:30:01.0 0 233496761
      91702 1543264200 2018-11-26 22:30:00.0 -1 852847350
      91702 1543263301 2018-11-26 22:15:01.0 0 257234761
      91702 1543263300 2018-11-26 22:15:00.0 -1 849443774
      91702 1543262401 2018-11-26 22:00:01.0 0 157438608
      91702 1543262400 2018-11-26 22:00:00.0 -1 734558905
      91702 1543261501 2018-11-26 21:45:01.0 0 41097313
      91702 1543261500 2018-11-26 21:45:00.0 -1 653603380
      91702 1543260600 2018-11-26 21:30:00.0 -1 595440828
      91702 1543260600 2018-11-26 21:30:00.0 0 989400052
      91702 1543259700 2018-11-26 21:15:00.0 -1 460560530
      91702 1543259700 2018-11-26 21:15:00.0 0 909424029
      91702 1543258801 2018-11-26 21:00:01.0 0 369127587
      91702 1543258800 2018-11-26 21:00:00.0 -1 742779529
      91702 1543257901 2018-11-26 20:45:01.0 0 340196886
      91702 1543257900 2018-11-26 20:45:00.0 -1 825428525
      91702 1543257000 2018-11-26 20:30:00.0 -1 455919971
      91702 1543257000 2018-11-26 20:30:00.0 0 878585879
      91702 1543256101 2018-11-26 20:15:01.0 -1 113022715
      91702 1543256101 2018-11-26 20:15:01.0 0 612015606
      91702 1543255201 2018-11-26 20:00:01.0 0 339520566
      91702 1543255200 2018-11-26 20:00:00.0 -1 906323000
      91702 1543254301 2018-11-26 19:45:01.0 0 7379978
      91702 1543254300 2018-11-26 19:45:00.0 -1 582138918
      91702 1543253401 2018-11-26 19:30:01.0 -1 117075630
      91702 1543253401 2018-11-26 19:30:01.0 0 731137371
      91702 1543252501 2018-11-26 19:15:01.0 0 276641608
      91702 1543252500 2018-11-26 19:15:00.0 -1 684483047
      91702 1543251601 2018-11-26 19:00:01.0 0 295392247
      91702 1543251600 2018-11-26 19:00:00.0 -1 676952300
      91702 1543250700 2018-11-26 18:45:00.0 -1 488759851
      91702 1543250700 2018-11-26 18:45:00.0 0 935841810
      91702 1543249800 2018-11-26 18:30:00.0 -1 321084815
      91702 1543249800 2018-11-26 18:30:00.0 0 820481663
      91702 1543248900 2018-11-26 18:15:00.0 -1 239024542
      91702 1543248900 2018-11-26 18:15:00.0 0 687782944
      91702 1543248001 2018-11-26 18:00:01.0 0 50081128
      91702 1543248000 2018-11-26 18:00:00.0 -1 369301201
      91702 1543247100 2018-11-26 17:45:00.0 -1 474511762
      91702 1543247100 2018-11-26 17:45:00.0 0 885206958
      91702 1543246201 2018-11-26 17:30:01.0 0 343458627
      91702 1543246200 2018-11-26 17:30:00.0 -1 821009693
      91702 1543245458 2018-11-26 17:17:38.0 0 121803396
      91702 1543245457 2018-11-26 17:17:37.0 -1 769677017
      91702 1543245301 2018-11-26 17:15:01.0 -1 238315213
      91702 1543245301 2018-11-26 17:15:01.0 0 631474998
      91702 1543244401 2018-11-26 17:00:01.0 0 251466114
      91702 1543244400 2018-11-26 17:00:00.0 -1 754783269
      91702 1543244220 2018-11-26 16:57:00.0 0 311592290
      91702 1543244219 2018-11-26 16:56:59.0 -1 928967639
      91702 1543244163 2018-11-26 16:56:03.0 0 273147587
      91702 1543244162 2018-11-26 16:56:02.0 -1 911504471
      91702 1543244151 2018-11-26 16:55:51.0 -1 99215735
      91702 1543244151 2018-11-26 16:55:51.0 0 486041785
      91702 1543244107 2018-11-26 16:55:07.0 -1 724338024
      91702 1543243862 2018-11-26 16:51:02.0 -1 851283430
      91702 1543243730 2018-11-26 16:48:50.0 -1 375207348
      91702 1543243500 2018-11-26 16:45:00.0 -1 949237133
      91702 1543243175 2018-11-26 16:39:35.0 -1 486944489
      91702 1543243175 2018-11-26 16:39:35.0 0 871837432
      91702 1543243059 2018-11-26 16:37:39.0 -1 513687265
      91702 1543242991 2018-11-26 16:36:31.0 -1 634703907
      91702 1543242967 2018-11-26 16:36:07.0 -1 619855144
      91702 1543242868 2018-11-26 16:34:28.0 -1 791694257
      91702 1543242834 2018-11-26 16:33:54.0 -1 438446070
      91702 1543240992 2018-11-26 16:03:12.0 -1 630977584
      91702 1543229100 2018-11-26 12:45:00.0 -1 807840318
      91702 1543228441 2018-11-26 12:34:01.0 0 466917034
      91702 1543228085 2018-11-26 12:28:05.0 -1 686364481
      91702 1543227569 2018-11-26 12:19:29.0 -1 624725081

      SQL:

      SELECT * FROM (--previous SQL--)
      WHERE rownum BETWEEN 0 AND 2
      

      returns only first 2 lines of previous result:

      ITEMID CLOCK CLOCK_CONVERTED VALUE NS
      91702 1543316406 2018-11-27 13:00:06.0 0 49716928
      91702 1543316400 2018-11-27 13:00:00.0 -1 518098657

      Maybe something like sorting by NS should be added.:

      SELECT * FROM history h WHERE h.itemid='91702' AND h.clock>1543221807 order by CLOCK desc, NS desc fetch first 2 rows only
      

      In PHP these 2 values are considered as already sorted:

      	$lastHistory = isset($history[$item['itemid']][0]) ? $history[$item['itemid']][0] : null;
      	$prevHistory = isset($history[$item['itemid']][1]) ? $history[$item['itemid']][1] : null;
      

      so, from 2 values 1st one will be considered as "Latest value" 

       

      We observed this behavior in frontend many times, but I think screenshots are redundant.

      Just in case - on MySQL backend everything is fine itself:

      SELECT * FROM history_uint h WHERE h.itemid='28515' AND h.clock>1543226958 ORDER BY h.clock DESC LIMIT 2
      

      where 1543226958 is timestanm for last 24hours - for safety as we know.
      result:

      +--------+------------+-------+-----------+
      | itemid | clock      | value | ns        |
      +--------+------------+-------+-----------+
      |  28515 | 1543313069 |    33 | 809401834 |
      |  28515 | 1543313069 |    22 | 805209006 |
      +--------+------------+-------+-----------+

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sasha Alexander Vladishev
                Reporter:
                zalex_ua Oleksiy Zagorskyi
              • Votes:
                1 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: