Fully Uninstall & Remove qTranslate-X with Database Cleanup of Unwanted Language Translations

»»»Fully Uninstall & Remove qTranslate-X with Database Cleanup of Unwanted Language Translations

qTranslate-X is the plugin that succeeds popular qTranslate multilingual plugin for WordPress which was abandoned by its author. qTranslate X provides functionality to maintain dynamic multilingual content on a WordPress site, by offering options to make title, content, excerpt, menu, meta and other part of websites multilingual.

qTranslate-X works by storing all the different language translations in the same post, page, menu or other elements, and thus in the same field of the database. Each translation is separated by special shortcode-like language tags that are parsed by qTranslate-X plugin to indicate the start and the end of the language.

Thus, after deactivating and uninstalling the qTranslate-X plugin, all the languages will be displayed on the frontend, together with the language tags (e.g. [:en] or {:en} or [:] or {:}) that are specifying the language codes, messing up everything. This is caused after qTranslate-X is deactivated, the plugin no longer provides the functions necessary to interpret and handle the shortcodes during the_content() call. Thus the language tags are not parsed and all content in the database field returns in whole.

The issue presents problem for WordPress administrators who want to uninstall qTranslate X and remove multilingual content in order to revert back to single language website. While removal of qTranslate-X plugin is easy, the clean up of database is not straightforward, especially for a large website with plenty of content already translated.

This tutorial provides a guide on how to clean up the database that was installed with qTranslate-X previously to create a multilingual website, but now want to return to monolingual website with only a single language of content left intact.

Please BACKUP DATABASE before performing any of the qTranslate-X translations removal steps.
The tutorial assumes only content in English will be kept, discarding all other translated languages of content. If you want to keep another language, replace the qTranslate-X language identifier, [:en]]for English, to the language tag for the intended language. qTranslate-X uses 2-letter language codes, which can be found here, or you can check the actual language tags that are actually written into the database fields.

Note that if your content has [: in it, whether it’s text or shortcode, the SQL queries may return erroneous results.

qTranslate-X plugin can be activated or deactivated while the cleanup is performed. But if it’s activated, do not make any changes to the content as it will reintroduce the qTranslate X shortcodes into the specific content.
  1. In phpMyAdmin or any other MySQL client, open the database for the WordPress website that you want to clean up the qTrasnlate-X mess.
  2. Run the following SQL query to strip and remove the content in other languages after the “en” content in wp_posts table:
    UPDATE wp_posts SET post_content = case when 
    LOCATE('[:en]', post_content) > 0 
    then 
    SUBSTRING(
        post_content
            FROM 1
            FOR LOCATE(
                    '[:', 
                    post_content,
                    LOCATE('[:en]', post_content) + 5
            ) - 1
    ) 
    else 
    post_content 
    end;
  3. Run the following SQL query to strip and remove the content in other languages before the “en” content in wp_posts table, including the language tag for the “en” content:
    UPDATE wp_posts SET post_content = case when 
    LOCATE('[:en]', post_content) > 0 
    then 
    SUBSTRING(
        post_content
            FROM LOCATE(
                    '[:en]', 
                    post_content
            ) + 5
    ) 
    else 
    post_content
    end;
  4. Run the following SQL query to strip and remove the title in other languages after the “en” title in wp_posts table:
    UPDATE wp_posts SET post_title = case when 
    LOCATE('[:en]', post_title) > 0 
    then 
    SUBSTRING(
        post_title
            FROM 1
            FOR LOCATE(
                    '[:', 
                    post_title,
                    LOCATE('[:en]', post_title) + 5
            ) - 1
    ) 
    else 
    post_title
    end;
  5. Run the following SQL query to strip and remove the title in other languages before the “en” title in wp_posts table, including the language tag for the “en” title:
    UPDATE wp_posts SET post_title = case when 
    LOCATE('[:en]', post_title) > 0 
    then 
    SUBSTRING(
        post_title
            FROM LOCATE(
                    '[:en]', 
                    post_title
            ) + 5
    ) 
    else 
    post_title
    end;
  6. Run the following SQL query to strip and remove the excerpts in other languages after the “en” excerpt in wp_posts table:
    UPDATE wp_posts SET post_excerpt = case when 
    LOCATE('[:en]', post_excerpt) > 0 
    then 
    SUBSTRING(
        post_excerpt
            FROM 1
            FOR LOCATE(
                    '[:', 
                    post_excerpt,
                    LOCATE('[:en]', post_excerpt) + 5
            ) - 1
    ) 
    else 
    post_excerpt
    end;
  7. Run the following SQL query to strip and remove the excerpts in other languages before the “en” excerpt in wp_posts table, including the language tag for the “en” excerpt:
    UPDATE wp_posts SET post_excerpt = case when 
    LOCATE('[:en]', post_excerpt) > 0 
    then 
    SUBSTRING(
        post_excerpt
            FROM LOCATE(
                    '[:en]', 
                    post_excerpt
            ) + 5
    ) 
    else 
    post_excerpt
    end;
  8. If you have translated other elements of WordPress site, such as menus, metas, themes and plugins, you may need to modify the SQL queries or manually remove unwanted languages from those elements.
  9. If qTranslate-X is using different language tag symbol, such as {} instead of [], you need to modify the above queries and run them again.
  10. Deactivate qTranslate-X plugin and check the frontend of the website to ensure that only one language is displayed and everything works properly.

If you’re worried about directly changing the database, a plugin is available that may do the job to clean up the qTranslate X. qTranslate X Cleanup and WPML Import can either cleanup the qTranslate X meta-HTML tags from your site and leave just one ‘clean’ language, or migrate all languages to WPML’s format, though the function that lets you clean up the language meta-tags and keep just one language in your site may not be working.

By | 2017-07-19T00:28:27+00:00 July 19th, 2017|Categories: Web Publishing|Tags: , , |6 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.

Connect with LK through Tech Journey on Facebook, Twitter or Google+.

  • bg17aw

    Hey, none of the queries do not work. The first one alone has 38 errors as reported by PHPMyadmin…

    • Admin

      What is the error message?

      • bg17aw

        Seems like phpmyadmin requires a “(” before “CASE” keyword, otherwise the parser will not work correctly

  • bg17aw

    After running all these queries, I am still left with “[:en]” at the beginning of wp_posts.post_content. The ending “[:]” are removed correctly, but not the starting once. This only happens for the wp_posts table though

  • Karim

    Hi there. Great post!
    One question. This works great if your initial language is english (and you added another later).
    ie if your tags are like this for example, right?
    [:en]english[fr:]french[:]

    But what if english was the second language you added on, hence looking like this:
    [:fr]french[en:]english[:]

    BUT YOU WANT TO KEEP ENGLISH. That wouldn’t work, would it?
    If not, do you have any plans to offer another version of this script for the use case. From the googling around, it seems like many people are in this case as well, but I can’t seem to find a solution.

  • Pete

    How about the widgets?

Pin It on Pinterest

Share This

Share This

Share this post with your friends!