Hi,
Similar to
www.joomlapolis.com/component/option,com_joomlaboard/Itemid,/func,view/catid,112/id,134328/#134328
This is the history of the problem:
1) Community/Directory site built with J1.5.17 and CB 1.2.2 (both were fresh installs)
2) site was built on DEVELOPMENT subdomain - >>>all functionality tested and working<<<
3) site was backed up with Akeeba 3
4) site was transfered to LIVE domain (same server/host)
5) reinstallation of site on LIVE domain with NEW mysql database and user/pass
6) all site functionality is normal on LIVE site except when using 'LIKE' filters, ie: full listing of profiles works, but when using a filter is applied an error 500 appears (ie: `cb_region` LIKE'%value-1%')
Error message on site:
<=====
There is an error in the database query. Site admin can turn site debug to on to view and fix the query.
=====>
Error message when joomla debug is activated
<=====
500 - JDatabaseMySQL::query: 1052 - Column 'cb_region' in where clause is ambiguous SQL=SELECT COUNT(*) FROM jos_comprofiler ue, jos_users u, jos_comprofiler t1 WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND u.gid IN (28) AND u.`id` = ue.`id` AND u.`id` = t1.`id` AND (`cb_region` LIKE'%value-1%')
=====>
NOTE THE SELECT STATEMENT INCLUDES BOTH jos_comprofiler ue, jos_comprofiler t1
I have since found forum posts that indicate that an upgrade to latest version will rectify problems.
7) upgrade was done on live site, >>>same errors persist<<<
8) upgrade was done on development site, >>>everything is STILL working normally as desired<<<
9) Running CB Tools reveals the following errors:
CHECK USERS DATABASE
<=====
ERROR: sql query: SELECT u.id FROM #__users u LEFT JOIN #__core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL : returned error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT u.id FROM jos_users u LEFT JOIN jos_core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL
=====>
<=====
Check CB fields data storage database: Results
CB fields data storage Database structure differences:
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
Table jos_comprofiler does not exist
The CB fields data storage database structure differences can be fixed (adjusted) by clicking here:
=====>
When I follow the prompts, and click FIX, the following message appears:
<=====
CB fields data storage Database adjustments errors:
CBSQLupgrader::createTableof Table jos_comprofiler failed with SQL error: Table 'jos_comprofiler' already exists SQL=CREATE TABLE `jos_comprofiler` ( `cb_displayservice` mediumtext NOT NULL ) ENGINE=MyISAM
CREATE TABLE `jos_comprofiler` ( `cb_displayservice` mediumtext NOT NULL ) ENGINE=MyISAM
CBSQLupgrader::createTableof Table jos_comprofiler failed with SQL error: Table 'jos_comprofiler' already exists SQL=CREATE TABLE `jos_comprofiler` ( `cb_region` mediumtext ) ENGINE=MyISAM
CREATE TABLE `jos_comprofiler` ( `cb_region` mediumtext ) ENGINE=MyISAM
CBSQLupgrader::createTableof Table jos_comprofiler failed with SQL error: Table 'jos_comprofiler' already exists SQL=CREATE TABLE `jos_comprofiler` ( `cb_location` varchar(255) DEFAULT NULL ) ENGINE=MyISAM
CREATE TABLE `jos_comprofiler` ( `cb_location` varchar(255) DEFAULT NULL ) ENGINE=MyISAM
CBSQLupgrader::createTableof Table jos_comprofiler failed with SQL error: Table 'jos_comprofiler' already exists SQL=CREATE TABLE `jos_comprofiler` ( `cb_suburbs` mediumtext ) ENGINE=MyISAM
CREATE TABLE `jos_comprofiler` ( `cb_suburbs` mediumtext ) ENGINE=MyISAM
CBSQLupgrader::createTableof Table jos_comprofiler failed with SQL error: Table 'jos_comprofiler' already exists SQL=CREATE TABLE `jos_comprofiler` ( `cb_whatcanidoforyou` mediumtext ) ENGINE=MyISAM
CREATE TABLE `jos_comprofiler` ( `cb_whatcanidoforyou` mediumtext ) ENGINE=MyISAM
etc
etc
etc
etc (pretty much every field is listed) followed by:
Table #__users Column username exists but is not of strict type, so not checked.
Table #__users Column name exists but is not of strict type, so not checked.
Table #__comprofiler Column firstname structure is up-to-date.
Table #__comprofiler Column middlename structure is up-to-date.
Table #__comprofiler Column lastname structure is up-to-date.
Table #__users Column username exists but is not of strict type, so not checked.
Table #__users Column name exists but is not of strict type, so not checked.
Table #__comprofiler Column firstname structure is up-to-date.
Table #__comprofiler Column middlename structure is up-to-date.
Table #__comprofiler Column lastname structure is up-to-date.
Table #__users Column username exists but is not of strict type, so not checked.
Table #__users Column name exists but is not of strict type, so not checked.
Table #__comprofiler Column firstname structure is up-to-date.
Table #__comprofiler Column middlename structure is up-to-date.
etc
etc
etc (pretty much every field is listed)
=====>
Any suggestions please?
I need to get this resolved before I add in CBsubs...
THANK YOU!
Post edited by: JoomIM, at: 2010/07/15 10:16
Post edited by: krileon, at: 2010/07/16 21:17