Fabrik error after github upgrade

[lcollong]

Hi,

I've updated a site running J3.1.5 to the 3.1 github version from Feb the 10th. I've the error below. The column does exist as it is a j_users column. Could it be a compatibility problem (upgrading to J3.2.2 ?). It is working on another server running the same version of Joomla and a F3.1 version with a some weeks ago github version.

Fabrik has generated an incorrect query for the list Evènements:
 
Unknown column 'qb4gr_users.name' in 'field list' SQL=SELECT  DISTINCT `fk_index`.`id` AS `fk_index___id`,  `fk_index`.`id` AS `fk_index___id_raw`,  `fk_index`.`categorie_id` AS `fk_index___categorie_id_raw`,  CONCAT_WS('', TRIM(concat(REPEAT('|— ', (SELECT count(*)-2 FROM qb4gr_categories AS n, qb4gr_categories AS p WHERE (n.lft BETWEEN p.lft AND p.rgt ) AND (n.id = qb4gr_categories.id))), ' ',qb4gr_categories.title))) AS `fk_index___categorie_id`,  `fk_index`.`content_id` AS `fk_index___content_id_raw`,  CONCAT_WS('', 'id : ',qb4gr_content.id,' - ',qb4gr_content.title) AS `fk_index___content_id`,  `fk_index`.`back_link` AS `fk_index___back_link`,  `fk_index`.`back_link` AS `fk_index___back_link_raw`,  `fk_index`.`titre` AS `fk_index___titre`,  `fk_index`.`titre` AS `fk_index___titre_raw`,  `fk_index`.`contenu` AS `fk_index___contenu`,  `fk_index`.`contenu` AS `fk_index___contenu_raw`,  `fk_index`.`type` AS `fk_index___type_raw`,  `fk_types`.`intitule` AS `fk_index___type`,  `fk_index`.`date_time` AS `fk_index___date_time`,  `fk_index`.`date_time` AS `fk_index___date_time_raw`,  `fk_index`.`pages` AS `fk_index___pages`,  `fk_index`.`pages` AS `fk_index___pages_raw`,  `fk_index`.`note` AS `fk_index___note`,  `fk_index`.`note` AS `fk_index___note_raw`,  `fk_index_16_repeat`.`id` AS `fk_index_16_repeat___id`,  `fk_index_16_repeat`.`id` AS `fk_index_16_repeat___id_raw`,  `fk_index_16_repeat`.`parent_id` AS `fk_index_16_repeat___parent_id`,  `fk_index_16_repeat`.`parent_id` AS `fk_index_16_repeat___parent_id_raw`,  `fk_index_16_repeat`.`user_id` AS `fk_index_16_repeat___user_id_raw`,  `qb4gr_users`.`name` AS `fk_index_16_repeat___user_id`,  `fk_index_16_repeat`.`prenom` AS `fk_index_16_repeat___prenom`,  `fk_index_16_repeat`.`prenom` AS `fk_index_16_repeat___prenom_raw`,  `fk_index_16_repeat`.`nom` AS `fk_index_16_repeat___nom`,  `fk_index_16_repeat`.`nom` AS `fk_index_16_repeat___nom_raw`,  `fk_index_34_repeat`.`id` AS `fk_index_34_repeat___id`,  `fk_index_34_repeat`.`id` AS `fk_index_34_repeat___id_raw`,  `fk_index_34_repeat`.`intitule` AS `fk_index_34_repeat___intitule`,  `fk_index_34_repeat`.`intitule` AS `fk_index_34_repeat___intitule_raw`,  `fk_index_34_repeat`.`parent_id` AS `fk_index_34_repeat___parent_id`,  `fk_index_34_repeat`.`parent_id` AS `fk_index_34_repeat___parent_id_raw`,  `fk_index_34_repeat`.`debut` AS `fk_index_34_repeat___debut`,  `fk_index_34_repeat`.`debut` AS `fk_index_34_repeat___debut_raw`,  `fk_index_34_repeat`.`fin` AS `fk_index_34_repeat___fin`,  `fk_index_34_repeat`.`fin` AS `fk_index_34_repeat___fin_raw`,  `fk_index_34_repeat`.`lieu` AS `fk_index_34_repeat___lieu_raw`,  `fk_lieux`.`lieu` AS `fk_index_34_repeat___lieu`,  `fk_index_34_repeat`.`agenda` AS `fk_index_34_repeat___agenda`,  `fk_index_34_repeat`.`agenda` AS `fk_index_34_repeat___agenda_raw`,  `fk_index_34_repeat`.`gcal_id` AS `fk_index_34_repeat___gcal_id`,  `fk_index_34_repeat`.`gcal_id` AS `fk_index_34_repeat___gcal_id_raw`,  `fk_index_34_repeat`.`description` AS `fk_index_34_repeat___description`,  `fk_index_34_repeat`.`description` AS `fk_index_34_repeat___description_raw`,  `fk_index`.`id` AS slug  , `fk_index`.`id` AS `__pk_val`  FROM `fk_index` LEFT JOIN  `qb4gr_content` AS `qb4gr_content` ON `qb4gr_content`.`id` = `fk_index`.`content_id` LEFT JOIN  `qb4gr_categories` AS `qb4gr_categories` ON `qb4gr_categories`.`id` = `fk_index`.`categorie_id` LEFT JOIN  `fk_index_16_repeat` AS `fk_index_16_repeat` ON `fk_index_16_repeat`.`parent_id` = `fk_index`.`id` LEFT JOIN  `fk_index_34_repeat` AS `fk_index_34_repeat` ON `fk_index_34_repeat`.`parent_id` = `fk_index`.`id` LEFT JOIN  `fk_lieux` AS `fk_lieux` ON `fk_lieux`.`id` = `fk_index_34_repeat`.`lieu` LEFT JOIN  `fk_types` AS `fk_types` ON `fk_types`.`id` = `fk_index`.`type` WHERE `fk_index_34_repeat`.`id` IN ('6830','6733','6372','5831','254','336','4227','3971','3925','3533','2945','2943','2944','3232','2115','2114','2111','218','216','2113','2112','1542','1541','1531','1533','1530','1532','334','252','1539','1540','1524','1523','1520','1521','1522','1518','1519','1511','1517','1516','1515','1513','1514','1512','1508','1505','1503','1504','1498','1497','1490','1500','1501','1502','1499','1495','1496','1494','1492','1493','1491','1488','1489','1487','1486','1484','1485','1483','1481','1482','1479','1480','1478','1475','1476','1477','1474','1473','1472','1470','1471','1469','1468','1467','1466','1465','1462','1463','1464','1460','1461','1458','1459','1456','1457','1455','1453','1451','1450','1452','1449','1448','1446','1447','1445','1442','1443','1444','1441','1439','1440','1438','1435','1434','1433','1427','1428','1432','1431','1430','1429','1425','1426','1423','1424','1422','1421','1419','1420','1418','1417','1415','1416','1414','1413','1412','836','262','590','344','672','426','754','508','1780','1781','1778','261','1779','1782','424','260','342','1773','1774','1775','1776','1777','1770','259','1771','1772','583','255','337','419','501','253','335','251','333','332','414','496','250','249','331','248','245','1563','1564','1565','1566','1567','1568','1785','1784','244','1783','1786','243','489','653','735','325','571','407','242','1791','1792','1787','1793','1788','1794','1789','1795','1790','241','1796','1797','1798','1799','1800','1801','1802','1803','1807','1808','240','1804','1805','1806','239','1814','1809','1815','1810','1816','1811','1812','1813','1837','1838','1839','1840','237','1832','1833','1834','1835','1836','1843','1844','1845','236','1841','1842','1852','1847','1853','1848','1849','1850','235','1851','1846','234','1854','1855','1856','1857','643','725','233','315','397','479','561','1672','477','231','559','313','395','394','640','722','476','804','230','558','886','312','475','557','229','311','393','1754','1753','1755','1756','1757','228','227','226','2072','2073','2074','225','224','1667','222','1859','1860','1861','1862','221','1858','220','1752','1749','1750','219','1751','1748','217','215','214','213','212','211','293','375','457','539','621','703','785','210','292','374','456','538','1576','209','1578','1575','1577','1574','1643','1572','1570','1573','207','1571','1569','1759','1760','1761','206','1758','1685','1686','1687','1688','1689','205','204','1762','1763','1764','1765','1864','1865','203','1863','776','858','940','1022','1104','1186','202','1268','284','1350','366','2217','448','530','200','1767','1768','1769','609','1766','199','281','363','445','527','1582','1593','1583','1594','1584','1595','1585','1596','1586','1597','1587','1588','198','1589','1579','1590','1580','1591','1581','1592','1872','1873','1874','1875','1876','1877','1878','197','1870','1871','196','1933','1931','1929','1930','1928','1927','1622','1935','1934','1932','1940','1941','1942','1943','1944','194','1938','1937','1936','1939','1947','1948','1949','1950','1951','1952','193','1945','1946','192','1953','1958','1954','1956','1957','1955','191','190','189','1967','188','1959','1960','1961','1962','1963','1964','1965','1966','2044','2055','2045','187','2046','2036','2047','2037','2048','2038','2049','2039','2050','2040','2051','2041','2052','2042','2053','2043','2054','760','186','514','268','596','350','678','432','1725','1715','1726','1716','1727','1717','1728','1718','1719','1720','1710','1721','1711','1722','1712','1723','1713','1724','1714','1730','1731','183','1732','1729','1736','1741','1742','1737','182','1738','1733','1739','1734','1740','1735','17','1920','1925','185','1921','1918','1923','1922','1924','1917','1926','1919','1454','1437','1436','1509','1507','1822','1537','256','1823','1538','338','1543','1824','257','1544','1825','1510','1545','1826','1506','1534','238','339','1546','1827','1535','1817','1547','1828','1525','1818','1829','1526','1819','1830','1527','1820','1831','1528','1821') AND `fk_index`.`id` IN ('14044','14043','14042','14041','13615','14032','14003','14002','14000','13999','13998','13997','13986','13985','13955','13579','13577','13957','13956','13691','13687','13613','13685','13683','13682','13681','13680','13679','13678','13677
 
The server encountered an internal error or misconfiguration and was unable to complete your request.

 

[rob]

HI Jerome
was that the full query - seems to be truncated at the end?
Also if you run it in phpmyadmin does it work?
I don't see it being a J3.2.2 issue, although I would suggest trying to edit and save the list just incase something got messed up.
-Rob

 

[Sophist]

Could it be something as simple as the SQL statement being too long i.e. this one seems to be truncated after about 8k characters?

It would seem to me that the IN lists are very long and if possible might make more sense to be held in a separate table which is joined rather than using an IN clause.

Just my tuppence-worth.

 

[lcollong]

Hi,

A simplified view shows this error when you can see that the construction of the non-raw part of the DBjoin element is not "ok" (in red). If I unpublish this element, the list is displayed ok. I've try several things but can't make it working. The element is a regular DBjoin one on the J! users table with "id" as fkey and "name" as the display field.

Trying to run the query in phpmyadmin shows an error also except if I delete the red lines. I tried to add the table "qb4gr_users" in the table list and it throws some more error. Seems like there is an hidden "quote" or stange character breaking the query somewhere".... ?

SELECT DISTINCT `fk_index`.`categorie_id` AS `fk_index___categorie_id_raw`,
CONCAT_WS('', TRIM(concat(REPEAT('|? ', (SELECT count(*)-2 FROM qb4gr_categories AS n, qb4gr_categories AS p WHERE (n.lft BETWEEN p.lft AND p.rgt ) AND (n.id = qb4gr_categories.id))), ' ',qb4gr_categories.title))) AS `fk_index___categorie_id`,
`fk_index`.`content_id` AS `fk_index___content_id_raw`,
CONCAT_WS('', 'id : ',qb4gr_content.id,' - ',qb4gr_content.title) AS `fk_index___content_id`,
`fk_index`.`back_link` AS `fk_index___back_link`,
`fk_index`.`back_link` AS `fk_index___back_link_raw`,
`fk_index`.`titre` AS `fk_index___titre`,
`fk_index`.`titre` AS `fk_index___titre_raw`,
`fk_index`.`contenu` AS `fk_index___contenu`,
`fk_index`.`contenu` AS `fk_index___contenu_raw`,
`fk_index`.`type` AS `fk_index___type_raw`,
`fk_types`.`intitule` AS `fk_index___type`,
`fk_index`.`id` AS `fk_index___id`,
`fk_index`.`id` AS `fk_index___id_raw`,
`fk_index`.`date_time` AS `fk_index___date_time`,
`fk_index`.`date_time` AS `fk_index___date_time_raw`,
`fk_index`.`pages` AS `fk_index___pages`,
`fk_index`.`pages` AS `fk_index___pages_raw`,
`fk_index`.`note` AS `fk_index___note`,
`fk_index`.`note` AS `fk_index___note_raw`,
`fk_index_16_repeat`.`id` AS `fk_index_16_repeat___id`,
`fk_index_16_repeat`.`id` AS `fk_index_16_repeat___id_raw`,
`fk_index_16_repeat`.`parent_id` AS `fk_index_16_repeat___parent_id`,
`fk_index_16_repeat`.`parent_id` AS `fk_index_16_repeat___parent_id_raw`,
`fk_index_16_repeat`.`user_id` AS `fk_index_16_repeat___user_id_raw`,
`qb4gr_users`.`name` AS `fk_index_16_repeat___user_id`,
`fk_index_16_repeat`.`prenom` AS `fk_index_16_repeat___prenom`,
`fk_index_16_repeat`.`prenom` AS `fk_index_16_repeat___prenom_raw`,
`fk_index_16_repeat`.`nom` AS `fk_index_16_repeat___nom`,
`fk_index_16_repeat`.`nom` AS `fk_index_16_repeat___nom_raw`,
`fk_index_34_repeat`.`id` AS `fk_index_34_repeat___id`,
`fk_index_34_repeat`.`id` AS `fk_index_34_repeat___id_raw`,
`fk_index_34_repeat`.`intitule` AS `fk_index_34_repeat___intitule`,
`fk_index_34_repeat`.`intitule` AS `fk_index_34_repeat___intitule_raw`,
`fk_index_34_repeat`.`parent_id` AS `fk_index_34_repeat___parent_id`,
`fk_index_34_repeat`.`parent_id` AS `fk_index_34_repeat___parent_id_raw`,
`fk_index_34_repeat`.`debut` AS `fk_index_34_repeat___debut`,
`fk_index_34_repeat`.`debut` AS `fk_index_34_repeat___debut_raw`,
`fk_index_34_repeat`.`fin` AS `fk_index_34_repeat___fin`,
`fk_index_34_repeat`.`fin` AS `fk_index_34_repeat___fin_raw`,
`fk_index_34_repeat`.`lieu` AS `fk_index_34_repeat___lieu_raw`,
`fk_lieux`.`lieu` AS `fk_index_34_repeat___lieu`,
`fk_index_34_repeat`.`agenda` AS `fk_index_34_repeat___agenda`,
`fk_index_34_repeat`.`agenda` AS `fk_index_34_repeat___agenda_raw`,
`fk_index_34_repeat`.`gcal_id` AS `fk_index_34_repeat___gcal_id`,
`fk_index_34_repeat`.`gcal_id` AS `fk_index_34_repeat___gcal_id_raw`,
`fk_index`.`id` AS slug
, `fk_index`.`id` AS `__pk_val`

FROM `fk_index`
LEFT JOIN `qb4gr_categories` AS `qb4gr_categories` ON `qb4gr_categories`.`id` = `fk_index`.`categorie_id`
LEFT JOIN `qb4gr_content` AS `qb4gr_content` ON `qb4gr_content`.`id` = `fk_index`.`content_id`
LEFT JOIN `fk_types` AS `fk_types` ON `fk_types`.`id` = `fk_index`.`type`
LEFT JOIN `fk_index_16_repeat` AS `fk_index_16_repeat` ON `fk_index_16_repeat`.`parent_id` = `fk_index`.`id`
LEFT JOIN `fk_index_34_repeat` AS `fk_index_34_repeat` ON `fk_index_34_repeat`.`parent_id` = `fk_index`.`id`
LEFT JOIN `fk_lieux` AS `fk_lieux` ON `fk_lieux`.`id` = `fk_index_34_repeat`.`lieu`
WHERE `fk_index_34_repeat`.`id` IN ('254','336','3232','2115','2114','1542','1541','1531','1532','1533','1530','508','754','836','590','672','1781','1779','1778','1780','1782','424','342','1771','1772','1773','1774','1775','1776','1777','1770','337','419','501','583','255','253','335','1566','1567','1568','1784','1783','1786','1785','407','571','653','489','735','1790','1791','1792','1793','1788','1794','1789','1795','1799','1800','1801','1802','1803','1797','1798','1804','1805','1806','1807','1808','1809','1815','1810','1816','1811','1812','1813','1814','1833','1834','1835','1836','1837','1838','1839','1840','1841','1842','1843','1844','1845','1853','1848','1849','1850','1851','1846','1852','1847','1854','1855','1856','1857','643','725','479','561','395','477','559','640','722','804','886','475','557','1754','1753','1755','1756','1757','2072','2073','2074','1862','1858','1859','1860','1861','1748','1752','1749','1750','1751','539','621','703','785','374','456','538','1574','1578','1575','1577','1576','1571','1572','1573','1761','1686','1687','1688','1689','1685','204','1762','1763','1764','1765','1863','1864','1865','776','858','940','1022','1104','1186','1268','1350','2217','365','1767','1768','1769','527','609','1766','1582','1593','1583','1594','1584','1595','1585','1596','1586','1597','1587','1588','1589','1590','1591','1592','1871','1872','1873','1874','1875','1876','1877','1878','1929','1930','1928','1935','1934','1932','1933','1931','1943','1944','1938','1937','1939','1940','1941','1942','1948','1949','1950','1951','1952','1945','1946','1947','192','1953','1958','1954','1956','1957','1955','190','1961','1962','1963','1964','1965','1966','1967','1960','2047','2048','2049','2039','2050','2040','2051','2041','2052','2042','2053','2043','2054','2044','2055','2045','2046','760','514','596','678','1721','1722','1723','1713','1724','1714','1725','1715','1726','1716','1727','1717','1728','1718','1719','1720','1729','1732','1730','1731','1738','1739','1734','1740','1735','1741','1736','1742','1737','1921','1926','1923','1919','1925','1918','1920','1922','1924','1823','1527','1824','1528','1825','1826','256','1827','1547','338','1828','257','1829','1534','1830','1820','339','1535','1831','1821','1525','1822','1526') AND `fk_index`.`id` IN ('13615','13997','13986','13985','13691','13687','13623','13622','13621','13620','13616','13614','13606','13605','13604','13603','13602','13601','13600','13598','13597','13596','13595','13594','13592','13591','13590','13589','13586','13582','13580','13572','13571','13570','13568','13567','13566','13565','13564','13563','13562','13561','13560','13559','13558','13819','13555','13554','13553','13552','13551','13549','13548','13547','13820','13544','13543','13546','13599','13690','13617','13695','13618','13688')
ORDER BY `fk_index`.`content_id` DESC

 

[rob]

hmm is the table in the same connection as your joomla database tables?

 

[lcollong]

Arghhh ! You're right ! This is a cloned site for test purpose. I've forgotten to verify the connection which was still on the regular site database. It's not the first time it's happen to me ... Modifying it makes things working ! Sorry for the disturb and thanks for your help.