Server Error Mysqli prepare error: Key 'last_post_date' doesn't exist in table 'thread

Brogan

XenForo moderator
Staff member
#2
Typically this indicates a corrupt backup (or restore), which is typical when using phpMyAdmin.

Can you run the following command and post the full output:
Code:
SHOW CREATE TABLE xf_thread;
 
#5
You need to click on the options and show full text.
Ah there we go, here you are :D https://gyazo.com/ee7559becda86132057e2375b51b18b3

Code:
CREATE TABLE `xf_thread` (
 `thread_id` int(10) unsigned NOT NULL,
 `node_id` int(10) unsigned NOT NULL,
 `title` varchar(150) NOT NULL,
 `reply_count` int(10) unsigned NOT NULL DEFAULT '0',
 `view_count` int(10) unsigned NOT NULL DEFAULT '0',
 `user_id` int(10) unsigned NOT NULL,
 `username` varchar(50) NOT NULL,
 `post_date` int(10) unsigned NOT NULL,
 `sticky` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `discussion_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
 `discussion_open` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `discussion_type` varchar(25) NOT NULL DEFAULT '',
 `first_post_id` int(10) unsigned NOT NULL,
 `first_post_likes` int(10) unsigned NOT NULL DEFAULT '0',
 `last_post_date` int(10) unsigned NOT NULL,
 `last_post_id` int(10) unsigned NOT NULL,
 `last_post_user_id` int(10) unsigned NOT NULL,
 `last_post_username` varchar(50) NOT NULL,
 `prefix_id` int(10) unsigned NOT NULL DEFAULT '0',
 `tags` mediumblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

Mike

XenForo developer
Staff member
#6
Unfortunately, your backup would appear to be corrupt or incomplete. It's missing all of the indexes on that table, including the primary key. Determining the differences across the database may be very difficult, particularly if you have add-ons installed.

You don't have any other backup?
 
#7
Unfortunately, your backup would appear to be corrupt or incomplete. It's missing all of the indexes on that table, including the primary key. Determining the differences across the database may be very difficult, particularly if you have add-ons installed.

You don't have any other backup?
I tried a previous one and that one was broken too somehow :eek: Oh no this is not good :x
 
#11
:eek: That's a lot of data gone oh no :s I tried 3 backups all within 3 days time, strange all got corrupted.

I tried those SQL backups but I have /var/www/ backed up too but doubt that will fix anything?
 

Mike

XenForo developer
Staff member
#12
How many add-ons do you have installed? What sort of add-ons are they?

Can you show the first CREATE TABLE line in your backup's SQL?
 
#13
How many add-ons do you have installed? What sort of add-ons are they?

Can you show the first CREATE TABLE line in your backup's SQL?
Hey Mike, we have a total of 6 add-ons installed
  • Post Rating - ratings
  • Taiga Chat - shoutbox
  • HeroDev - minecraft server status
  • Widget Framework - sidebar widgets
  • Xenporta - home page etc
  • All rich usernames
 

Tracy Perry

Well-known member
#16
:eek: that's a lot of data gone oh no :s I tried 3 backups all within 3 days time, strange all got corrupted.
One of the big weaknesses in using a PhpMyAdmin export as a backup (which is what I assume you did). Invariably if the DB is large the export times out and you end up with a "shortened" export that does not contain all the data.
The same can happen on an import of a large DB.
 
#17
That would suggest an issue with the restore process.

Again, if using phpMyAdmin, that's not uncommon.
One of the big weaknesses in using a PhpMyAdmin export as a backup (which is what I assume you did). Invariably if the DB is large the export times out and you end up with a "shortened" export that does not contain all the data.
The same can happen on an import of a large DB.
Thank you very much guys! I'm sorry for the confusion. To prevent further issues what's the best recommended way to take backups?
 

Brogan

XenForo moderator
Staff member
#18
I use SSH.
You will invariably require root access for that.

Dump:
Rich (BB code):
mysqldump -udatabase_user -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql

Zip:
Rich (BB code):
tar -czf database_name_$(date +%d.%m.%y).tar.gz /path/to/backup/database_name_$(date +%d.%m.%y).sql

Restore:
Rich (BB code):
mysql -udatabase_user -p database_name < backup.sql
 

Tracy Perry

Well-known member
#19
Depends on if you are on a VPS/dedi or a shared host. If VPS/dedi then using mysqldump is pretty much standard and works well to export the DB via a CRON script.
If you are on a shared hosting and they allow the PHP exec() function then this may work: https://xenforo.com/community/resources/solidmean-forumbackup.3605/
Otherwise, you can try increasing the time-out limit in your php.ini (your shared hosting provider should be able to provide you instructions on how).
 
#20
I use SSH.
You will invariably require root access for that.

Dump:
Rich (BB code):
mysqldump -udatabase_user -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql

Zip:
Rich (BB code):
tar -czf database_name_$(date +%d.%m.%y).tar.gz /path/to/backup/database_name_$(date +%d.%m.%y).sql

Restore:
Rich (BB code):
mysql -udatabase_user -p database_name < backup.sql
Depends on if you are on a VPS/dedi or a shared host. If VPS/dedi then using mysqldump is pretty much standard and works well to export the DB via a CRON script.
If you are on a shared hosting and they allow the PHP exec() function then this may work: https://xenforo.com/community/resources/solidmean-forumbackup.3605/
Otherwise, you can try increasing the time-out limit in your php.ini (your shared hosting provider should be able to provide you instructions on how).
Thank you very much again guys I really appreciate the support and swift assistance! Will definitely work on our backup method. Have a nice day and Happy New Year!
 
Top