View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000922 | Main CAcert Website | account administration | public | 2011-04-21 15:27 | 2013-11-12 21:13 |
| Reporter | wytze | Assigned To | NEOatNHNG | ||
| Priority | high | Severity | major | Reproducibility | sometimes |
| Status | closed | Resolution | fixed | ||
| Product Version | 2011 Q2 | ||||
| Target Version | 2013 Q2 | Fixed in Version | 2013 Q2 | ||
| Summary | 0000922: CAcert application code problem causing missing "certificate about to expire" messages | ||||
| Description | While investigating CAcert Support case "[s20110413.54] [CAcert.org] Your certificate / expired, no warning" I have discovered that the current code base is not functioning properly. When creating a new domain certificate (or renewing one), a new record is inserted in the domaincerts table. To complete this operation there should also be a new record inserted in the domlink table, linking the new domaincerts record with an existing domains record. But this does not always happen (for reasons you may be able to explain, I can't ...). As a result, many domaincerts are present in the current database without an associated domlink record. The scripts/warning.php script, which is used to send warnings for "about to expire" certificates to users, relies on the presence of this domlink record to send out warnings for expiring domaincerts, viz. this code: $query = "SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `users`, `domaincerts`, `domlink`, `domains` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < $day * 86400 AND `domaincerts`.`renewed`=0 AND `domaincerts`.`warning` <= '$warning' AND `domaincerts`.`revoked`=0 AND `users`.`id` = `domains`.`memid` AND `domlink`.`certid` = `domaincerts`.`id` AND `domains`.`id` = `domlink`.`domid`"; The last line in this code snippet is the crucial one: `domlink`.`certid` = `domaincerts`.`id` AND `domains`.`id` = `domlink`.`domid`" When no domlink record is present, the domaincert will simply never be found by this script. Please apply your expertise to determine whether the scripts/warning.php code is wrong (so that last line should be rewritten to avoid using a domlink record?), or whether the code in includes/account.php is incorrect, because it is sometimes failing to create a domlink record for a new domaincerts record. | ||||
| Additional Information | proposed notifications: 1d, 15d, 30d, 45d before expiry | ||||
| Tags | No tags attached. | ||||
| Reviewed by | NEOatNHNG, BenBE | ||||
| Test Instructions | |||||
| related to | 0000911 | closed | NEOatNHNG | Wrong expiration time in newly added GPG Key if Key has no Expire date |
| related to | 0000820 | new | no warning mails for organization assurance certs | |
| related to | 0001078 | new | Cleanup of ambigiously used variables | |
| related to | 0000089 | needs work | Sourcerer | GPG Revokation Escrow Service |
| related to | 0001079 | needs work | GPG key can not be revoked | |
| related to | 0001026 | needs work | Uli60 | Server Certificate was revoked but not by the user |
| related to | 0000483 | closed | INOPIAE | Please send more verbose emails concerning certificate revocation |
|
|
Having studied the code base for a little while, I am now wondering why this "domlink" table even exists in the CAcert database. It is used only in two places, and it appears that those queries can easily be rewritten to gather the same data without using domlink. Maybe somebody with a better understanding of the application and the database can prove me wrong. Here's the suggested solution for one of the two cases, the problem we have on hand now, the scripts/warning.php script. Replace the query for expiring domain certificates by this: $query = "SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`expire`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `domaincerts`, `domains`, `users` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < $day * 86400 AND `domaincerts`.`renewed`=0 AND `domaincerts`.`warning` <= '$warning' AND `domaincerts`.`revoked`=0 AND `domaincerts`.`domid` = `domains`.`id` AND `users`.`id` = `domains`.`memid`"; If the application experts can agree with this, we have a quick and painless solution for the missing "certificate about to expire" msgs, since we wouldn't have to worry anymore about the missing domlink records in the database. |
|
|
Not that easy. domlink is important for revoking certs when a domain is deleted. Essentially domlinks does a n:m relation between domains and certificates. |
|
|
There is a fix proposal presented in the bug report, but the main problem with this fix is, the fix cannot be tested :-P so software-assessors have to do a deep code review, to decide if they'll want to apply this patch or not w/o testing testing requires manual modification of expire dates for issued certs that cannot be handled with the current testserver environment. The "automated" expire of issued certs will first come in effect in about 6 months (if WoT user certs are used) |
|
|
The proposed fix can easily be tested within the current testserver environment by temporarily adjusting the values in the $days array employed by the warning code to issue the 'about to expire' warnings much earlier than in real life. |
|
|
> Not that easy. domlink is important for revoking certs when a domain is > deleted. Essentially domlinks does a n:m relation between domains and > certificates. for certs-going-to-be expire missing domlinks is worthless regarding certs going to be expire. There are certs listed in the domaincerts table and this needs to be notified to the user. The active certs counts and the user can be identified with the infos from the domaincerts table. If there are missing links in the domainlinks table, maybe - this is subject to another bug# if eg domaincerts to remove needs to be handled through the domainlinks table and missing links prevents the removal of some domain certs domaincert (servercert) found going to be expire => domaincert.id with data available: domaincert.domid (serial and other infos too) domaincert.domid relates direct to domains.id found domains.id record has domains.memid domains.memid realates direct to users.id, all the users infos incl. primary email address to contact the user |
|
|
Completely getting rid of the domlink table in the databse will not be possible because as I said the table is used to store which certificates are contained in a certificate. However I have adjusted the SQL queries in the warning.php cron job so they are more robust against missing domlink entries (only domaincerts.domid or domlink.domid needs to be present) Searching for other occurrences of the domlink table this also seems to be a problem when deleting a domain (my commit message is misleading here, it is when you delete one of your own domains not on dispute, although there might be a bug that certs are not properly revoked on dispute too). I also tried to figure out why the domlinks are missing in the first place but the code in that area is quite complex and confusing so I'm not able to tell yet. Please test and review the changes. |
|
|
Created new certs on 2012-06-02 needs review on 2012-07-02 a client cert for mm@inopiae.com 110A on 2012-06-02 expiring 2012-07-02 a server cert for www.inopiae.com 10B9 on 2012-06-02 expiring 2012-07-02 an org client certificate for m.maengel@test.inopiae.com 110B on 2012-06-02 expiring 2012-07-02 an org server certificate for test.inopiae.com 10BA on 2012-06-02 expiring 2012-07-02 |
|
|
added user bug922.user1@w.de 0 pts user create client cert class1, serno 110E Not Revoked 2012-06-08 21:50:18 create server cert class1, serno 110F Not Revoked 2012-06-08 22:20:24 added user bug922.user2@w.de 100 pts, is-assurer user client certs class1 wot_user class1 bug922.user2 class3 wot_user Valid bug922.user2@w.de 10BC Not Revoked 2012-07-05 23:01:39 Valid bug922.user2@w.de 10BB Not Revoked 2012-07-05 23:01:01 Valid bug922.user2@w.de 1111 Not Revoked 2012-07-05 23:00:20 Valid bug922.user2@w.de 1110 Not Revoked 2012-07-05 22:59:51 server certs class1 class3 class3 serno 10BD Not Revoked 2012-07-05 23:10:54 class1 serno 1112 Not Revoked 2012-07-05 23:09:23 orgadmin 3 orgs using Wiamail with 2 domains org client certs class1 Bug922 User3 class3 Bug922 User4 class3 bug922.user4@w.de 10BE Not Revoked 2012-06-12 23:35:36 class1 bug922.user3@w.de 1113 Not Revoked 2012-06-12 23:35:03 org server certs 1. class1 test2 2. class3 test2 class3 serno 10BF Not Revoked 2012-07-05 23:43:34 class1 serno 1114 Not Revoked 2012-07-05 23:40:20 gpg procedures see under bug#911 http://bugs.cacert.org/view.php?id=911 https://wiki.cacert.org/Software/CurrentTest/bug911 gpg --gen-key gpg --export --armor>ascii-key-filename.extension website - new gpg key error message: Please upload only one key at a time. For debugging: gpg -v ascii-key-filename.extension again: gpg --export --armor FD745B95>ascii-key-filename.extension paste content of ascii-key-filename.extension to gpg new field bug922.user2@w.de 2013-06-07 01:20:34 D489CD93FD745B95 |
|
|
review to session from 2012-06-06 check 1: 0 pts user bug922.user1@w.de clients certs view: empty, view all certs create client cert class1, serno 110E Not Revoked 2012-06-08 21:50:18 Expired 110E Not Revoked 2012-06-08 21:50:18 server certs view: empty, view all certs create server cert class1, serno 110F Not Revoked 2012-06-08 22:20:24 Expired 110F Not Revoked 2012-06-08 22:20:24 message client cert created, with link to download client cert Tue, 5 Jun 2012 23:50:21 with fingerprints: Root cert fingerprint = A6:1B:37:5E:39:0D:9C:36:54:EE:BD:20:31:46:1F:6B Root cert fingerprint = 135C EC36 F49C B8E9 3B1A B270 CD80 8846 76CE 8F33 fingerprints OK message server cert created, with link to download server cert Wed, 6 Jun 2012 00:20:27 reminders: Your Certificate is about to expire Wed, 6 Jun 2012 00:45:05 3 days time, client cert Wed, 6 Jun 2012 00:45:05 3 days time, server cert Wed, 6 Jun 2012 00:45:05 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert Wed, 6 Jun 2012 00:45:06 3 days time, server cert (15) 89 more upto Thu, 7 Jun 2012 22:45:05 1 days time, server cert ???? result is questionable result is questionable |
|
|
bug922.user2@w.de received 358 (!!!) notifications (!!!) for client/server certs details impossible to discover :-P using 4 client certs using 2 server certs using 1 gpg cert received 0 notification (!) (but the key is still valid: Expires Key ID Valid 2013-06-07 01:20:34 D489CD93FD745B95 |
|
|
two users cannot be found :( login impossible class3 cert, bug922.user4@w.de class1 cert, bug922.user3@w.de nor thru admin console find user :( nor thru TMS login :( found under orgadmin@w.de as w.de is under this OA enabled test account two client certs expired: class3 cert, bug922.user4@w.de class1 cert, bug922.user3@w.de Expired bug922.user4@w.de 10BE Not Revoked 2012-06-12 23:35:36 Expired bug922.user3@w.de 1113 Not Revoked 2012-06-12 23:35:03 two org server certs expired Expired test2.w.de 10BF Not Revoked 2012-07-05 23:43:34 Expired test2.w.de 1114 Not Revoked 2012-07-05 23:40:20 zero notifications received (!) under orgadmin@w.de user |
|
|
Win 7 IE 9.0 Assurer orgadmin@inopiae.com class1 high 111A 2012-08-23 without name class1 medium 111B 2012-08-23 with name class3 high 10C7 2012-08-23 with name class3 medium 10C8 2012-08-23 without name org client cert do not work WIN XP SP3 IE 6.0 1.964@acme.com 0 points class1 high 111C 2012-07-27 class1 medium 111D 2012-07-27 Win 7 Chrome12 2.964@acme.com 50 points class1 high grade 111A 2012-08-23 without name class1 mittlere stufe 111B 2012-08-23 with name class3 high grade 10C7 2012-08-23 with name class3 mittlere stufe 10C8 2012-08-23 without name orgcertificate orgadmin@inopiae.com 1.964@test.inopiae.com class3 mittlere stufe 10CC 2012-07-31 class3 high grade 1122 2012-07-31 class3 high grade 10CB 2012-07-31 class1 mittlere stufe 1121 2012-07-31 class1 high grade 1120 2012-07-31 With the IE the login flag is always set if ticked or not. With chrome it works. |
|
|
I have modified the SQL queries to only select distinct data set which should prevent the multiple mails. Please test and review. |
|
|
in bug#1054, bug#440 tests I've created a couple of certs client certs: VC VAC Valid certs.test@w.d 10D8 Not Revoked 2012-10-20 21:46:32 + + (1) Valid certs.test@w.d 115E Not Revoked 2012-10-20 21:37:02 + + (2) Valid certs.test@w.d 10D7 Not Revoked 2012-10-20 21:32:52 + + (3) Valid certs.test@w.d 115D Not Revoked 2012-10-20 21:26:44 + + (4) Valid certs.test@w.d 10D6 Not Revoked 2012-10-20 21:14:31 + + (5) and 2 client certs that I've later renewed Valid certs.test@w.d 1161 Not Revoked 2012-10-21 13:02:39 + (13) renewed by: Valid certs.test@w.d 1165 Not Revoked 2012-10-21 21:41:56 + + (15) Valid certs.test@w.d 115C Not Revoked 2012-10-20 21:04:00 + (6) renewed by: Valid certs.test@w.d 1164 Not Revoked 2012-10-21 21:26:44 + + (14) (vc) view certs https://cacert1.it-sls.de/account.php?id=5 (vac) view all certs https://cacert1.it-sls.de/account.php?id=5&viewall=1 7 valid client certs + 2 still valid replaced certs server certs: vc vac Valid test1.avintec.com 1162 Not Revoked 2012-10-21 13:50:27 + + (9) Valid test1.avintec.com 1160 Not Revoked 2012-10-21 12:43:39 + + (8) Valid test1.avintec.com 115F Not Revoked 2012-10-21 12:19:20 + (7) replaced by: valid test1.avintec.com 1166 Not Revoked 2012-10-21 22:06:42 + + (11) Valid test1.avintec.com 1163 Not Revoked 2012-10-21 14:41:43 + (10) replaced by: Valid test1.avintec.com 1167 Not Revoked 2012-10-21 22:17:20 + + (12) 4 valid server certs + 2 still valid replaced certs 15 notifications "Your Cert is about to expire" received notifications client cert, 30 days, from Thu, 20 Sep 2012 23:45:01 +0200 (CEST) (1) client cert, 30 days, from Thu, 20 Sep 2012 23:45:01 +0200 (CEST) (2) client cert, 30 days, from Thu, 20 Sep 2012 23:45:01 +0200 (CEST) (3) client cert, 30 days, from Thu, 20 Sep 2012 23:45:01 +0200 (CEST) (4) client cert, 30 days, from Thu, 20 Sep 2012 23:45:01 +0200 (CEST) (5) client cert, 30 days, from Fri, 21 Sep 2012 00:45:03 +0200 (CEST) (6) server cert, 30 days, from Fri, 21 Sep 2012 14:45:01 +0200 (CEST) (7) server cert, 30 days, from Fri, 21 Sep 2012 14:45:01 +0200 (CEST) (8) client cert, 30 days, from Fri, 21 Sep 2012 15:45:02 +0200 (CEST) (13) server cert, 30 days, from Fri, 21 Sep 2012 16:45:03 +0200 (CEST) (9) server cert (SANs), 30 days, from Fri, 21 Sep 2012 16:45:04 +0200 (CEST) (10) client cert, 30 days, from Fri, 21 Sep 2012 23:45:01 +0200 (CEST) (14) client cert, 30 days, from Fri, 21 Sep 2012 23:45:02 +0200 (CEST) (15) server cert, 30 days, from Sat, 22 Sep 2012 00:45:02 +0200 (CEST) (11) server cert (SANs), 30 days, from Sat, 22 Sep 2012 00:45:02 +0200 (CEST) (12) trying to sort the received notifications to the related certs (numbering 1-15 see above) all 30 days notifications received for all client certs and all server certs. => ok see also test reports 2012-09-20 ff. under https://bugs.cacert.org/view.php?id=1054 with 15 received "Certs about to expire" messages, it will become helpful to give a hint which one of the certs is about to expire eg serial# or CertID (which is visible if you hover over the email or server link eg https://cacert1.it-sls.de/account.php?id=15&cert=302039) ^^^^^^ this makes also test reporting easier to relate a message to a distinctive cert |
|
|
2012-10-02 ca-mgr1 login certs.test@w.d, no new mail ~ day # 12 after first notification, about 3 days to go before next notification will be sent |
|
|
Certificate for orgadmin@inopiae.com class1 high 111A 2012-08-23 without name class1 medium 111B 2012-08-23 with name class3 high 10C7 2012-08-23 with name class3 medium 10C8 2012-08-23 without name org client cert do not work for each certificate one message with exirey in approx 1 day was received. (The previous mails ware deleted) For 2.964@acme.com there are some certificates with expiring date 2012-09-13 for each certificate I received a mail 30, 15 and 1 day before expiration. Every things seems ok. It would be great if the certificate number is qouted in the mail. |
|
|
Added serial number to the email |
|
|
Renew a client certificate for orgadmin@inopaie.com with SN 1168. The 30 day message shows now the SN. => ok |
|
|
bug1004.userb4@w.d created client cert Valid bug1004.userb4@w.d 1169 Not Revoked 2012-11-01 23:24:27 Hi bug1004, You are receiving this email as you are the listed contact for: /CN=bug1004.userb4@w.d/emailAddress=bug1004.userb4@w.d Your certificate with the serial number 1169 is set to expire in approximately 30 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=5 Best Regards CAcert Support => ok |
|
|
according to https://bugs.cacert.org/view.php?id=922#c3221 I've received 5 emails "[CAcert.org] Your Certificate is about to expire" on Fri Oct 5 4 emails 21:45 1 email 22:45 and addtl. 6 emails "[CAcert.org] Your Certificate is about to expire" 1 email 12:45 1 email 14:45 2 emails 21:45 2 emails 22:45 total: 11 notifications received in detail: Fri, 5 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D6 is set to expire in approximately 15 days time. Fri, 5 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 115D is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=5 Fri, 5 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D7 is set to expire in approximately 15 days time. Fri, 5 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 115E is set to expire in approximately 15 days time. Fri, 5 Oct 2012 22:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D8 is set to expire in approximately 15 days time. Sat, 6 Oct 2012 12:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1160 is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=12 Sat, 6 Oct 2012 14:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1162 is set to expire in approximately 15 days time. Sat, 6 Oct 2012 21:45:02 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 1164 is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=5 Sat, 6 Oct 2012 21:45:02 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 1165 is set to expire in approximately 15 days time. Sat, 6 Oct 2012 22:45:02 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1166 is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=12 Sat, 6 Oct 2012 22:45:02 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com /subjectAltName=DNS:mail.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:mail.avintec.com /subjectAltName=DNS:www.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:www.avintec.com /subjectAltName=DNS:www.fra.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:www.fra.avintec.com /subjectAltName=DNS:mx.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:mx.avintec.com /subjectAltName=DNS:support.avintec.com /subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:support.avintec.com Your certificate with the serial number 1167 is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=12 client certs listing: https://cacert1.it-sls.de/account.php?id=5 NTFY Valid certs.test@w.d 1165 Not Revoked 2012-10-21 21:41:56 NTFY Valid certs.test@w.d 1164 Not Revoked 2012-10-21 21:26:44 NTFY Valid certs.test@w.d 10D8 Not Revoked 2012-10-20 21:46:32 NTFY Valid certs.test@w.d 115E Not Revoked 2012-10-20 21:37:02 NTFY Valid certs.test@w.d 10D7 Not Revoked 2012-10-20 21:32:52 NTFY Valid certs.test@w.d 115D Not Revoked 2012-10-20 21:26:44 NTFY Valid certs.test@w.d 10D6 Not Revoked 2012-10-20 21:14:31 client certs listing -All- : https://cacert1.it-sls.de/account.php?id=5&viewall=1 NTFY Valid certs.test@w.d 1165 Not Revoked 2012-10-21 21:41:56 NTFY Valid certs.test@w.d 1164 Not Revoked 2012-10-21 21:26:44 Valid certs.test@w.d 1161 Not Revoked 2012-10-21 13:02:39 <== NV NTFY Valid certs.test@w.d 10D8 Not Revoked 2012-10-20 21:46:32 NTFY Valid certs.test@w.d 115E Not Revoked 2012-10-20 21:37:02 NTFY Valid certs.test@w.d 10D7 Not Revoked 2012-10-20 21:32:52 NTFY Valid certs.test@w.d 115D Not Revoked 2012-10-20 21:26:44 NTFY Valid certs.test@w.d 10D6 Not Revoked 2012-10-20 21:14:31 Valid certs.test@w.d 115C Not Revoked 2012-10-20 21:04:00 <== NV Expired certs.test@w.d 10A3 Not Revoked 2012-03-22 21:56:34 Expired certs.test@w.d 10D7 Not Revoked 2012-03-22 21:55:49 Expired certs.test@w.d 10A2 Not Revoked 2012-03-22 21:54:57 Expired certs.test@w.d 10D6 Not Revoked 2012-03-22 21:53:42 Expired certs.test@w.d 10A1 Not Revoked 2012-03-22 21:52:39 Expired certs.test@w.d 10D5 Not Revoked 2012-03-22 21:51:09 server certs listing: https://cacert1.it-sls.de/account.php?id=12 NTFY Valid test1.avintec.com 1167 Not Revoked 2012-10-21 22:17:20 NTFY Valid test1.avintec.com 1166 Not Revoked 2012-10-21 22:06:42 NTFY Valid test1.avintec.com 1162 Not Revoked 2012-10-21 13:50:27 NTFY Valid test1.avintec.com 1160 Not Revoked 2012-10-21 12:43:39 server certs listing -All-: https://cacert1.it-sls.de/account.php?id=12&viewall=1 NTFY Valid test1.avintec.com 1167 Not Revoked 2012-10-21 22:17:20 NTFY Valid test1.avintec.com 1166 Not Revoked 2012-10-21 22:06:42 Valid test1.avintec.com 1163 Not Revoked 2012-10-21 14:41:43 NTFY Valid test1.avintec.com 1162 Not Revoked 2012-10-21 13:50:27 NTFY Valid test1.avintec.com 1160 Not Revoked 2012-10-21 12:43:39 Valid test1.avintec.com 115F Not Revoked 2012-10-21 12:19:20 Expired test1.avintec.com 10A7 Not Revoked 2012-03-23 00:20:44 Expired test1.avintec.com 10DB Not Revoked 2012-03-23 00:12:53 Expired test1.avintec.com 10A6 Not Revoked 2012-03-23 00:02:34 Expired test1.avintec.com 10DA Not Revoked 2012-03-22 23:59:21 summary: all notifications received for newly created certs. no notifications received for renewed certs => Problem => correction, => no problem ... by default, you receive an expiry warning, you are going to the website and starting renewal of the cert. this scenario is tested in this test. despite the fact the old renewed certs didn't expired the day after renewal its still listed as valid cert, but has been replaced by the renewed one. You receiving an expiry warning for the new renewed key, but not for the old (in test scenario not expired), but replaced cert So this behavior is as expected. => Ok. |
|
|
login certs.test@w.d to TMS, checking emails rcvd 12 certs about to expire notifications one new server cert created Tue 16th Tue, 16 Oct 2012 15:14:34 +0200 (CEST) and first expire message dated Tue, 16 Oct 2012 15:45:02 +0200 (CEST) DNS:test3b.avintec.com by going to the following location: https://www.cacert.org/account.php?id=15&cert=302041 ^^^^^^^^^^^^^^ link isn't correct for the testserver, but ok ... (still not noticed yet in the previous tests) ah, ok ... from report https://bugs.cacert.org/view.php?id=922#c3244 ................................................... Fri, 5 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 115D is set to expire in approximately 15 days time. URL https://www.cacert.org/account.php?id=5 [^] ................................................... => ok expiry notification for new server cert: 30 d notice /subjectAltName=DNS:test3b.avintec.com/subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test3b.avintec.com Your certificate with the serial number 10D9 is set to expire in approximately 30 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=12 so in total, 11 expiry notifications received between Fri, 19 Oct 2012 21:45:01 +0200 (CEST) and Sat, 20 Oct 2012 22:45:01 +0200 => ok in detail Your Certificate is about to expire ... Fri, 19 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D6 is set to expire in approximately 1 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=5 Fri, 19 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 115D is set to expire in approximately 1 days time. You can renew it by going to the following URL: Fri, 19 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D7 is set to expire in approximately 1 days time. You can renew it by going to the following URL: Fri, 19 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 115E is set to expire in approximately 1 days time. You can renew it by going to the following URL: Fri, 19 Oct 2012 22:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 10D8 is set to expire in approximately 1 days time. You can renew it by going to the following URL Sat, 20 Oct 2012 12:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1160 is set to expire in approximately 1 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=12 Sat, 20 Oct 2012 14:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1162 is set to expire in approximately 1 days time. You can renew it by going to the following URL Sat, 20 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 1164 is set to expire in approximately 1 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=5 Sat, 20 Oct 2012 21:45:01 +0200 (CEST) /CN=certs.test@w.d/emailAddress=certs.test@w.d Your certificate with the serial number 1165 is set to expire in approximately 1 days time. You can renew it by going to the following URL: Sat, 20 Oct 2012 22:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com Your certificate with the serial number 1166 is set to expire in approximately 1 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=12 Sat, 20 Oct 2012 22:45:01 +0200 (CEST) /CN=test1.avintec.com/subjectAltName=DNS:test1.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:test1.avintec.com/ subjectAltName=DNS:mail.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:mail.avintec.com/ subjectAltName=DNS:www.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:www.avintec.com/ subjectAltName=DNS:www.fra.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:www.fra.avintec.com/ subjectAltName=DNS:mx.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:mx.avintec.com/ subjectAltName=DNS:support.avintec.com/ subjectAltName=otherName:1.3.6.1.5.5.7.8.5;UTF8:support.avintec.com Your certificate with the serial number 1167 is set to expire in approximately 1 days time. You can renew it by going to the following URL: https://www.cacert.org/account.php?id=12 client certs listing https://cacert1.it-sls.de/account.php?id=5 0 certs advanced view (show all certs) https://cacert1.it-sls.de/account.php?id=5&viewall=1 ok Expired certs.test@w.d 1165 Not Revoked 2012-10-21 21:41:56 ok Expired certs.test@w.d 1164 Not Revoked 2012-10-21 21:26:44 Expired certs.test@w.d 1161 Not Revoked 2012-10-21 13:02:39 ok Expired certs.test@w.d 10D8 Not Revoked 2012-10-20 21:46:32 ok Expired certs.test@w.d 115E Not Revoked 2012-10-20 21:37:02 ok Expired certs.test@w.d 10D7 Not Revoked 2012-10-20 21:32:52 ok Expired certs.test@w.d 115D Not Revoked 2012-10-20 21:26:44 ok Expired certs.test@w.d 10D6 Not Revoked 2012-10-20 21:14:31 Expired certs.test@w.d 115C Not Revoked 2012-10-20 21:04:00 Expired certs.test@w.d 10A3 Not Revoked 2012-03-22 21:56:34 Expired certs.test@w.d 10D7 Not Revoked 2012-03-22 21:55:49 Expired certs.test@w.d 10A2 Not Revoked 2012-03-22 21:54:57 Expired certs.test@w.d 10D6 Not Revoked 2012-03-22 21:53:42 Expired certs.test@w.d 10A1 Not Revoked 2012-03-22 21:52:39 Expired certs.test@w.d 10D5 Not Revoked 2012-03-22 21:51:09 server certs listing https://cacert1.it-sls.de/account.php?id=12 1 remaining cert (newly created 16th october, see top of this report) -> ok Valid DNS:test3b.avintec.com 10D9 Not Revoked 2012-11-15 13:14:31 show advanced view https://cacert1.it-sls.de/account.php?id=12&viewall=1 Valid DNS:test3b.avintec.com 10D9 Not Revoked 2012-11-15 13:14:31 ok Expired test1.avintec.com 1167 Not Revoked 2012-10-21 22:17:20 ok Expired test1.avintec.com 1166 Not Revoked 2012-10-21 22:06:42 Expired test1.avintec.com 1163 Not Revoked 2012-10-21 14:41:43 ok Expired test1.avintec.com 1162 Not Revoked 2012-10-21 13:50:27 ok Expired test1.avintec.com 1160 Not Revoked 2012-10-21 12:43:39 Expired test1.avintec.com 115F Not Revoked 2012-10-21 12:19:20 Expired test1.avintec.com 10A7 Not Revoked 2012-03-23 00:20:44 Expired test1.avintec.com 10DB Not Revoked 2012-03-23 00:12:53 Expired test1.avintec.com 10A6 Not Revoked 2012-03-23 00:02:34 Expired test1.avintec.com 10DA Not Revoked 2012-03-22 23:59:21 (other certs are out of scope of this 11 different certs testing, so to be ok here) total 11 msgs => ok, all expected messages rcvd =>>> one full cycle of notifications received <<<= test scenario with 11 client and server certs (mixed scnearios, single address, multiple SANs) according to previous reports https://bugs.cacert.org/view.php?id=922#c3221 (date: 2012-09-23 10:27) https://bugs.cacert.org/view.php?id=1054#c3215 (date: 2012-09-21) https://bugs.cacert.org/view.php?id=1054#c3212 (date: 2012-09-21) and under https://bugs.cacert.org/view.php?id=440 (date: 2012-09-21 certs created: 2012-09-21 around 2012-09-21 and 2012-09-21 -> 30 days notice received 2012-10-15 22:46 -> 15 days notice received 20 Oct 2012 -> 1 days notice received => as expected, ok |
|
|
tested by 2, needs 2nd review |
|
|
Dirk Astrath reviewed the code and gave his ok |
|
|
The fix has been installed on the production server on November 1, 2012. See also: https://lists.cacert.org/wws/arc/cacert-systemlog/2012-11/msg00000.html |
|
|
The fix that was installed on the production server earlier today has been backed out again (on November 1, 2012), since the new queries invoked by it appear to lock up the mysql server. See also: https://lists.cacert.org/wws/arc/cacert-systemlog/2012-11/msg00008.html |
|
|
First analysis of the problems seems to indicate at least the following things to be done: 1. Improve INDICES on the tables to better cover what is usually asked for. Having had a look at the current structure shows that the database server quite often has to do computations on large temporary tables due to missing index definitions. At least a basic index should exist for. 1a) each field which is filtered by 1b) combinations of fields which are commonly filtered by 2. Regularly run an OPTIMIZE TABLE on all database tables to ensure contigious and optimal alignment of the database 3. Optimize the query to use JOINS where possible. 4. Optimize the query to avoid full table scans. 5. Introduce first-hand filtering rules on the table index which allow to pre-filter relevant rows prematurely (e.g. filter expired certificates prior to checking revoked ones). 6. Reduce number of fields queried for; i.e. avoiding tablename.* or simular things 7. Reorder filtering queries and JOINS to be as least-inclusive as possible. In particular only join foreign tables only when the number of records of the first table has already been properly reduced. In extreme cases this can be more or less enforced using a temporary table like SELECT fields FROM (SELECT intermediate FROM source WHERE filters) AS source_filtered JOIN source2 ON ... WHERE final_filters given that the initial filtering of the source sufficiently reduces the number of records to be returned. 8. Hope and pray that it gets better. |
|
|
Only adding indices doesn't help much: mysql> explain select distinct `domaincerts`.`id` from `domaincerts`, `domlink` where `domaincerts`.`domid` = '167918' or ( `domaincerts`.`id` = `domlink`.`certid` and `domlink`.`domid` = '167918'); +----+-------------+-------------+-------+--------------------+-------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+--------------------+-------+---------+------+------+------------------------------------+ | 1 | SIMPLE | domaincerts | ALL | PRIMARY,domid | NULL | NULL | NULL | 75 | Using temporary | | 1 | SIMPLE | domlink | index | index,certid,domid | index | 8 | NULL | 75 | Using where; Using index; Distinct | +----+-------------+-------------+-------+--------------------+-------+---------+------+------+------------------------------------+ Using explicit joins makes the distinct superfluous: mysql> explain select `domaincerts`.`id` from `domaincerts` left join `domlink` on (`domaincerts`.`id` = `domlink`.`certid`) where `domaincerts`.`domid` = '167933' or `domlink`.`domid` = '167933'; +----+-------------+-------------+------+---------------+-------+---------+-----------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+-------+---------+-----------------------+------+--------------------------+ | 1 | SIMPLE | domaincerts | ALL | domid | NULL | NULL | NULL | 75 | | | 1 | SIMPLE | domlink | ref | index,certid | index | 4 | cacert.domaincerts.id | 1 | Using where; Using index | +----+-------------+-------------+------+---------------+-------+---------+-----------------------+------+--------------------------+ while using a union would still require removing duplicates in a temporary table but the remaining data sets should be very few and it saves us one full table scan: mysql> explain select `domaincerts`.`id` from `domaincerts` where `domaincerts`.`domid` = '167933' union distinct select `domaincerts`.`id` from `domaincerts`, `domlink` where `domaincerts`.`id` = `domlink`.`certid` and `domlink`.`domid` = '167933'; +----+--------------+-------------+--------+--------------------+---------+---------+-----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+--------------------+---------+---------+-----------------------+------+-------------+ | 1 | PRIMARY | domaincerts | ref | domid | domid | 4 | const | 6 | | | 2 | UNION | domlink | ref | index,certid,domid | domid | 4 | const | 6 | | | 2 | UNION | domaincerts | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domlink.certid | 1 | Using index | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+-------------+--------+--------------------+---------+---------+-----------------------+------+-------------+ Will make some more research in that area later. |
|
|
Here's the second query: mysql> explain SELECT DISTINCT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `users`, `domaincerts`, `domlink`, `domains` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < 45 * 86400 AND `domaincerts`.`renewed` = 0 AND `domaincerts`.`warning` <= '5' AND `domaincerts`.`revoked` = 0 AND ( `domaincerts`.`domid` = `domains`.`id` OR ( `domaincerts`.`id` = `domlink`.`certid` AND `domlink`.`domid` = `domains`.`id` ) ) AND `domains`.`memid` = `users`.`id`; +----+-------------+-------------+--------+-----------------------+---------+---------+----------------------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+-----------------------+---------+---------+----------------------+------+------------------------------+ | 1 | SIMPLE | domaincerts | ref | PRIMARY,revoked,domid | revoked | 8 | const | 55 | Using where; Using temporary | | 1 | SIMPLE | domains | ALL | PRIMARY,memid,memid_2 | NULL | NULL | NULL | 54 | | | 1 | SIMPLE | domlink | index | index | index | 8 | NULL | 75 | Using where; Using index | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domains.memid | 1 | | +----+-------------+-------------+--------+-----------------------+---------+---------+----------------------+------+------------------------------+ Optimised using a left join: mysql> explain SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `users`, `domaincerts` LEFT JOIN `domlink` ON (`domaincerts`.`id` = `domlink`.`certid`), `domains` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < 45 * 86400 AND `domaincerts`.`renewed` = 0 AND `domaincerts`.`warning` <= '5' AND `domaincerts`.`revoked` = 0 AND ( `domaincerts`.`domid` = `domains`.`id` OR `domlink`.`domid` = `domains`.`id` ) AND `domains`.`memid` = `users`.`id`; +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ | 1 | SIMPLE | domaincerts | ref | revoked,domid | revoked | 8 | const | 55 | Using where | | 1 | SIMPLE | domlink | ref | index | index | 4 | cacert.domaincerts.id | 1 | Using index | | 1 | SIMPLE | domains | ALL | PRIMARY | NULL | NULL | NULL | 54 | Range checked for each record (index map: 0x1) | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domains.memid | 1 | | +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ Further explicit joins do not help: mysql> explain SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM ((`domaincerts` LEFT JOIN `domlink` ON (`domaincerts`.`id` = `domlink`.`certid`)) JOIN `domains` ON (`domaincerts`.`domid` = `domains`.`id` OR `domlink`.`domid` = `domains`.`id`)) JOIN `users` ON (`domains`.`memid` = `users`.`id`) WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < 45 * 86400 AND `domaincerts`.`renewed` = 0 AND `domaincerts`.`warning` <= '5' AND `domaincerts`.`revoked` = 0; +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ | 1 | SIMPLE | domaincerts | ref | revoked,domid | revoked | 8 | const | 55 | Using where | | 1 | SIMPLE | domlink | ref | index | index | 4 | cacert.domaincerts.id | 1 | Using index | | 1 | SIMPLE | domains | ALL | PRIMARY | NULL | NULL | NULL | 54 | Range checked for each record (index map: 0x1) | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domains.memid | 1 | | +----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+------------------------------------------------+ Unions are also possible, avoid full table scans, but contain lots of duplicate query code: mysql> explain SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `users`, `domaincerts`, `domains` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < 45 * 86400 AND `domaincerts`.`renewed` = 0 AND `domaincerts`.`warning` <= '5' AND `domaincerts`.`revoked` = 0 AND `domaincerts`.`domid` = `domains`.`id` AND `domains`.`memid` = `users`.`id` UNION DISTINCT SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` FROM `users`, `domaincerts` LEFT JOIN `domlink` ON (`domaincerts`.`id` = `domlink`.`certid`), `domains` WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < 45 * 86400 AND `domaincerts`.`renewed` = 0 AND `domaincerts`.`warning` <= '5' AND `domaincerts`.`revoked` = 0 AND `domlink`.`domid` = `domains`.`id` AND `domains`.`memid` = `users`.`id`; +----+--------------+-------------+--------+-----------------------+---------+---------+--------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-----------------------+---------+---------+--------------------------+------+--------------------------+ | 1 | PRIMARY | domaincerts | ref | revoked,domid | revoked | 8 | const | 55 | Using where | | 1 | PRIMARY | domains | eq_ref | PRIMARY,memid,memid_2 | PRIMARY | 4 | cacert.domaincerts.domid | 1 | | | 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domains.memid | 1 | | | 2 | UNION | domaincerts | ref | PRIMARY,revoked | revoked | 8 | const | 55 | Using where | | 2 | UNION | domlink | ref | index | index | 4 | cacert.domaincerts.id | 1 | Using where; Using index | | 2 | UNION | domains | eq_ref | PRIMARY,memid,memid_2 | PRIMARY | 4 | cacert.domlink.domid | 1 | | | 2 | UNION | users | eq_ref | PRIMARY | PRIMARY | 4 | cacert.domains.memid | 1 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+-------------+--------+-----------------------+---------+---------+--------------------------+------+--------------------------+ |
|
|
I have submitted a patch with optimised SQL queries some time ago but apparently forgot to update the status. Please test and review. |
|
|
2nd review ok. |
|
|
check my mailbox. I do not have any duplicates of mails. Server and client certs. Shows mail 45/30/15/1 day. =>ok |
|
|
created client certs for 2 test accounts. received 45, 30, 15, 1 day notifications no dupes, no missing ones |
|
|
please review as at least 2 tester aproved the tests |
|
|
Follow-up patch sent to critical admins |
|
|
The improved patch has been installed on the production server on June 11, 2013. See also: https://lists.cacert.org/wws/arc/cacert-systemlog/2013-06/msg00001.html |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2011-04-21 15:27 | wytze | New Issue | |
| 2011-04-26 07:44 | wytze | Note Added: 0001942 | |
| 2011-04-26 21:10 | NEOatNHNG | Priority | normal => high |
| 2011-04-26 21:25 | NEOatNHNG | Note Added: 0001945 | |
| 2011-08-23 12:24 | Uli60 | Note Added: 0002328 | |
| 2011-09-01 10:02 | Uli60 | Relationship added | related to 0000820 |
| 2011-12-21 17:12 | NEOatNHNG | Assigned To | => NEOatNHNG |
| 2011-12-22 08:53 | wytze | Note Added: 0002754 | |
| 2011-12-22 10:53 | Uli60 | Note Added: 0002755 | |
| 2011-12-22 11:02 | Uli60 | Note Edited: 0002755 | |
| 2012-04-18 17:05 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver 39477e53 |
| 2012-04-18 17:05 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver 61b9d45e |
| 2012-04-18 17:05 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver b41f35ea |
| 2012-04-18 17:15 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver ac79c83e |
| 2012-04-18 17:15 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver ded64e3b |
| 2012-04-18 17:27 | NEOatNHNG | Note Added: 0002943 | |
| 2012-04-18 17:27 | NEOatNHNG | Status | new => needs review & testing |
| 2012-04-18 17:27 | NEOatNHNG | Reviewed by | => NEOatNHNG |
| 2012-06-02 12:43 | INOPIAE | Note Added: 0003050 | |
| 2012-06-05 22:23 | Uli60 | Note Added: 0003052 | |
| 2012-06-05 23:00 | INOPIAE | Note Edited: 0003050 | |
| 2012-06-05 23:13 | Uli60 | Note Edited: 0003052 | |
| 2012-06-05 23:46 | Uli60 | Note Edited: 0003052 | |
| 2012-06-06 01:24 | Uli60 | Note Edited: 0003052 | |
| 2012-06-06 01:25 | Uli60 | Relationship added | related to 0000911 |
| 2012-06-26 21:20 | BenBE | Relationship added | related to 0001078 |
| 2012-07-10 20:01 | Uli60 | Note Added: 0003087 | |
| 2012-07-17 02:59 | Uli60 | Note Added: 0003092 | |
| 2012-07-17 03:00 | Uli60 | Note Edited: 0003092 | |
| 2012-07-17 03:01 | Uli60 | Note Edited: 0003092 | |
| 2012-07-17 03:07 | Uli60 | Note Added: 0003093 | |
| 2012-07-17 03:10 | Uli60 | Note Edited: 0003093 | |
| 2012-07-17 03:18 | Uli60 | Note Edited: 0003093 | |
| 2012-07-17 03:21 | Uli60 | Note Edited: 0003093 | |
| 2012-07-24 17:47 | INOPIAE | Note Added: 0003101 | |
| 2012-07-24 19:37 | INOPIAE | Note Edited: 0003101 | |
| 2012-07-24 20:47 | INOPIAE | Note Edited: 0003101 | |
| 2012-07-25 00:24 | NEOatNHNG | Status | needs review & testing => needs work |
| 2012-08-14 22:15 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver 609654ab |
| 2012-08-14 22:15 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver b3166523 |
| 2012-08-14 22:32 | NEOatNHNG | Note Added: 0003138 | |
| 2012-08-14 22:32 | NEOatNHNG | Status | needs work => needs review & testing |
| 2012-09-23 10:27 | Uli60 | Note Added: 0003221 | |
| 2012-09-25 22:28 | Uli60 | Additional Information Updated | |
| 2012-10-02 20:36 | Uli60 | Note Added: 0003226 | |
| 2012-10-02 21:23 | INOPIAE | Note Added: 0003227 | |
| 2012-10-02 21:54 | INOPIAE | Note Edited: 0003227 | |
| 2012-10-02 21:55 | INOPIAE | Note Edited: 0003227 | |
| 2012-10-02 21:57 | INOPIAE | Note Edited: 0003227 | |
| 2012-10-02 23:20 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver acab02bd |
| 2012-10-02 23:20 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver 6dd2854e |
| 2012-10-02 23:24 | NEOatNHNG | Note Added: 0003233 | |
| 2012-10-02 23:25 | INOPIAE | Note Added: 0003234 | |
| 2012-10-02 23:25 | Uli60 | Note Added: 0003235 | |
| 2012-10-02 23:25 | Uli60 | Note Edited: 0003235 | |
| 2012-10-15 22:46 | Uli60 | Note Added: 0003244 | |
| 2012-10-15 22:50 | Uli60 | Note Edited: 0003244 | |
| 2012-10-15 22:51 | Uli60 | Note Edited: 0003244 | |
| 2012-10-23 02:28 | Uli60 | Note Added: 0003263 | |
| 2012-10-23 23:17 | Uli60 | Note Added: 0003269 | |
| 2012-10-23 23:17 | Uli60 | Status | needs review & testing => needs review |
| 2012-10-23 23:17 | Uli60 | Assigned To | NEOatNHNG => egal |
| 2012-10-31 00:08 | INOPIAE | Note Added: 0003303 | |
| 2012-10-31 17:59 | BenBE | Reviewed by | NEOatNHNG => dastrath, NEOatNHNG |
| 2012-10-31 17:59 | BenBE | Status | needs review => ready to deploy |
| 2012-10-31 18:45 | BenBE | Source_changeset_attached | => cacert-devel release fb0e466b |
| 2012-11-01 13:58 | wytze | Note Added: 0003311 | |
| 2012-11-01 13:58 | wytze | Status | ready to deploy => solved? |
| 2012-11-01 13:58 | wytze | Resolution | open => fixed |
| 2012-11-01 19:30 | wytze | Note Added: 0003316 | |
| 2012-11-01 19:30 | wytze | Status | solved? => needs work |
| 2012-11-07 13:39 | BenBE | Note Added: 0003319 | |
| 2012-11-19 03:40 | NEOatNHNG | Note Added: 0003343 | |
| 2012-11-20 01:35 | NEOatNHNG | Note Edited: 0003343 | |
| 2012-11-20 02:03 | NEOatNHNG | Note Added: 0003347 | |
| 2012-12-20 08:19 | Werner Dworak | Relationship added | related to 0000089 |
| 2012-12-20 08:36 | Werner Dworak | Relationship added | related to 0001079 |
| 2012-12-22 20:46 | Werner Dworak | Relationship added | related to 0001026 |
| 2013-01-22 21:10 | NEOatNHNG | Assigned To | egal => NEOatNHNG |
| 2013-01-22 21:10 | NEOatNHNG | Reviewed by | dastrath, NEOatNHNG => |
| 2013-01-22 22:35 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver-stable 69cab228 |
| 2013-01-22 22:35 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver-stable de583d9e |
| 2013-04-16 20:49 | NEOatNHNG | Note Added: 0003889 | |
| 2013-04-16 20:49 | NEOatNHNG | Status | needs work => needs review & testing |
| 2013-04-16 20:52 | NEOatNHNG | Reviewed by | => NEOatNHNG |
| 2013-04-16 21:09 | BenBE | Reviewed by | NEOatNHNG => NEOatNHNG, BenBE |
| 2013-04-16 21:09 | BenBE | Note Added: 0003892 | |
| 2013-04-16 21:09 | BenBE | Status | needs review & testing => needs testing |
| 2013-04-16 21:09 | BenBE | Product Version | => 2011 Q2 |
| 2013-04-16 21:09 | BenBE | Target Version | => 2013 Q2 |
| 2013-04-16 21:20 | INOPIAE | Note Added: 0003894 | |
| 2013-04-23 21:32 | Uli60 | Note Added: 0003918 | |
| 2013-04-23 21:35 | INOPIAE | Note Added: 0003919 | |
| 2013-04-23 21:35 | INOPIAE | Status | needs testing => ready to deploy |
| 2013-05-01 09:36 | INOPIAE | Relationship added | related to 0000483 |
| 2013-06-04 21:09 | NEOatNHNG | Note Added: 0004040 | |
| 2013-06-04 21:20 | NEOatNHNG | Source_changeset_attached | => cacert-devel release d2ad5c5c |
| 2013-06-11 10:08 | wytze | Note Added: 0004045 | |
| 2013-06-11 10:08 | wytze | Status | ready to deploy => solved? |
| 2013-06-11 10:08 | wytze | Fixed in Version | => 2013 Q2 |
| 2013-07-31 20:35 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver-stable ea42d51a |
| 2013-08-06 22:05 | NEOatNHNG | Source_changeset_attached | => cacert-devel testserver-stable 6744dcf1 |
| 2013-11-12 21:12 | INOPIAE | Status | solved? => closed |
