coppermine-gallery.com/forum

If I search my gallery http://www.picturethis.com.mt/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       
  
  
  ==========================
  

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.

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

The following mod is not tested, but should work. Open include/search.inc.php, find
$sort_order = "$criteria $direction '$criteria_pid' OR $criteria = '$criteria_pid' AND pid < $pid";
and above, add
if ($criteria == 'title') {
    $criteria = 'p.title';
}

Thanks!!

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.