xoops forums
  1. Board index
  2. XOOPS Modules Support forums
  3. Module troubleshooting
  4. SQL error with Tag Module
Register

mjoel

Quite a regular
Posted on: 2019/11/7 6:22
mjoel
mjoel (Show more)
Quite a regular
Posts: 297
Since: 2006/12/9
#1

SQL error with Tag Module

2019/11/7 6:22 Hi..
Im using tag module 2.34RC2 downloaded from here https://github.com/XoopsModules25x/tag/
XOOPS Version XOOPS 2.5.10
PHP Version 5.6.40
mySQL Version 5.7.28

no tags are listed in index and blocks.. i received this sql error in debug message

SELECT DISTINCT(o.tag_id), o.tag_term, o.tag_status, SUM(l.tag_count) AS count, l.tag_modid FROM tag_tag AS o LEFT JOIN tag_stats AS l ON l.tag_id = o.tag_id GROUP BY o.tag_id ORDER BY count DESC LIMIT 0, 100

Error message: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxx.l.tag_modid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

zyspec

Module Developer
Posted on: 2019/11/7 15:14
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#2

Re: SQL error with Tag Module

2019/11/7 15:14 Let me first say that I HAVEN'T ACTUALLY TRIED THIS.... so it could I'm mis-reading the code but:

It looks like ./block/block.php line 115
$criteria->add(new Criteria('l.tag_modid', $modid));

should be:
$criteria->add(new Criteria('o.tag_modid', $modid));


and in line 120:
if (!$tags = &$tagHandler->getByLimit(0, 0, $criteria, null, empty($options[1]))) {

should be:
if (!$tags = $tagHandler->getByLimit(0, 0, $criteria, null, empty($options[1]))) {


In theory that should help fix the issue in the block. Let us know if that works and if so then we can find the other place(s) in the code that may have similar issues.

mjoel

Quite a regular
Posted on: 2019/11/13 0:03
mjoel
mjoel (Show more)
Quite a regular
Posts: 297
Since: 2006/12/9
#3

Re: SQL error with Tag Module

2019/11/13 0:03 still have this error in the index page (no tags are listed) and top page where top tag block is activated

0.000475 - SELECT DISTINCT(o.tag_id), o.tag_term, o.tag_status, SUM(l.tag_count) AS count, l.tag_modid FROM tag_tag AS o LEFT JOIN tag_stats AS l ON l.tag_id = o.tag_id WHERE (o.tag_status = '0') GROUP BY o.tag_id ORDER BY count DESC LIMIT 0, 50
Error number: 1055
Error message: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxxx.l.tag_modid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]

zyspec

Module Developer
Posted on: 2019/11/14 16:50
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#4

Re: SQL error with Tag Module

2019/11/14 16:50 Sorry... I apparently didn't read far enough down in the code...

You also need to change lines 265 and 405 in ./blocks/block.php from:
$criteria->add(new Criteria('l.tag_modid', $modid))

to:
$criteria->add(new Criteria('o.tag_modid', $modid))

mjoel

Quite a regular
Posted on: 2019/11/15 0:11
mjoel
mjoel (Show more)
Quite a regular
Posts: 297
Since: 2006/12/9
#5

Re: SQL error with Tag Module

2019/11/15 0:11 thank you zyspec..but after i edited the block.php file i received blank page 500 error..
in debug message the sql error is now gone but i received blank page..

disable the block..the 500 error is gone

i double check the index page where my tags are not listed

i have the same sql error

0.000370 - SELECT DISTINCT(o.tag_id), o.tag_term, o.tag_status, SUM(l.tag_count) AS count, l.tag_modid FROM tag_tag AS o LEFT JOIN tag_stats AS l ON l.tag_id = o.tag_id GROUP BY o.tag_id ORDER BY count DESC LIMIT 0, 100
Error number: 1055
Error message: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxx.l.tag_modid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Mage

Core Developer
Posted on: 2019/11/15 8:05
Mage
Mage (Show more)
Core Developer
Posts: 183
Since: 2009/8/2 1
#6

Re: SQL error with Tag Module

2019/11/15 8:05 This problem is explained here:

https://www.grafikart.fr/tutoriels/only-full-group-by-sql-1206

He is present with mysql 5.7! The problem may need to be fixed in the core of xoops.

zyspec

Module Developer
Posted on: 2019/11/15 14:31
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#7

Re: SQL error with Tag Module

2019/11/15 14:31 @mjoel - sorry this hasn't worked (yet). I'll load the module on my test site and look at it within the next several days.

@Mage - good catch. This should be able to be fixed though by modifying the 'Criteria' used to build the SQL query string. There's a way to build a query that includes the ability to add a "function" on a column. Once we figure out which column is 'unique' then we ensure we add the right function to the query....

zyspec

Module Developer
Posted on: 2019/11/15 22:53
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#8

Re: SQL error with Tag Module

2019/11/15 22:53 @mjoel,

Can you tell me what other modules you're using? Using the following config I don't get any errors yet.
Quote:

XOOPS Version XOOPS 2.5.10
PHP Version 7.2.24
mySQL Version 5.7.27-log

I installed the module and enabled each of the blocks one-at-a-time on the home page without any problems. I also do not get any errors if I go to the module home page (./modules/tag/index.php).

I do not have any tags yet because I want to make sure I load a module with tag support similar to what you're doing so I can replicate the problem.

I checked to make sure the SQL call is being executed - here's the resulting SQL statement with the Cumulus Tag Block enabled:
SELECT DISTINCT(o.tag_id), o.tag_term, o.tag_status, SUM(l.tag_count) AS count, l.tag_modid FROM tag_tag AS o LEFT JOIN tag_stats AS l ON l.tag_id = o.tag_id WHERE (o.tag_status = '0' AND l.tag_modid = '11' AND l.tag_catid = '0') GROUP BY o.tag_id ORDER BY count DESC LIMIT 0, 100

mjoel

Quite a regular
Posted on: 2019/11/17 23:22
mjoel
mjoel (Show more)
Quite a regular
Posts: 297
Since: 2006/12/9
#9

Re: SQL error with Tag Module

2019/11/17 23:22 thank you zyspec

i only use the tags only in my publisher module and another 4 clone publisher module..my current publisher is 1.07 final

XOOPS Version XOOPS 2.5.10
PHP Version 5.6.40
mySQL Version 5.7.28

System 2.14
Publisher 1.07 Final (4 clones)
Tag 2.34 RC2
MyLinks 3.11 RC4
xoopstube 1.06RC1
TDMDownloads 1.66 Beta 1
User Profile 1.9
PM 1.14 Final
XSitemap 1.52RC1
Sitemap 1.3
extgallery 1.13RC1
extcal 2.39 Final
xoopspoll 1.4 Beta 1
xoopsfaq 1.01
isearch 1.9
logcounterx 2.73
protector 3.61 Final
indexscan 2.03
xlanguage 3.11
defacer 1.12 Beta1
myMenu manager 1.3

currently the table xxx_tag_tag have 524 entries and the xxx tag_link have 1648 entries

zyspec

Module Developer
Posted on: 2019/11/19 15:36
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#10

Re: SQL error with Tag Module

2019/11/19 15:36 @mjoel,

Thanks... I'll load publisher and create a few test tags. I'll enable "ONLY_FULL_GROUP_BY" in my SQL server and make sure to test with it enabled.
(1) 2 »