persistent sql error when converting yabb 2.1

[pqx]

Hi,


For the past month I have been trying to convert my YaBB 2.1 forum into SMF 1.1.4, using JayBachatero's latest converter, and have met with little success.

This is what I have tried so far:

-The first thing I tried was using a Windows XP virtual machine (under VMware) and installed apache, php, and mysql on it, then loaded a blank smf forum and my yabb forum's files onto it, then ran the converter. In all attempts, even with doing something differently every time, the converter would jump directly to a long page containing about 70 different errors, and at the bottom of the page it would say that the conversion is complete; Additionally, the blank smf forum's database would become so corrupted that even the mysql command line could not recognize it. Plus the blank smf forum would no longer load & would complain about the database being crashed or something. This happened on every single attempt.

-Thinking the problem might be Windows, I tried a (Linux) Slax Server Edition Virtual machine. This time, the converter went through all stages correctly (after a few tries), except that at the 2nd stage of converting topics it would run forever and never stop. I gave up after 9 hours, my forum's not *THAT* huge (15,000 topics, 1800+ members). And when checking the blank smf forum, it would contain users, PMs, categories... but no topics. During the 9 hours it was running, the CPU was being consumed as if there was lots of work being done.

-In a last attempt, I decided to try this directly on my host's server. I got everything set up, and this time, it also converted the users, the PMs, and the categories, but no topics again. AND, this time the converter DID return an error (seen below). I tried to run the conversion again several times, always reinstalling the blank smf forum on every attempt, but on all attempts I ran into this same catastrophic error. Apparently it is trying to set a key on a table using columns that are filled with zeros in every one of their fields (as I saw in phpmyadmin).

Code Select Expand

Converting topics (part 2)... Unsuccessful!
This query:

    ALTER TABLE `twoyrnet_smf`.smf_log_topics
    ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
    DROP COLUMN tempID;

Caused the error:

    Duplicate entry '0-0' for key 1



http://img.2yr.net/smfconv.png [nofollow]

I have logs of most of the other errors if you need them.

Thanks for any and all help, I really want to move into SMF, it is a million times better than YaBB!


*********************************  UPDATE  ******************************

I found a workaround for the key problem. I merely replaced the zeros in one column with numbers 1-12, beginning with the second record (leaving the first field set to zero).

I used this:

Code Select Expand


UPDATE smf_log_topics set ID_TOPIC=1 where tempID=1181700743;
UPDATE smf_log_topics set ID_TOPIC=2 where tempID=1181705854;
UPDATE smf_log_topics set ID_TOPIC=3 where tempID=1181701735;
UPDATE smf_log_topics set ID_TOPIC=4 where tempID=1181700637;
UPDATE smf_log_topics set ID_TOPIC=5 where tempID=1181970917;
UPDATE smf_log_topics set ID_TOPIC=6 where tempID=1181970963;
UPDATE smf_log_topics set ID_TOPIC=7 where tempID=1181970993;
UPDATE smf_log_topics set ID_TOPIC=8 where tempID=1181971033;
UPDATE smf_log_topics set ID_TOPIC=9 where tempID=1181971177;
UPDATE smf_log_topics set ID_TOPIC=10 where tempID=1181971452;
UPDATE smf_log_topics set ID_TOPIC=11 where tempID=1181974327;
UPDATE smf_log_topics set ID_TOPIC=12 where tempID=1181976558;

After this, I ran the converter again, and it picked up right away, and continued to run for about 7 hours (overnight), spending practically all of it converting the posts at stage 2. (is that too long for a yabb 2.1 forum with 240,000 posts?) Then, it actually finished, showing the following:

Converting...

Notice: Undefined index: convert_script in /home/public_html/smftest/convert.php on line 578

Warning: Invalid argument supplied for foreach() in /home/public_html/smftest/convert.php on line 581

Notice: Undefined index: convert_script in /home/public_html/smftest/convert.php on line 586
Successful.
Recalculating forum statistics...
Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 933

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 943

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 951

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 961

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 974

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 987

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1033

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1035

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1041

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1047

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1058

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1059

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1067

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1069

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1075

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1076

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1082

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1083

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1107

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1109

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1115

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1127

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1152

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1154

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1190

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1191

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1229

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1234

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1295

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1297

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1333

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1352

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1825

Notice: Undefined variable: result in /home/public_html/smftest/convert.php on line 1826

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1375

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/public_html/smftest/convert.php on line 1376
Successful.



Conversion Complete

Congratulations, the conversion has completed successfully. If you have or had any problems with this converter, or need help using SMF, please feel free to look to us for support.
Please check this box to delete the converter right now for security reasons. (doesn't work on all servers.)

Now that everything is converted over, your SMF installation should have all the posts, boards, and members from the
Notice: Undefined index: name in /home/public_html/smftest/convert.php on line 1452
installation.

We hope you had a smooth transition!

Then, I went to the forum. The topics were not showing on the index, nor inside the subsections themselves. I went to the forum maintenance section and ran "Find and repair any errors." It returned this error:

Code Select Expand

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
File: /home/public_html/smftest/Sources/RepairBoards.php
Line: 860


Then, I went into phpMyAdmin and ran SET SQL_BIG_SELECTS=1, and set SQL_MAX_JOIN_SIZE to 10GB (10000000000). Then I tried running "Find and repair any errors." again, but I got the exact same error quickly. I also tried with smaller increments, first to 200MB, then 300MB, 400MB, 500MB, 1GB, and finally 10GB, running the repair script each time, but it would still return the same error quickly, and having no effect on the forum.

Then, I tried running "Recount all forum totals and statistics." This completed successfully, and after that, the posts began showing on the index; but still not inside the subsections themselves (no threads either, just empty space). The size of the database is about the same size as the YaBB forum's data, indicating that the data is all there, just not being read out.

I also noticed about 8 corrupted records in the smf_messages table, which I deleted. But still nothing improved.

What should I do? Please, I need some help from you guys, the pros of SMF... I have been trying hard not to bug you, but it's got to the point where I can't fix this anymore on my own.


*********************************  UPDATE 2  ******************************

Well, first of all I hope updating this post isn't going to bump this topic... But I felt this info could really help someone out there.

I found a workaround for another conversion error:

Code Select Expand

Converting attachments (if the mod is installed)... Unsuccessful!
This query:

    SELECT ID_MSG, temp_filename
    FROM `twoyrnet_smf`.smf_messages
    WHERE temp_filename != ''
    LIMIT 0, 100;

Caused the error:

    Unknown column 'temp_filename' in 'field list'


I solved it by running the following query in PhpMyAdmin:

Code Select Expand

alter table smf_messages ADD COLUMN temp_filename tinytext NOT NULL default ''

In my case, the error soon reappeared, but I simply used the command above one more time and the error went away for good.

I figured that if the problem was that a column was missing, why not just create it??

I hope that helps someone out there who isn't very familiar with SQL.

[JayBachatero]

You shouldn't get duplicate key errors nor the drop table error.  I wrote a few functions to handle that for YaBB.  Which files are you using?

[pqx]

I am using:

convert.php:           61.1 KB (62,668 bytes)
yabb21_to_smf.php: 57.2 KB (58,619 bytes)

I also tried the converter on the main site (61.3 KB, 58.1 KB) but they gave errors almost immediately after starting the conversion.

My only remaining problem appears to be that none of the threads/posts show up inside the forum sections. Some (not all) of the section's page numbers do appear (example: "Pages: [1] 2 3"). I keep trying to run "Find and repair any errors" but I keep getting that same error posted above ("The SELECT would examine more than MAX_JOIN_SIZE rows..." etc.)


Thanks in advance for any help 

[JayBachatero]

Do you have mods installed in YaBB?

[pqx]

No mods installed, however over time I have had to update some of the YaBB sources, because they had many bugs that the YaBB developers refused to fix with an update package (They told people to go to their CVS and pick the individual source files and try each version of each one, and with no guarantees; even this did not fix most of the problems). Which is why I'm so disappointed with YaBB. So, here's a detailed version summary:

Code Select Expand


YaBB File Users Version

YaBB.pl YaBB 2.1 Build 1.5
AdminIndex.pl YaBB 2.1 Build 1.10
English Language Pack YaBB 2.1
Admin.pl YaBB 2.1 Build 1.6
AdminEdit.pl YaBB 2.1 Build 1.13
Attachments.pl YaBB 2.1 Build 1.1
BoardIndex.pl YaBB 2.1 Build 1.4
DateTime.pl YaBB 2.1 Build 1.8
Decoder.pl YaBB 2.1 Build 1.4
Display.pl YaBB 2.1 Build 1.5
DoSmilies.pl YaBB 2.1 Build 1.2
EditHelpCentre.pl YaBB 2.1 Build 1.1
Favorites.pl YaBB 2.1 Build 1.1
Guardian.pl YaBB 2.1 Build 1.3
GuardianAdmin.pl YaBB 2.1 Build 1.7
HelpCentre.pl YaBB 2.1 Build 1.5
ICQPager.pl YaBB 2.1 Build 1.1
InstantMessage.pl YaBB 2.1 Build 1.6
Load.pl YaBB 2.1 Build 1.3
LogInOut.pl YaBB 2.1 Build 1.4
Maintenance.pl YaBB 2.1 Build 1.1
MailMembers.pl YaBB 2.1 Build 1.4
ManageBoards.pl YaBB 2.1 Build 1.6
ManageCats.pl YaBB 2.1 Build 1.3
ManageTemplates.pl YaBB 2.1 Build 1.3
MemberGroups.pl YaBB 2.1 Build 1.1
Memberlist.pl YaBB 2.1 Build 1.2
MessageIndex.pl YaBB 2.1 Build 1.7
ModifyMessage.pl YaBB 2.1 Build 1.10
ModList.pl YaBB 2.1 Build 1.1
AdvSettings.pl YaBB 2.1 Build 1.1
MoveTopic.pl YaBB 2.1 Build 1.3
Notify.pl YaBB 2.1 Build 1.2
Poll.pl YaBB 2.1 Build 1.7
Post.pl YaBB 2.1 Build 1.18
Printpage.pl YaBB 2.1 Build 1.2
Profile.pl YaBB 2.1 Build 1.15
Recent.pl YaBB 2.1 Build 1.1
Register.pl YaBB 2.1 Build 1.8
RegistrationLog.pl YaBB 2.1 Build 1.2
RemoveOldTopics.pl YaBB 2.1 Build 1.1
RemoveTopic.pl YaBB 2.1 Build 1.1
Search.pl YaBB 2.1 Build 1.2
SendTopic.pl YaBB 2.1 Build 1.1
Security.pl YaBB 2.1 Build 1.3
SetStatus.pl YaBB 2.1 Build 1.1
Smilies.pl YaBB 2.1 Build 1.1
SubList.pl YaBB 2.1 Build 1.1
AdminSublist.pl YaBB 2.1 Build 1.6
Sessions.pl YaBB 2.1 Build 1.1
SplitSplice.pl YaBB 2.1 Build 1.2
Subs.pl YaBB 2.1 Build 1.15
ViewMembers.pl YaBB 2.1 Build 1.3
YaBBC.pl YaBB 2.1 Build 1.22


[JayBachatero]

Can you give me FTP access and I'll try to take a look at this and see what is going on.

[pqx]

You mean the YaBB forum? Sure, I can give you FTP access, however I must tell you that the messages folder contains more items than my server is willing to send at a time (the list of items gets "truncated"). Not sure if you plan to look at that though. Let me know and I'll set it up immediately  Or if it's easier, I can provide you with any files you might need to look at.

[SleePy]

pqx,

I am sure Jay wants to look at the data quickly and maybe perform some conversion tests. So include that information in the PM.

[pqx]

Alright, all info sent via PM to JayBachatero. 

Just one question, is the message supposed to get saved to my "sent items" section? I checked and don't see anything there...

[SleePy]

It should.
Are you sure it was checked?

It may be possible its a 2.0 bug as well. If you are sure you checked it I can try to verify if its a bug or not.

[pqx]

Looking back at the SMF PM form layout, no, I did not check the box. (what I meant by "checked" was that I "took a peek" at the sent items folder, and saw nothing there). I was just confused because my current forum saves sent messages to the sentbox automatically, and I had not seen that box while I sent the PM. So, no worries. I think your system is better though, it saves storage space 

[SleePy]

Well you can check it for each Personal Message you send or have it checked by default via the below options:

SMF 1.0 and 1.1:
Profile -> Personal Messages Options
Then checking "Save a copy of each Personal Message in my outbox by default."

SMF 2.0:
My Messages -> Settings
Then checking "Save a copy of each Personal Message in my outbox by default."

[JayBachatero]

Also, the way that SMF does it, it doesn't waste space.  It just uses the same pm with a special flag.  So if you store in outbox, you and the member you sent it to are looking at the exact same PM.  It doesn't get duplicated.  If you delete the PM stays there and then if the receiver deletes it gets removed and vise versa.    If you want to restore the sent PMs you can use a query to set the flag for store in outbox.

[pqx]

I see... that's brilliant, especially when compared to YaBB! I'm glad I'm moving into SMF; you certainly know your system design books 

So, how's it going? Any news about this conversion problem?

[JayBachatero]

Meh.  I though I had taken care of this conversion.  I did a few conversions and someone over looked this one.  I'll try to take a look at it tonight or tomorrow.