Prevent changing the length of post_name column on WordPress Update

Question

Prevent changing the length of post_name column on WordPress Update

Jafar Abazeed

2022年2月1日 12:23

We've some Arabic websites running on WordPress, and use Arabic slugs in the posts' URLs.

You know that post slug is stored in the post_name column in wp_posts table, and the default length of this column is 200 characters, which is not enough for Arabic slugs that are being encoded before being stored in this column. For that, we changed the length of this column to 1000 characters to hold the long encoded slugs that we have.

The issue, when we update the WordPress code to a major update, it trims this column back to 200 characters, and this breaks the posts' URLs. This happens if you login for the first time after the update to the CMS or do a Network Upgrade that is prompted after each major update.

Is there any way to prevent WordPress from touching the tables' structure?

UPDATE

I tried to run a SQL query after the Network Upgrade to alter the post_name column, but I got a SQL syntax error, while I didn't get any issue when running the same query using the DB console.

Here's my code:

function after_network_upgrade() {
    global $wpdb;
    // Get the length of the `post_name` column in the `posts` table.
    $query = $wpdb-prepare(  
        SELECT CHARACTER_MAXIMUM_LENGTH
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
            TABLE_NAME   = '%s' AND 
            COLUMN_NAME  = 'post_name'
        , $wpdb-base_prefix.'posts' );
    $post_name_max_length = $wpdb-get_results($query)[0]-CHARACTER_MAXIMUM_LENGTH;
    // if the length is set to the default value `200`, then do the fix.
    if ($post_name_max_length == 200) {
        $wpdb-query(
            ALTER TABLE wp_posts   MODIFY `post_name` VARCHAR(1000) NULL;
            ALTER TABLE wp_2_posts MODIFY `post_name` VARCHAR(1000) NULL;
            ALTER TABLE wp_3_posts MODIFY `post_name` VARCHAR(1000) NULL;
            ALTER TABLE wp_4_posts MODIFY `post_name` VARCHAR(1000) NULL;
            ALTER TABLE wp_terms   MODIFY `slug`      VARCHAR(500)  NULL;
            ALTER TABLE wp_2_terms MODIFY `slug`      VARCHAR(500)  NULL;
            ALTER TABLE wp_3_terms MODIFY `slug`      VARCHAR(500)  NULL;
            ALTER TABLE wp_4_terms MODIFY `slug`      VARCHAR(500)  NULL;
            UPDATE wp_posts   SET `post_name` = `post_name_copy`;
            UPDATE wp_2_posts SET `post_name` = `post_name_copy`;
            UPDATE wp_3_posts SET `post_name` = `post_name_copy`;
            UPDATE wp_4_posts SET `post_name` = `post_name_copy`;
            UPDATE wp_terms   SET `slug`      = `slug_copy`;
            UPDATE wp_2_terms SET `slug`      = `slug_copy`;
            UPDATE wp_3_terms SET `slug`      = `slug_copy`;
            UPDATE wp_4_terms SET `slug`      = `slug_copy`;
        );
        if ($wpdb-last_error !== '') {
            print p class='wpdberror'strongWordPress database error:/strongcode;
            print_r ($wpdb-last_error);
            print /codebr //div;
        } else {
            print divThe length of the `post_name` column length is FIXED!/div;
        }
    }
}
add_action( 'after_mu_upgrade', 'after_network_upgrade', 10, 1 );

The post_name_copy and slug_copy are custom columns that keep a backup of the post_name and slug values to use when the issue happens.

And this is the printed SQL error I got:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE wp_2_posts MODIFY post_name VARCHAR(1000) NULL; ALTER ' at line 2

Any help on this, please?

Topic slug upgrade database updates Wordpress

Category Web

mozboz · Accepted Answer · 2020年8月12日 13:47

I don't have a definitive answer for you, but I had a quick look at some of the upgrade code and noticed that a very old upgrade function rewrites the post_name using the sanitize_title function, and that calls sanitize_title_with_dashes, and that contains this bit of code:

  if ( seems_utf8( $title ) ) {
    if ( function_exists( 'mb_strtolower' ) ) {
      $title = mb_strtolower( $title, 'UTF-8' );
    }
    $title = utf8_uri_encode( $title, 200 );
  }

Note that this is pretty interesting because only if the function mb_strtolower is not available will this code cause the string to become truncated to 200 characters!

You could do a quick check on your system to see if you have the PHP multibyte string extension installed, because if you don't, then sanitize title will truncate your titles, and so maybe you can solve your problem by installed the PHP multibyte extension.

To check if that's the case, have a look at phpinfo();, or just run this somewhere directly on your server:

if ( function_exists( 'mb_strtolower' ) ) {
   echo "yes mb strings installed";
} else {
   echo "no mb strings not installed";
}

Please note: There's too much code for me to validate that this is definitely what's happening in your case. Someone else may have a definite answer. The '200' value which aligns perfectly with what's happening for you seems like a good indiciation that this could be the issue, but I didn't find any specific place where this is called on every single upgrade.

Prevent changing the length of post_name column on WordPress Update

UPDATE

About