Error 1064 with Mysql Syntax, Gallery won't show full album

[CeeCee]

Hi, I've come across this issue on my website and I have searched the board and Google and haven't found anything close to helping my issue.

My issue: When trying to view an album that I just batched which has 1,386 files in it I hit a wall when trying to view the entire album. It allows you to view only so many images before it throws out an error. It seems to stop before any images past 1,000 can show.

This album works when showing thumbnails for pages 1-20 (https://sebastian-stan.com/imgs/thumbnails.php?album=937) and then doesn't show anymore pages though there are over 1,000 images and the last thumbnail it shows is 999.jpg

I thought it was my theme, so I tried viewing the album using the Curve theme. Same issue. I adjusted "Number of albums to display" in my config and no matter the variation is won't go any further. I have a few Coppermine galleries on the same host/server and my other site that has over 1,000 in one album works without issue. So it seems to be only on this one.

Last working album link: https://sebastian-stan.com/imgs/thumbnails.php?album=937&page=20
Breaks on page 21: https://sebastian-stan.com/imgs/thumbnails.php?album=937&page=21

Note: I originally upgraded to Release 1.6.10 last year but thought I messed something up and after replacing files with the zip file of that version I downloaded the cpg_installer_stub.php script and allowed it to install the 1.6.09 version. Though ultmiately this seems to be a MYSQL issue and those go right over my head so hopefully you guys can help.

Database Log:

Code Select Expand

Feb 20, 2021 at 02:57 AM - While executing query 'SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip FROM cpg_pictures AS r
WHERE ((aid = 937 ) )
ORDER BY filename DESC, pid DESC
LIMIT 0 ,-54' in include/functions.inc.php on line 1435 the following error was encountered:
1064 : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-54' at line 4

Debug Information:

Code Select Expand

USER:
------------------
Array
(
    [ID] => 75fb4095e9e275fa98e7d12f6dda4170
    [am] => 1
    [lang] => english
    [liv] => Array
        (
            [0] => 41202
            [1] => 39569
            [2] => 24447
            [3] => 23895
            [4] => 40766
        )

    [liv_a] => Array
        (
            [0] => 877
            [1] => 878
            [2] => 937
            [3] => 599
            [4] => 719
        )

    [theme] => curve
    [sort] => na
)

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

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [can_post_comments] => 1
    [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:176] (0.25 ms)
    [1] => SELECT * FROM cpg_plugins ORDER BY priority [include/plugin_api.inc.php:51] (0.14 ms)
    [2] => SELECT user_id, time FROM `sebstan_cpg`.cpg_sessions WHERE session_id = '6f47b39d22fd470a8c505619d566dca0' [bridge/coppermine.inc.php:237] (0.17 ms)
    [3] => SELECT user_id, user_password FROM `sebstan_cpg`.cpg_users WHERE user_id=1 [bridge/udb_base.inc.php:59] (0.07 ms)
    [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `sebstan_cpg`.cpg_users AS u LEFT JOIN `sebstan_cpg`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:59] (0.07 ms)
    [5] => SELECT user_group_list FROM `sebstan_cpg`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/udb_base.inc.php:59] (0.06 ms)
    [6] => 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:355] (0.06 ms)
    [7] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:360] (0.05 ms)
    [8] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:373] (0.05 ms)
    [9] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:333] (0.47 ms)
    [10] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:391] (0.23 ms)
    [11] => DELETE FROM cpg_mod_online WHERE last_action < NOW() - INTERVAL 10 MINUTE [plugins/onlinestats/codebase.php:119] (0.27 ms)
    [12] => REPLACE INTO cpg_mod_online (user_id, user_name, user_ip, last_action) VALUES ('1', 'Carol', '173.94.155.0', NOW()) [plugins/onlinestats/codebase.php:121] (0.22 ms)
    [13] => DELETE FROM cpg_banned WHERE expiry < '2021-02-20 07:12:47' [include/init.inc.php:446] (0.95 ms)
    [14] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '173.94.155.0' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:462] (0.24 ms)
    [15] => SELECT category, title, aid, keyword, description, alb_password_hint FROM cpg_albums WHERE aid = 937 [thumbnails.php:96] (0.43 ms)
    [16] => SELECT p.cid, p.name FROM cpg_categories AS c,
                cpg_categories AS p
                WHERE c.lft BETWEEN p.lft AND p.rgt
                AND c.cid = 80
                ORDER BY p.lft [include/functions.inc.php:2939] (0.1 ms)
    [17] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2524] (0.13 ms)
    [18] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2291] (0.1 ms)
    [19] => SELECT title, keyword FROM cpg_albums WHERE aid = 937 [include/functions.inc.php:2457] (0.09 ms)
    [20] => SELECT COUNT(*) FROM cpg_pictures WHERE ((aid='937'  ) )  [include/functions.inc.php:1404] (0.09 ms)
    [21] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip FROM cpg_pictures AS r
                    WHERE ((aid = 937  ) )
                    ORDER BY filename DESC, pid DESC
                     LIMIT 0 ,-54 [include/functions.inc.php:1435] (0.22 ms)
)

==========================
GET :
------------------
Array
(
    [album] => 937
    [page] => 21
)

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

==========================
COOKIE :
------------------
Array
(
    [HstCfa2057725] => 1587093622016
    [__utmc] => 78008982
    [CookieInfoScript] => 1
    [__dtsu] => 6D00159770203522ED30B33C00379367
    [tk_tc] => 8nfsfjmK3KFCZY/Q
    [bfp_sn_rf_8b2087b102c9e3e5ffed1c1478ed8b78] => Direct/External
    [bfp_sn_rt_8b2087b102c9e3e5ffed1c1478ed8b78] => 1598152114127
    [bafp] => eea22fd0-e4ed-11ea-a7d7-934bcb886006
    [__gads] => ID=615867bc0e77e9ef-225d854233c40034:T=1603084136:RT=1603084136:S=ALNI_MbB0L8uLGaWoPjOMpVI5GuD81Onwg
    [wordpress_test_cookie] => WP Cookie check
    [c_ref_2057725] => https://charlie-hunnam.net/
    [__utmz] => 78008982.1608102546.20.4.utmcsr=charlie-hunnam.net|utmccn=(referral)|utmcmd=referral|utmcct=/
    [HstCmu2057725] => 1611380566898
    [HstCnv2057725] => 11
    [__utma] => 78008982.255281549.1587093622.1613777270.1613793619.27
    [wordpress_logged_in_0a4dbe6b8ce43279308f937b4b43cf3b] => Carol|1613966996|fnW1Mr4ZdlbeJhvJQIC9gKckz151WgTpkp9Paz7yrTZ|ecaa6ec63551998fd7ae4939fb0579a29855dc325e3bd76365f345052a8dfa33
    [HstCns2057725] => 18
    [05220587b41979fa8c1985bd96432d59] => 2db2fedefda2946aec92d8322a6035be
    [_lscache_vary] => 248bff540a006b4931747446dae444e0
    [HstCla2057725] => 1613798209545
    [HstPn2057725] => 125
    [HstPt2057725] => 235
    [PHPSESSID] => acac6a5ad4d11c5ca83f6a06900ff558
    [cpg15x_data] => YTo3OntzOjI6IklEIjtzOjMyOiI3NWZiNDA5NWU5ZTI3NWZhOThlN2QxMmY2ZGRhNDE3MCI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czo3OiJlbmdsaXNoIjtzOjM6ImxpdiI7YTo1OntpOjA7czo1OiI0MTIwMiI7aToxO3M6NToiMzk1NjkiO2k6MjtzOjU6IjI0NDQ3IjtpOjM7czo1OiIyMzg5NSI7aTo0O3M6NToiNDA3NjYiO31zOjU6Imxpdl9hIjthOjU6e2k6MDtpOjg3NztpOjE7aTo4Nzg7aToyO2k6OTM3O2k6MztpOjU5OTtpOjQ7aTo3MTk7fXM6NToidGhlbWUiO3M6NToiY3VydmUiO3M6NDoic29ydCI7czoyOiJuYSI7fQ==
)

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

==========================
VERSION INFO :
------------------
PHP version        7.4.15              OK   
MySQL version      10.3.27-MariaDB-log OK   
Coppermine version 1.6.09              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                      1                         
XBM Support                      1                         
WebP Support                     1                         
BMP Support                      1                         
TGA Read Support                 1                         
JIS-mapped Japanese Font Support                           

==========================
Key config settings
------------------
site_url             https://sebastian-stan.com/imgs/                                           
charset              utf-8                                                                     
allow_private_albums 1                                                                         
cookie_name          cpg15x                                                                     
cookie_path          /                                                                         
impath               /usr/bin/                                                                 
lang                 english                                                                   
main_page_layout     lastalb,2/breadcrumb/catlist/alblist/lastup,2/random,1/lasthits/onlinestats
silly_safe_mode      0                                                                         
smtp_host                                                                                       
theme                curve                                                                     
thumb_method         gd2                                                                       

==========================
Plugins
------------------
Name           Who is online?                                                               
Enabled        1                                                                             
Actions        plugin_install, page_start, plugin_uninstall, plugin_cleanup, plugin_configure
Filters        plugin_block                                                                 
--------------
Name           CoreH5A Upload                                                               
Enabled        1                                                                             
Actions        upload_form, plugin_install, plugin_uninstall                                 
Filters        upload_options                                                               
--------------
Name           CoreSGL Upload                                                               
Enabled        1                                                                             
Actions        upload_form                                                                   
Filters        upload_options                                                               
--------------

==========================
Server restrictions
------------------
safe_mode                                                                                                                                                                                               
safe_mode_exec_dir                                                                                                                                                                                       
safe_mode_gid                                                                                                                                                                                           
safe_mode_include_dir                                                                                                                                                                                   
sql.safe_mode                                                                                                                                                                                           
disable_functions        system, exec, shell_exec, passthru, phpinfo, show_source, proc_open, fopen_with_path, dbmopen, dbase_open, filepro, filepro_rowcount, filepro_retrieve, posix_mkfifo, pcntl_exec
file_uploads             1                                                                                                                                                                               
include_path             .:/opt/cpanel/ea-php74/root/usr/share/pear                                                                                                                                     
open_basedir                                                                                                                                                                                             
allow_url_fopen                                                                                                                                                                                         
max_execution_time       90                                                                                                                                                                             
max_input_time           90                                                                                                                                                                             
upload_max_filesize      35M                                                                                                                                                                             
post_max_size            35M                                                                                                                                                                             
memory_limit             128M                                                                                                                                                                           
suhosin.post.max_vars                                                                                                                                                                                   
suhosin.request.max_vars                                                                                                                                                                                 


==========================
Page (performance)
------------------
Parameter        Current  Peak   
Memory usage     2.94 MiB 4.13 MiB
Page generation  62.47 ms 62.47 ms
Page query time  4.45 ms  4.45 ms
Page query count 22       22     


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

[ron4mac]

If you do the math, 20 pages X 72 files per page = 1440 files.  So they're all there.  What you are seeing is that, when viewed by file name, all files greater than 999 are interspersed among all the others.  If you view the album by position, you'll see what you expect.  At issue is how mysql sorts and how the files were named.  Had the files all been named with 4 digits (0001,0002,0003,...), viewing by filename would work differently (as you had expected).

You're getting the mysql error because there is no page 21.

[CeeCee]

Ah, I feel dumb now

See I had this problem years ago on another site I had where I had over 1,000 images and the order got all messed up with the images the end of the set were shown at the beginning etc and fixed it by renaming them with 4 digit format. I didn't even consider all of the images were showing and it was out of order. I even tried to see if the images past 1,000 were showing but didn't see them but that was clearly my own inattention. No more late night problem solving for me.

Appreciate the help even if it it was right in front of my face.