search

mdjnelson / moodle-mod_customcert

Enables the creation of dynamically generated certificates with complete customisation via the web browser.
https://moodle.org/plugins/mod_customcert
GNU General Public License v3.0
94 stars 160 forks source link

Deleting erroneously issued certificates #639

Closed dragos5436 closed 3 months ago

dragos5436 commented 3 months ago

Hi there,

Last week we upgraded our Moodle and a day after we discovered that hundreds (potentially thousands) of certificates have been issued by mistake due to the bug described here: Certificates being issued on enrollment.

I have seen that this bug has now been resolved (https://github.com/mdjnelson/moodle-mod_customcert/commit/94cfd4b130392e1a204844a0074c8938d09bf642). Thank you for that.

I was wondering whether you could also provide a way to bulk delete/revoke all the certificates that have been issued in error? Whether it's a database query based approach or a code based approach, we're just looking for some tips on how to resolve this issue, as manually deleting all the thousands of certificates would be quite a painstaking task.

Thank you in advance.

Best wishes, Dragos

mdjnelson commented 3 months ago

I am really sorry about this.

I created a unit test so this won't happen again.

The issues for the certificates are stored in the mdl_customcert_issues database table. This table has a column called timecreated which is the date it was issued. The format it is stored in is called a unix timestamp. If you visit https://www.unixtimestamp.com/ and put in the date when you upgraded and it started issuing erroneously and convert that date to a unix timestamp using the site I mentioned you can delete all issued certificates from that date with a SQL query like "DELETE FROM mdl_customcertissues WHERE timecreated >= :value" - note "mdl" is the default prefix for database tables but is configurable so may be different in your setup - look at your DB for the table name to make sure. Also, please be very careful when executing this query. You should make a backup of the table before executing this.

HTH,

Mark

mdjnelson commented 3 months ago

Closing as there is no code solution unless I was to loop all over the issues in the table and check if the certificate is available for that user and delete it if it isn't. This would be very performance heavy.