error when trying to restore db through bigdump; need fix

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Ideas Centre
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
ember1
Registered User
Posts: 20
Joined: Fri Aug 21, 2009 2:51 am

error when trying to restore db through bigdump; need fix

Post by ember1 »

preface: i am an sql noob:

i am trying to use bigdump to restore a database to a new server. when i run the script, i get the following error:

Error at the line 401593: INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('�caliber', '16780', '0');

Query: INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('�caliber', '16780', '0');

MySQL: Duplicate entry '' for key 1

looking at the code in the backup sql file, it looks like this:

INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('“caliber', '16780', '0');

this error seems to occur due to the open quotes not having a closed quotes. there are thousands of lines like this, but the majority of them (tens of thousands) have close quotes or no quotes at all and the error doesn't occur on those lines. besides manually deleting the offending lines in wordpad, is there a way to fix the backup sql file?

thanks!

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50984
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: error when trying to restore db through bigdump; need fix

Post by stevemaury »

Delete from the backup file all of the INSERT INTO statements for the three search_* tables and then you can rebuild the search index in the ACP later.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

ember1
Registered User
Posts: 20
Joined: Fri Aug 21, 2009 2:51 am

Re: error when trying to restore db through bigdump; need fix

Post by ember1 »

thanks for the follow up. is there an automated way of doing what you have suggested? i have been deleting the lines manually in wordpad, but it's taking forever. i selected lines via shift + pgdn for an hour and i am still not selecting all the lines i want to erase.
thanks.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50984
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: error when trying to restore db through bigdump; need fix

Post by stevemaury »

The easiest way, if you still have access to the database, is to merely exclude those three tables and do another backup.

Otherwise, get Notepad++ (free, Google it) and Search for INSERT INTO phpbb_search_results. Note the line number of the first such line. Then, Search for CREATE TABLE phpbb_search_wordlist and note the line number just above that. Then, click at the end of that line, Go To the first line number and shift + click and delete.

Repeat twice for the other two tables, changing the search terms, of course. Also, make sure to work with a copy.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

ember1
Registered User
Posts: 20
Joined: Fri Aug 21, 2009 2:51 am

Re: error when trying to restore db through bigdump; need fix

Post by ember1 »

Thanks for that tip. That was WAY better than working in wordpad! Just a few minutes of slicing out code. After the gutting I am left with the following. I don't know syntax very well, but dos this look ok:

#
# TABLE: phpbb_search_results
#
DROP TABLE IF EXISTS phpbb_search_results;
CREATE TABLE phpbb_search_results(
search_id int(11) unsigned NOT NULL,
session_id varchar(32) NOT NULL,
search_time int(11) NOT NULL,
search_array text NOT NULL,
PRIMARY KEY (search_id),
KEY session_id (session_id)
);

#
# Table Data for phpbb_search_results
#

I
# TABLE: phpbb_search_wordlist
#
DROP TABLE IF EXISTS phpbb_search_wordlist;
CREATE TABLE phpbb_search_wordlist(
word_text varchar(50) NOT NULL,
word_id mediumint(8) unsigned NOT NULL auto_increment,
word_common tinyint(1) unsigned NOT NULL,
PRIMARY KEY (word_text),
KEY word_id (word_id)
);

#
# Table Data for phpbb_search_wordlist
#


#
# TABLE: phpbb_search_wordmatch
#
DROP TABLE IF EXISTS phpbb_search_wordmatch;
CREATE TABLE phpbb_search_wordmatch(
post_id mediumint(8) unsigned NOT NULL,
word_id mediumint(8) unsigned NOT NULL,
title_match tinyint(1) NOT NULL,
KEY post_id (post_id),
KEY word_id (word_id)
);

#
# Table Data for phpbb_search_wordmatch
#

INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match) VALUES('37', '1573', '0');
#
# TABLE: phpbb_sessions
#

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50984
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: error when trying to restore db through bigdump; need fix

Post by stevemaury »

Delete this:

Code: Select all

INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match) VALUES('37', '1573', '0');
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Locked

Return to “[3.0.x] Support Forum”