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

Sort by date Sort by votes
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;
 
Upvote 0 Downvote
Brogan said:
You need to click on the options and show full text.
Click to expand...

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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Mike said:
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?
Click to expand...

I tried a previous one and that one was broken too somehow :o Oh no this is not good :x
 
Upvote 0 Downvote
: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?
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Mike said:
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?
Click to expand...

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
 
Upvote 0 Downvote
KevinEssence said:
:eek: that's a lot of data gone oh no :s I tried 3 backups all within 3 days time, strange all got corrupted.
Click to expand...
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.
 
Upvote 0 Downvote
Brogan said:
That would suggest an issue with the restore process.

Again, if using phpMyAdmin, that's not uncommon.
Click to expand...

Tracy Perry said:
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.
Click to expand...

Thank you very much guys! I'm sorry for the confusion. To prevent further issues what's the best recommended way to take backups?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
Brogan said:
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
Click to expand...

Tracy Perry said:
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).
Click to expand...

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!
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

V
XF\Db\InvalidQueryException: MySQL statement prepare error [1176]: Key 'PRIMARY' doesn't exist in table 'topics' in src/XF/Db/AbstractStatement.php at
Replies
0
Views
84
Vintage175
V
Race Sim Central
vB -> xF 'threadid' doesn't exist in table 'post'
Replies
8
Views
1K
Race Sim Central
Race Sim Central
S
  • Solved
XF 2.2 Key 'post_date' doesn't exist in table 'xf_profile_post'
Replies
2
Views
643
SurferJon
S
snoopy5
  • Question Question
XF 1.5 Mysqli prepare error: Table 'xxx.xf_resource' doesn't exist
Replies
4
Views
724
Jeremy P
Jeremy P
Jake B.
Not a bug vBulletin 3.8 "Key 'threadid' doesn't exist in table 'post'"
Replies
4
Views
638
Jake B.
Jake B.
Back
Top Bottom