Coverting masspirates.org to https

Converting masspirates.org to https.

piratenkleider repair

Our wordpress theme stores a collection of attributes in the wp_options table. Here's a sample.

 localhost:masspirates_wp> select * from wp_options where option_id = 300067\G
 *************************** 1. row ***************************
    option_id: 300067
  option_name: theme_mods_piratenkleider
 option_value: a:4:{i:0;b:0;s:18:"nav_menu_locations";a:3:{s:7:"primary";i:0;s:3:"top";i:33;s:3:"sub";i:0;}s:12:"header_image";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:17:"header_image_data";O:8:"stdClass":5:{s:13:"attachment_id";i:1301;s:3:"url";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:13:"thumbnail_url";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:6:"height";i:0;s:5:"width";i:0;}}
     autoload: yes
 1 row in set (0.00 sec)

These are serialized php objects, where strings are all length-prefixed. For example, s:6:"height"; means "string of six characters, which are 'height'". In this case, you can't simply edit the values -- you have to get the lengths right.

Do do this, you'll need to deserialize the php objects, dump them out, change the values, and then re-serialize. I did this with a semi-manual process, because we only had three options to change.

 <?php
 $text = 'a:4:{i:0;b:0;s:18:"nav_menu_locations";a:3:{s:7:"primary";i:0;s:3:"top";i:33;s:3:"sub";i:0;}s:12:"header_image";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:17:"header_image_data";O:8:"stdClass":5:{s:13:"attachment_id";i:1301;s:3:"url";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:13:"thumbnail_url";s:81:"http://masspiratesweb.mayfirst.org/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:6:"height";i:0;s:5:"width";i:0;}}';
 $obj = unserialize($text);
 var_export($obj);

This pretty prints the deserialized object. Given the pretty-printed copy, we can edit the values, and re-serialize.

 <?php
 $x = array (
   0 => false,
   'nav_menu_locations' => 
   array (
     'primary' => 0,
     'top' => 33,
     'sub' => 0,
   ),
   'header_image' => '/blog/wp-content/uploads/2013/03/TabLogoF-1.png',
   'header_image_data' => (object)
   array(
      'attachment_id' => 1301,
      'url' => '/blog/wp-content/uploads/2013/03/TabLogoF-1.png',
      'thumbnail_url' => '/blog/wp-content/uploads/2013/03/TabLogoF-1.png',
      'height' => 0,
      'width' => 0,
         ),
             );
 print serialize($x) . "\n";

Once we have the new serialized value, we update the wp_options table

 update wp_options
 set option_value = 'a:4:{i:0;b:0;s:18:"nav_menu_locations";a:3:{s:7:"primary";i:0;s:3:"top";i:33;s:3:"sub";i:0;}s:12:"header_image";s:47:"/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:17:"header_image_data";O:8:"stdClass":5:{s:13:"attachment_id";i:1301;s:3:"url";s:47:"/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:13:"thumbnail_url";s:47:"/blog/wp-content/uploads/2013/03/TabLogoF-1.png";s:6:"height";i:0;s:5:"width";i:0;}}'
 where option_id = 300067;

Then, repeat for the other values. These are the options that required changes

 +-----------+------------------------------------+
 | option_id | option_name                        |
 +-----------+------------------------------------+
 |    300067 | theme_mods_piratenkleider          |
 |    300082 | piratenkleider_theme_options       |
 |    300101 | piratenkleider_theme_defaultbilder |
 +-----------+------------------------------------+

I wasn't able to get the unserialize-modify-serialize trick to work with widget_text, so I think we'll have to modify that through the UI.

http://masspirates.org also appears in widget_text. I wasn't able to get that to deserialize, so we may have to fix it via the UI.

Updating the rest of the database

When deploying these changes, I noticed some bad interactions with Firefox and WP Super Cache (which I didn't notice on my development copy). Disabling WP Super Cache seemed to fix this.

I applied the changes to wordpress's wp_options table, and changed

• Settings > General > Wordpress Address
• Settings > General > Site URL

to https://masspirates.org/. Doing this much, wordpress seems to change http://masspirates.org links to https://masspirates.org.

After further investigation, I noticed that wordpress seemed up rewrite most of the http urls to https, but not all of them. In addition, we have a mixture of references to http://masspirates.org and http://www.masspirates.org/; those should be cleaned up.

 mysql>  select count(*) from wp_posts where post_content like '%http://masspirates.org%';
  +----------+
  | count(*) |
  +----------+
  |      522 |
  +----------+
  1 row in set (0.04 sec)
  
  mysql>  select count(*) from wp_posts where post_content like '%http://www.masspirates.org%';
  +----------+
  | count(*) |
  +----------+
  |      484 |
  +----------+
  1 row in set (0.05 sec)

Here's the basic replacement strategy:

 mysql> select replace("see http://masspirates.org/blog/my-post", 'http://masspirates.org', 'https://masspirates.org') as 'example';
  +------------------------------------------+
  | example                                  |
  +------------------------------------------+
  | see https://masspirates.org/blog/my-post |
  +------------------------------------------+

Here's the backup

 mysqldump --verbose --complete-insert=1 masspira_wrd1 >> masspira_wrd1.sql
 xz -vv -z9 -f masspira_wrd1.sql

And here are the database updates

 mysql> update wp_posts set post_content = replace(post_content, 'http://masspirates.org', 'https://masspirates.org');
  Query OK, 522 rows affected (0.15 sec)
  Rows matched: 2265  Changed: 522  Warnings: 0
  
  mysql> update wp_posts set post_content = replace(post_content, 'http://www.masspirates.org', 'https://masspirates.org');
  Query OK, 484 rows affected (0.12 sec)
  Rows matched: 2265  Changed: 484  Warnings: 0
  
  mysql> update wp_comments set comment_content = replace(comment_content, 'http://masspirates.org', 'https://masspirates.org');
  Query OK, 1 row affected (0.04 sec)
  Rows matched: 419  Changed: 1  Warnings: 0
  
  mysql> update wp_comments set comment_content = replace(comment_content, 'http://www.masspirates.org', 'https://masspirates.org');
  Query OK, 2 rows affected (0.02 sec)
  Rows matched: 419  Changed: 2  Warnings: 0
  
  mysql> update wp_comments set comment_author_url = replace(comment_author_url, 'http://masspirates.org', 'https://masspirates.org');
  Query OK, 11 rows affected (0.01 sec)
  Rows matched: 419  Changed: 11  Warnings: 0
  
  mysql> update wp_comments set comment_author_url = replace(comment_author_url, 'http://www.masspirates.org', 'https://masspirates.org');
  Query OK, 22 rows affected (0.00 sec)
  Rows matched: 419  Changed: 22  Warnings: 0
  
  mysql> update wp_posts set post_content = replace(post_content, 'http://www.youtube.com', 'https://www.youtube.com');
  Query OK, 159 rows affected (0.12 sec)
  Rows matched: 2265  Changed: 159  Warnings: 0