Can't save object with nil foreign keys

Schema & Data
1

This bug have been persistent now for a couple of releases of DF and it have been driving me crazy.

I have a foreign that CAN BE nil. When I try to edit any other field and save I get that error

 DreamFactory Table Module
DreamFactory Table Module: Failed to patch records in 'theme'.
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`bitnami_dreamfactory`.`theme`, CONSTRAINT `fk_theme_applock` FOREIGN KEY (`applock`) REFERENCES `app` (`id`))

API Error
Failed to patch records in 'theme'.
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`bitnami_dreamfactory`.`theme`, CONSTRAINT `fk_theme_applock` FOREIGN KEY (`applock`) REFERENCES `app` (`id`))

Obviously there is an incorrect constraint on the foreign key.

The weird thing is that if I try to clear out ONLY that foreign key then save, it does work. It only save if the current save is clearing the foreign key, if any other properties are involved in the save then it fails.

Is there any work around that issue?

1 Like
2

This can get a little tricky… Can you send a screenshot of the two tables that you have “related” to each other - so that we know that they’re defined properly.

Are you able to patch records using related tables successfully? It looks like the relationship is somehow not setup correctly within your DB schema… For example, if the field your referencing (id) cannot be nil but the created table that references that field can be - it won’t go through, because in the case of “id” - being null - there will be an error as primary keys must have a value…

Picture of how you setup the fields would be great, possibly even some JSON within the Live API upon patch.

Thanks,

  • Mark
3

I emailed you the full scheme I think the issue is what you are saying most probably.

I basically can’t have a field that allows null that references a primary key in another table?

5

okay I think I finally figured it out. That issue happens if I set the “ALLOW NULL” as checked. If I don’t then it works just fine.

MySQL foreign keys are nullable by default so I don’t have to select anything. The edge case is “ALLOWS NULL” . I am not entirely sure what is happening in the backend but that seems like a bug in DF.

7

Hey, one quick question for ya here:

which table(s) ->field(s) did you set to “Allow Null” values? If you’d like you can email me the 3 JSON schemas again with the config that was able to get it to work, then I’ll be able to easily compare the two sets.

Thanks,

  • Mark
8

The foreign key. I just don’t use “Allow Null” for foreign keys anymore and everything behaves like it should.

I have “Allow Null” unchecked for the foreign key yet I can still set nil and everything works just fine without any errors.

9

i upgraded to 1.7.8 and this error is back. Now I HAVE TO set the foreign key as “Allow Null” otherwise it won’t let me set it as null and if I do that then I get the old error back.

This has become insanely frustrating.

10

Hey,

It looks like with the upgrade to 1.7.8 the actual issue was fixed, as you shouldn’t be able to not have “allow null” checked and insert null values. So, although it may require you going back through your schema and changing these fields back, in the long run, it’s a better solution.

Also, be sure to do the /web/logout after you upgraded, this will update the schema/db if there is anything to update there.

We’re here to help you. Just know that. Also, as far as the memcache issue - send me an email mark@dreamfactory.com - I’ll guide you through getting the pre-req packages installed for your linux machine.

Thanks man - hang in there, we’ll get everything taken care of. I’m about to reply to your email with a couple links that I’ve been looking at - I just need to be sure of your environment. It’s AWS running Ubuntu 12.04 or 14 - that’s my assumption, at least.

Be sure to respond, and I’ll get ya taken care of.

Thanks,

  • Mark
11

Hi, I am having a similar issue, only using the API to add a user:

/rest/user/register

[2015-09-26 18:15:25][app][ERROR ] REST Exception #23000 > Failed to register new user!
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (dreamfactory.df_sys_user, CONSTRAINT fk_df_sys_user_created_by_id FOREIGN KEY (created_by_id) REFERENCES df_sys_user (id)) {“host”:“svc.xxx-xxx.com:80”,“request_uri”:"/rest/user/register?app_name=headache_diary",“source_ip”:“76.27.117.48”,“sapi_name”:“apache2handler”}
[2015-09-26 18:15:25][app][ERROR ] CDbCommand::execute() failed: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (dreamfactory.df_sys_user, CONSTRAINT fk_df_sys_user_created_by_id FOREIGN KEY (created_by_id) REFERENCES df_sys_user (id)). The SQL statement executed was: INSERT INTO df_sys_user (is_active, is_sys_admin, is_deleted, user_source, created_date, email, first_name, display_name, role_id, confirm_code, password, default_app_id, last_modified_date, last_modified_by_id, created_by_id, user_data, last_login_date) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13, :yp14, :yp15, :yp16). Bound with :yp0=1, :yp1=0, :yp2=0, :yp3=0, :yp4=‘2015-09-26 18:15:25’, :yp5=‘jj6@example.com’, :yp6=‘jj6’, :yp7=‘jjg’, :yp8=2, :yp9=‘y’, :yp10=’$2a$13$EYvaaznjQyo15yjpSRzIL.WHELAXEE.ztZTxaChANVSqsaX413jRG’, :yp11=NULL, :yp12=‘2015-09-26 18:15:25’, :yp13=12, :yp14=12, :yp15=’[]’, :yp16=NULL.

12

Interestingly, it actually worked once - with no code changes. I researched online, posted the above post, etc., and then for kicks I hit “register” in my app once again, and it said that email was already in use - odd! So thinking against my better reasoning that it magically fixed, I deleted that user in the Dreamfactory CP, hit register again, and back to the above issue. Very odd!

Thanks,
Tim

13

This is a stretch, but @Mark, or anyone else, if you’re out there. I’m having what might be the same problem. Running PostgreSQL and have a table with no foreign keys at all but getting the message. I made an issue at "Cannot add or update child row" error when creating role