Critical SQL error

[flapane]

Hi everyone,

Today I updated from 1.5.48 to the last 1.6.x in the hope to solve the issue, unfortunately without luck.

I noticed such errors in the HOME/ALBUM LIST, as well as in MY GALLERY (and basically in any album), USERS/MY PROFILE, but I may be missing other spots as well. LAST UPLOADS seems to work, though there is a PHP error while clicking on the middle size file, likely because of an old line in my theme (see below, I tried also with a standard cpg theme).

I have already disabled the APIs for the plugin, so to exclude possible causes. I also tried to enable one of the default themes and disabling the custom footer and headers, as well as switching between PHP 5.3 and 7.2, without luck. It somehow seems like a query error, but I would tend to exclude this, otherwise everyone would have the same issue.

Randomly navigating in the sql tables via phpadmin doesn't suggest anything has blown up. HOWEVER, the query number 18 in the attached debug data from HOME/ALBUM LIST produces an error also in Phpmyadmin.

For any tests the gallery is in my signature. I would appreciate if you could help me.

Here the debug data:

HOME/ALBUM LIST

Code Select Expand


While executing query 'SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid, MAX(ctime) AS last_upload, depth AS level, lft
        FROM cpg_categories AS c
        INNER JOIN cpg_albums AS r ON r.category = c.cid
        INNER JOIN cpg_pictures AS p ON p.aid = r.aid
        WHERE c.depth >= 0 + 1
        AND approved = 'YES'
       
       
        GROUP BY r.aid
        ORDER BY NULL' in index.php on line 362

database error: 1055 : 'flapanec_db.c.cid' isn't in GROUP BY
File: /home/flapanec/domains/flapane.com/public_html/gallery/include/functions.inc.php - Line: 250


DEBUG INFO:

Code Select Expand


USER:
------------------
Array
(
    [ID] => 498695d08287a22e9d31a973c3432d43
    [am] => 1
    [lang] => english
    [liv] => Array
        (
        )

)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => flapane
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [can_post_comments] => 0
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [access_level] => 3
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [can_create_public_albums] => 0
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [allowed_albums] => Array
        (
        )

)

==========================
Queries:
------------------
Array
(
    [0] => SELECT name, value FROM cpg_config [include/init.inc.php:180] (0.27 ms)
    [1] => SELECT user_id, time FROM `flapanec_db`.cpg_sessions WHERE session_id = 'fa840586b0c315ef815885579445fcbb' [bridge/coppermine.inc.php:237] (0.07 ms)
    [2] => SELECT user_id, user_password FROM `flapanec_db`.cpg_users WHERE user_id = 1 [bridge/udb_base.inc.php:57] (0.06 ms)
    [3] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `flapanec_db`.cpg_users AS u LEFT JOIN `flapanec_db`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:57] (0.06 ms)
    [4] => SELECT user_group_list FROM `flapanec_db`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/udb_base.inc.php:57] (0.06 ms)
    [5] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:353] (0.09 ms)
    [6] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:358] (0.06 ms)
    [7] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:371] (0.06 ms)
    [8] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:337] (0.07 ms)
    [9] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:395] (0.06 ms)
    [10] => SHOW TABLES LIKE 'cpg_languages' [include/functions.inc.php:4250] (0.38 ms)
    [11] => SELECT * FROM cpg_languages [include/functions.inc.php:4257] (0.1 ms)
    [12] => DELETE FROM cpg_banned WHERE expiry < '2019-09-14 20:31:42' [include/init.inc.php:450] (0.17 ms)
    [13] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '89.247.126.119' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:466] (0.06 ms)
    [14] => DELETE FROM cpg_temp_messages WHERE time < 1568482302 [include/functions.inc.php:5070] (0.13 ms)
    [15] => SELECT cid, lft, rgt, name, description, thumb, depth AS level, '0' AS alb_count, '0' AS subalb_count
        FROM cpg_categories
        WHERE depth BETWEEN 0 + 1 AND 0 + 1
       
        ORDER BY lft [index.php:299] (0.08 ms)
    [16] => SELECT COUNT(DISTINCT(p.aid)) AS alb_count, COUNT(*) AS pic_count
                FROM cpg_albums AS r
                INNER JOIN cpg_pictures AS p ON p.aid = r.aid
                WHERE r.category > 10000
                AND approved = 'YES'
                 [index.php:313] (0.08 ms)
    [17] => SELECT aid, title, r.description, keyword, alb_hits, category, visibility, r.thumb, r.owner, depth AS level, lft, '0' AS pic_count
        FROM cpg_categories AS c
        INNER JOIN cpg_albums AS r ON r.category = c.cid
        WHERE c.depth >= 0 + 1
       
       
        ORDER BY r.pos ASC, r.aid ASC [index.php:335] (0.11 ms)
    [18] => SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid, MAX(ctime) AS last_upload, depth AS level, lft
        FROM cpg_categories AS c
        INNER JOIN cpg_albums AS r ON r.category = c.cid
        INNER JOIN cpg_pictures AS p ON p.aid = r.aid
        WHERE c.depth >= 0 + 1
        AND approved = 'YES'
       
       
        GROUP BY r.aid
        ORDER BY NULL [index.php:362] (0.22 ms)
    [19] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2524] (0.07 ms)
    [20] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2287] (0.06 ms)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
)

==========================
COOKIE :
------------------
Array
(
    [__eucookielaw] => true
    [PHPSESSID] => t3od8ajk1du0miblar9cc38tt5
    [wf_loginalerted_10f997b46bbb01af041d8787afc7a0a27f34e61f83d8c8cf34d9da567c8d940e] => 9e52f24abb11d9660d4e6e3d7b83d03a5bbff70845d6ecc90dcf3560e466836b
    [cpg15x_data] => YTo0OntzOjI6IklEIjtzOjMyOiI0OTg2OTVkMDgyODdhMjJlOWQzMWE5NzNjMzQzMmQ0MyI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czo3OiJlbmdsaXNoIjtzOjM6ImxpdiI7YTowOnt9fQ==
    [f522076206b6e0248e6db76909965468] => cca037a24c475c9f5050619e99b5b361
)

==========================
SESSION :
------------------
Array
(
)

==========================
VERSION INFO :
------------------
PHP version        7.2.22 OK   
MySQL version      5.6.42 OK   
Coppermine version 1.6.06 stable

==========================
Module: GD
------------------
Exact version                    2.1.0                     
GD Version                       bundled (2.1.0 compatible)
FreeType Support                 1                         
FreeType Linkage                 with freetype             
GIF Read Support                 1                         
GIF Create Support               1                         
JPEG Support                     1                         
PNG Support                      1                         
WBMP Support                     1                         
XPM Support                                               
XBM Support                      1                         
WebP Support                     1                         
BMP Support                      1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             http://www.flapane.com/gallery/             
charset              utf-8                                       
allow_private_albums 1                                           
cookie_name          cpg15x                                     
cookie_path          /                                           
impath               /usr/bin/                                   
lang                 english                                     
main_page_layout     breadcrumb/catlist/alblist/random,2/lastup,2
silly_safe_mode      0                                           
smtp_host                                                       
theme                flapane_cpg                                 
thumb_method         gd2                                         

==========================
Plugins
------------------

==========================
Server restrictions
------------------
safe_mode                                                                                                               
safe_mode_exec_dir                                                                                                       
safe_mode_gid                                                                                                           
safe_mode_include_dir                                                                                                   
sql.safe_mode                                                                                                           
disable_functions                                                                                                       
file_uploads             1                                                                                               
include_path             .:/usr/local/php72/lib/php                                                                     
open_basedir             /home/flapanec/:/tmp/:/var/tmp/:/usr/local/php72/lib/::/usr/local/php72/lib/:/usr/local/lib/php/
allow_url_fopen          1                                                                                               
max_execution_time       30                                                                                             
max_input_time           60                                                                                             
upload_max_filesize      64M                                                                                             
post_max_size            64M                                                                                             
memory_limit             128M                                                                                           
suhosin.post.max_vars                                                                                                   
suhosin.request.max_vars                                                                                                 


==========================
Page (performance)
------------------
Parameter        Current  Peak   
Memory usage     3.23 MiB 3.97 MiB
Page generation  33.75 ms 33.75 ms
Page query time  2.33 ms  2.33 ms
Page query count 21       21     


==========================
               






USERS:

Code Select Expand


There was an error while processing a database query.

While executing query 'SELECT user_name, user_email, user_group, user_active, UNIX_TIMESTAMP(user_regdate) AS user_regdate, group_name, user_profile1, user_profile2, user_profile3, user_profile4, user_profile5, user_profile6, user_group_list, COUNT(pid) AS pic_count, SUM(total_filesize) AS disk_usage, group_quota FROM cpg_users AS u INNER JOIN cpg_usergroups AS g ON user_group = group_id LEFT JOIN cpg_pictures AS p ON p.owner_id = u.user_id WHERE user_id ='1' GROUP BY user_id ' in profile.php on line 465

database error: 1055 : 'flapanec_db.u.user_name' isn't in GROUP BY


File: /home/flapanec/domains/flapane.com/public_html/gallery/include/functions.inc.php - Line: 250


Code Select Expand


USER:
------------------
Array
(
    [ID] => 498695d08287a22e9d31a973c3432d43
    [am] => 1
    [lang] => english
    [liv] => Array
        (
        )

)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => flapane
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [can_post_comments] => 0
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [access_level] => 3
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [can_create_public_albums] => 0
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
    [allowed_albums] => Array
        (
        )

)

==========================
Queries:
------------------
Array
(
    [0] => SELECT name, value FROM cpg_config [include/init.inc.php:180] (0.27 ms)
    [1] => SELECT user_id, time FROM `flapanec_db`.cpg_sessions WHERE session_id = 'fa840586b0c315ef815885579445fcbb' [bridge/coppermine.inc.php:237] (0.08 ms)
    [2] => SELECT user_id, user_password FROM `flapanec_db`.cpg_users WHERE user_id = 1 [bridge/udb_base.inc.php:57] (0.07 ms)
    [3] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `flapanec_db`.cpg_users AS u LEFT JOIN `flapanec_db`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:57] (0.06 ms)
    [4] => SELECT user_group_list FROM `flapanec_db`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/udb_base.inc.php:57] (0.06 ms)
    [5] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:353] (0.08 ms)
    [6] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:358] (0.09 ms)
    [7] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:371] (0.06 ms)
    [8] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:337] (0.13 ms)
    [9] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:395] (0.06 ms)
    [10] => SHOW TABLES LIKE 'cpg_languages' [include/functions.inc.php:4250] (0.55 ms)
    [11] => SELECT * FROM cpg_languages [include/functions.inc.php:4257] (0.1 ms)
    [12] => DELETE FROM cpg_banned WHERE expiry < '2019-09-14 20:45:26' [include/init.inc.php:450] (0.19 ms)
    [13] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '89.247.126.119' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:466] (0.06 ms)
    [14] => SELECT user_name, user_email, user_group, user_active, UNIX_TIMESTAMP(user_regdate) AS user_regdate, group_name, user_profile1, user_profile2, user_profile3, user_profile4, user_profile5, user_profile6, user_group_list, COUNT(pid) AS pic_count, SUM(total_filesize) AS disk_usage, group_quota FROM cpg_users AS u INNER JOIN cpg_usergroups AS g ON user_group = group_id LEFT JOIN cpg_pictures AS p ON p.owner_id = u.user_id WHERE user_id ='1' GROUP BY user_id  [profile.php:465] (0.26 ms)
    [15] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2524] (0.11 ms)
    [16] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2287] (0.07 ms)
)

==========================
GET :
------------------
Array
(
    [op] => edit_profile
)

==========================
POST :
------------------
Array
(
)

==========================
COOKIE :
------------------
Array
(
    [__eucookielaw] => true
    [PHPSESSID] => t3od8ajk1du0miblar9cc38tt5
    [wf_loginalerted_10f997b46bbb01af041d8787afc7a0a27f34e61f83d8c8cf34d9da567c8d940e] => 9e52f24abb11d9660d4e6e3d7b83d03a5bbff70845d6ecc90dcf3560e466836b
    [cpg15x_data] => YTo0OntzOjI6IklEIjtzOjMyOiI0OTg2OTVkMDgyODdhMjJlOWQzMWE5NzNjMzQzMmQ0MyI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czo3OiJlbmdsaXNoIjtzOjM6ImxpdiI7YTowOnt9fQ==
    [f522076206b6e0248e6db76909965468] => cca037a24c475c9f5050619e99b5b361
)

==========================
SESSION :
------------------
Array
(
)

==========================
VERSION INFO :
------------------
PHP version        7.2.22 OK   
MySQL version      5.6.42 OK   
Coppermine version 1.6.06 stable

==========================
Module: GD
------------------
Exact version                    2.1.0                     
GD Version                       bundled (2.1.0 compatible)
FreeType Support                 1                         
FreeType Linkage                 with freetype             
GIF Read Support                 1                         
GIF Create Support               1                         
JPEG Support                     1                         
PNG Support                      1                         
WBMP Support                     1                         
XPM Support                                               
XBM Support                      1                         
WebP Support                     1                         
BMP Support                      1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             http://www.flapane.com/gallery/             
charset              utf-8                                       
allow_private_albums 1                                           
cookie_name          cpg15x                                     
cookie_path          /                                           
impath               /usr/bin/                                   
lang                 english                                     
main_page_layout     breadcrumb/catlist/alblist/random,2/lastup,2
silly_safe_mode      0                                           
smtp_host                                                       
theme                flapane_cpg                                 
thumb_method         gd2                                         

==========================
Plugins
------------------

==========================
Server restrictions
------------------
safe_mode                                                                                                               
safe_mode_exec_dir                                                                                                       
safe_mode_gid                                                                                                           
safe_mode_include_dir                                                                                                   
sql.safe_mode                                                                                                           
disable_functions                                                                                                       
file_uploads             1                                                                                               
include_path             .:/usr/local/php72/lib/php                                                                     
open_basedir             /home/flapanec/:/tmp/:/var/tmp/:/usr/local/php72/lib/::/usr/local/php72/lib/:/usr/local/lib/php/
allow_url_fopen          1                                                                                               
max_execution_time       30                                                                                             
max_input_time           60                                                                                             
upload_max_filesize      64M                                                                                             
post_max_size            64M                                                                                             
memory_limit             128M                                                                                           
suhosin.post.max_vars                                                                                                   
suhosin.request.max_vars                                                                                                 


==========================
Page (performance)
------------------
Parameter        Current  Peak   
Memory usage     3.12 MiB 3.91 MiB
Page generation  37.45 ms 37.45 ms
Page query time  2.32 ms  2.32 ms
Page query count 17       17     


==========================
               


[flapane]

The hosting company today confirmed there haven't been any relevant MySQL Server updates.
I am absolutely unable to find further hints.