Another (Illegal mix of collations error)

[belini]

Hello,
Look here again with more problem to solve.

My system has 47 folders / categories,  so far so good, so I started copying files (Word .dot files) from another server to those folders. At first it was all right and automatic monitoring was updating the downloads with their respective categories.

When I realized some categories were not being updated, even having files inside the server folders, it was then that the following error began to appear:

 

Code Select Expand

1267 Illegal mix of collations (utf8_general_ci, IMPLICIT) and (utf8mb4_general_ci, COERCIBLE) for operation '='

I turned off the automatic updater monitoring and on the control panel I pressed the "Check Download area now." Link, but the error continued, see attachment.

I researched the forum and found a post with a similar error: http://www.jdownloads.com/index.php/support-forum.html?topic=8245.msg34167#msg34167

I followed the guidelines of the post, all my tables and database uses utf8_general_ci, what could be going wrong?



[gelöscht durch Administrator]

[Arno]

Hi,

My system has 47 folders / categories,  so far so good, so I started copying files (Word .dot files) from another server to those folders. At first it was all right and automatic monitoring was updating the downloads with their respective categories.

When I realized some categories were not being updated, even having files inside the server folders, it was then that the following error began to appear:

Code: [Select]
1267 Illegal mix of collations (utf8_general_ci, IMPLICIT) and (utf8mb4_general_ci, COERCIBLE) for operation '='
I turned off the automatic updater monitoring and on the control panel I pressed the "Check Download area now." Link, but the error continued, see attachment.

1. Can it be that the problem exist only when your filenames has special characters? (see your pic)
2. Have you read this informations about this scenario?
3. Have you used also prior filenames with special characters?
4. Have you corrected something in your DB after you have find and read the other topic?

For me it seems that you have still different collations in your DB. This can also be single fields. Send me login data for your phpmyadmin and a link via PM then I will check it.

[belini]

Hi,1. Can it be that the problem exist only when your filenames has special characters? (see your pic)
2. Have you read this informations about this scenario?
3. Have you used also prior filenames with special characters?
4. Have you corrected something in your DB after you have find and read the other topic?

For me it seems that you have still different collations in your DB. This can also be single fields. Send me login data for your phpmyadmin and a link via PM then I will check it.

Well, this is all very strange because it was working.

When I checked the solution for the other topic, I noticed that I had some utf8mb4_general_ci and other utf8_general_ci tables, so I converted the entire DB, all tables and all fields to utf8_general_ci, I checked everything one by one.

As I said before, everything was working, when I started having problems I thought that solution of the other topic would work, but it did not help much.

As I know that special characters generate many problems I avoid using them and I configured the jdownloads to avoid this, configuring just like the image attached to the post.

The only place that still appears utf8mb4_general_ci is in the Joomla configs, see the attachment. That option "Database Connection Collation" should be something internal of Joomla, here a link of the Joomla Issue Tracker about this: https://issues.joomla.org/tracker/joomla-cms/7173

I do not understand where the collation conflict might be, the only place that has a special character is in the title of the download or category, but when I type only. In the automatic process of monitoring the directories all the files and directories are without special characters and without spaces.

The only thing different are files that have long names, sometimes longer than 30 characters, but all without spaces (underscore or dash replacing) or special characters.

I'll keep trying to fix it here.

[gelöscht durch Administrator]

[Arno]

Hi,
many thanks for the link. Maybe should we change also in jDownloads all collations to utf8mb4_general_ci.
But I'm not sure that this really solve your problems.

It is bad that your error message has not more informations about the table or data field which create the error. Or a script line. Maybe could you activate the debug option in Joomla and try it again? 

[belini]

Hi,
many thanks for the link. Maybe should we change also in jDownloads all collations to utf8mb4_general_ci.
But I'm not sure that this really solve your problems.

It is bad that your error message has not more informations about the table or data field which create the error. Or a script line. Maybe could you activate the debug option in Joomla and try it again? 

Ok,

I enabled Joomla debug and "Download directory monitoring?" to "Yes" in jDownloads/Configuration tab "Monitoring".

Then I clicked in jDownloads/Control Panel,  below I pasted the footer page information of the error

I hope this helps.

An error has occurred.  1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='                                       
JROOT/libraries/joomla/database/driver/mysqli.php:650                                 

		Call stack
#	Function	Location
1	()	JROOT/libraries/joomla/database/driver/mysqli.php:650
2	JDatabaseDriverMysqli->execute()	JROOT/libraries/joomla/database/driver.php:1691
3	JDatabaseDriver->loadObjectList()	JROOT/administrator/components/com_jdownloads/helpers/jdownloadshelper.php:2363
4	JDownloadsHelper::runMonitoring()	JROOT/administrator/components/com_jdownloads/views/jdownloads/tmpl/default.php:206
5	include()	JROOT/libraries/src/MVC/View/HtmlView.php:695
6	Joomla\CMS\MVC\View\HtmlView->loadTemplate()	JROOT/libraries/src/MVC/View/HtmlView.php:230
7	Joomla\CMS\MVC\View\HtmlView->display()	JROOT/administrator/components/com_jdownloads/views/jdownloads/view.html.php:35
8	jdownloadsViewjdownloads->display()	JROOT/libraries/src/MVC/Controller/BaseController.php:672
9	Joomla\CMS\MVC\Controller\BaseController->display()	JROOT/administrator/components/com_jdownloads/controller.php:46
10	jdownloadsController->display()	JROOT/libraries/src/MVC/Controller/BaseController.php:710
11	Joomla\CMS\MVC\Controller\BaseController->execute()	JROOT/administrator/components/com_jdownloads/jdownloads.php:39
12	require_once()	JROOT/libraries/src/Component/ComponentHelper.php:392
13	Joomla\CMS\Component\ComponentHelper::executeComponent()	JROOT/libraries/src/Component/ComponentHelper.php:367
14	Joomla\CMS\Component\ComponentHelper::renderComponent()	JROOT/libraries/src/Application/AdministratorApplication.php:101
15	Joomla\CMS\Application\AdministratorApplication->dispatch()	JROOT/libraries/src/Application/AdministratorApplication.php:159
16	Joomla\CMS\Application\AdministratorApplication->doExecute()	JROOT/libraries/src/Application/CMSApplication.php:267
17	Joomla\CMS\Application\CMSApplication->execute()	JROOT/administrator/index.php:51

[Arno]

Thanks for the posted result. I will check it.

[Arno]

1. Can it be that the problem exist only when your filenames (or folders) has special characters? (see your pic)
2. Have you read this informations about this scenario?
3. Have you used also prior filenames (or folders) with special characters?
4. Have you corrected something in your DB after you have find and read the other topic?

5. Can you create new categories with special characters?
Could you please give me the answers?

When possible send me also data for a backend access via PM.

[belini]

5. Can you create new categories with special characters?
Could you please give me the answers?

When possible send me also data for a backend access via PM.

Login for phpmyadmin and joomla backend?

[Arno]

Hi,
please wait a day. It is possible that we have a bug in the scan.php. 

[belini]

Hi,
please wait a day. It is possible that we have a bug in the scan.php. 

Ok
Good luck

[ColinM]

Hi
latest jD should have fixed the problem  (v3.2.56)
Colin

[belini]

Hi
latest jD should have fixed the problem  (v3.2.56)
Colin

I'm using version 3.2.56 and the error problem continues.
Just do not understand, because this occurs, because the directories on the server are names with no spaces (underline in place) and no special accents, the files also have no spaces (underline in place) and no special accents, I use only normal characters ( "A ... Z", "a ... z", "0 ... 9", "-", "_")
The only thing that has accentuation is the title of the category, but this is just for storage in the database.
Also remembering that it was working, but from one moment to another began to appear the error reported at the beginning of this thread.

[ColinM]

Hi
I also do not understand !  There was a bug in the scan routine which Arno has fixed.  So it is possible that when the downloads were created whilst the bug was present that the collation mix up occurred.  So does this happen with specific display of certain Categories or certain Downloads?
Colin

[belini]

Hi
I also do not understand !  There was a bug in the scan routine which Arno has fixed.  So it is possible that when the downloads were created whilst the bug was present that the collation mix up occurred.  So does this happen with specific display of certain Categories or certain Downloads?
Colin

The error occurs when I enable automatic monitoring, or if it is disabled, when I click "Check Download area now", which theoretically does the same thing, but manually.
See the first posts, where the problem is explained.
I have no problems downloading, the problem is in the ability to recognize the files in the directories, which stopped working.
Another strange thing is that it works, starts reading the first directories, but stops halfway.

[Arno]

Hi belini,
I have not forget you. But I be ill since a few days. Not heavy but I can not work fulltime at the moment.
I hope I could find the reason for your problem but need a few days more.

In the meantime could you try to activate a hidden option in the db table _jdownloads_config. Here exist at the end the option use.files.and.folder.settings.for.monitoring (see pic). You could change the value to 1 to activate it. Try it then again.

[gelöscht durch Administrator]