Problem on Element : Database Join in New Version off Joomla 3.6.2

[faustofurtado@hotmail.com]

Hello,

I have one table : SOLUCAO imported By CVS.

This table has an element type : databasejoin USER

the SOLUCAO_USER is collected from native table joomla users. Everything was working before the upgrade of Joomla. Can anyone tell me how to fix this problem below:

Unknown column '#__users.id' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `tb_solucao`.`id` AS `tb_solucao___id`,
`tb_solucao`.`id` AS `tb_solucao___id_raw`,
`tb_solucao`.`Cod_Solucao` AS `tb_solucao___Cod_Solucao`,
`tb_solucao`.`Cod_Solucao` AS `tb_solucao___Cod_Solucao_raw`,
`tb_solucao`.`Nome_Solucao` AS `tb_solucao___Nome_Solucao`,
`tb_solucao`.`Nome_Solucao` AS `tb_solucao___Nome_Solucao_raw`,
`tb_solucao`.`Executivo_Patrocinador` AS `tb_solucao___Executivo_Patrocinador`,
`tb_solucao`.`Executivo_Patrocinador` AS `tb_solucao___Executivo_Patrocinador_raw`,
`tb_solucao`.`Gerente_Geneg` AS `tb_solucao___Gerente_Geneg`,
`tb_solucao`.`Gerente_Geneg` AS `tb_solucao___Gerente_Geneg_raw`,
`tb_solucao`.`id_Resp_Geint` AS `tb_solucao___id_Resp_Geint_raw`,
`#__users`.`id` AS `tb_solucao___id_Resp_Geint`,
`tb_solucao`.`id_Resp_Geint_Backup` AS `tb_solucao___id_Resp_Geint_Backup_raw`,
`#__users_0`.`name` AS `tb_solucao___id_Resp_Geint_Backup`,
`tb_solucao`.`Fones_Geneg` AS `tb_solucao___Fones_Geneg`,
`tb_solucao`.`Fones_Geneg` AS `tb_solucao___Fones_Geneg_raw`,
`tb_solucao`.`Estagio_de_Atuacao` AS `tb_solucao___Estagio_de_Atuacao`,
`tb_solucao`.`Estagio_de_Atuacao` AS `tb_solucao___Estagio_de_Atuacao_raw`,
`tb_solucao`.`Inicio_Acomp_Geint` AS `tb_solucao___Inicio_Acomp_Geint`,
`tb_solucao`.`Inicio_Acomp_Geint` AS `tb_solucao___Inicio_Acomp_Geint_raw`,
`tb_solucao`.`id` AS slug
, `tb_solucao`.`id` AS `__pk_val`

FROM `tb_solucao` LIMIT 0, 10

Thanks

 

[Barcellos]

Adding more information to Fausto's issue.

Old site: Joomla 3.4.8 Fabrik 3.1.1
Updated to: Joomla 3.6.2 Fabrik 3.5
Updated from fabrik-master from GitHuB​

The first error was:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `tb_solucao`.`id` AS `tb_solucao___id`

The same of these topics:
http://fabrikar.com/forums/index.php?threads/error-1267-with-joomla-3-5-update.43574/
https://issues.joomla.org/tracker/joomla-cms/9423
http://forum.joomla.org/viewtopic.php?t=913699

It happens in only one element, which uses the plugin databasejoin and joins to #__users.

I tried to change the collation to make all the tables the same.
Tried both: turning all tables to unicode_ci and in the other backup restore general_ci.

When I do this, the error becomes the one that Fausto mentioned:
Unknown column '#__users.id' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `tb_solucao`.

users.id is the element called by databasejoin. It is the label configurated in the data tab.

I have checked and all the elements had the collation changed too.

Any suggestion?

 

[cheesegrits]

Did you migrate the site to a new server? If so, did you edit and save the 'site default' connection one time, to make sure it has the right configuration for the local database?

Did you really update from 3.1.1?

As mentioned in another thread, you should really consider taking out a support subscription. This isn't the kind of thing I can deal with in Community support.

-- hugh

 

[Barcellos]

Thank you, Hugh!

Did you migrate the site to a new server?

No, same server.

Did you really update from 3.1.1?

Yes, I restored the backup and checked again.

I will try to migrate other sites in the same situation to check if the issue will happen again or if it is an isolated issue.
If we really need, we will pay for upgrading support. But who will pay is Fausto - the one who opened the thread.

 

[Barcellos]

[SOLVED]

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `tb_solucao`.`id` AS `tb_solucao___id`

The issue happens:

• After updating from Joomla 3.5- to Joomla 3.5+
• When we have a join between a table created by Fabrik (old default general_ci) and Joomla table (now utf8_unicode_ci). Example: a form which uses a databasejoin with the table #__users.

HOW TO SOLVE:

1) Change the collation of database, table and fields of the table created by Fabrik to utf8_unicode_ci.
2) Delete the join element with issue and create it again - otherwise the tables will still not appear.


To change collation:
ALTER DATABASE `database` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `table` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB;
ALTER TABLE `field` CHANGE `nome` `nome` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; (I didn't test this line. I changed by phpMyAdmin)

Fonte: http://www.uolhost.uol.com.br/faq/b...lation-de-uma-base-de-dados-como-alterar.html

 

[cheesegrits]

Thanks for the update, might help other people.

-- hugh