CUNY Academic Commons

Thanks for the report. I see the same thing and I'll spend some time debugging.

Regarding the 404 issue, several of the BP tables were corrupt. Eg:

> describe wp_bp_xprofile_fields;
ERROR 1030 (HY000): Got error 194 "Tablespace is missing for a table" from storage engine InnoDB

As such, BP wasn't finding group 1, which resulted in the 404.

I checked all BP tables and found four problematic ones: messages_messages, profile_fields, profile_groups, profile_wire. I manually repaired them by dropping, recreating, and repopulating them from the snapshot files.

I don't know the underlying cause of this issue, but I wrote a tool that crawls through and identifies bad tables, then fixes them in the same manner:

#!/bin/bash

# Ensure the database name, schema directory, and data directory are provided
if [[ $# -lt 3 ]]; then
  echo "Usage: $0 <database_name> <schema_dir> <data_dir>" 
  exit 1
fi

DB_NAME="$1" 
SCHEMA_DIR="$2" 
DATA_DIR="$3" 

echo "Checking for bad tables in the database: $DB_NAME" 

# Fetch all tables from the database
tables=$(mysql --defaults-file=~/.my.cnf -D "$DB_NAME" -e "SHOW TABLES;" | awk '{ print $1 }' | grep -v "^Tables_in_")

# Loop through each table and check its status
for table in $tables; do
  result=$(mysql --defaults-file=~/.my.cnf -D "$DB_NAME" -e "CHECK TABLE \`$table\`;" 2>/dev/null | grep -E "Warning|Error|Corrupt")

  if [[ -n $result ]]; then
    echo "Issues found in table: $table" 
    echo "$result" 
    echo

    # Extract the prefix (up to the last `_`)
    prefix=$(echo "$table" | grep -oE '^[^_]+_[^_]+')
    echo "Identified prefix: $prefix" 

    # Build file paths for schema and data
    SCHEMA_FILE="$SCHEMA_DIR/${prefix}_schema.sql" 
    DATA_FILE="$DATA_DIR/${prefix}_data.sql" 

    if [[ -f "$SCHEMA_FILE" && -f "$DATA_FILE" ]]; then
      echo "Repairing all tables for prefix: $prefix" 

      # Drop and recreate all tables for the prefix
      mysql --defaults-file=~/.my.cnf -D "$DB_NAME" < "$SCHEMA_FILE" 
      if [[ $? -ne 0 ]]; then
        echo "Error applying schema for prefix: $prefix" 
        exit 1
      fi  

      # Reimport data for all tables in the prefix
      mysql --defaults-file=~/.my.cnf -D "$DB_NAME" < "$DATA_FILE" 
      if [[ $? -ne 0 ]]; then
        echo "Error applying data for prefix: $prefix" 
        exit 1
      fi  

      echo "Successfully repaired all tables for prefix: $prefix" 

      # Exit after the first repair for testing
#      echo "Stopping script for testing purposes. First repaired prefix: $prefix" 
#      exit 0                                                                                              
    else
      echo "Missing schema or data file for prefix: $prefix" 
      echo "Expected files:" 
      echo "  Schema: $SCHEMA_FILE" 
      echo "  Data: $DATA_FILE" 
      exit 1
    fi  
  fi  
done

echo "Table checks completed." 

It's running right now and finding a handful of bad blog prefixes. There doesn't seem to be a lot of rhyme or reason to it. But I'll just have to add this to my checklist for after migration.

Looks like this is also the cause for the wp-admin issue that you found.

I wanted also to flag that this might be a new issue since yesterday's mysql outage. It could be that some tablespaces got corrupted by the sync process, or by the daemon crash, or something like that. As such, I'm hopeful that it was a one-off problem.