SMF Version: SMF 1.1 RC3
I have converted a snitz/MS Access based forum to Snitz/MySql using the procedure at http://forum.snitz.com/forum/topic.asp?TOPIC_ID=42438 (other people have asked for the procedure so there it is).
When I then tried to convert it to SMF with the snitz->SMF converter I get the following errors -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2FImage2.jpg&hash=376cbd18d3320fac5031aa92602eaad07ef2d46b)
The 'Parse Error' goes on ~100 times (yes I counted them), then you get -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2FImage4.jpg&hash=2b36af4b6dc0b43ea77dc457b70bb68b41181d2c)
This is followed by all the data from our posts then this at the end-
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2FImage6.jpg&hash=92317496011f9956bd990aa3d6e59c70935d4ee6)
Help, I'm not really a coder so I'm stuck.
Thanks in advance.
Try this one. A couple of lines were missing a " at the end of them.
Thanks for that, Michael. It now produces this -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fa1.jpg&hash=c9ba08684340ab2bf315c4a1642613134b2d1797)
There are many pages of the 'Parse error' before the end -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fa2.jpg&hash=88fadfafa500c23950e8a14447152ce60e577da6)
If we then go to the forum, you get this -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fa3.jpg&hash=df65098cdf541e480d7cb19e4a0e710505439314)
Notice that there is no 'Started by' entry and all the dates are 31 December 1969 16:00:00
When you go into a post, there is no Author, no body and all dates are as above.
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fa4.jpg&hash=9eb24960c3200dc308736b0a9b171078608757ff)
I was looking around the snitz database yesterday and noticed that all the data is there, ie, in the fields you can see authors, times, replys and body, etc.
Any further help is much appreciated.
Hi, Telf,
I'm at about the same stage of the Snitz conversion process as you. However, using the Snitz converter on the download page, I'm not encountering any parse errors.
The first problem I encountered is that an empty birthdate in Snitz is not a valid value for insertion into a date column in SMF. That problem was easily resolved by updating the Snitz Members table with dummy birthdates of '00010101'.
The real problems began with the Messages table. I can see that the conversion code consolidates posts from the separate Topics and Replies tables of Snitz for eventual insertion into the single Messages table of SMF. However, as you can see in your screenshot above, the converter then attempts to insert the consolidated data into columns of the Messages table that do not exist. The first such nonexistent column encountered is TPosterTime, which raises the error you see displayed in your screenshot above.
I'm looking forward to any guidance we can get from others who have been through this process already.
By the way, as someone new to SMF, I am simply amazed at the level of support offered on these forums, and by Oldiesmann's support in particular. :)
Ken
Try this one.
I'm still getting...
QuoteUnknown column 'TposterTime' in 'field list'
Ken
What's the exact error that you're getting?
Hi again Michael.
I tried your new script and got the same error as before except its on line 18 -
Parse error: syntax error, unexpected ';' in /home/asra03/public_html/smf/convert.php(814) : eval()'d code on line 18
The forum still shows exactly the same problems as yesterday.
Hi Ken, yes this guy's level of support is amazing as seen by his helping of others. I don't know how to say thanks to people like this but they are definitely appreciated.
Thanks again in advance from me 'down under'.
Here's the error (with sensitive data snipped):
QuoteConverting members... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting posts (this may take some time)... Unsuccessful!
This query:
INSERT INTO `smf`.smf_messages
(ID_TOPIC, ID_BOARD, subject, TposterTime, TID_MEMBER, TposterIP, TposterName, Tbody, TposterEmail, TmodifiedName, TmodifiedTime, RposterTime, RID_MEMBER, RposterIP, RposterName, Rbody, RposterEmail, RmodifiedName, RmodifiedTime)
VALUES ('74', '20', 'Aspiration', '0', '14', ... , '', '');
Caused the error:
Unknown column 'TposterTime' in 'field list'
Michael, Ken,
I used to get that Tposter error before I asked for support.
I played around with the smf database a bit and it went away.
Should I totally clear my database and start again just in case I did something before that has fixed the error, ie, if I start from a clean database and start getting the error again, then there is definitely still a Tposter error with the script?
Found a missing ) in my code. Try this one.
Thanks Michael.
I now get -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fb1.jpg&hash=86f15793f92baf15f71943fd86dc126474c00e37)
These errors on lines 5, 9, 14 and 18 repeat for quite a while then this happens -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fb2.jpg&hash=5d7392f75bd2737a88b89f68297c672d1205ab11)
A heap of forum posting data then appears then -
(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fwww.asra.org.au%2Fimagetmp%2Fb3.jpg&hash=f57a29ab6a6282243bbac4cb28d8b3747a6e0457)
BTW-
Server (hosted) is linux 2.6.9-42.0.2.ELsmp
PHP is 4.4.4
Mysql is 4.1.21-standard
Apache is 1.3.37
Perl is 5.8.7
Thanks again,
telf.
Try this one.
Mate, you are fantastic.
It is almost all in order!!!!
The only thing that I can't find are attachments and images.
I have found out that the old snitz image directory (called 'uploaded') wasn't uploaded to the webserver.
Is it as simple as just uploading all the images and attachments to a directory and SMF will link to them or is it harder than that?
If so, where should it be uploaded to?
Again, many thanks for your work so far.
telf
Telf,
I made a couple of modifications to Michael's latest .sql file and I've gotten past all the error messages. I have about 60,000 messages to convert, so it will be a while before the conversion is complete. I'll report back about the outcome when it is.
In the meantime, you might want to try the files I'm using at the moment. I'm using the convert.php file for Snitz from the download page. I'm using Michael's latest snitz_to_smf.sql file with the following modifications:
At line 95, change
$row['TmodifiedName'] = 'NULL';
to
$row['TmodifiedName'] = "''";
At line 108, change
$row['RmodifiedName'] = 'NULL';
to
$row['RmodifiedName'] = "''";
Good luck!
Ken
Although the conversion completed without reporting any error, the resulting database is quite a mess. Among the many problems: At most, only one post shows up per topic, even though reply counts are displayed correctly. Post counts and topic counts are shown as being the same, no matter how may replies a topic has received. Some topics don't display any posts at all. All dates are displayed as "December 31, 1969, 04:00:00 pm." No user is able to log in.
I haven't had time to investigate what may have happened. However, I promised a report, so here it is. At the moment, I am very discouraged.
Ken
I had that at one stage but the last file Michael posted worked for me apart from attachments.
All my posts are there and in order.
Haven't heard from Michael for a day or so but hey, he can have a day or 3 off.
His help has been awesome so far.
Hope he gets you sorted to at least where I'm at.
Let me know if you ever get your attachments working.
telf
I figured out why the memberName column ended up empty for each row of smf_members. It's because Snitz allows M_USERNAME to be NULL. So change line 34 of snitz_to_smf.sql from
SUBSTRING(IF(M_USERNAME = '', M_NAME, M_USERNAME), 1, 80) AS memberName,
to
SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,
Also, in order to get SHA256 encryption to work on my Windows installation of PHP, I found that I needed to add a new environment value, PHPRC, and set its value to the root PHP directory. Otherwise, PHP is unable to locate libmhash.dll.
Finally, the failure of dates to transfer correctly was due to a problem with the ODBC import of data into MySQL from MS SQL Server using the native SQL Server provider:
In some cases, text and varchar data in SMF end up with a null character ('\0') replacing the final character in the original data. I've never seen it happen with PHP 4.x, but it does happen with PHP 5.x. A workaround is to import first into an Access database and then export from Access into your MySQL database.
Ken
Quote from: kgrimsby on November 12, 2006, 11:13:10 PM
I figured out why the memberName column ended up empty for each row of smf_members. It's because Snitz allows M_USERNAME to be NULL. So change line 34 of snitz_to_smf.sql from
SUBSTRING(IF(M_USERNAME = '', M_NAME, M_USERNAME), 1, 80) AS memberName,
to
SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,
This is much simpler:
SUBSTRING(IFNULL(M_USERNAME, M_NAME), 1, 80) AS memberNameI'll wait until I hear back from you before making any changes to the sql file :)
Michael,
Quote from: Oldiesmann on November 13, 2006, 01:55:39 PMI'll wait until I hear back from you before making any changes to the sql file :)
The conversion last night worked very well! :) So far, the only problems I have discovered are some incorrect post counts, which were probably carried over from Snitz. Running the SMF Admin cleanup utilities seems to have resolved these minor problems.
So I feel confident that my changes to the .sql file are ready for inclusion in the live copy. At line 95:
$row['TmodifiedName'] = "''";At line 108:
$row['RmodifiedName'] = "''";At line 34, we have two options, my original modification,
SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,and your simplification,
SUBSTRING(IFNULL(M_USERNAME, M_NAME), 1, 80) AS memberName,You might actually prefer my version, because it deals with the fact that Snitz columns often include
both NULL and zero-length string data. This complication occurs because Snitz changed its database design several times without cleaning up data created with earlier releases.
I'm submitting my converted forums to a group of testers today. I'll let you know if we discover any further anomalies.
Thank you very much for all of your help! :)
Ken
Thanks for the info. I'll keep your version in there since it could be either way.
I'll go ahead and make the changes to the SQL file and post the new version here, then tell Jay to use it :)
EDIT: Updated SQL file attached ;)
Well, I've tried everything convert.php and .sql I could find in these forums. All do the same.
I enter all the info, hit continue, it churns for 30+ minutes. Then returns a page "step1" says a couple of things are successful but stops on "converting messages..(this take some time....)". I get no other output.
Now, what I had to do was use enterprise manager, conntect to server, download database to a local installation of MSDE. Then on the same machine I installed XAMPP. Used the migration tools from MySQL to get the data from MSSQL to MySQL. Seemed to work without errors.
Installed smf in it's own database then ran the converter script. I get the forum with all the boards, but no members and no messages and I also can't log in with the admin account.
Any suggestions?
How big is your foum (posts/members)?
32k post and 2900 users.
With the files you've provided here I've got past the substep 8 (forum posts) but get this error when trying to convert the topics.
Converting posts (this may take some time)... Successful.
Converting topics... Unsuccessful!
This query:
SELECT
t.TOPIC_ID AS ID_TOPIC, t.T_STICKY AS isSticky, t.FORUM_ID AS ID_BOARD,
MIN(m.ID_MSG) AS ID_FIRST_MSG, MAX(m.ID_MSG) AS ID_LAST_MSG,
t.T_AUTHOR AS ID_MEMBER_STARTED, t.T_LAST_POST_AUTHOR AS ID_MEMBER_UPDATED,
t.T_REPLIES AS numReplies, t.T_VIEW_COUNT AS numViews,
t.T_STATUS = 0 AS locked
FROM (`mydatabase`.FORUM_TOPICS AS t, `mydatabase`.smf_messages AS m)
WHERE m.ID_TOPIC = t.TOPIC_ID
GROUP BY t.TOPIC_ID
HAVING ID_FIRST_MSG != 0
AND ID_LAST_MSG != 0
LIMIT 0, 500;
Caused the error:
'mydatabase.t.T_STICKY' isn't in GROUP BY
I'm presuming it's something within this section of code but I'm a bit stuck at the moment.
/******************************************************************************/
--- Converting topics...
/******************************************************************************/
TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;
---* {$to_prefix}topics
SELECT
t.TOPIC_ID AS ID_TOPIC, t.T_STICKY AS isSticky, t.FORUM_ID AS ID_BOARD,
MIN(m.ID_MSG) AS ID_FIRST_MSG, MAX(m.ID_MSG) AS ID_LAST_MSG,
t.T_AUTHOR AS ID_MEMBER_STARTED, t.T_LAST_POST_AUTHOR AS ID_MEMBER_UPDATED,
t.T_REPLIES AS numReplies, t.T_VIEW_COUNT AS numViews,
t.T_STATUS = 0 AS locked
FROM ({$from_prefix}FORUM_TOPICS AS t, {$to_prefix}messages AS m)
WHERE m.ID_TOPIC = t.TOPIC_ID
GROUP BY t.TOPIC_ID
HAVING ID_FIRST_MSG != 0
AND ID_LAST_MSG != 0;
---*
Any help with this would be appreciated.
Try this one.
Have been following this post through and have applied all SQL files to this point. I am now getting these errors.
Converting members... Unsuccessful!
This query:
SELECT
MEMBER_ID AS ID_MEMBER, SUBSTRING(M_NAME, 1, 255) AS realName,
SUBSTRING(M_PASSWORD, 1, 64) AS passwd,
SUBSTRING(M_EMAIL, 1, 255) AS emailAddress,
SUBSTRING(M_COUNTRY, 1, 255) AS location,
SUBSTRING(M_HOMEPAGE, 1, 255) AS websiteTitle,
SUBSTRING(M_HOMEPAGE, 1, 255) AS websiteUrl,
SUBSTRING(REPLACE(M_SIG, '\n', '<br />'), 1, 65534) AS signature,
SUBSTRING(M_AIM, 1, 16) AS AIM, SUBSTRING(M_ICQ, 1, 255) AS ICQ,
SUBSTRING(M_MSN, 1, 255) AS MSN, SUBSTRING(M_YAHOO, 1, 32) AS YIM,
IF(M_LEVEL = 3, 1, 0) AS ID_GROUP, M_POSTS AS posts,
UNIX_TIMESTAMP(REPLACE(M_DATE, '\0', '')) AS dateRegistered,
SUBSTRING(M_TITLE, 1, 255) AS usertitle,
UNIX_TIMESTAMP(REPLACE(M_LASTHEREDATE, '\0', '')) AS lastLogin, M_HIDE_EMAIL AS hideEmail,
IF(M_RECEIVE_EMAIL, 4, 0) AS notifyTypes, M_DOB AS birthdate,
CASE M_SEX WHEN 'Male' THEN 1 WHEN 'Female' THEN 2 ELSE 0 END AS gender,
SUBSTRING(IF(IFNULL(M_USERNAME , '') = '', 1, 80) AS memberName,
SUBSTRING(M_LAST_IP, 1, 255) AS memberIP, '' AS lngfile, '' AS buddy_list,
'' AS pm_ignore_list, '' AS messageLabels, '' AS personalText,
'' AS timeFormat, '' AS avatar, '' AS secretQuestion, '' AS secretAnswer,
'' AS validation_code, '' AS additionalGroups, '' AS smileySet,
'' AS passwordSalt, SUBSTRING(M_LAST_IP, 1, 255) AS memberIP2
FROM `pra073111034676`.FORUM_MEMBERS
LIMIT 0, 500;
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 'AS memberName,
SUBSTRING(M_LAST_IP, 1, 255) AS memberIP, '' A
Try using the converter in this topic: Snitz Forums (http://www.simplemachines.org/community/index.php?topic=147215)
Also please keep further discussion in that topic. I'll lock this topic for now.