After upgrading to WordPress 4.2.2, the PostgreSQL plugin broke. My blog stuck in infinite database update loop. If I logged out, I could not log back in, suck in infinite login loop. After looking at the nginx log at /var/log/nginx/error.log, I see a lot of database errors like the following. I remembered seeing the PostgreSQL plugin, PG4WP, does not have official support. Very likely that it is broken by WordPress 4.2.2. Then I decided to convert to MySQL and try upgrading again. And there you go, upgraded fine!
PHP message: WordPress database error ERROR: syntax error at or near "FULL"
LINE 1: SHOW FULL COLUMNS FROM wp_options
^ for query SHOW FULL COLUMNS FROM `wp_options` made by delete_site_transient, delete_site_option, delete_option" while reading response header from upstream, client: 192.168.9.128, server: localhost, request: "GET /wp-admin/upgrade.php?_wp_http_referer=%2Fwp-admin%2F HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "testblog.jiang925.com"
PHP message: WordPress database error ERROR: syntax error at or near "a"
LINE 1: DELETE a, b FROM wp_options a, wp_options b
^ for query DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_transient\_%'
AND a.option_name NOT LIKE '\_transient\_timeout\_%'
AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
AND b.option_value < 1433294537 made by wp_upgrade, upgrade_all, populate_options
There are tools, like pg2mysql, MySQL WorkBench. However, I tried an easier and faster way using only stock PostgreSQL and MySQL clients.
I have WordPress hosted on Heroku that is using the Heroku PostgreSQL addon. Get a MySQL database ready either by adding a MySQL addon or somewhere else. Having a local set up will definitely help.
Step 1: Install clean WordPress on MySQL
Prepare the new MySQL database, write up the database url and replace the DATABASE_URL with it. Then go to the url for your site as if you are installing a clean WordPress. The point is to get WordPress set up the database for you. Fill in the information on the first page and hit continue. Don’t have to proceed once the database is set up.
Step 2: Export PostgreSQL data and import to MySQL
Your DATABASE_URL is formatted in this way: [db_type]://[username]:[password]@[hostname]:[port]/[db_name]. You’ll need those information in this step.
# dump data from PostgreSQL, use your PostgreSQL information
pg_dump -h [hostname] -p [port] -W -U [username] --inserts -a --format p -f data.sql [db_name]
# insert these lines at the beginning of data.sql
DELETE FROM wp_commentmeta;
DELETE FROM wp_comments;
DELETE FROM wp_links;
DELETE FROM wp_options;
DELETE FROM wp_postmeta;
DELETE FROM wp_posts;
DELETE FROM wp_term_relationships;
DELETE FROM wp_term_taxonomy;
DELETE FROM wp_terms;
DELETE FROM wp_usermeta;
DELETE FROM wp_users;
# insert data to MySQL, use your MySQL information
mysql -u [username] -p -h [hostname] -P [port] [db_name] < data.sql
Step 3: Complete the set up
Now change the DATABASE_URL to point to at your MySQL and you’re set!