Help! Anonymous User Comments Disappeared!

Looks like some MySQL data export/import operations can fail for anonymous users. I just did a data dump from my local development server to the live site and all the comments by users who aren't logged in were not showing up. Gone. Missing. It turns out MySQL was trying to stick the autoincrement value in the `uid` field in the place of the supplied `0`.

mysql auto increment

When the users table got migrated, UID zero ended up being 23 instead, and it seemed like all my anonymous comments were deleted. I started turning off modules and more or less panicking. All my comments with UID 0 were still in the comments table, so I tried changing the UID of a few comments - they showed up.

This happens because Drupal can't join the database tables if there is not a proper row to join against.

phpMyAdmin edit screen
Eventually, I went to phpMyAdmin and found the offending record in the users table. Change the row with the blank username back to zero, and everything is back to normal. Just click the little pencil and type away.

Hope this helps some folks out there when they're migrating data back and forth. Personally, I use NaviCat, which is lots of fun, but just about any desktop or web-based tool is going to work for shifting data around. Just watch out for these pitfalls.

P.S. Thanks to nvahalik for a glance over my shoulder on this one.

Comments

Thank you! This was my exact issue and I'm so glad it has been resolved!

Submitted by Stefan (not verified) on Sat, 05/07/2011 - 13:00

Add new comment