Site breaking error...unknown column 'ua.id'

[JayGreco]

Any ideas on how to fix this ?

If not, I can restore from backup...

Thanks !

- Jay


Unknown column 'ua.id' in 'on clause' SQL=SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.images, a.urls, a.language, a.metakey, a.metadesc, a.metadata,c.title AS category_title, c.alias AS category_alias,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM tpjt8_content as a LEFT JOIN tpjt8_categories AS c ON c.id = a.catid LEFT JOIN tpjt8_users AS ua ON ua.id = a.created_by LEFT JOIN tpjt8_users AS um ON um.id = a.modified_by LEFT JOIN tpjt8_content_rating AS v ON a.id = v.content_id LEFT JOIN tpjt8_viewlevels AS vl ON a.access = vl.id LEFT JOIN tpjt8_languages AS l ON a.language = l.lang_code LEFT OUTER JOIN (select rsi.provider_id, rsi.order from tpjt8_roksprocket_items as rsi where module_id = 204) rsi on a.id = rsi.provider_id WHERE ((a.access IN(1,1,2,3) AND (a.state != -2)) AND a.catid IN (15)) ORDER BY rsi.order
Unknown column 'ua.id' in 'on clause' SQL=SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.images, a.urls, a.language, a.metakey, a.metadesc, a.metadata,c.title AS category_title, c.alias AS category_alias,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM tpjt8_content as a LEFT JOIN tpjt8_categories AS c ON c.id = a.catid LEFT JOIN tpjt8_users AS ua ON ua.id = a.created_by LEFT JOIN tpjt8_users AS um ON um.id = a.modified_by LEFT JOIN tpjt8_content_rating AS v ON a.id = v.content_id LEFT JOIN tpjt8_viewlevels AS vl ON a.access = vl.id LEFT JOIN tpjt8_languages AS l ON a.language = l.lang_code LEFT OUTER JOIN (select rsi.provider_id, rsi.order from tpjt8_roksprocket_items as rsi where module_id = 155) rsi on a.id = rsi.provider_id WHERE ((a.access IN(1,1,2,3) AND (a.state != -2)) AND a.id IN (77,80,79,78)) ORDER BY IF(ISNULL(rsi.order),1,0),rsi.order

 

[JayGreco]

I can see the lists under list view...but, get this error message under form view:

Unknown column 'u.id' in 'on clause' SQL=SELECT e.*, e.ordering AS ordering,e.id FROM tpjt8_fabrik_elements AS e LEFT JOIN tpjt8_users AS u ON checked_out = u.id LEFT JOIN tpjt8_fabrik_groups AS g ON e.group_id = g.id LEFT JOIN tpjt8_fabrik_formgroup AS fg ON fg.group_id = e.group_id LEFT JOIN tpjt8_fabrik_lists AS l ON l.form_id = fg.form_id WHERE (e.published IN (0, 1)) ORDER BY ordering

 

[JayGreco]

Somehow it seems that I have deleted the id field in the users table. Added the field again and everything seems to work (a restored the individual user ids, but am still checking that I have the field attributes e.g. auto_increment) set correctly.

Not sure how I did it but - I had been creating and deleting and renaming several elements, then attempting to clean up the trash and deleting unused elements.

I am still absolutely delighted with Fabrik. : - )

My goal over this first week was to experiment and design. And, with the prototype I have -- I will probably enter the fabrik changes from scratch on a clean version of the initial site.

Additional insight here -- appreciated.

Best regards,

- Jay

 

[p38]

I think there is a bug here, as I also encountered this, esp when you use a user element. I also deleted and added and it came right. I had to clear the cache as well, it seems F3.1 is more prone to cache issues, not updating.

Rob, please look into this, if you add a user element, and set the element to hidden, and disable listview, for some reason it is still trying to left join the id of the jos_users table

 

[cheesegrits]

I've identified an issue where it will use the wrong field name the first time you load a list with a new user element in it, but that particular bug will self-fix, and should clear up by just loading the list again.

Is this not the case with your new user elements?

-- hugh

 

[cheesegrits]

Oh ... also ... there is a chance you may have some extra, orphaned rows in your #__fabrik_joins table. We had a bug for a while that was re-creating rather than updating rows for join elements when editing. I think under certain circumstance, this can cause an un-necessary LEFT JOIN to be added to our main data query.

Have a look at that table, using phpMyAdmin, and see if it looks like there is any obviously duplicate rows in there, where everything (except the main id) is the same.

-- hugh

 

[JayGreco]

Thanks Hugh.

I wound up restoring the site from recent backup, in part because I had done so much tinkering and testing that I wanted to start again clean. Sometime the first round is best as a throwaway.

Anyway, this gives me an idea for a feature set or way of thinking for you guys.

1. Put into the global options a 'protected mode' switch, which prevents fabrik from modifying any non-fabrik tables.

2. Add a import/export feature that lets a user backup/restore just the fabrik structures and/or data. This could just be a set of instructions for using Akeeba that helps people move only their fabrik SQL tables.

Ideally, a person could create a set of fabrik forms then easily move these to another site. This would probably not include fabrik content strings embeded in articles or modules, but either a clean-up tool or a simple stub that ignores {fabrik calls} would be cool.

If you make it easy to import individual lists, forms and structures -- perhaps as packages (?) -- your user community can start exchanging forms etc.. Fore example, I will be immplementing a timesheet as part of my HR system. I could then offer just the timesheet to others for their fabrik projects...

3. Related to #2, above, in the global options there is a clean fabrik feature. Not sure this works and may be worth a test. Making it easy to uninstall / reinstall / port fabrik portions of a site is good extension etiquette that can save lots of user requests later...

Of course, getting a robust 3.1 out is the first priority...

Best regards,

- Jay

 

[rob]

1. Put into the global options a 'protected mode' switch, which prevents fabrik from modifying any non-fabrik tables.
The 'Alter field types' option does this already I think

2. Packages were designed to allow for exporting a series of lists/forms for installation on other sites, they really rough around the edges but should allow you to export a zip which can be installed via the Joomla installer.
The issue with a simple 'export the db structure' is that if you want to install your exported data along side an existing fabrik set up, then all the internal id references won't match up. So the package code creates a new set of fabrik tables - prefix with the package name and the Fabrik code is clever enough to know which package it is in and to use the designated set of tables. As you say a stable 3.1 is important but I also agree that packages would be really useful.