• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

SQL syntax error issue on List edit save

railer

Member
I have a new Fabrik Form and List in a development site and I was editing an existing record in the list. When I clicked Save, I got the following error:

"Error
1064 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
/home/myaccount/public_html/libraries/joomla/database/driver/mysqli.php:650"

This happens on both the front-end and the backend.

The site is running Joomla 3.8.6 with PHP 7.1.16 and Fabrik 3.8.1.

Is this a Fabrik incompatibility or a PHP misconfiguration?

Thanks!
 
Is this development site a copy of an existing site?
Did you update the Fabrik connection?
 
Is there a fuller stack trace which gives details of the Fabrik php file (inc.cline no) which called Joomla's SQL stack?
 
I have a feeling I have had this error myself - and it was something not defined in an element or list setting - maybe the primary key.

The reason I asked for the php file (likely list.php) and line no. is that one of my unmerged PRs might enhance the error message to give you more details of what is causing the error because the error above is pretty unhelpful in helping you diagnose the cause.
 
My developer has determined that it appears that the specific Fabrik table is corrupt. All other Fabrik lists can be edited except that one. She also turned on Joomla debugging. And then did some checks in phpMyAdmin for joins and group by errors. She is in the process of creating a new table and moving the elements over to it. Then we will see.
 
It seems to me most likely that either:

a. One of the elements settings has become corrupt; or
b. One of the elements settings are incorrect and creating invalid SQL as a result.

As I said I have experienced this myself before though perhaps not quite this error message.

If you are still able to provide me with the stack trace that would be produced, I can code an enhancement that will provide e.g. the SQL that is failing - and then you can track down which element and which setting you need to fix.

I am not sure that fabrik debugging gets any output when you get one of these hard errors, but if you got any is there a copy of the SQL in any of the output accordion rows that can help you.

This is a whole lot less work than trying to migrate an entire table across to a new one.
 
Ok - the stacktrace shows that the SQL call came from line 7820 in /components/com_fabrik/models/list.php which is the SQL execute statement in method insertObject. So it would seem that you are getting this error when you are trying to insert a new row. I assume since you haven't included it, then you are not getting any fabrikdebug output.

What we need to do is to provide an enhanced error reporting on this line. So edit /components/com_fabrik/models/list.php and go to line 7820 which should be:
PHP:
       $db->execute();
and replace it with:
PHP:
        try
        {
            $db->execute();
        }
        catch (Exception $e)
        {
            $msg = 'FABRIK ERROR: Incorrect insert for the list "' . $table . '"';
            if (FabrikHelperHTML::isDebug(true))
            {
                $msg .= ': ' . $e->getMessage() . ': ' . (string) $db->getQuery();
            }
            throw new Exception($msg, $e->getCode());
        }

Let me know if this works (so I can put it into a PR), and what you get as a new error message (so we can try to work out what the issue is).

S
 
New error after inserting your code above is:
Incorrect insert for the list "myaccount_fabrik_joblistings".
Please see attached...
 

Attachments

  • fabrik_error02_stacktrace.png
    fabrik_error02_stacktrace.png
    312.9 KB · Views: 12
Turn Joomla / Fabrik Debug on and retry - and you should get the SQL INSERT statement shown also. From that you will hopefully be able to work out what the problem is.
 
Hi Sophist ? Finally getting around to addressing this problem. So I enabled Joomla Debugging and also Fabrik Debugging. To refresh, in this example, I am attempting to edit an element in a list record and when I save, I get this error when I first select "View Data" next to the list:

Error
An error has occurred with a eval'd field - please inform the web-site owner.
Debug: Eval exception : publish_status_calculated::preFormatFormJoins() : : Constant JROUTER_MODE_SEF already defined.

And then when I attempt to save an edit to one of the records in the list, I get this error:

1064 FABRIK ERROR: Incorrect insert for the list "mysite_fabrik_joblistings": 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: UPDATE `mysite_fabrik_joblistings` SET `jobset_publish_status`='1',`jobset_jobclosed`='0',`jobset_closedbyadmin`='0',`console_output`='Mon Jun 18 2018 15:48:56 GMT-0400 (EDT)',`jobdet_position`='Pre-k Group Supervisor',`jobdet_overview`=' Overview text string here. ',`jobdet_publish_date`='2018-05-04 00:00:00',`jobdet_expiration_date`='2018-06-29 00:00:00',`jobdet_responsibilites`='? Job responsibilities list here.',`jobdet_jobtype`='Full-Time',`jobdet_number_jobs_avail`='1',`jobdet_required_studies`='Bachelors Degree',`jobdet_salary_option`='Hourly',`jobdet_required_lang`='English',`jobdet_salary_req`='0.00',`jobdet_pref_start_date`='2018-05-02 04:00:00',`jobdet_benefits`='Job benefits here. ',`jobdet_start_time`='0830',`jobdet_end_time`='0230',`jobdet_expected_startend_time`='8:30 am-2:30 pm',`jobdet_keystone_star_level`='STAR 1',`jobdet_agegroupposition`='Preschool',`jobdet_min_ece_credits_req`='18',`jobdet_min_career_lattice_level`='Level Here',`jobdet_req_experience`='2-5 years',`jobdet_certification`='No',`jobdet_apply_by`='Email',`jobdet_appl_must_include`='Resume',`jobdet_applyto_value`='user@anydomain.com',`jobdet_otherdocs_req`='',`jobdet_agree_terms`='[\"true\"]',`jobdet_recaptcha`='',`jobdet_agency_name`='Agency Name',`jobdet_agency_type`='0',`jobdet_agencytype_addt_info`='',`agency_details_address`='',`agency_suite_floor`='',`jobdet_region`='Select area/region...',`jobdet_county`='Countyname',`jobdet_city`='CityName',`jobdet_state`='XX',`jobdet_zipcode`='000000',`publish_status_calculated`='',`jobpay_amount`='0',`jobcalc_user`='495',`jobpay_promo_code`='CODE14',`txn_id`='',`ipn_custom`='<br />Promo Code -CODE14 was applied. Amount is now 0',`amt`='0.00',`status`='Completed',`address`='',`subid`='',`jobpay_codevalue`=NULL WHERE
/home/myaccount/domains/mysite.com/subdomains/parent/public_html/components/com_fabrik/models/list.php:7831

Joomla 3.8.8; Fabrik 3.8.1
 
First off, fix that calc element. I doubt it's the root cause, but needs fixing anyway. As a quick test, just unpublish the calc, and see if the query error persists.

Obviously the problem with that query is that the WHERE clause is empty, which at least gives us a clue. However, there's a limit to how much time I can devote to free support.

My developer has determined that [...]

If you can afford a developer to debug Fabrik, I don't think it's unreasonable to get a Fabrik sub, to pay us a few bucks to debug it for you. ;)

-- hugh
 
Check that your List / Data settings has the primary key defined. The where clause should have something like "`id` = 789" to define which record needs updating, so most likely cause as far as I can guess is that Fabrik doesn't know what the primary key field is. That said, it was able to work out how to display the record in the first place so maybe not.
 
Hi Hugh and Sophist ? Thanks for your quick replies. I don't mind chipping in, however, for this project I already have a developer working on the Fabrik part, so I'll have to pass your comments on to her. I guess I could have had her run the debug, but I already have this topic thread running and I want to finish it off, so that it might help someone else. I'll post the outcome here.
 
She said the error is the primary key of the list. It does not allow us to select the correct internal id. She also said the dropdown should only show INT type fields but is showing on VARCHAR fields in the dropdown. The dropdown allows to show console output as an option and the console_output is a varchar field.
 
1. MySQL allows varchars to be primary keys. So I am not sure what the problem is with the dropdown displaying them? That does not, of course, mean that any old varchar should be selected as the primary key any more than any old int should be selected as a primary field - as usual you need to select a field that is suitable for being a primary key.

2. Were you able to select the primary key you wanted, and make the Update work?
 
She said the error is the primary key of the list. It does not allow us to select the correct internal id. She also said the dropdown should only show INT type fields but is showing on VARCHAR fields in the dropdown. The dropdown allows to show console output as an option and the console_output is a varchar field.

Are you talking about the dropdown to select the PK in the List settings Data tab? That will show you any elements in the list which are of type 'internalid' or 'field':

Code:
        <field name="db_primary_key"
               type="listfields"
               valueformat="tableelement"
               nojoins="true"
               filter="internalid|field"
               label="COM_FABRIK_FIELD_PRIMARY_KEY_LABEL"
               description="COM_FABRIK_FIELD_PRIMARY_KEY_DESC"
        />

The 'internalid' type is typically created by Fabrik when you create a new table from scratch when creating a list. But you might need to use a simple 'field' element if you are creating a list from an existing table, where we will default all INT and VARCHAR table fields to Fabrik 'field' elements (although if we find a PK on the table, we will set that to 'internalid').

In other words, that dropdown doesn't look at the underlying MySql field types, it looks at the Fabrik element types.

So if you aren't seeing the element you want to use as the PK in that dropdown, it's probably set to be some other element type. Which is Not A Good Idea (<tm>) for a PK. Either a hidden field set to format as INT, or an internalid are the only two element types you should really ever use as a PK.

-- hugh
 
Thanks for your replies! After all of that analysis, it looks like the reason the internal_id was missing was something so basic I'm embarrassed to share it. But here it is, because it could happen to others.

This particular form has 4 groups associated with it, displaying 4 sections of questions in the form. I had moved all of the elements I created out of the original group into a different group, so thinking it was empty, I unpublished it. But, as you are probably guessing, that group still contained the date_time and id (internalid) elements, which was missing in the dropdown selector.

Re-publishing the group solved the problem and we are now able to assign the id and edit the records.

Also, I'm not sure if this latest problem was related to the problem in the original post I made on April 18th, since I'm pretty sure I unpublished the group between then and June 6th when I resumed posting to the thread. Sorry if this is confusing.
 
Last edited:
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top