CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Bug: DB error adding negative date range
Pages: [1]

Author Topic: Bug: DB error adding negative date range  (Read 2468 times)

sonicthoughts

  • Ask me questions
  • ****
  • Posts: 498
  • Karma: 10
Bug: DB error adding negative date range
April 11, 2011, 11:10:47 am
We are trying to do date validation such that applicatant age in profile must be at least 18.  I decided to create a new date field and see what would happen if I entered a negative in the "dates may be up to..." section of the date range.  now i agree it was silly but i thought i would try.  any thoughts on how to do a proper validation of age?

got an error which i reproduced on the demo site:

Database Error Code: Invalid default value for 'dob_submitted_34', 1067
Additional Details:
Array
(
    [callback] => Array
        (
           
  • => CRM_Core_Error
    • [1] => handle
            )

       
Code: [Select]
=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] =>         ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']
    [type] => DB_Error
    [user_info] =>         ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="        ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']"]
)

sonicthoughts

  • Ask me questions
  • ****
  • Posts: 498
  • Karma: 10
UPDATE: database inconsistency
April 11, 2011, 11:33:20 am
update:
Tried to delete the custom field (which also shows up in selector) and got an error.  Looks like it was partially created but not fully....
Database Error Code: Can't DROP 'dob_submitted_34'; check that column/key exists, 1091
Additional Details:
Array
(
    [callback] => Array
        (
           
  • => CRM_Core_Error
    • [1] => handle
            )

       
Code: [Select]
=> -4
    [message] => DB Error: not found
    [mode] => 16
    [debug_info] =>         ALTER TABLE civicrm_value_additional_information_2 DROP COLUMN `dob_submitted_34` [nativecode=1091 ** Can't DROP 'dob_submitted_34'; check that column/key exists]
    [type] => DB_Error
    [user_info] =>         ALTER TABLE civicrm_value_additional_information_2 DROP COLUMN `dob_submitted_34` [nativecode=1091 ** Can't DROP 'dob_submitted_34'; check that column/key exists]
    [to_string] => [db_error: message="DB Error: not found" code=-4 mode=callback callback=CRM_Core_Error::handle prefix="" info="        ALTER TABLE civicrm_value_additional_information_2 DROP COLUMN `dob_submitted_34` [nativecode=1091 ** Can't DROP 'dob_submitted_34'; check that column/key exists]"]
)

[quote author=sonicthoughts link=topic=19434.msg80884#msg80884 date=1302545447]
We are trying to do date validation such that applicatant age in profile must be at least 18.  I decided to create a new date field and see what would happen if I entered a negative in the "dates may be up to..." section of the date range.  now i agree it was silly but i thought i would try.  any thoughts on how to do a proper validation of age?

got an error which i reproduced on the demo site:

Database Error Code: Invalid default value for 'dob_submitted_34', 1067
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] =>         ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']
    [type] => DB_Error
    [user_info] =>         ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="        ALTER TABLE civicrm_value_additional_information_2
        ADD COLUMN `dob_submitted_34` datetime DEFAULT 'null',
        ADD INDEX INDEX_dob_submitted_34 ( dob_submitted_34 ) [nativecode=1067 ** Invalid default value for 'dob_submitted_34']"]
)
[/quote]

Kurund Jalmi

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4169
  • Karma: 128
    • CiviCRM
  • CiviCRM version: 4.x, future
  • CMS version: Drupal 7, Joomla 3.x
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Bug: DB error adding negative date range
April 12, 2011, 09:32:04 am
for validation  why are you not using validate hook?

Kurund
Found this reply helpful? Support CiviCRM

sonicthoughts

  • Ask me questions
  • ****
  • Posts: 498
  • Karma: 10
Re: Bug: DB error adding negative date range
April 12, 2011, 10:04:35 am
to avoid maintaining code as much as possible.    I guess ideally, we should allow in date offset.  I'll look into validator hook.  Either way, it shouldn't cause a database error like this.
any ideas about how to remove the partially created custom field?

Erik Hommel

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1773
  • Karma: 59
    • EE-atWork
  • CiviCRM version: all sorts
  • CMS version: Drupal
  • MySQL version: Ubuntu's latest LTS version
  • PHP version: Ubuntu's latest LTS version
Re: Bug: DB error adding negative date range
April 12, 2011, 10:43:26 am
There are a couple of tables usually involved in creating custom fields. If you create a custom data set named "my set" you will get a table called "civicrm_value_my_set_xxx" where xxx is a sequential number, the first custom data set gets number 1 etc.
There will also be a record for the group in the table civicrm_custom_group.
Additionally each field is added to the table civicrm_custom_field. If you check all those tables I think you should have the complete picture, and be able to find out how much has been created?
Consultant/project manager at EEatWork and CiviCooP (http://www.civicoop.org/)

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Bug: DB error adding negative date range
April 28, 2011, 05:36:54 pm
I've committed a fix for 3.4.1 so that negative start and end date offsets can now be configured for specific date types (like Birth Date) and can be used for custom fields.

http://issues.civicrm.org/jira/browse/CRM-7988
Protect your investment in CiviCRM by  becoming a Member!

sonicthoughts

  • Ask me questions
  • ****
  • Posts: 498
  • Karma: 10
Re: Bug: DB error adding negative date range
May 09, 2011, 09:52:48 am
tried this on sandbox but it still generates an error: Sorry. A non-recoverable error has occurred. used -18 in both offset fields.  :-\
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Bug: DB error adding negative date range

This forum was archived on 2017-11-26.