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
