View Issue Details

IDProjectCategoryView StatusLast Update
0000922Main CAcert Websiteaccount administrationpublic2013-11-12 21:13
Reporterwytze Assigned ToNEOatNHNG  
PriorityhighSeveritymajorReproducibilitysometimes
Status closedResolutionfixed 
Product Version2011 Q2 
Target Version2013 Q2Fixed in Version2013 Q2 
Summary0000922: CAcert application code problem causing missing "certificate about to expire" messages
DescriptionWhile 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 Informationproposed notifications:
1d, 15d, 30d, 45d before expiry
TagsNo tags attached.
Reviewed byNEOatNHNG, BenBE
Test Instructions

Relationships

related to 0000911 closedNEOatNHNG 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 workSourcerer GPG Revokation Escrow Service 
related to 0001079 needs work GPG key can not be revoked 
related to 0001026 needs workUli60 Server Certificate was revoked but not by the user 
related to 0000483 closedINOPIAE Please send more verbose emails concerning certificate revocation 

Activities

wytze

2011-04-26 07:44

developer   ~0001942

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.

NEOatNHNG

2011-04-26 21:25

administrator   ~0001945

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.

Uli60

2011-08-23 12:24

updater   ~0002328

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)

wytze

2011-12-22 08:53

developer   ~0002754

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.

Uli60

2011-12-22 10:53

updater   ~0002755

Last edited: 2011-12-22 11:02

View 2 revisions

> 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

NEOatNHNG

2012-04-18 17:27

administrator   ~0002943

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.

INOPIAE

2012-06-02 12:43

updater   ~0003050

Last edited: 2012-06-05 23:00

View 2 revisions

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

Uli60

2012-06-05 22:23

updater   ~0003052

Last edited: 2012-06-06 01:24

View 4 revisions

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

Uli60

2012-07-10 20:01

updater   ~0003087

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

Uli60

2012-07-17 02:59

updater   ~0003092

Last edited: 2012-07-17 03:01

View 3 revisions

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

Uli60

2012-07-17 03:07

updater   ~0003093

Last edited: 2012-07-17 03:21

View 4 revisions

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

INOPIAE

2012-07-24 17:47

updater   ~0003101

Last edited: 2012-07-24 20:47

View 3 revisions

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.

NEOatNHNG

2012-08-14 22:32

administrator   ~0003138

I have modified the SQL queries to only select distinct data set which should prevent the multiple mails. Please test and review.

Uli60

2012-09-23 10:27

updater   ~0003221

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

Uli60

2012-10-02 20:36

updater   ~0003226

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

INOPIAE

2012-10-02 21:23

updater   ~0003227

Last edited: 2012-10-02 21:57

View 4 revisions

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.

NEOatNHNG

2012-10-02 23:24

administrator   ~0003233

Added serial number to the email

INOPIAE

2012-10-02 23:25

updater   ~0003234

Renew a client certificate for orgadmin@inopaie.com with SN 1168.
The 30 day message shows now the SN.

=> ok

Uli60

2012-10-02 23:25

updater   ~0003235

Last edited: 2012-10-02 23:25

View 2 revisions

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

Uli60

2012-10-15 22:46

updater   ~0003244

Last edited: 2012-10-15 22:51

View 3 revisions

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.

Uli60

2012-10-23 02:28

updater   ~0003263

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

Uli60

2012-10-23 23:17

updater   ~0003269

tested by 2, needs 2nd review

INOPIAE

2012-10-31 00:08

updater   ~0003303

Dirk Astrath reviewed the code and gave his ok

wytze

2012-11-01 13:58

developer   ~0003311

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

wytze

2012-11-01 19:30

developer   ~0003316

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

BenBE

2012-11-07 13:39

updater   ~0003319

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.

NEOatNHNG

2012-11-19 03:40

administrator   ~0003343

Last edited: 2012-11-20 01:35

View 2 revisions

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.

NEOatNHNG

2012-11-20 02:03

administrator   ~0003347

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 | |
+----+--------------+-------------+--------+-----------------------+---------+---------+--------------------------+------+--------------------------+

NEOatNHNG

2013-04-16 20:49

administrator   ~0003889

I have submitted a patch with optimised SQL queries some time ago but apparently forgot to update the status. Please test and review.

BenBE

2013-04-16 21:09

updater   ~0003892

2nd review ok.

INOPIAE

2013-04-16 21:20

updater   ~0003894

check my mailbox.
I do not have any duplicates of mails.
Server and client certs.
Shows mail 45/30/15/1 day.
=>ok

Uli60

2013-04-23 21:32

updater   ~0003918

created client certs for 2 test accounts.
received 45, 30, 15, 1 day notifications
no dupes, no missing ones

INOPIAE

2013-04-23 21:35

updater   ~0003919

please review as at least 2 tester aproved the tests

NEOatNHNG

2013-06-04 21:09

administrator   ~0004040

Follow-up patch sent to critical admins

wytze

2013-06-11 10:08

developer   ~0004045

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

Issue History

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 View Revisions
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 View Revisions
2012-06-05 23:13 Uli60 Note Edited: 0003052 View Revisions
2012-06-05 23:46 Uli60 Note Edited: 0003052 View Revisions
2012-06-06 01:24 Uli60 Note Edited: 0003052 View Revisions
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 View Revisions
2012-07-17 03:01 Uli60 Note Edited: 0003092 View Revisions
2012-07-17 03:07 Uli60 Note Added: 0003093
2012-07-17 03:10 Uli60 Note Edited: 0003093 View Revisions
2012-07-17 03:18 Uli60 Note Edited: 0003093 View Revisions
2012-07-17 03:21 Uli60 Note Edited: 0003093 View Revisions
2012-07-24 17:47 INOPIAE Note Added: 0003101
2012-07-24 19:37 INOPIAE Note Edited: 0003101 View Revisions
2012-07-24 20:47 INOPIAE Note Edited: 0003101 View Revisions
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 View Revisions
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 View Revisions
2012-10-02 21:55 INOPIAE Note Edited: 0003227 View Revisions
2012-10-02 21:57 INOPIAE Note Edited: 0003227 View Revisions
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 View Revisions
2012-10-15 22:46 Uli60 Note Added: 0003244
2012-10-15 22:50 Uli60 Note Edited: 0003244 View Revisions
2012-10-15 22:51 Uli60 Note Edited: 0003244 View Revisions
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 View Revisions
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