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

Hi,

ue.`cb_region` LIKE '%value-1%'

Inserting ue. into the query does work, the lists are displaying correctly. thank you!

However, when I create new list filters using the simple filter, I still have to then change it using the advanced filter and include "ue.".

This will cause all references to #__TABLE to change to PREFIX_TABLE (jos_TABLE). Please review your database and ensure all PREFIX_table (jos_comprofiler) is equivalent to #__TABLE (#__comprofiler).

I am not sure how to check/fix this. Can you point me to some doc/instructions please?

I will contact my host and get them to change the table collations.



I have built the site on another domain as a sub domain and want to move the site from sub domain to it's own domain. If I perform a cPanel backup and restore it will include the domain and all subdomains even if I only want backup/relocate one subdomain.

(I have used Akeeba to do this to transfer other sites, and they have been successful. The only difference here is additional files/db for CB.)

Given that these issues have occurred when relocating the site to the new location, what is the best way to perform this relocation?

Thanks again.

Hi,

[Solved]!!

My host got back to me and talked me through a problem that I came across before I tried Akeeba.

Steps listed to help anyone else who may find this problem when relocating from development server to live server on it's own domain.

1) log in to your OLD cpanel
2) use FILE MANAGER, select your public_html folder, compress it, download to your pc
3) use PHPMYADMIN to EXPORT your mysql database

4) log in to your NEW cpanel
5) create new MySQL Database and User/Password
6) on YOUR computer, edit your MySQL export file (from step 3) and change the database name to the new database name created in step 5 (looks like CPANELUSER_JO151)
7) use MYPHPADMIN, upload and IMPORT database
8) using FILE MANAGER upload compressed public_html file to root folder and uncompress
9) edit your CONFIGURATION.PHP to include the new settings as per:
var $log_path = '/home/NEWCPANELUSER/public_html/log';
var $tmp_path = '/home/NEWCPANELUSER/public_html/tmp';
var $user = 'NEWMYSQLuser';
var $db = 'NEWMYSQLdb';
var $dbprefix = 'jos_';
var $password = 'NEWMYSQLpass';
10) view and test live site

This is fairly straight forward, and shouldn't take too long to do it this way. Even if you use Akeeba to relocate your site, you still need to perform steps 4, 5, 9 and 10.

Good luck to all!

I'm now about to install cbsubs