One of my WordPress-based sites (http://crowdspace.net) was not working when I tried to publish posts containing images from Windows Live Writer. I was getting a 500 Internal Server Error.
I was able to fix the problem by making a small change to the database.
The Error Message
I enabled Failed Request Tracing in IIS7 to find out what was happening server-side to cause the HTTP 500 error. I discovered that the PHP script was trying to insert a row into the wp_posts table with a value of -1 in the post_parent column:
WordPress database error Out of range value for column ‘post_parent’ at row 1 for query INSERT INTO `wp_posts` (`post_author`,`post_date`,`post_date_gmt`, `post_content`, `post_content_filtered`, `post_title`, `post_excerpt`, `post_status`, `post_type`, `comment_status`,`ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_parent`,`menu_order`,`post_mime_type`,`guid`) VALUES (‘1′,’2009-07-15 14:37:14′,’2009-07-15 17:37:14′,”,”,’2827426439_7b744abd30_m.jpg’, ”, ‘inherit’, ‘attachment’,’open’,’open’, ”, ‘2827426439_7b744abd30_m-jpg’,”,”,’2009-07-15 14:37:14′,’2009-07-15 17:37:14′,’-1′, ‘0’, ‘image/jpeg’, ‘http://crowdspace.net/files/2827426439_7b744abd30_m5.jpg’) made by wp_xmlrpc_server->wp_xmlrpc_server, IXR_Server->IXR_Server, IXR_Server->serve, IXR_Server->call, wp_xmlrpc_server->mw_newMediaObject, wp_insert_attachment
Apparently the database didn’t like thatâ€¦ I investigated further and discovered that the post_parent column in my database was set to BIGINT UNSIGNED. In other words, that database column could not hold the value -1 (unsigned integers are zero or higher).
Some Sanity Checking
I checked several other WordPress databases that I control and found that post_parent column is not UNSIGNED in any of my other WP databases.
So why the difference? All my other databases were created with earlier versions of WordPress and upgraded. The database in question, however, had been created with the latest WordPress release (2.8 at the time). So I popped open the PHP file that defines the database (schema.php) and discovered:
post_parent bigint(20) unsigned NOT NULL default ‘0’,
What’s Going On Here?
When the XMLRPC script uploads an image (called an attachment in WordPress), it uploads it with a post_parent of -1. Then after the post is created, the script updates attachments with post_parent=-1 with the actual ID of the freshly created post.
My guess is that a developer on the project decided to update the schema to UNSIGNED because posts do not have negative values for IDs and the post_parent column references a post ID. Obviously that developer did not realize the special use case in xmlrpc.php.
How Do I Fix This?
Fortunately the workaround is easy. Just run the following database query to change the data type on the post_parent column:
ALTER TABLE wp_posts CHANGE post_parent post_parent BIGINT;
If you don’t know how to run queries against your MySQL database, well, this is a good time to learn. I did it at the command line on my 64-bit Windows server:
cd "C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\"
mysql.exe â€“u root -p
Enter password: ***********************
Welcome to the MySQL monitor. Commands end with ; or \g.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use crowdspace
mysql> ALTER TABLE wp_posts CHANGE post_parent post_parent BIGINT;
The process is similar on a Linux box. I’m going to go out on a limb here and assume that you can figure out how to run mysql on Linux if you’re running your own Linux server.
If you’re using a shared web hosting service, you should be able to use a web interface such as phpMyAdmin to run this command.
That’s all it took to fix the problem for me. Happy blogging!