Critical database error after search

steveclv · April 20, 2014, 10:39:05 PM

If I search my gallery http://www.picturethis.com.mt/gallery/ and then click on one of the results (try using DOME as the search term) I then get a critical database error.

I enabled debug mode and this is what I get - can anyone help please?

While executing query 'SELECT COUNT(*) FROM cpg15x_pictures AS p
LEFT JOIN `picture_db`.cpg15x_users AS u ON p.owner_id = u.user_id
WHERE ((p.title LIKE '%dome%')) AND approved = 'YES'
AND (title < 'Mellieha Church Dome and Bell Tower' OR title = 'Mellieha Church Dome and Bell Tower' AND pid < 446)' in include/search.inc.php on line 255

mySQL error: Column 'title' in where clause is ambiguous

USER:
------------------
Array
(
[ID] => f8bc9479b69450327e43736e3e494227
[am] => 1
[lang] => english
[liv] => Array
(

=> 257
[1] => 24
[2] => 25
[3] => 37
[4] => 41
)

[upload_method] => swfupload
[liv_a] => Array
(
=> 1
[1] => 2
)

[search] => Array
(
[params] => Array
(
[title] => on
[type] => full
)

[search] => dome
)

)

==========================
USER DATA:
------------------
Array
(
[user_id] => 1
[user_name] => admin
[groups] => Array
(
=> 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
(
=> SELECT name, value FROM cpg15x_config [include/init.inc.php:179] (1.59 ms)
[1] => SELECT * FROM cpg15x_plugins ORDER BY priority [include/plugin_api.inc.php:52] (1.07 ms)
[2] => SELECT user_id, time FROM `picture_db`.cpg15x_sessions WHERE session_id = '82fa16383265a4fe16fd0dffe62abd14' [bridge/coppermine.inc.php:267] (0.94 ms)
[3] => SELECT user_id, user_password FROM `picture_db`.cpg15x_users WHERE user_id = 1 [bridge/coppermine.inc.php:279] (0.95 ms)
[4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `picture_db`.cpg15x_users AS u LEFT JOIN `picture_db`.cpg15x_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (1.14 ms)
[5] => SELECT user_group_list FROM `picture_db`.cpg15x_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:205] (0.92 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 cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (1.14 ms)
[7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.9 ms)
[8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.73 ms)
[9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (1.01 ms)
[10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 1 [include/init.inc.php:388] (0.76 ms)
[11] => SELECT name, value FROM cpg15x_cpgshop_config [plugins/cpgshop/libs/cpgDB.class.php:24] (1.12 ms)
[12] => SHOW TABLES LIKE 'cpg15x_languages' [include/functions.inc.php:4133] (1.54 ms)
[13] => SELECT * FROM cpg15x_languages [include/functions.inc.php:4140] (1.26 ms)
[14] => SELECT cart FROM `cpg15x_cpgshop_cart` WHERE cid='2' AND save=0 LIMIT 1; [plugins/cpgshop/libs/cpgDB.class.php:36] (1.04 ms)
[15] => DELETE FROM cpg15x_banned WHERE expiry < '2014-04-20 16:22:24' [include/init.inc.php:444] (0.81 ms)
[16] => SELECT null FROM cpg15x_banned WHERE (user_id=1 OR '92.251.76.241' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.8 ms)
[17] => SELECT title FROM cpg15x_pictures WHERE pid = 446 [include/search.inc.php:236] (0.98 ms)
[18] => SELECT COUNT(*) FROM cpg15x_pictures AS p
LEFT JOIN `picture_db`.cpg15x_users AS u ON p.owner_id = u.user_id
WHERE ((p.title LIKE '%dome%')) AND approved = 'YES'
AND (title < 'Mellieha Church Dome and Bell Tower' OR title = 'Mellieha Church Dome and Bell Tower' AND pid < 446) [include/search.inc.php:255] (0.83 ms)
[19] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2421] (1.43 ms)
[20] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (1.2 ms)
[21] => SELECT user_name FROM cpg15x_users WHERE user_id = '' [plugins/description_metas/codebase.php:188] (0.85 ms)
)

==========================
GET :
------------------
Array
(
[album] => search
[cat] => 0
[pid] => 446
)

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

==========================
COOKIE :
------------------
Array
(
[cpg15x_data] => YTo3OntzOjI6IklEIjtzOjMyOiJmOGJjOTQ3OWI2OTQ1MDMyN2U0MzczNmUzZTQ5NDIyNyI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czo3OiJlbmdsaXNoIjtzOjM6ImxpdiI7YTo1OntpOjA7czozOiIyNTciO2k6MTtzOjI6IjI0IjtpOjI7czoyOiIyNSI7aTozO3M6MjoiMzciO2k6NDtzOjI6IjQxIjt9czoxMzoidXBsb2FkX21ldGhvZCI7czo5OiJzd2Z1cGxvYWQiO3M6NToibGl2X2EiO2E6Mjp7aTowO2k6MTtpOjE7aToyO31zOjY6InNlYXJjaCI7YToyOntzOjY6InBhcmFtcyI7YToyOntzOjU6InRpdGxlIjtzOjI6Im9uIjtzOjQ6InR5cGUiO3M6NDoiZnVsbCI7fXM6Njoic2VhcmNoIjtzOjQ6ImRvbWUiO319
[cpg15x_cpgshop] => YToxOntzOjc6ImNhcnRfaWQiO2k6Mjt9
[f17bba29415467b2b37b6086fd25218c] => c98c0d80f2432428e7f3d0593fce1efa
[PHPSESSID] => 84cdd0250e67bbab4dd4522a3f5fe584
)

==========================
SESSION :
------------------
Array
(
[php_captcha] => 1ae86dbd34a4ee7a77a4a5d5bf9f0c7f
)

==========================
VERSION INFO :
------------------
PHP version 5.2.17 OK
MySQL version 5.0.67-community OK
Coppermine version 1.5.28 stable

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

==========================
Key config settings
------------------
site_url http://www.picturethis.com.mt/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 zaffatasa
thumb_method gd2

==========================
Plugins
------------------
Name Delete Favorite
Actions plugin_install, plugin_uninstall, page_start
Filters page_html
--------------
Name File replacer
Actions page_start
Filters file_data
--------------
Name cpgShop
Actions plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start, profile_display_form, profile_submit_form, register_form_submit
Filters register_form_create, admin_menu, gallery_header, gallery_footer, file_data
--------------
Name SocialShare Plugin
Actions page_start, plugin_install, plugin_uninstall
Filters file_data
--------------
Name Add Meta Description
Actions plugin_install, plugin_uninstall
Filters theme_pageheader_params
--------------
Name Sitemap Generator
Actions plugin_install, plugin_uninstall
Filters
--------------
Name Search Engine Friendly URLs
Actions plugin_install, plugin_uninstall, plugin_configure, plugin_cleanup
Filters page_html
--------------

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

==========================
Page (performance)
------------------
Parameter Current Peak
Memory usage 5.17 MiB 5.25 MiB
Page generation 325.51 ms 325.51 ms
Page query time 23 ms 23 ms
Page query count 22 22

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

steveclv · April 20, 2014, 10:46:51 PM

I did find another thread that talked about a plugin adding a column called 'Title' to the user table and suggesting a fix to search_inc.php

I checked the php code and the 'fix' was already in there but I also checked the user table and there is a column in there called 'title' - I renamed it to 'title2' and it fixed the problem but how can I fix the problem correctly in the code without changing the database as I don't know what other errors may now occur.

gmc · April 21, 2014, 03:18:12 AM

Since both pictures (cpg15x_pictures AS p) from base code and users (cpg15x_users AS u) from a mod/plugin have a field named 'title' - it needs to be qualified as p.title in the sql being issued to tell MySQL it is referring the the title in the pictures title...
That qualification comes from the variable '$FORBIDDEN_SET' which is used in many queries - and they may not all define 'p' as pictures...

I'd need to look closer at the other thread and the code (search seems to be having some issues at the moment...)
I'll take a look tomorrow unless someone else gets to it first.

(The change you made renaming title to title2 in users table won't affect base code - but will affect whatever mod is expecting it to be there.)

Αndré · April 22, 2014, 03:12:17 PM

The following mod is not tested, but should work. Open include/search.inc.php, find

Code Select

$sort_order = "$criteria $direction '$criteria_pid' OR $criteria = '$criteria_pid' AND pid < $pid";
and above, add

Code Select

if ($criteria == 'title') {
    $criteria = 'p.title';
}

steveclv · April 22, 2014, 10:20:37 PM

Thanks!!

Αndré · April 23, 2014, 08:50:52 AM

Marking thread as solved. Next time, please do this yourself as explained in the board rules:

Quote from: Joachim Müller on September 28, 2008, 12:46:26 PM
Resolve your threads
If you have found an answer to your question, resolve your thread. Don't just post "I have found the answer", but tell others what you actually did to solve your issues. Posting a link to the page where you found the answer might help. Describing what you did might help as well. Finally, you can tag your thread as "solved" by clicking on the "Topic Solved" button on the bar at the left hand side at the bottom of your thread.

coppermine-gallery.com/forum

News:

Critical database error after search

steveclv

steveclv

gmc

Αndré

steveclv

Αndré