1. 

   ajdjackson, Member

   Hi

   I've just setup(?) a new installation of mydbr on a Windows Server 2016 with MySQL 8.0 and PHP 7.2.

   It appears to have installed correctly and purchased a new premium license.

   I've come across a couple of issues:

   1) When updating mydbr I don't get brought back to the update 'result' page I get a 500 - Internal server error page although it looks like I'm running the latest version.

   2) When creating a report and using the Query Builder and select the schema where the data tables are stored I get the following:

   DB error (1267): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '=' SQL: call sp_MyDBR_db_objects('schema name');

   This obviously some db configuration error and I would like to sort these before continuing.

   Any help would be most appreciated.

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   1) When updating mydbr I don't get brought back to the update 'result' page I get a 500 - Internal server error page although it looks like I'm running the latest version.
   

   Error "500 - Internal server error" is a generic error code web servers produce when something has gone wrong. Usually it is a server configuration issue. Check the web server logs for more information about the cause of the error.

   2) When creating a report and using the Query Builder and select the schema where the data tables are stored I get the following:

   When creating the myDBR database, make sure you use same character set and collation that the server uses (usually utf8_general_ci). You can check the collations with:

   SELECT @@character_set_database, @@collation_database;

   The INFORMATION_SCHEMA-database which myDBR uses to get the metadata, should have collation utf8_general_ci. See:

   https://dev.mysql.com/doc/refman/8.0/en/charset-collation-information-schema.html

   MySQL 8 changed the information_schema-tables to be views (instead of temporary tables). You can see the character set and collation with:

   use information_schema
   
   $$
   
   show create view tables
   
   $$
   
   

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   After trying to resolve issue 2 to no avail I decided to re-install mydbr.

   I dropped the mydbr schema from the database and deleted the mydbr app.

   Now when I get to the Report database setup and enter the root password and click continue I get the 500 - internal server error.

   I've looked at the webserver logs and nothing is jumping out. The last entry is http://localhost/install/index.php?controller=install&action=database&db_limit=mssql,sqlsrv 500 0 0 281

   Any thoughts?

   Jake
4. 

   ajdjackson, Member

   Hi

   An update.

   I'd forgotten to recreate the mydbr database.

   Once I did that the installation finished ok.

   However the same 2 issues still remain.

   Is issue 2 down to using MySQL 8? Should I downgrade?

   Ive no idea how to sort the 500 error - Im no server expert :)

   Any help would be most appreciated.

   Jake
5. 

   myDBR Team, Key Master

   To troubleshoot the issue 2, log into MySQL, and into the mydbr database. What is the output of:

   SELECT @@character_set_database, @@collation_database;

   Also, what is the MySQL 8 version you are using? See:

   SELECT version();

   --
   myDBR Team
6. 

   ajdjackson, Member

   Hi

   Mydbr:

   utf8mb4, utf8mb4_0900_ai_ci

   Data tables

   utf8, utf8_general_ci

   information_schema

   utf8, utf8_general_ci

   Version

   8.0.11

   Thanks

   Jake
7. 

   myDBR Team, Key Master

   OK,
   do you get a collation error from:

   select table_name, 'T', 1
   
   from information_schema.tables
   
   where table_schema=cast('mydbr' AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci

   --
   myDBR Team
8. 

   ajdjackson, Member

   Hi

   Yes.

   Error Code: 1267. Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '=' 0.031 sec

   Jake
9. 

   myDBR Team, Key Master

   The collation for the MySQL system tables should be utf8_general_ci:
   https://dev.mysql.com/doc/refman/8.0/en/charset-collation-information-schema.html

   There has been bugs with collations in MySQL 8's system tables (see: https://bugs.mysql.com/bug.php?id=90690) and this could be the same one.

   We'll take a look if we can replicate this.

   If you absolutely do not need MySQL 8 for now, you might want to try older version of MySQL or just use MariaDB.

   --
   myDBR Team
10. 

    ajdjackson, Member

    Hi

    No need to be bleeding edge :)

    I'll uninstall 8 tomorrow and put 'older' version on and report back.

    Thanks for your help

    Jake
11. 

    ajdjackson, Member

    Hi

    I've now uninstalled MySQL 8 and replaced it with MariaDB 10.3.7

    Issue 2 is now gone.

    Unfortunately issue 1 still exists. I can run the installer successfully but the updater after seeming to run displays the ERROR 500.

    The last entry in the log file is:

    2018-06-12 10:16:45 109.228.56.206 GET /install/index.php controller=update&action=update 443 - XXX.XXX.58.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64;+rv:61.0)+Gecko/20100101+Firefox/61.0 https://xx-xxx.com/install/index.php?controller=update 500 0 0 6270

    Any thoughts?

    Jake
12. 

    myDBR Team, Key Master

    What is the web server you are using?

    The log entry looks like the access log entry instead of the error log entry . Can you find the web server error log?

    Are the myDBR objects created into the database? Does the username that you are using have write access to myDBR database?

    --
    myDBR Team
13. 

    ajdjackson, Member

    Hi

    I'm using IIS on Windows Server 2016.

    That seems to the only logfile created. It's located in C:\inetpub\logs\LogFiles\W3SVC1. Is there somewhere else I need to look in?

    Yes MyDBR objects are created in the db. The application installed OK and I'm using it. The issue I'm getting is when running an update.

    Cheers

    Jake
14. 

    ajdjackson, Member

    Hi

    I enabled IIS-Logging in Event Viewer and reran the update. This is what I see:

    + System

    - Provider

    [ Name] Microsoft-Windows-IIS-Logging [ Guid] {7E8AD27F-B271-4EA2-A783-A47BDE29143B}

    EventID 6200

    Version 0

    Level 4

    Task 0

    Opcode 0

    Keywords 0x8000000000000000

    - TimeCreated

    [ SystemTime] 2018-06-12T17:42:42.081321800Z

    EventRecordID 26

    Correlation

    - Execution

    [ ProcessID] 1852 [ ThreadID] 3244

    Channel Microsoft-IIS-Logging/Logs

    Computer vm1D4208F

    - Security

    [ UserID] S-1-5-18

    - EventData

    EnabledFieldsFlags 2478079 date 2018-06-12 time 17:42:39 c-ip 109.153.58.213 cs-username - s-sitename W3SVC1 s-computername vm1D4208F s-ip 109.228.56.206 cs-method GET cs-uri-stem /install/index.php cs-uri-query controller=update&action=update sc-status 500 sc-win32-status 0 sc-bytes 1400 cs-bytes 505 time-taken 6681 s-port 443 csUser-Agent Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64;+rv:61.0)+Gecko/20100101+Firefox/61.0 csCookie - csReferer https://xx-xxx.com/install/index.php?controller=update cs-version - cs-host - sc-substatus 0 CustomFields

    Don't know if this helps.

    Jake
15. 

    myDBR Team, Key Master

    Not much extra info there to guess what might be wrong. Check the file permissions that web server can update the files.

    --
    myDBR Team
16. 

    ajdjackson, Member

    Hi

    I checked all the folders and permissions look OK.

    One thing I've done with this installation different from my other servers running mydbr is that I have installed the application straight into the wwwroot folder not wwwroot/mydbr. Is this the cause for the update issue? If so is there a way round this?

    This installation is purely for mydbr so users only have to enter the domain name.

    Cheers

    Jake
17. 

    myDBR Team, Key Master

    Installation location does not matter. Something else in the configuration is causing the problem.

    —
    myDBR Team
18. 

    ajdjackson, Member

    Hi

    Just when I thought I had everything setup ok I've hit another issue.

    I have set a couple of users up but when they try to login they are getting the following:

    

    It says they need to enter a new license and they are not getting the 'tailored' login screen that I see with Company Logo etc.

    They have tried from different locations and devices.

    I have only one installation of mydbr on this server. It's a Windows Server 2016 and mydbr is installed in the site root ie c:/inetpub/wwwroot/ folder.

    I can logon from several devices OK.

    Any help would be most appreciated. I really need to get this up soon.

    Jake
19. 

    myDBR Team, Key Master

    Jake,
    make sure the license is installed and that the users use the same URL as in your license.

    —
    myDBR Team
20. 

    ajdjackson, Member

    Hi

    I think it's to do with SSL.

    It seems to work by entering https://mydomain.com but not with https://www.mydomain.com but I can't see what's wrong.

    I've binded the site as follows:

    https mydomain.com 443 *

    The certificate is installed to *.mydomain.com issued to *.mydomain.com and expires in a year.

    No idea how to get it to work for http://www.mydomain.com or http://mydomain.com or https://www.mydomain.com :(

    Another thing is it to do withe fact I got the license issued to mydomain.com instead of http://www.mydomain.com?

    Jake
21. 

    myDBR Team, Key Master

    Jake,
    domain.com != http://www.domain.com.

    Inform users to use the URL that matches the license or better yet, redirect users to correct address.

    —
    myDBR Team
22. 

    ajdjackson, Member

    Hi

    Thanks for that.Silly mistake on my part.

    You can guess I'm not a server guy :)

    I'm trying to set up the redirect.

    What I need to do is to redirect the following:

    http://www.mydomain.com --> https://mydomain.com

    http://mydomain.com --> https://mydomain.com

    https://www.mydomain.com --> https://mydomain.com

    I've added the following to the web.config file but it doesn't work

    <rule name="Force WWW and SSL" enabled="true" stopProcessing="true"> <match url="(.*)" /> <conditions logicalGrouping="MatchAny"> <add input="{HTTP_HOST}" pattern="^[^www]" /> <add input="{HTTPS}" pattern="off" /> </conditions> <action type="Redirect" url="https://mydomain.com/{R:1}" appendQueryString="true" redirectType="Permanent" /> </rule>

    If I enter https://mydomain.com (which works with rule disabled) I get 'The page isn’t redirecting properly'

    If I enter https://www.mydomain.com I get 'HTTP Error 404'

    If I enter http://www.mydomain.com I get 'HTTP Error 404'

    and if I enter http://mydomain.com I get 'HTTP Error 403 - forbidden'

    Any help would be most appreciated.

    Cheers

    Jake
23. 

    myDBR Team, Key Master

    You can try to use separate rules for HTTP -> HTTPS and http://www.mydomain.com -> mydomain.com.

    See:
    https://nishantpant.wordpress.com/2014/11/04/iis-rewrite-rules-force-www-to-non-www-and-http-to-https/

    --
    myDBR Team

Reply

You must log in to post.