Character set issues: MySQL, Wordpress and WebmatrixRSS

5 replies

Last post Feb 07, 2017 08:13 AM by Yuk Ding

  • Character set issues: MySQL, Wordpress and Webmatrix

    Jan 17, 2017 06:50 PM|sparvier|LINK

    I'm going crazy with very weird things.

    I'm building my new website with Wordpress, MySQL and Webmatrix.

    I made a local installation and after some hacks, everything was going well: I made some sample posts  and published via Webmatrix.

    All was working: both locally and on the hosting server.

    When everything seemed OK, I added two pages with the privacy policy and the cookie policy.

    Locally it was OK, but when published, it scr**ed my site...

    After long investigation, I found out that everything was depending on using the è character Unicode Character 'LATIN SMALL LETTER E WITH GRAVE' (U+00E8)...

    If I delete the è character on that page, everything works fine again...

    I read a lot of documentation tryng to solve this problem and I found the solution: force MySQL both locally and on the remote server to use the same character set...But it didn't work.

    Finally I discovered a very very very weird issue that I can't explain...

    From the official MySQL doc there's a query that shows how the variables (relative to charsets) are set:

    SHOW VARIABLES LIKE  'char%';

    This Query executed on the same LOCAL database reports different things if executed with MySql Workbench 6.3 CE (the official tool from Oracle) and executed inside Webmatrix!!!!

    DB (local): Wordpress793

    Query: SHOW VARIABLES LIKE  'char%';

    *** MySql Workbench 6.3 CE OUTPUT ***
    Variable_name    Value
    character_set_client    utf8
    character_set_connection    utf8
    character_set_database    utf8
    character_set_filesystem    binary
    character_set_results    utf8
    character_set_server    latin1
    character_set_system    utf8
    character_sets_dir    "C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\"

    *** Webmatrix OUTPUT ***
    character_set_client    latin1
    character_set_connection    latin1
    character_set_database    latin1
    character_set_filesystem    binary
    character_set_results    
    character_set_server    latin1
    character_set_system    utf8
    character_sets_dir    C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\

    How is that possible?

    How can I solve my problem with è à ò ì ù characters????

  • Re: Character set issues: MySQL, Wordpress and Webmatrix

    Jan 18, 2017 07:26 AM|Yuk Ding|LINK

    Hi sparvier,

    Where did you published your website? IIS express or IIS.

    Best Regards,

    Yuk Ding

    Yuk Ding

    MSDN Community Support
    Please remember to "Mark as Answer" the responses that resolved your issue.
  • Re: Character set issues: MySQL, Wordpress and Webmatrix

    Jan 18, 2017 04:43 PM|sparvier|LINK

    Hi, Yuk: thanks for answering.

    On my PC (local) Webmatrix installed IIS Express. On my PC everything works fine (apart those strange results from that query).

    I published it on:

    • Windows Server 2012 R2
    • Microsoft IIS 8.5

    This is my web provider and my host plan (with WebMatrix support):

    https://hosting.aruba.it/en/hosting/windows-easy.aspx

    I also bought MySQL option:

    https://hosting.aruba.it/en/hosting/additional-services/mysql-database.aspx

    To better explain what happens, if I put an "illegal" character on a post or in a plugin field, for example, all the text following the illegal character is truncated after publishing via Webmatrix. And I investigated: the *content of the field* is truncated on the remote DB. There no trace of it. But if I edit (as wp-admin) my site on the remote server via Dashboard and put an illegal character on a post, it all works...

    So something goes wrong when publishing the db...

    If can be useful, take a look: www[dot]paolosparvieri[dot]com

    The upper banner *should* show the cookie policy.

    On my PC it is correctly showed.

    But on the remote site you will see a PHP error because in the text of the banner (a field of the plugin) there's a "è" and all the fields following are messed up...

  • Re: Character set issues: MySQL, Wordpress and Webmatrix

    Jan 25, 2017 09:06 AM|Yuk Ding|LINK

    Hi sparvier,

    Maybe you could encode the character.

    Best Regards,

    Yuk Ding

    Yuk Ding

    MSDN Community Support
    Please remember to "Mark as Answer" the responses that resolved your issue.
  • Re: Character set issues: MySQL, Wordpress and Webmatrix

    Jan 26, 2017 03:40 PM|sparvier|LINK

    Hi Yuk, what you mean by "encode your character"?

    --- --- ---

    I made these attempts on my PC:

    1. I uninstalled MySQL, deleted its folder, uninstalled Webmatrix
    2. Then I reinstalled Webmatrix (I had a copy on my PC) and reinstalled Wordpress and MySql (within Webmatrix)

    So, with a fresh new install of Webmatrix I wrote a post with "à è ì ò ù" and I published the site to the remote host....

    Same error: everything following "à è ì ò ù" is truncated...

    I think there's something wrong within Webmatrix when publishing a db containing non-asciii characters...

    A BUG?


    PS

    I finally tried to import the db (after creating a local SQL dump) from the myPHP console of the remote server (without using Webmatrix).

    After changing some fields of the db - to get logged on Wordpress - the problem disappeared (post containing à è ì ò ù now *work*).

    But most of my site doesn't work because of broken links...

  • Re: Character set issues: MySQL, Wordpress and Webmatrix

    Feb 07, 2017 08:13 AM|Yuk Ding|LINK

    Hi sparvier,

    Thanks for sharing your experience.

    Best Regards,

    Yuk Ding

    Yuk Ding

    MSDN Community Support
    Please remember to "Mark as Answer" the responses that resolved your issue.