Page MenuHomePhabricator
Create Task
Maniphest T163422

Fix anonymisation of data sets
Closed, ResolvedPublic1 Estimated Story Points
Actions

Description

On the OC server there is a cronjob, that purges personal data from exported data sets (donations and membership applications).

The anonymisation is currently broken, because we try to set 0000-00-00 as the value for a date field. This apparently happens since we switched to MySQL 5.7, which uses STRICT_TRANS_TABLES mode by default.

PHP Fatal error:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'dob' at row 23350 in /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91
Stack trace:
#0 /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(91): PDOStatement->execute(NULL)
#1 /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(987): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(208): Doctrine\DBAL\Connection->executeUpdate('UPDATE request ...', Array, Array)
#3 /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/src/Anonymization/MembershipApplicationAnonymizer.php(55): Doctrine\DBAL\Query\QueryBuilder->execute()
#4 /usr/share/nginx/ww in /usr/share/nginx/www/backend.wikimedia.de/release-20170329141021/src/Anonymization/MembershipApplicationAnonymizer.php on line 58

See http://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime/36374690#36374690 for further explanation.

Related Objects
Search...

StatusSubtypeAssignedTask
 Resolvedkai.nissenT163200 Fix broken cron jobs
 Resolvedkai.nissenT163422 Fix anonymisation of data sets

Event Timeline

kai.nissen created this task.Apr 20 2017, 8:50 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 20 2017, 8:50 AM
kai.nissen moved this task from Backlog to Sprint ready on the WMDE-Fundraising-Tech board.Apr 20 2017, 8:52 AM
kai.nissen claimed this task.Apr 20 2017, 10:48 AM
kai.nissen triaged this task as High priority.
kai.nissen set the point value for this task to 1.
kai.nissen moved this task from Todo to Doing on the WMDE-Fundraising-Sprint-1 board.
kai.nissen added a comment.Apr 20 2017, 11:06 AM

fixed in PR#257

kai.nissen moved this task from Doing to Review on the WMDE-Fundraising-Sprint-1 board.Apr 20 2017, 11:06 AM
kai.nissen added a parent task: T163200: Fix broken cron jobs.
kai.nissen closed this task as Resolved.Apr 20 2017, 4:04 PM
kai.nissen moved this task from Review to Done on the WMDE-Fundraising-Sprint-1 board.
gabriel-wmde added a comment.Nov 6 2017, 11:22 AM

I ran into the MySQL error message again while altering the schema of the testing db. Running

UPDATE request SET dob=NULL WHERE dob = '0000-00-00';

resulted in the error Incorrect date value: '0000-00-00' for column 'dob' at row 1.

I dimly remember having solved this before with date conversion functions, but did not find my howto again. So here is the solution again:

Before running the UPDATE command, run

SET sql_mode = '';

to turn off date checking temporarily.

See https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime/36374690#36374690

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL