XF 2.2 Issues after database restore

[nathanw]

After restoring the XF database, I'm having a two issues I haven't been able to solve:

1. Server error

   XF\Db\InvalidQueryException: Template public:approval_item_user error: MySQL statement prepare error [1176]: Key 'user_id_log_date' doesn't exist in table 'xf_ip' src/XF/Db/AbstractStatement.php:230

   In table xf_ip, the column user_id_log_date exists, but it is set as INT(11) with a default value of null. Can someone please confirm how this column should be configured?
2. The approval queue shows how many approvals are outstanding, but when selected, the html structure loads, but nothing is populated. The xf_approval_queue table shows the correct amount of rows (as shown on the user interface) and has three columns: content_type, content_id, and content_date. Are these correct? Is there any other reason why this data wouldn't show?
   Side note: the Reports section works as expected.
   

Is there any script that I can run to check the integrity of the database (e.g. check that all tables and columns exist and are configured correctly)? Or is this information published anywhere?

 

[nathanw]

Both issues were fixed by correctly updating the xf_ip table user_id_log_date index to include keys for uiser_id, content_id, ip, and log_date.

I'm still interested in a script or list of the default XF database structure so I can double check the installation though.

 

[Paul B]

You can see the schema in upload\src\XF\Install\Data\MySql.php .

 

[nathanw]

Thanks! That's a lot to work through, but I'll get there.