"DB Error: unknown error" when merging if duplicate contact has null created_date
This is partly a data quality issue on our end, but when merging two contacts a DB error is thrown if the one on the left has a null created date (the default value for the column) and the one on the right doesn't.
I am able to reproduce this with latest CiviCRM on https://dmaster.demo.civicrm.org
Steps to reproduce on dmaster.demo.civicrm.org:
start the merge:
- get a listing of contacts
- go to find contacts - https://dmaster.demo.civicrm.org/civicrm/contact/search?reset=1
- click search
- check box next to two individual contacts (not organizations)
- select "merge contacts" from actions menu
- get the contact id for the contact on the left (hover over the name and remember the value of cid in the url that pops up)
use the api to set created_date to null for the duplicate contact:
- open a new tab and go to - https://dmaster.demo.civicrm.org/civicrm/api3
- entity: contact
- action: create
- parameter: contact id = {{ duplicate contact id }}
- parameter: created date = "" (two double quotes)
- execute
perform the merge:
- go back to the tab with the merge page and refresh it - "created" should now be empty on the duplicate
- click merge
Information from logs:
UPDATE civicrm_contact SET created_date = '' WHERE id = 123 [nativecode=1292 ** Incorrect datetime value: '' for column `dev_civicrm`.`civicrm_contact`.`created_date` at row 1]