field setting 'not null' => TRUE can cause problems with MySQL in strict mode
heyrocker - May 25, 2007 - 18:46
| Project: | Phone (CCK) |
| Version: | 6.x-2.9 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Description
In strict mode, MySQL cannot accept an empty string when a field's "not null" property is set to TRUE. This can cause MySQL errors to be thrown in certain situations.
As far as I know there is no downside to setting 'not null'->FALSE, many CCK core fields do that now.
Patch attached, this is my first patch submission! Let me know if you need anything :)
| Attachment | Size |
|---|---|
| phone.module.patch | 756 bytes |
#1
I can confirm this is also an issue with the D6 version. Running on a PostgreSQL database, this prevents the node from being created because the database says you're trying to insert null into a not-null field. Example error from watchdog:
# warning: pg_query() [function.pg-query]: Query failed: ERROR: null value in column "field_phone_home_value" violates not-null constraint in /var/www/signon/includes/database.pgsql.inc on line 139.# user warning: query: INSERT INTO content_type_profile (vid, nid, field_firstname_value, field_lastname_value, field_signup_method_value, field_allow_contact_value, field_address_aname, field_address_street, field_address_additional, field_address_city, field_address_province, field_address_country, field_address_postal_code, field_address_is_primary, field_phone_home_value, field_phone_mobile_value) VALUES (403, 403, 'Test 399', 'User 399', 'import', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL) in /var/www/signon/sites/all/modules/cck/content.module on line 1207.
The trigger for this error is either programmatically creating nodes without the phone field set (which is fine if it's not a "required" field), for example using node_import to import content. Another cause is using something like the content_permissions module that ships with CCK to turn off the phone field for some roles, so the value isn't submitted.
I imagine the patch remains similar to the one already attached, and it needs to be applied as this causes complete breakage on Postgres in the mentioned circumstances.
#2
#3
I agree. Adding on to request this change be made.
I can't load data that I've exported because of this constraint. c: Here is a patch file for 6.x version
#4
Do we need an update function to change this on existing fields?
#5
Yes that's probably a good idea. Would anyone like to re-craft the patch? I can if noone else wants to.
#6
+1
This issue causes a problem with node profile module when hiding the phone on the registration field: http://drupal.org/node/514080
#7
I am using the phone cck in advanced profile kit but the field is not visible upon registration. Nonetheless, when new users sign up, they are greeted with this error message.
* user warning: Column 'field_phone_ottawa_value' cannot be null query: INSERT INTO content_field_phone_ottawa (vid, nid, delta, field_phone_ottawa_value) VALUES (15, 15, 0, NULL) in /home/uomeds/public_html/sites/all/modules/cck/content.module on line 1213.I patched not null TRUE to FALSE. But it didn't help existing fields. I just tried deleting all the phone fields and re-creating them and it seems to work now.
#8
Taken into account in 6.2.9 version
#9
I'm getting the following when saving a node with no content in the phone field:
user warning: Column 'field_phonenumber_value' cannot be null query: INSERT INTO content_field_phonenumber (vid, nid, field_phonenumber_value) VALUES (107, 95, NULL) in /srv/welfaretowork.co.uk/public/htdocs/sites/all/modules/cck/content.module on line 1213.Suspect this bug may still be alive?
#10
Oops. Forgot to mention I'm using latest versions of all modules, including Phone (CCK) 6.x-2.9
#11
Update - I've fixed the problem by removing then readding the offending field. No idea what went wrong the first time round. Apologies for the trouble...
#12
Looks like this bug still exists. I just updated from 2.8 to 2.9 and my phone fields would throw the following error
user warning: Column 'field_phone_value' cannot be null query: INSERT INTO content_type_personnel (vid, nid, field_title_value, field_phone_value, field_email_email, field_personnel_photo_fid, field_personnel_photo_list, field_personnel_photo_data) VALUES (250, 250, 'Dramaturg', NULL, NULL, NULL, NULL, NULL) in /Users/john/canary/wilma/sites/all/modules/cck/content.module on line 1213.I had to delete the field and re-create. I think the new version just needs to run an update on the existing fields to make them accept null values.
#13
Forgot to make it active
#14
I upgraded from 2.8 to 2.9 and I am also getting the user warning 'cannot be null'.
#15
A query needs to be run to update all old phone fields. I only had one so I jumped in the database and changed it manually. Set Null to Yes Null and everything works swimmingly.
ALTER TABLE `content_type_event` CHANGE `field_event_phone_value` `field_event_phone_value` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL#16
Fixed in 6.2.10 release
#17
Automatically closed -- issue fixed for 2 weeks with no activity.