News:

Dear forum visitors, if the support forum is not available, please try again a few minutes later. Thanks!

Main Menu
Support-Forum

Another (Illegal mix of collations error)

Started by belini, 15.09.2017 21:40:52

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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:

  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,
QuoteMy 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.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

belini

Quote from: Arno on 16.09.2017 11:25:15
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?  ::)
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

belini

#4
Quote from: Arno on 20.09.2017 11:42:34
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.


QuoteAn 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.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Arno

#6
Quote1. 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.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

belini

Quote from: Arno on 25.09.2017 11:41:33
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.  :-\
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

belini

Quote from: Arno on 27.09.2017 09:03:23
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
Colin M
  •  

belini

Quote from: ColinM on 29.09.2017 01:07:41
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
Colin M
  •  

belini

#13
Quote from: ColinM on 29.09.2017 18:27:37
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]
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •