Announcement

Collapse
No announcement yet.

vBulletin 5 Database Tools

Collapse
X
Collapse
  •  

  • vBulletin 5 Database Tools

    The vBulletin Database Tools are a series of command line scripts that allow you to quickly make modifications to your database in order to provide performance fixes and update them to UTF8 standards.

    IMPORTANT- These files must be run via the command line, either if you have local access to the server or ssh access. If you are on shared hosting your access may be limited. You may ask your host for ssh access. If they say it is not available ask if they will run the commands for you.

    Update VB5 tables to InnoDB and UTF8

    Each script is listed under these steps with specific information for that script.

    1. Rename vbutil_config.php.new to vbutil_config.php
    2. Edit vbutil_config.php with a code editor and enter your database credentials. If unsure of the database credentials they can be found in the /core/includes/config.php file.
    3. Upload the utf8_db_tools directory to your server. They do not need to be in your vBulletin directory. They do need to be accessible from the command line.
    4. In forum Admin CP turn off vBulletin under Settings -> Options -> Turn your Forum On/Off
    5. Take a full backup of the forum database.
    6. Reread step 4. This cannot be skipped. I actually recommend using a full copy of your database for the first run.
    7. With command line change the directory to the directory where you placed the files.
    8. If you are not using INNODB tables, Run the MyISAM fix script listed below.
      • This is only necessary if your are not using INNODB tables.
    9. If you are using English on your site, then you would run the UTF8Tablefix script.
      • If you are not using English, do not use this script.
    10. Run the UTF8Convert script listed below.
      • Wait again, this can take a long time, especially the node table.
    11. Go to your Admin CP and open Languages & Phrases -> Language Manager
    12. Edit the settings of each language. Check the HTML Character Set setting and if it isn't UTF-8 change it to UTF-8.
    13. Rebuild your search engine.
    14. Check forum, make sure it is working as expected.
    15. When you are finished, delete the utf8_db_tools directory from your server.
    16. Turn your vBulletin back on in the AdminCP Settings.

    MyISAMFIX

    This tool will convert vBulletin 5 tables to INNODB from MyISAM. This change will generally provide a performance boost to a website. In addition it can help prevent table crashes and improve replication services. Before running this, use your database tool to check if it is needed. If your tables are already INNODB, there is no need to run this. This file will only convert default vBulletin 5 tables. Tables from previous versions or addon products will remain untouched.

    php myisamfix.phar -dofix
    

    UTF8TableFix

    This script is only useful if you're converting an English Language database to UTF-8. It has no parameters or options. The command is:

    php utf8tablefix.phar
    

    UTF8Convert

    For sites in other encodings or with multiple languages you need UTF8Convert. This script has several parameters. We recommend using UTF8MB4 with MySQL and MariaDB. This will allow you to use multiple languages properly as well as the popular UTF-8 Emoticon standard. This file will attempt to convert your actual data.

    php utf8convert.phar --options
    

    Required options are --connectionCharset and --charset. e.g.

    php utf8convert.phar --connectionCharset=latin1 --charset=utf8mb4
    

    All Options

    • --connectionCharset=[value] (required): This refers to whatever the previous site used. Commonly older sites will have latin1 and newer sites will have utf8, but it might be something different. This is the value from config.php for $config['Mysqli']['charset'], if specified. If the value is not specified in your /core/includes/config.php file it can be determined by running this query:
    SHOW SESSION VARIABLES LIKE 'character\_set\_client';
    
    • --charset=[value] (required) : This is the new charset, and should always be 'utf8mb4'
    • --wipeSearch=1 (optional) : If set the utility will truncate all the searchtowords tables before converting. I recommend you always set this, for two reasons. First, there are often words that are NOT duplicates in one encoding but ARE duplicates in a different encoding. By far the easiest way to avoid these problems is to wipe the data. Second, the conversion process is time consuming for anything over a few hundred thousand posts, and setting this makes it quicker.
    • --collateCI=[value] (optional) : This will default to utf8mb4_unicode_ci. This is the recommended collation.
    • -–collatebin=[value] (optional) : This will default to 'bin' added to the charset.

    Database Compare

    This is a database repair tool. It will compare two databases and create a list of queries to help synchronize the structures between them. This script should work with any version of vBulletin. To use it, set the Source values in the vbutil_config.php file to match a clean installation of vBulletin. This installation should match the version you want to compare with. The standard database options in the vbutil_config.php file should point to the database that is currently experiencing issues.

    php dbcompare.phar indexscript > update.sql
    

    You can run the queries by importing them in any tool that allows you to run MySQL Queries

    Example:

    mysql -u%user% -p %databas% < update.sql
    

    Replace %user% and %database% as appropriate.

    Last edited by Wayne Luke; Mon 3rd Feb '20, 8:04pm.

    • Wayne Luke
      #14
      Wayne Luke commented
      Editing a comment
      Don't actually see the error there. Please open a proper support topic in the forums. Include all of the output.

    • Logam
      #15
      Logam commented
      Editing a comment
      Hello, the error is the 2 last lines:
      UPDATE vbul_language SET charset='utf-8'
      Errors: array (
      )

    • Wayne Luke
      #16
      Wayne Luke commented
      Editing a comment
      Did you read the error? It has absolutely no information. You need to open a proper support topic if you want help.
    Posting comments is disabled.

About the Author

Collapse

Wayne Luke A curious juxtaposition of nature, technology and sustainability. Find out more about Wayne Luke

Article Tags

Collapse

Latest Articles

Collapse

  • vBulletin 5 Database Tools
    by Wayne Luke
    The vBulletin Database Tools are a series of command line scripts that allow you to quickly make modifications to your database in order to provide performance fixes and update them to UTF8 standards. IMPORTANT- These files must be run via the command line, either if you have local access to the server or ssh access. If you are on shared hosting your access may be limited. You may ask your host for ssh access. If they say it is not available ask if they will run the commands for you. Update VB5 ...
    Mon 21st Oct '19, 9:29am
  • vBulletin 5 Database Best Practices
    by Wayne Luke
    This is part of a best practices series to manage your vBulletin installation. The database is the heart and soul of your vBulletin site. All content and user information is stored in the database. Protect the database and you protect your site. This document will go over the creation and usage of a MySQL database for the use of vBulletin 5 Connect. If you have shared hosting and are provided a web-based control panel like cPanel, you will need to access your hosting provider's documentation on how to carry out these operations. This document assumes a general familiarity with the command line operations of your Operating System. All commands listed assume that you are accessing your server via SSH. ...
    Wed 31st Oct '18, 7:18am
  • Installing Memcached for vBulletin
    by Wayne Luke
    Note: Installing and using Memcached requires access to the command line and the ability to install software on your server. If you are using a Shared Hosting Package, then you may not have access to this capability. If you are in doubt, please contact your hosting provider. If you are running vBulletin in a Virtual Machine or on a Dedicated Server, you can improve performance with Memcached. This allows you to move some of the caching systems from vBulletin's database and into memory. On the s...
    Sat 27th Oct '18, 12:00pm
  • Using Tools.php
    by Wayne Luke
    Within your vBulletin Download Package, we provide a file called tools.php, this file isn't uploaded to the server by default as it is considered to be a significant security risk. However, there are times when you need to change specific settings and aren't able to access your AdminCP directly. Uploading to the Server It is recommended that tools.php is installed in the /core/install directory. To do this, follow the steps below: Connect to your server with your favorite SFTP or SCP client. In you...
    Sat 27th Oct '18, 11:45am
  • Converting your forum to https
    by Mark.B
    CONVERTING YOUR FORUM TO HTTPS
    Applies to self-hosted versions of:
    vBulletin 3; vBulletin 4; vBulletin 5;
    Cloud sites have https enabled by default and you do not need to do anything.

    This FAQ explains how to convert your vBulletin forum to use secure https (SSL) rather than http, and why you might need to.
    Note: This guide contains links to external sites. vBulletin Solutions is not responsible for the content of external links and cannot be held responsible...
    Fri 9th Dec '16, 2:59am
Working...
X