Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Trash bin 1.1.4 - SQL error 1062 Duplicate entry for key 'PRIMARY'
#1
Thanks a lot for the plugin, it helped me a lot once in a while.

But now a deleted thread cannot be restored.
Mybb reports:
 
SQL Error: 1062 - Duplicate entry '204376' for key 'PRIMARY'
Query: 
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		
Back Trace: #0  errorHandler->email_error(20, SQL Error: 1062 - Duplicate entry '204376' for key 'PRIMARY'
Query: 
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		, , 0) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/class_error.php:195]
#1  errorHandler->error(20, Array ([error_no] => 1062,[error] => Duplicate entry '204376' for key 'PRIMARY',[query] => 
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		)) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/db_mysqli.php:597]
#2  DB_MySQLi->error(
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/db_mysqli.php:337]
#3  DB_MySQLi->query(
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		, 0, 1) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/db_mysqli.php:370]
#4  DB_MySQLi->write_query(
			INSERT
			INTO prefix_posts (`pid`,`tid`,`replyto`,`fid`,`subject`,`icon`,`uid`,`username`,`dateline`,`message`,`ipaddress`,`includesig`,`smilieoff`,`edituid`,`edittime`,`editreason`,`visible`)
			VALUES ('204376','9531','204351','7','RE: Original Topic title','0','0','removed-user','1453713361','Hello, its me, the removed user.  I cause big problems for the admin :D',X'','0','0','0','0','','1')
		) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/db_mysqli.php:830]
#5  DB_MySQLi->insert_query(posts, Array ([pid] => '204376',[tid] => '9531',[replyto] => '204351',[fid] => '7',[subject] => 'RE: Original Topic title',[icon] => '0',[uid] => '0',[username] => 'removed-user',[dateline] => '1453713361',[message] => 'Hello, its me, the removed user.  I cause big problems for the admin :D',[ipaddress] => X'',[includesig] => '0',[smilieoff] => '0',[edituid] => '0',[edittime] => '0',[editreason] => '',[visible] => '1')) called at [/var/www/vhosts/DomainName.de/httpdocs/inc/plugins/trashbin.php:318]
#6  trashbin_restore_thread(9531) called at [/var/www/vhosts/DomainName.de/httpdocs/Secret_Admin_Directory/modules/tools/trashbin.php:239]
#7  require(/var/www/vhosts/DomainName.de/httpdocs/Secret_Admin_Directory/modules/tools/trashbin.php) called at [/var/www/vhosts/DomainName.de/httpdocs/Secret_Admin_Directory/index.php:821]
The Mybb version is up to date and the forum works very well so far.

So there is a double primary key in the database: pid=204376
The post with the corresponding pid comes from a user who has already been deleted, see error message. I can easily restore other topics, but not this one.
What have I done now?

1. in PhpMyAdmin, in the table posts, I searched for the corresponding pid and deleted it.
2. afterwards I tried to restore the topic again. However, the same error message appears, with the difference that it is a different pid and accordingly a different post, but the user is the same deleted user.
3. so i also deleted this post in PhpMyAdmin.
4. after that i tried again to restore the topic with the result that exactly the same error message (with same pid and same user) as described in the error message above appears, although i deleted this column in PhpMyAdmin.

I can't get any further here, does anyone have an idea?

One more hint, maybe it has something to do with it:

The topic has relatively many posts (1477) and it's a bit older, but that shouldn't be relevant, I think. More relevant might be that it was renamed in the meantime. The error message shows the original title, while the Trash bin plugin (Admin-CP) shows the new title.

Have a nice day Smile
Reply
#2
Try and replace in "/inc/plugins/trashbin.php" (line #318)

$db->insert_query("posts", $post);

with:
$db->replace_query("posts", $post);

EDIT:
I tried the unmodified plugin now on a local installation with different kinds of deletion mody and it works very well in every cases.
Reply
#3
The thing with the curly bracket is, of course, nonsense, I wasn't thinking properly. If you don't have to deal with it every day anymore, troubleshooting can sometimes be tedious. Smile
The error message says that it is a syntax error and where to find it. So I looked in the database.  The fields ipaddress seem to contain broken, corrupt values in the column posts as well as in the column trashbin_posts. Sometimes something like ]?# or like P?ڨ , sometimes HEX values like this 0x5c4c0274 and sometimes the field is empty. This is probably because I installed or activated the plugin Extended Useradmininfo during the thread, no idea.
On my test server, where there is a 1 to 1 copy of the system, only with an older database, there is an 8-digit HEX value, like ab01cd23, which looks much tidier.
Now I could actually do these fields:
1. convert?
or
2. Empty/delete?
3. maybe repair the whole column with the Sql command repair?

Am I on the right track?

EDIT: I lost my previous post, what happened here? I only replied to my previous post. It was quoted and I thought it was unnecessary and deleted it. But now the post is gone. Huh

I probably accidentally came to Edit....in my forum the editing of the posts is limited to half an hour, so I didn't expect it....Shame on me Undecided
Reply
#4
I solved it at the end with the following Sql query

UPDATE `prefix_trashbin_posts` SET `ipaddress` = NULL


After that I have applied the replace statement again and everything was in the right place Smile

Thanks again to SvePu!
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)