XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Modules Support forums / 
  3. Module troubleshooting 
  4.  / SQL error with Tag Module
Register
1
mjoel
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

le="color: #000000"><?php 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
zyspec
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
le="color: #000000"><?php $criteria->add(new Criteria('l.tag_modid', $modid));

should be:
le="color: #000000"><?php $criteria->add(new Criteria('o.tag_modid', $modid));


and in line 120:
le="color: #000000"><?php if (!$tags = &$tagHandler->getByLimit(0, 0, $criteria, null, empty($options[1]))) {

should be:
le="color: #000000"><?php 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
mjoel
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

le="color: #000000"><?php 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
zyspec
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:
le="color: #000000"><?php $criteria->add(new Criteria('l.tag_modid', $modid))

to:
le="color: #000000"><?php $criteria->add(new Criteria('o.tag_modid', $modid))
5
mjoel
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

le="color: #000000"><?php 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
Mage
Re: SQL error with Tag Module

  • 2019/11/15 8:05

  • Mage

  • Core Developer

  • Posts: 211

  • 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
zyspec
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
zyspec
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:
le="color: #000000"><?php 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
mjoel
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
zyspec
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.
(1) 2 »

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Donat-O-Meter

Stats
Goal: $15.00
Due Date: Jul 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!

Latest GitHub Commits