Database Restoration Tricks: Solving the MySQL DEFINER Problem

by

Just wrapped up a tricky Confluence restoration project and thought I’d share some quick notes on what worked.

The biggest challenge? Database privileges. Our restore kept failing with this error:

ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

This happens because the SQL dump contains DEFINER clauses that reference specific database users from the source environment. These users don’t exist in the target environment, causing permission errors during restoration.

The solution was surprisingly simple – strip those DEFINER statements from the SQL dump:

gunzip -c backup.sql.gz | sed 's/DEFINER=`[^`]*`@`[^`]*`//g' | gzip > modified.sql.gz

This one command saved hours of troubleshooting and allowed the restore to proceed smoothly.

For validating the restoration, I recommend checking:

  • Database size comparison between source and target
  • Row counts in key tables using these queries:
-- Check content pages
SELECT COUNT(*) FROM confluence.CONTENT WHERE contenttype='PAGE';

-- Check body content
SELECT COUNT(*) FROM confluence.BODYCONTENT;

-- Check spaces
SELECT COUNT(*) FROM confluence.SPACES;

-- Check database size
SELECT table_schema as 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'confluence'
GROUP BY table_schema;

  • Functional testing of spaces, pages, and attachments

Other key lessons:

  • Always check database sizes after restoration (our first attempt showed significant discrepancies)
  • Don’t forget to sync the attachments directory
  • One final restart after attachment sync ensures everything connects properly

Leave a Comment