Editing a form then saving returns 'You have an error in your SQL syntax"

[dealio]

I have a form based on a join of two lists. I can add new record fine. Data is inserted into dbase tables correctly. But if I edit the record and save it, I get the following error.

=================================================
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=UPDATE `dbase23_mv_content` SET `ContentId`='84',`date_time`='2013-09-15 00:51:05',`report_id`='92',`firstname`='sdffdsfsd',`createdby`='108',`updatedby`='108' WHERE
===================================================

It stops at WHERE clause.

I checked the ids of both lists and the elements are all published and linked. I had a similar issue in the past and it was because the id was not specified in the list configuration/data section.

Thanks.

 

[jfquestiaux]

Sometimes, re-saving the join solves the problem: open your list, check that the join data are correct and save/close the list.

 

[dealio]

Hi, I restored from a back-up database and re-did the join and still get the same error.

Steps:

1.I made a copy of list (ensure the primary id is specified "data_config_screen.jpg")
2. I joined the copied list to another list (see screen shot join.jpg")
3. Saved changes.
4. Adding a record works fine and the date is stored fine in the database (see screen shot "save_new_record_OK.jpg")
5. When I Edit the same record and save changes, it returns the syntax error. (see image "edit_record_save.jpg")


Your right something is wrong with the join because the stand alone lists I can add and edit data just fine. It's only affecting the joined list.

I download and patched from github 3 days ago

Thanks

 

[dealio]

I restored to vanilla 3.1 beta (the original downloadable file). I re-created the join in the screen shot above and it works fine. I can edit and save the data without getting the syntax error message. My configuration is identical to the screenshots above.

 

[dealio]

I just applied the latest github, tried to save an edited record and get the syntax error. So It looks like something in git hub update causing it to error out.

 

[Sophist]

Can you check in List / Data /Data that the Primary Key is set?

(The missing WHERE clause should probably say something like "WHERE id = 254".)

 

[dealio]

Primary key is set. I replaced the github file with the vanilla fabrik file and i'm able to save fine. as soon as I upload the github files then it returns the syntax error.

 

[cheesegrits]

Can you try a LEFT rather than INNER join?

I'll take a look at this when I wake up, but it's coming up 5:30am, time to get some sleep.

-- hugh

 

[dealio]

Yes i switched it to left join as you suggested.

See the screenshots in order of what i did.


1. Check join in database see (image: "mysql_fabrik_join_table.jpg" and "mysql_fabrik_join_table2.jpg")

2. This images Shows I'm attempting to save a edited record using the latest github. I get the syntax error when I click on save or apply. ( Image: "save_edited_record_github.jpg")

3. Now, I replace the github update files with original fabrik vanilla 3.1 files ( see image:"restore to vanilla fabrik_3_1.jpg" )

4. Finally, I went back to the form and edit the form and click on apply and saves record successfully. (see image: "save_edited_record_no_github.jpg") no error message, I checked the database and the date values are good.

 

[cheesegrits]

OK, I'll see if I can replicate here.

-- hugh

 

[dealio]

I was able to reproduce this after another clean install and test then github update. I narrowed it down to, within the lists that are joined, if you change the internal "id" primary key element name to anything but the default "id" it will return the syntax error upon saving an edited view form based on a 2 joined lists.

When I name both joined elements keys ids back to "id" then it works. This only happens with the github update. If I roll back to vanilla fabrik beta this doesn't happen and it works fine, I can name the the internal ids to anything and it it will not error out.


Steps to reproduce , get latest github and patch
1. create two lists and join them
2. change both internal "id" name to something else, save it
3. go to list, re-select the new primary key name, save it
4. add data to form, it will be fine.
5. No try to edit the form, when you save you will get syntax error.
6. rename the interalid elements back to original "id" save.
7. got to list re-select primary key, and save
8. go back to form edit, and now it will work.

9. Now roll back to vanilla and repeat the above steps and you won't get the syntax error.

 

[troester]

Not sure if this is really a bug.
I think trying to rename (PK) elements AFTER they have been used in a join is some sort of corner case.
I assume your join is still showing ON x.id = y.id.
Did you recreate the join after renaming the elements?

BTW: I don't think it makes sense to join two autoincrement PKs at all.

 

[dealio]

Its only the left table PK join table. I tried as you suggested, I renames the PK "id" to "reportID" then deleted the join then recreated but it still return the error. Then i rolled back the to vanilla files (no github updates) and it works fine.


Also when this issue originally occurred I migrate the fabrik from 3.o8 tables to 3.1 table so the pk id was never modified. But if i when I did name it back to "id" it works.

However in my 3.08 instance, I can rename the "id" to something else element in 3.o8 fine and it works fine.



I'm jut going to leave PK as name"id", it seems to work fine that way and Im happy with it.

 

[Sophist]

As I understand it, one of the big changes between 3.0 and 3.1 is in how joins are handled. So it is possible that there is a bug which expects the primary key to be called id.

I think that it is Hugh who is the joins expert, so we need to wait for him to get back from his weekend away and take a look.

 

[troester]

Ah, I've got a vague recollection of such an issue (Fabrik2/3.0/3.1?).
Maybe it crept in again.

 

[dealio]

This is with 3.1 recent github.

 

[ghiebert]

Just started with Fabrik. I couldn't edit either, although I could add and delete. I got the same error message. When I set up my form yesterday, all my 'varchar' fields (that's all I had) were changed to 'text' by Fabrik, not sure why. (What's wrong with varchar?) Since that stops me from having a primary, because of the fields being text, I thought that was the issue. But not so. Somewhere in the process yesterday I had added an autoincrement ID field because I know you need an indexed field. I then took a clue from Sophist above and added the ID field as an Element in Fabrik and made it hidden. Now I can edit without an error. Also I have changed one field to varchar and made it primary just because I am stubborn.
We'll see if Fabrik dislikes that.

 

[jmoises]

I did get the same error and in fact i did find why.

I have a form with many elements and groups for some reason i remove from the list of groups in the form, the main group where the ID and Date use to be, after notices that in the Liste the ID was not as primary key i notices that the elements where not there anymore, so i move the ID and Date to a valid and active group and all work again.

hopes helps somebody

 

[cheesegrits]

I think troester is probably correct, that changing the element name after you have used that element in a join will probably break something. Off the top of my head, I don't think we go and check the #_fabrik_joins metadata table to see if that element is being used in any joins. We probably should do, but then ... there's a lot of things we probably should do. But there's only 24 hours in a day.

As for why we change varchar to 'text' ... varchar is by it's nature restricted in length. For a lot of element types, we have no a-priori knowledge of what size the data will be, so we use text fields. You can always stop us from changing field types, by setting "alter existing" to "No" in the advanced list settings.

-- hugh