Database question regarding collation latin1 vs utf8 vs utf8mb4

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Mr B
    Member
    • Mar 2007
    • 97
    • 5.3.x

    [Server Config] Database question regarding collation latin1 vs utf8 vs utf8mb4

    We got several unresolved issues with vBulletin 5.4.5 (mostly issues related to special characters like åäö in the swedish language) and the support here have suggested us to convert the MySQL database from latin1 to utf8mb4. The problem is that the conversation is a very complex task to do.

    Is vBulletin 5.4.5 compatible with the latin1 collation or not? We have used vBulletin from 2002 until now and it have worked very good with latin1 until the latest upgrade from 4.2.5 to 5.4.4/5.4.5. Yes, i know that version 5.4.5 is a complete new version and works in different ways than ver 3.x and 4.x. But, shouldnt it work in ver 5.x also?

    Nothing in the online manual https://www.vbulletin.com/en/manual/ states that latin1 is not supported. It does not suggest utf8 or utf8mb4 either. Not in the file vb5readme.html included in the downloaded package either. The upgrade script did not warn for this either…. I have seen that fresh installs warns and informs about it but i saw that after our production upgrade.

    Do we really really really need to convert the database from latin1 to utf8mb4 to get everything working? If so, how can we do it in easiest possible way? Is there upgrade scripts for this available?
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73976

    #2
    If you want to support UTF-8 characters used in languages other than English on modern computers, then you will need to convert your database to a UTF8 character set. We recommend UTF8MB4 for future compatibility. I've attached scripts to your previous requests on this that does this in 15 minutes or less for most databases.

    vBulletin 4.2.5 does not store UTF-8 characters. It converts them to HTML Entities. When vBulletin 4 was released almost a decade ago, MySQL didn't support storing UTF-8 characters.

    vBulletin 5 tries to do this properly and store UTF-8 within MySQL because it is supported now.

    If you're using English like the majority of our customers do, then you can use Latin1. The manual doesn't discuss it because even when vBulletin 5 originally shipped, we didn't support UTF-8. It is only in 5.3.0 and higher that this is even possible.

    You can use the scripts previously provided or purchase a Support Contract for $49.00 and have us do it. In order for us to do it, we either need a complete backup of your database (your forums will need to be offline during the conversion) or command-line access to your server.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • Mr B
      Member
      • Mar 2007
      • 97
      • 5.3.x

      #3
      Originally posted by Wayne Luke
      If you want to support UTF-8 characters used in languages other than English on modern computers, then you will need to convert your database to a UTF8 character set. We recommend UTF8MB4 for future compatibility. I've attached scripts to your previous requests on this that does this in 15 minutes or less for most databases.

      vBulletin 4.2.5 does not store UTF-8 characters. It converts them to HTML Entities. When vBulletin 4 was released almost a decade ago, MySQL didn't support storing UTF-8 characters.

      vBulletin 5 tries to do this properly and store UTF-8 within MySQL because it is supported now.

      If you're using English like the majority of our customers do, then you can use Latin1. The manual doesn't discuss it because even when vBulletin 5 originally shipped, we didn't support UTF-8. It is only in 5.3.0 and higher that this is even possible.

      You can use the scripts previously provided or purchase a Support Contract for $49.00 and have us do it. In order for us to do it, we either need a complete backup of your database (your forums will need to be offline during the conversion) or command-line access to your server.
      Thanks for the fast reply.

      Where have you attached the scripts needed for converting the database, tables and columns from latin1 to utf8mb4? Can you post them again or give a link to them? I can do a test run in our test environment and see how it goes. Will all data with special characters be correct after the scripts are executed?

      I think that we have an active Support Contract. We have access to the "Support Ticket System" in the Member Area. I think we have problems to send the complete database or give command-line access to the server. TeamViewer will work for us. But, i can test the scripts by myself first.

      Comment

      • Wayne Luke
        vBulletin Technical Support Lead
        • Aug 2000
        • 73976

        #4
        I don't see a support contract on your account. The link is static because the Member's Area is almost 2 decades old.

        Here are the scripts. There is a readme.md file in the package with instructions.

        Attached Files
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment

        • Mr B
          Member
          • Mar 2007
          • 97
          • 5.3.x

          #5
          Originally posted by Wayne Luke
          I don't see a support contract on your account. The link is static because the Member's Area is almost 2 decades old.

          Here are the scripts. There is a readme.md file in the package with instructions.
          Where can i verify if we have a support contract or not? And how do i purchase it if we do not already have it? I cant find the info in "My Account", "Purchase", "Renewals / New Purchases". The support form loads if i click on the "Support" button on your main site. Im sending our customer and license# as a private message.

          Thanks for the scripts! I must have missed them before but will test them out today. The file appears in 2 topics when i search the forum. This topic is one and then its another topic from another member.

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73976

            #6
            If you have a support contract it will be listed under the license that it is attached to on the front page of https://members.vbulletin.com. It will list the start and end date of the contract. I've already looked at your account and there isn't an active support contract.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API

            Comment

            Related Topics

            Collapse

            Working...