Comparing SQL tables

I ran into a small problem with one of the TODBs, and I needed to create new years of data using the admin years facility. I wanted to copy the current year to the previous year, then delete the current year and recreate it as a new year.

Before deleting the current year, I wanted to be sure that the copy was complete. I tried mysqldumping the two tables for comparison, but some differences appeared (there was a 1-byte difference in the file sizes). So I used diff. However, all it told me was that there was a difference in one line, but that line contains all the data for the table - 1000s of characters! Not helpful.

So instead, I figured this out (with the generous help of Google):

1. Dump the tables using mysqldump:
   mysqldump -u root databasename tablename_old > table_old.sql;
   mysqldump -u root databasename tablename_new > table_new.sql;
2. Reformat the files to trim the lines to manageable lengths:
   fmt -s table_old.sql > table_old_fmt.sql;
   fmt -s table_new.sql > table_new_fmt.sql
3. Then run the diff on them:
   diff table_old_fmt.sql table_new_fmt.sql

Voila! Instant diff with information on where the differences occurred in that one long line!

If you are interested, it turns out that every record was different because the id numbers had auto-incremented... Back to square one (almost - see next post for sorting out the id numbers).