SMF DB Checker

[shawnb61]

Database changes may have been made to your forum over the years.  Sometimes, changes to the SMF DB can cause performance issues, and if you are having issues upgrading, DB changes are the likely culprit.  The upgrader freezes when it encounters DB structures that it wasn't expecting - especially if columns or tables have been removed or significantly modified.

So...  I wrote this utility to compare a forum's database to the vanilla SMF database:
https://github.com/sbulen/sjrbTools/blob/master/smf_db_compare.php

Just copy it to your forum root folder (where Settings.php is) and run it from your browser.

You will probably see adds/changes/deletes.  Make sure they make sense. 

Notes:
- It compares to the latest vanilla release of each major version, with no bells & whistles. (2.1, 2.0, 1.1, 1.0, & YaBB SE 1.5.5)
- For 2.0 & prior it assumes latin1_swedish_ci collation.
- Due to lots of renames and CaPiTaLiZaTiOn changes over the eons, it attempts to do all compares in a case insensitive fashion.
- It will ignore table prefixes on all compares.
- Index comparisons are based on index signature (i.e., the column list), not index names.  So index name changes don't create erroneous warnings...
- It allows you to choose a version to compare to.  We've seen some (rare) cases where smfVersion was wrong, e.g., after multiple failed upgrade attempts.  This utility may help identify those.
- Click on the headers to collapse sections to reduce scrolling.
- MySQL only.
- For working 1.0 forums, you will need to copy a version of Subs_Db_mysql.php to your sources folder.  (It didn't exist in 1.0 & is required...)

Green rows are ADDs:
- These are probably tables, columns and indexes added by mods.  That's OK.
- In some cases, tables are added by enabling features within SMF (e.g., some search tables).  You may see green SMF tables.  That's OK. 

Red rows are DELETEs:
- These need the most scrutiny, especially if you are having problems upgrading...
- Sometimes there is a corresponding Green row if something has been redefined and is now very different.  E.g., extremely large forums sometimes modify their indexes for performance reasons.  This might look like a DELETE and an ADD.

Yellow cells:
- If you set your collations to suit your language, for SMF <= 2.0, these will show up.  That's OK.
- LOTS of indexes have been renamed.  These may show up if your forum has been around a while.  That's OK.
- Lots of variation of integer display lengths over the years (e.g., int(10)).  This utility ignores all display lengths (in conformance with mysql 8+) & will report all as yellow.  OK to ignore.
- The 1.x "Extra" column on timestamp fields may be flagged as yellow.  OK to ignore.
- Sometimes datatypes have been changed.  Note that the UTF8 conversion will change your TEXT columns to MEDIUMTEXT.  Also, forums that encounter VERY LARGE posts have sometimes changed message bodies to be MEDIUMTEXT.  If you see these, hopefully you know why such changes were done to your forum.

Not all changes are bad.  But at the very least you should be aware of them, especially if you are having upgrader issues.

The screenshot below shows some sample output from the index section, which shows adds due to mods and also some index renames.

I hope you find this helpful.

You cannot view this attachment.

[Shades.]

Any chance you can compress that to a zip file? I tried to copy & paste but it crashes my browser lol!

[shawnb61]

In classic git fashion, it takes a few steps to do something simple like download a single file...   

To download:
 - Follow the link to the file
 - Click on the Raw button
 - Right click on it in your browser
 - Choose "Save As" (or whatever your browser calls it...)

From:
https://capiche.com/q/how-do-you-download-files-from-github

[Shades.]

Thanks for educating me!  

[JBlaze]

This is actually quite a great tool! Awesome work!

[shawnb61]

This is actually quite a great tool! Awesome work!

Thanks!

[Shades.]

So you said reds are deletes so not sure what to do or if I need to do anything with this:

You cannot view this attachment.

[Arantor]

smf_member_logins is a standard table in 2.1, with the same structure as that.

[Shades.]

smf_member_logins is a standard table in 2.1, with the same structure as that.

Yeah but on the left side is my 2.1.1 database in red which is blank so do I need to re-add these tables/columns and how (if needed)?

[Arantor]

Well, if you're not using the 'log member login history' option, you don't need it - but the only way to add it in at this point is manually since the 2.1 upgrader should have done so?

[shawnb61]

You clipped off the green immediately underneath. 

What does it look like if you include both the red and the green? 

As noted above, under some circumstances, the tool sometimes displays a significant change as a delete/add.

Do you actually have the table?

[shawnb61]

FYI, I just checked, and if you have the logging disabled (Days to keep login history = 0), it doesn't care if the table exists or not. 

However, if the table is missing & you set that value, bad things happen...

The upgrader does create the table.

If it were me & this were a production forum, I'd create the table.

[Shades.]

You clipped off the green immediately underneath. 

What does it look like if you include both the red and the green? 

As noted above, under some circumstances, the tool sometimes displays a significant change as a delete/add.

Do you actually have the table?

You cannot view this attachment.

And yes I have the _member_logins table in my database so I'm good then?

You cannot view this attachment.

Edit:  Days to keep login history is set to 30.

[shawnb61]

Yep.

[shawnb61]

Thanks, @Shades. for reporting that.  Fix applied.  It shouldn't do that again.

[greddy]

hello,

but this tool only for looking the database, not for edit or delete, right ?

I have maybe 20-30 red lines ... and the 2.1.1 Forum is not running after the upgrade from 2.0.19,
is this possible that the database is the reason ?

read here:
https://www.simplemachines.org/community/index.php?topic=581876.0

greetings

[shawnb61]

Correct. Read only.