Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 4.6.8
-
Fix Version/s: Unscheduled
-
Component/s: CiviCRM Search, Dedupe
-
Labels:None
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Needs Funding
Description
I ran into this issue when trying to do de-duplication that includes birth dates. It results in an error. I have tested this on the demo site as well as my local install.
I also tested to see if I can just do a search for birthdates that are not empty using the "Search Builder". It also results in an error when searching for "is empty" or "not empty".
The error for "Search Builder" is different from the error when deduping, but they may be related?
The following is related to the query that is run when attempting to dedupe
I've run the query excluding the following:
"INSERT INTO dedupe (id1, id2, weight)"
"ON DUPLICATE KEY UPDATE "
"weight = weight + VALUES(weight) "
The query returns id1 and id2 properly without the insert statement.
However, when I add back that portion of the query I get the error about "Incorrect date value" at row 1.
backTrace
#0 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Core/Error.php(182): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#4 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#5 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...", "DB_Error", TRUE)
#6 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-1, NULL, NULL, NULL, "1292 ** Incorrect date value: '' for column 'birth_date' at row 1")
#7 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#9 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/DataObject.php(2442): DB_common->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#10 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#11 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Core/DAO.php(325): DB_DataObject->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#12 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Dedupe/BAO/RuleGroup.php(261): CRM_Core_DAO->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight F...")
#13 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Dedupe/Finder.php(62): CRM_Dedupe_BAO_RuleGroup->fillTable()
#14 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Dedupe/Finder.php(158): CRM_Dedupe_Finder::dupes("8", (Array:306))
#15 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Contact/Page/DedupeFind.php(176): CRM_Dedupe_Finder::dupesInGroup("8", "174")
#16 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Core/Invoke.php(312): CRM_Contact_Page_DedupeFind->run((Array:3), NULL)
#17 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#18 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))
#19 /var/aegir/platforms/drupal-7.31/sites/all/modules/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:3))
#20 [internal function](): civicrm_invoke("contact", "dedupefind")
#21 /var/aegir/platforms/drupal-7.31/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#22 /var/aegir/platforms/drupal-7.31/index.php(21): menu_execute_active_handler()
#23 {main}
Sorry but we are not able to provide this at the moment.
DB Error: unknown error
Error Details
Database Error Code: Incorrect date value: '' for column 'birth_date' at row 1, 1292
Additional Details:
Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)
[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (birth_date) WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.birth_date IS NOT NULL AND t1.birth_date <> '' AND (t1.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721) OR t2.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721)) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1292 ** Incorrect date value: '' for column 'birth_date' at row 1]
[type] => DB_Error
[user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (birth_date) WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.birth_date IS NOT NULL AND t1.birth_date <> '' AND (t1.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721) OR t2.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721)) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1292 ** Incorrect date value: '' for column 'birth_date' at row 1]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 0 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (birth_date) WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.birth_date IS NOT NULL AND t1.birth_date <> '' AND (t1.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721) OR t2.id IN (6930,6931,6939,6948,6977,6978,6979,7009,7027,7028,7031,7036,7037,7050,7051,7067,7068,7070,7071,7075,7076,7091,7104,7105,7135,7136,7137,7144,7145,7148,7149,7165,7170,7180,7181,7187,7188,7189,7206,7215,7217,7226,7230,7244,7254,7255,7256,7272,7273,7275,7298,7299,7300,7301,7306,7307,7332,7333,7334,7346,7347,7348,7354,7358,7359,7366,7373,7375,7418,7419,7420,7421,7440,7450,7451,7452,7454,7493,7844,7867,7891,8004,8016,8018,8089,8099,8135,8136,8173,8182,8221,8222,8249,8253,8257,8258,8260,8343,8355,8468,8479,8526,8590,8603,8658,8663,8672,8731,8779,8807,8811,8813,8816,8818,8826,8829,8965,8967,8969,9060,9061,9062,9063,9064,9065,9078,9171,9172,9189,9248,9249,9285,9375,9376,9382,9401,9402,9485,9486,9487,9489,9659,9681,9761,9762,9917,10015,10027,10089,10091,10133,10135,10141,10142,10145,10149,10150,10215,10242,10243,10249,10277,10299,10300,10380,10384,10393,10394,10395,10396,10399,10446,10499,10573,10580,10581,10608,10615,10629,10672,10698,10735,10779,10780,10785,10833,10877,10885,10892,10962,10963,10967,10968,10974,10975,10988,11045,11090,11096,11099,11117,11120,11179,11180,11182,11189,11197,11199,11214,11215,11216,11222,11223,11224,11233,11262,11269,11270,11311,11312,11313,11314,11316,11376,11383,11393,11394,11397,11398,11399,11433,11434,11443,11470,11471,11472,11481,11489,11500,11522,11524,11525,11534,11570,11656,11700,11708,11712,11721,11753,11789,11835,11836,11842,11851,11861,11873,11875,11919,12030,12092,12094,12095,12150,12152,12156,12158,12179,12180,12182,12183,12184,12264,12312,12315,12316,12329,12386,12390,12392,12434,12447,12448,12488,12489,12492,12527,12534,12536,12555,12556,12557,12560,12561,12563,12566,12571,12596,12600,12601,12676,12683,12684,12686,12691,12721)) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1292 ** Incorrect date value: '' for column 'birth_date' at row 1]"]
)
Return to home page.