{"action":"created","issue":{"url":"https://api.github.com/repos/openxpki/openxpki/issues/631","repository_url":"https://api.github.com/repos/openxpki/openxpki","labels_url":"https://api.github.com/repos/openxpki/openxpki/issues/631/labels{/name}","comments_url":"https://api.github.com/repos/openxpki/openxpki/issues/631/comments","events_url":"https://api.github.com/repos/openxpki/openxpki/issues/631/events","html_url":"https://github.com/openxpki/openxpki/issues/631","id":309283120,"number":631,"title":"Certificate Expiry Notification is broken after upgrade to 1.20","user":{"login":"StaceZ","id":19473052,"avatar_url":"https://avatars1.githubusercontent.com/u/19473052?v=4","gravatar_id":"","url":"https://api.github.com/users/StaceZ","html_url":"https://github.com/StaceZ","followers_url":"https://api.github.com/users/StaceZ/followers","following_url":"https://api.github.com/users/StaceZ/following{/other_user}","gists_url":"https://api.github.com/users/StaceZ/gists{/gist_id}","starred_url":"https://api.github.com/users/StaceZ/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/StaceZ/subscriptions","organizations_url":"https://api.github.com/users/StaceZ/orgs","repos_url":"https://api.github.com/users/StaceZ/repos","events_url":"https://api.github.com/users/StaceZ/events{/privacy}","received_events_url":"https://api.github.com/users/StaceZ/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-03-28T09:14:52Z","updated_at":"2018-03-30T14:28:00Z","closed_at":null,"author_association":"NONE","body":"My notify_expiry.yaml does not found any certificates after upgrade from 1.19 to 1.20.\r\n\r\nIf server started with debug: \"**openxpkictl start --debug OpenXPKI::Server::Workflow::Activity::Tools::SearchCertificates:64**\" I see:\r\n\r\n---\r\n2018-03-28 15:53:16.681858 DEBUG:32 PID:4112 OpenXPKI::Server::Workflow::Activity::Tools::SearchCertificates::execute (line 159): Full query $VAR1 = {\r\n          'CERT_ATTRIBUTES' => [],\r\n          'ENTITY_ONLY' => 1,\r\n          'NOTAFTER' => {\r\n                          'OPERATOR' => 'BETWEEN',\r\n                          'VALUE' => [\r\n                                       1522227196,\r\n                                       1523436796\r\n                                     ]\r\n                        },\r\n          'NOTBEFORE' => {\r\n                           'OPERATOR' => 'LESS_THAN',\r\n                           'VALUE' => 1522227196\r\n                         },\r\n          'STATUS' => 'ISSUED'\r\n        };\r\n\r\n2018-03-28 15:53:17.261367 DEBUG:64 PID:4112 OpenXPKI::Server::Workflow::Activity::Tools::SearchCertificates::execute (line 162): Search returned $VAR1 = [];\r\n\r\n---\r\n\r\nBut \"**openxpkiadm certificate list --realm ca-NPK -all -v -v -v**\" show certificate expired soon:\r\n\r\n---\r\n\r\n Identifier: G4EGUuv1CujY6zysB9K0uW7C0B0\r\n    Subject:      UID=planshet+CN=fda,DC=npk,DC=ru\r\n    Issuer DN:      emailAddress=hostmaster@npk.ru,CN=NPK CONTACT Registration Authority,OU=IT,O=NPK CONTACT,L=Novosibirsk,ST=Novosibirskaya Oblast,C=RU\r\n    Chain:      G4EGUuv1CujY6zysB9K0uW7C0B0 -> YpTRZvamM-NMJNlIt_FVeGBAEH0 -> JPUHlj0bqlqReF2O0lcRgB6TnWE(complete)\r\n    subject key identifier:      F1:B7:75:53:F1:F5:F6:B6:54:66:8B:DB:F7:36:F0:12:B5:93:5B:31\r\n    authority key identifier:      63:AA:EB:CF:6D:09:78:7D:29:CD:9E:1E:66:0D:A2:1E:CD:DF:9A:DE\r\n    cert key:      412369357278824652630783\r\n    issuer identifier:      YpTRZvamM-NMJNlIt_FVeGBAEH0\r\n    status:      ISSUED\r\n    notafter:      1522310840\r\n    notbefore:      1506672440\r\n    req key:      22527\r\n\r\n---\r\n"},"comment":{"url":"https://api.github.com/repos/openxpki/openxpki/issues/comments/377535784","html_url":"https://github.com/openxpki/openxpki/issues/631#issuecomment-377535784","issue_url":"https://api.github.com/repos/openxpki/openxpki/issues/631","id":377535784,"user":{"login":"StaceZ","id":19473052,"avatar_url":"https://avatars1.githubusercontent.com/u/19473052?v=4","gravatar_id":"","url":"https://api.github.com/users/StaceZ","html_url":"https://github.com/StaceZ","followers_url":"https://api.github.com/users/StaceZ/followers","following_url":"https://api.github.com/users/StaceZ/following{/other_user}","gists_url":"https://api.github.com/users/StaceZ/gists{/gist_id}","starred_url":"https://api.github.com/users/StaceZ/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/StaceZ/subscriptions","organizations_url":"https://api.github.com/users/StaceZ/orgs","repos_url":"https://api.github.com/users/StaceZ/repos","events_url":"https://api.github.com/users/StaceZ/events{/privacy}","received_events_url":"https://api.github.com/users/StaceZ/received_events","type":"User","site_admin":false},"created_at":"2018-03-30T14:27:59Z","updated_at":"2018-03-30T14:27:59Z","author_association":"NONE","body":"I think the error somewhere in the API ;(\r\n'NOTAFTER' in JSON query converted to \"certificate.notbefore\" in SQL ;(((\r\n\r\nDetails: \r\n1) notify_expiry.yaml:\r\n```initialize:\r\n        class: OpenXPKI::Server::Workflow::Activity::Tools::SearchCertificates\r\n        param:\r\n          entity_only: 1\r\n          target_key: tmp_queue\r\n          cutoff_notafter: +000030\r\n```\r\n2) Full query $VAR1 = {\r\n```          'CERT_ATTRIBUTES' => [],\r\n          'ENTITY_ONLY' => 1,\r\n          'NOTAFTER' => {\r\n                          'OPERATOR' => 'BETWEEN',\r\n                          'VALUE' => [\r\n                                       1521549059,\r\n                                       1530966659\r\n                                     ]\r\n                        },\r\n          'NOTBEFORE' => {\r\n                           'OPERATOR' => 'LESS_THAN',\r\n                           'VALUE' => 1522413059\r\n                         },\r\n          'STATUS' => 'ISSUED'\r\n        };\r\n```\r\n3) Generated MySQL query:\r\nSELECT certificate.* FROM certificate WHERE ( ( ( certificate.notbefore BETWEEN '1522419581' AND '1525011583' ) AND certificate.pki_realm = 'ca-NPK' AND certificate.req_key IS NOT NULL AND certificate.status = 'ISSUED' ) ) ORDER BY certificate.cert_key DESC\r\n\r\n"}}
{"actor":{"display_login":"StaceZ"}}