phpbb_db_backup

[stevemaury]

878 GB? 878 KB? Is that decimal point a thousands separator? kb = kiloBIT

[Popp Singh]

858 mbs .

[stevemaury]

Try this editor - http://www.gena01.com/win32pad/ - but filesize is limited to available memory.

[D¡cky]

The phpbb_db_backup.sql is 878.779 kbs.

The cmd screen says ----->

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Dokumente und Einstellungen\Administrator>C:\xampp\mysql\bin\mysql.exe -u roo
t phpbb2 < "C:\xampp\htdocs\phpbb2\dump\phpbb_db_backup.sql"
ERROR 1062 (23000) at line 1523525: Duplicate entry '├▒' for key 'PRIMARY'

C:\Dokumente und Einstellungen\Administrator>

That error may be due to improper encoding. Try entering this on your command line first:

Code: Select all

C:\xampp\mysql\bin\mysql.exe -u root --default-character-set=utf8 phpbb2 < "C:\xampp\htdocs\phpbb2\dump\phpbb_db_backup.sql"

If that should fail for some reason, use EmEditor to open your backup file. EmEditor is not free, but it has a 30 day trial period and will open your file without you having to worry about how much memory your computer has.
Then go to the line that is in your error message. If that line happens to be in a search table, I suggest you remove that table from the file. If you do manage to fix the line with the error, more likely than not the same error will occur further down the file.

[Popp Singh]

I ran the comand line that D¡cky sugested and got this ---->

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Dokumente und Einstellungen\Administrator>C:\xampp\mysql\bin\mysql.exe -u roo
t --default-character-set=utf8 phpbb2 < "C:\xampp\htdocs\phpbb2\dump\phpbb_db_ba
ckup.sql"
ERROR 1062 (23000) at line 1468975: Duplicate entry 'olß' for key 'PRIMARY'

C:\Dokumente und Einstellungen\Administrator>


I had spent hours trying with notepad++ and it took over a minute to open the file and then i had to search for that line = it took hours but i found it and its in the word search list . . Now i`ve put emeditor in and get a sreen that says "Some characters cannot be encode useing the specifyed encoding" Then it gives me 4 choices to opening it = as "ASCII View" or "Hexadecimal View" ( Wich has been chosen by the program by putting a dot in the choice circle ) . Then "Select encodeing from List" and then "continue opening as current encoding" . Then there is a warning "The file will be corupted if you continue editing and save the file as the current encoding . The cursor will be jumped to the first invalid character" . I tried looking at it with the hexadecimal choice that it had chosen and looking at it freaked me out .

So i dont know what to do next . I also dont know where the word search list starts and ends = Exactly where to start deleteing and where to stop . Also i`ve looked for the lines and there isnt , or at least i cant find , the list of wich line i`m on like there is in notepad++ .

It seems to me that the problem comes because we must have set the site up falsly and the problem comes because there is a mixture of english , greek , hebrew and latin words / characters .

Would it be possible to use a command to delete the whole words list as that seems to me to be the easyest solution if its possible ?

My brain hurts .

[stevemaury]

Well, you really don't need any of the three search_* tables in order to do a conversion. If you still have access to the database, you can make another backup nd exclude those three tables.

If not, open the backup file in the editor Dicky mentioned and find this line:

Code: Select all

CREATE TABLE phpbb_search_results (

Delete that line and everything after it down to, but not including, this line:

Code: Select all

CREATE TABLE phpbb_sessions (

Work with a copy of the file.

[Popp Singh]

Ummmmm------------------------------ Errrrrrrrrr-----------------------------guys i`ve managed to mark and delete all of search wordlist and now i`m in search wordmatch .......... this goes on for about at a guess 70 % of the whole data base and my finger is blue and has blisters on it from holding the left mouse button down and letting the screen scrole down . If i have to keep on like this its going to take a day.....at least.... am i missing a delete from to option or have i got to sit here for the next day... ...or three..... ?????

[D¡cky]

Dragging the mouse is the tough way to do it.

Place your cursor at the beginning of the section.
Scroll down, or Page Down until you see the end of the section.
Hold down the Shift key
Click the left mouse button. This should highlight the whole section
Press Delete.

[Oleg]

How about using --force instead?

C:\xampp\mysql\bin\mysql.exe -u root --force phpbb2 < "C:\xampp\htdocs\phpbb2\dump\phpbb_db_backup.sql"

[Dave Bean]

I have the same issue here. Not with a phpbb export but with a similar phpmyadmin dump (not compressed). I've tried bigdump to import backup file, but getting the error and am now trying the command line approach which is currently running.

I have a 2 M + record database on one linux server, use phpmyadmin to dump for backups to my windows xp PC as 195 MB .sql file. I have xampp installed on the pc and I'm trying to load the data into the pc database to experiment with some trimming. Then hopefully, I'll upload to a different server.

... checking .... still running from command line request, but progressing... 1.3 M records of the 2 M + so far loaded. ooops exceeded 300 seconds execution time... will try changing a setting and report back.

[D¡cky]

Dave,

The problem is most likely caused by utf8 characters and your sql file being saved in ASCII. You should look at your sql file to be sure of the exact problem. Look at the line where the error occurs and see if it contains a utf8 character, then look at the previous lines and see if one contains the same character.

I just had a similar situation where the wordlist contained £100 and £500. The £ character was corrupt because the file was not saved in the proper format. The first time the import program comes across a corrupt utf8 character, a blank entry is made in the database. This is because of the way MySQL works. When the import program comes across the second corrupt character, a blank entry is attempted to be inserted into the database again and this is where the duplicate entry error occurs because entries have to be unique and two blank entries would be the same.

The solution is to save the file in utf8 w/o BOM and set your import program to use utf8. With BigDump, set $db_connection_charset = utf8. With the command line, use --default-character-set=utf8.