Re: SQL error with Tag Module [Module troubleshooting]

1

SQL error with Tag Module

2019/11/7 6:22
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

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

2

Re: SQL error with Tag Module

2019/11/7 15:14
zyspec
Module Developer
Posts: 1095
Since: 2004/9/21

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.

3

Re: SQL error with Tag Module

2019/11/13 0:03
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

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]

4

Re: SQL error with Tag Module

2019/11/14 16:50
zyspec
Module Developer
Posts: 1095
Since: 2004/9/21

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))

5

Re: SQL error with Tag Module

2019/11/15 0:11
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

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

6

Re: SQL error with Tag Module

2019/11/15 8:05
Mage
Core Developer
Posts: 209
Since: 2009/8/2 1

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.

7

Re: SQL error with Tag Module

2019/11/15 14:31
zyspec
Module Developer
Posts: 1095
Since: 2004/9/21

@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....

8

Re: SQL error with Tag Module

2019/11/15 22:53
zyspec
Module Developer
Posts: 1095
Since: 2004/9/21

@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

9

Re: SQL error with Tag Module

2019/11/17 23:22
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

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

10

Re: SQL error with Tag Module

2019/11/19 15:36
zyspec
Module Developer
Posts: 1095
Since: 2004/9/21

@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.

Stats
Goal:	AU$15.00
Due Date:	Jul 31
Gross Amount:	AU$0.00
Net Balance:	AU$0.00
Left to go:	AU$15.00

SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Login

Search

Recent Posts

Re: New module: Smartshare

SmartLike (coming)

Re: New module: Weather

Re: XOOPS Custom Field Module

Re: XOOPS Custom Field Module

XOOPS Custom Field Module

Re: New module: Smartshare

Re: New module: Weather

Re: New module: Weather

New module: Weather

Who's Online

Donat-O-Meter

Latest GitHub Commits

{{ record.sha.slice(0, 7) }} - {{ record.commit.message | truncate }}