The Robservatory

Robservations on everything…

 

Back to posting…after fixing a snafu

I actually intended to start posting again a few days ago, but I was running into a couple of issues with the site: It was incredibly slow (annoying, but survivable), and the editor box was missing when I added a new post (which made posting quite tricky). I tried the usual troubleshooting steps—made sure everything was updated, disabled plug-ins, even briefly changed the site’s theme to see if it was a theme issue. No luck with anything.

Then I enabled WP_DEBUG on the site, which is something you should never do on a live site, as it will fill every page with tons of mostly meaningless warning and error messages. But in my case, I had to see what was happening when I tried to load the new post page. What I saw was troubling…

WordPress database error: [INSERT command denied to user '#######'@'###.###.###.###' for table 'wp_options']
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_timeout_jetpack_sync_constants_await', '1502403204', 'no') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

WordPress database error: [INSERT command denied to user '#######'@'###.###.###.###' for table 'wp_options']
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_jetpack_sync_constants_await', '1502399604.7325', 'no') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)
...
...

This went on and on, with screenfuls of such errors. Uh oh. Web searches found lots of possible causes with fixes, but none that worked for me. So I logged into 1and1 to check on the database…and I didn’t have to get any further than the summary screen to see the problem, as revealed in the image at right: The site’s database was using 150MB of the 100MB allocated to it—whoops!

100MB is not a lot of space, and it’s not what I get when I create a new database on 1and1—the limit is now 1GB. So why so puny? Basically because I’ve had robservatory.com since 2005, and never ever updated the database! I have no idea how long it’s been over the limit, but apparently it was finally over the limit enough that no more data could be written to the database.

The problem is that 1and1 can’t just bump the size of the database; you have to create a new one and migrate your data over to it. Thankfully, that’s not overly hard (read the rest if you’d like to know how I did it; documented for my own future sanity). After I moved to the new database, my edit window was back (hooray!), and as an added benefit, the site got much faster: The database is now hosted on an SSD, and the site’s not throwing 5,000 errors per second any more.

And now, I can get back to real blog posts.

Here’s how I migrated the data from the old database to the new, and updated the site to use the new database…

  1. Make a new database on 1and1, and note the relevant info (database name, user, password, and host). It will take 1and1 up to 15 minutes to get the new database fully set up.
  2. Duplicate the wp-config.php file; call it wp-config-NEW.php or whatever.
  3. Edit the duplicated file, and update all the database references with the new information. Save the file.
  4. Connect to 1and1 via ssh in Terminal, and dump the current database: mysqldump --add-drop-table -u OLD-DBuser -pOLD-DBpass -h OLD-DBhost OLD-DBname > DBdump.sql
  5. Once the database is set up, import your dumped data: mysql -u NEW-DBuser -pNEW-DBpass -h NEW-DBhost NEW-DBname<DBdump.sql
  6. Swap the two config files: mv wp-config.php wp-config-OLD.php;mv wp-config-NEW.php wp-config.php
  7. Load the site and make sure it works; if it does, you can delete the DB dump file and the old config file (though you may wish to keep it around for a bit, just in case). You can similarly delete the database from 1and1 once you’re sure everything works.

For me, this worked perfectly; the site moved seamlessly over to the new database. If it hadn’t, it would’ve been simple to undo the final mv command to put the old data back online while I debugged.

Leave a Reply

The Robservatory © 2017 Built from the Frontier theme