Eblah 9.71B conversion to SMF issues

Started by SimplyScripts, July 15, 2008, 07:43:20 PM

Previous topic - Next topic

SimplyScripts

Here is the error I get: Any thoughts?  Suggestions? 

Sincere Thanks in advance.

Don

Converting members... Unsuccessful!
This query:

    ALTER TABLE `simplysc_smf`.simplysc_members
    ORDER BY ID_MEMBER = 0, dateRegistered;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 0, dateRegistered' at line 2
No matter where you go, there you are.

SleePy

Give the attached file a try. I changed the offending code.

Let me know if this does fix it though and things work out. So that way I can update the converter for everyone else :)
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

Sleepy,

The file got me farther down the road. 

I got this error:
++++++++++++

INSERT INTO `simplysc_smf`.simplysc_personal_messages
(msgtime, subject, fromName, body, ID_MEMBER_FROM, deletedBySender, temp_toName)
VALUES ('1207329971', 'Recent Discussion Board Issues', 'admin', 'Hi, <br /><br />Yesterday... [all messages listed out]

Caused the error:

    Got a packet bigger than 'max_allowed_packet' bytes
+++++++++++++++
Think this could be cured by simply deleting those private messsages? Or, increasing the "max_allowed_packet" ?

Thank you,

Don
No matter where you go, there you are.

SleePy

Try increasing the max allowed packets on the server.

This usually happens when you have a big message(s) and t hey are all being attempted to inserted at once or grabbed at once.
MySQL doesn't like the packet size and kicks an error.

For the SMF 2.0 converter file I did attempt to have it override this. I could try and backport the changes and see if it helps out though (would be later on today after I do the other things I need done).
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

Sleepy,

Thanks.  Unfortunately, I'm not smart enough to change the max allowed packet size.  When you have time, if you could modify the converter file to override the max packet size issue, I'd appreciate it.

Don
No matter where you go, there you are.

SimplyScripts

Sleepy,

Is there any voodoo that I can change on the convert file to grab less stuff?  I don't care how long the conversion takes.

Thanks in advance,

Don
No matter where you go, there you are.

SleePy

Yes the converter lets you do voodoo. But only for the sections it is working on.

Is it still on the personal messages?

If so..
Open up the ebla php file.
Find (on about line 501):
            if (count($block) > 100)

then change 100 to a smaller number of your picking.
The converter is smart enough in that section to not need any other numbers changed :)
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

#7
SleePy,

Thanks, that worked.  I had one guy with 1.7 mb of personal messages.  After I removed that file, it worked perfectly.

Thanks much!  You're the greatest.

Don
No matter where you go, there you are.

SimplyScripts

New error

    UPDATE `simplysc_smf`.simplysc_log_topics
    SET ID_TOPIC = 8506
    WHERE tempID = 0;

Caused the error:

    Duplicate entry '8506-971' for key 1

I tried a mysql query

DELETE FROM simplysc_log_topics
                WHERE ID_TOPIC = 8506
                    OR ID_MEMBER = 971

but I just get a new error with a different ID_MEMBER number.

Thoughts?
No matter where you go, there you are.

SleePy

Well there are duplicate entries then for the log topics stuff.

If the mark read data is not important to you, it could simply be emptied. The log_topics just keeps track of what topics a user has read. Which could easily be replaced by clicking mark read on the forum when it is up :D

You could run the following query:
SELECT ID_TOPIC
FROM `simplysc_smf`.simplysc_log_topics
GROUP BY (ID_TOPIC)
HAVING COUNT(ID_MEMBER) > 1;


That should show you all duplicate topics.
You then delete them. I don't think its possible to do a delete with a group or having so it has to be done without it.
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

Getting closer...

    INSERT INTO `simplysc_smf`.simplysc_messages
    (ID_TOPIC, ID_BOARD, subject, posterName, body, posterIP, posterEmail, posterTime, smileysEnabled, modifiedTime, modifiedName, icon, temp_filename)
    VALUES (....)

Caused the error:

    Duplicate entry '0-0' for key 1

What is key 1 and how do I get rid of duplicate entries.

Thanks in advance.
No matter where you go, there you are.

SleePy

Go into phpMyadmin
Click on smf_messages table.
Then the structure tab.

If you scroll to the bottom on the left is a table holding some info about keys.

What keys are present still?
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

These are the keys present in messages

ID_MEMBER
ipIndex
participation
showPosts
ID_TOPIC


Don
No matter where you go, there you are.

SleePy

remove the ID_MEMBER index (Not the field!!!).

Flat file converters are not easy. So when it is importing the data it drops the primary and unique indexes (I should find a way to improve this really). This way it can have duplicates in the table until it fixes them up.
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

#14
Sleepy,

Thanks.  I'm getting the ball a little farther down the field.  I'm putting together a list of the errors I get and what I've done to correct them.  I hope this will be of use to you in refining the conversation file for eblah.

Don
No matter where you go, there you are.

SleePy

Yes it would be useful :) Its hard to debug flat file converters and it doesn't help much when I only have a small test site to even try it with, so I can't get the testing that a large forum has :)

I am currently gearing up for oscon so I haven't had much time to put towards working on fixing any of the converters. Just doing the simple things at the moment.
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

Sleepy,

Thanks. 

The last error I got is below, if you can give me a nudge in the right direction, that would be great.  :


    ALTER TABLE `simplysc_smf`.simplysc_smileys
    ORDER BY LENGTH(code) DESC;

Caused the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LENGTH(code) DESC' at line 2

+++++++++++++++++++++++

Here is the Full List of errors and what actions I took

Error:

This query:

    INSERT INTO `simplysc_smf`.simplysc_log_boards
    (logTime, ID_MEMBER, ID_BOARD)
    VALUES ('0', '952', '38'),

Caused the error:

    Unknown column 'logTime' in 'field list'

ACTION:

Added to simplysc_log_boards new field
logTime, Varcar, 25

ERROR:
    ALTER TABLE `simplysc_smf`.simplysc_log_topics
    ADD tempID int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY;

Caused the error:

    Can't DROP 'PRIMARY'; check that column/key exists

ACTION:

made ID_MEMBER primary

ERROR:

INSERT INTO `simplysc_smf`.simplysc_log_topics
(logTime, ID_MEMBER, tempID)
VALUES ('1187631040', '952', '1152363687'),

Caused the error:

    Unknown column 'logTime' in 'field list'


ACTION:

Added logTime, varchar, 25


Error:

    ALTER TABLE `simplysc_smf`.simplysc_log_topics
    ADD tempID int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY;

Caused the error:

    Can't DROP 'PRIMARY'; check that column/key exists

ACTION

Made tempID Primary

    INSERT INTO `simplysc_smf`.simplysc_log_mark_read
    (logTime, ID_MEMBER, ID_BOARD)
    VALUES ('1216152974', '6', '2'),
    ('1216101934', '6', '27'),
    ('1216076508', '6', '26'),
    ('1215505246', '6', '1'),
    ('1214172052', '6', '6');

Caused the error:

    Unknown column 'logTime' in 'field list'

ACTION:

Added logTime, varchar, 25

ERROR:

    ALTER TABLE `simplysc_smf`.simplysc_boards
    DROP COLUMN tempID;

Caused the error:

    Can't DROP 'tempID'; check that column/key exists

ACTION:
added tempID, varchar, 25

ERROR:

This query:

    UPDATE `simplysc_smf`.simplysc_log_topics
    SET ID_TOPIC = 1
    WHERE tempID = 1060011413;

Caused the error:

    Unknown column 'tempID' in 'where clause'

ACTION:

added tempID, varchar, 25

ERROR:

    ALTER TABLE `simplysc_smf`.simplysc_boards
    DROP COLUMN tempID;

Caused the error:

    Can't DROP 'tempID'; check that column/key exists

ACTION:

added tempID, varchar, 25

ERROR:

    ALTER IGNORE TABLE `simplysc_smf`.simplysc_log_topics
    DROP COLUMN tempID,
    ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER);

Caused the error:

    Multiple primary key defined

ACTION:

dropped primary key

ERROR:

This query:

    INSERT INTO `simplysc_smf`.simplysc_messages
    (ID_TOPIC, ID_BOARD, subject, posterName, body, posterIP,

posterEmail, posterTime, smileysEnabled, modifiedTime, modifiedName,

icon, temp_filename)
    VALUES ('1', '30', 'Batman: Dead End', 'AmericanSyCo', '&quot;The

best Batman film since Tim Burton\'s original&quot; is what Harry

Knowles called this- I couldn\'t agree more. <br /><br />Thank God for

Sandy Collora!  Though it\'s only eight minutes long, &quot;Dead

End&quot; packs a punch.  A punch that will knock you off your feet and

kick you while you\'re down.  I have now seen this movie four times, and

every time it\'s like seeing a comic book come to life!  Every shot

(especially the one where Batman slowly rises from the ground, his cape

rising up and cloaking his entire body) looks like a panel taken right

out of the source material.<br /><br />Thank God for Sandy Collora!  And

the two surprises!  Though it doesn\'t sound too professional, HOLY

CRAP!  Granted, after you encounter the first surprise, you\'ll probably

figure out the second one.  Still, m

Caused the error:

    Duplicate entry '0-0' for key 1

ACTION:

Removed ID_Member from Index

ERROR:

    ALTER TABLE `simplysc_smf`.simplysc_messages
    CHANGE COLUMN ID_MSG ID_MSG int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY,
    DROP INDEX topic,
    DROP INDEX ID_BOARD;

Caused the error:

    Can't DROP 'PRIMARY'; check that column/key exists

Added Primary to ID_MSG

ERROR:

This query:

    ALTER TABLE `simplysc_smf`.simplysc_messages
    CHANGE COLUMN ID_MSG ID_MSG int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY,
    DROP INDEX topic,
    DROP INDEX ID_BOARD;

Caused the error:

    Can't DROP 'topic'; check that column/key exists

ACTION:

Dropped primary key


ERROR:

This query:

    ALTER TABLE `simplysc_smf`.simplysc_messages
    CHANGE COLUMN ID_MSG ID_MSG int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY,
    DROP INDEX topic,
    DROP INDEX ID_BOARD;

Caused the error:

    Can't DROP 'topic'; check that column/key exists

ACTION: added topic as Index

ERROR:

    ALTER TABLE `simplysc_smf`.simplysc_messages
    CHANGE COLUMN ID_MSG ID_MSG int(10) unsigned NOT NULL default 0,
    DROP PRIMARY KEY,
    DROP INDEX topic,
    DROP INDEX ID_BOARD;

Caused the error:

    Can't DROP 'ID_BOARD'; check that column/key exists

ACTION: Made ID_Board index

ERROR:

    ALTER TABLE `simplysc_smf`.simplysc_smileys
    ORDER BY LENGTH(code) DESC;

Caused the error:

    You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'LENGTH(code) DESC' at line 2


ACTION:

Wept copiously


No matter where you go, there you are.

SleePy

Some of those are the same errors and might be related to it having the errors.

As for the last one
Common conversion errors.

The good thing that means the conversion is almost done :D
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

SimplyScripts

Sleepy,

Thanks for the info.  I was able to complete the conversion.  None of the private messages were converted, but other than that the conversion is complete and the board seems to be in good shape.  I'm going to dork around the site a bit, then wipe it out and do a complete conversion from start to finish.   

Along with the private messages, the only thing that would be really neat is to be able to map the old message ids to the new message ids.  That is probably a reach, tho.

Thank you very much for your professional patience and help.  I plan on leaving the old board up for some time, so if you need a test bed to test any modifications to the eblah conversion, please let me know.  I'd be glad to help.

Don
No matter where you go, there you are.

SleePy

The message ids changed? Are they a similar change such as a certain number incremented or what?

Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

Advertisement: