ElkArte Community

General => OpenImporter => Topic started by: andy on April 05, 2016, 04:16:12 pm

Title: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 04:16:12 pm
Thought I could wriggle out of this easily by deleting the column and the reference to it but the values don't add up on import. As I do not know sql in details I can't get past this, unless I do not import members.



Unsuccessful!
This query:

    SELECT
    id_member, member_name, date_registered, posts, id_group, lngfile, last_login,
    real_name, unread_messages, unread_messages, new_pm, buddy_list, pm_ignore_list,
    pm_prefs, mod_prefs, message_labels, passwd, email_address, personal_text,
    gender, birthdate, website_url, website_title, location, hide_email, show_online,
    time_format, signature, time_offset, avatar, pm_email_notify,
    usertitle, notify_announcements, notify_regularity, notify_send_body,
    notify_types, member_ip, member_ip2, secret_question, secret_answer, 1 AS id_theme, is_activated,
    validation_code, id_msg_last_visit, additional_groups, smiley_set, id_post_group,
    total_time_logged_in, password_salt, ignore_boards,
    IFNULL(warning, 0) AS warning, passwd_flood,
    pm_receive_from AS receive_from
    FROM `padding_smf`.elk_members
    LIMIT 0, 500;

Caused the error:

    Unknown column 'pm_receive_from' in 'field list'
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Frenzie on April 05, 2016, 04:20:43 pm
Could you also post the original query that you modified?
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 04:28:37 pm
I dropped the table for smf_members, edited out the column and reference at the top of the dump, then tried importing it again but it wouldnt accept it.  I guess each member has a value for it so it might be hard editing that out for every person.

MySQL said: Documentation
#1136 - Column count doesn't match value count at row 1

I guess I need the correct query to remove it properly. I hope this isn't going to keep happening during import.


This is the ORIGINAL sql copy.

--
-- Table structure for table `smf_members`
--

DROP TABLE IF EXISTS `smf_members`;

CREATE TABLE `smf_members` (
`id_member` mediumint(8) unsigned NOT NULL auto_increment,
`member_name` varchar(80) NOT NULL default '',
`date_registered` int(10) unsigned NOT NULL default 0,
`posts` mediumint(8) unsigned NOT NULL default 0,
`id_group` smallint(5) unsigned NOT NULL default 0,
`lngfile` varchar(255) NOT NULL default '',
`last_login` int(10) unsigned NOT NULL default 0,
`real_name` varchar(255) NOT NULL default '',
`instant_messages` smallint(5) NOT NULL default 0,
`unread_messages` smallint(5) NOT NULL default 0,
`new_pm` tinyint(3) unsigned NOT NULL default 0,
`buddy_list` text NOT NULL,
`pm_ignore_list` varchar(255) NOT NULL default '',
`pm_prefs` mediumint(8) NOT NULL default 0,
`mod_prefs` varchar(20) NOT NULL default '',
`message_labels` text NOT NULL,
`passwd` varchar(64) NOT NULL default '',
`openid_uri` text NOT NULL,
`email_address` varchar(255) NOT NULL default '',
`personal_text` varchar(255) NOT NULL default '',
`gender` tinyint(4) unsigned NOT NULL default 0,
`birthdate` date NOT NULL default '0001-01-01',
`website_title` varchar(255) NOT NULL default '',
`website_url` varchar(255) NOT NULL default '',
`location` varchar(255) NOT NULL default '',
`icq` varchar(255) NOT NULL default '',
`aim` varchar(255) NOT NULL default '',
`yim` varchar(32) NOT NULL default '',
`msn` varchar(255) NOT NULL default '',
`hide_Email` tinyint(4) NOT NULL default 1,
`show_online` tinyint(4) NOT NULL default 1,
`time_format` varchar(80) NOT NULL default '',
`signature` text NOT NULL,
`time_offset` float NOT NULL default 0,
`avatar` varchar(255) NOT NULL default '',
`pm_email_notify` tinyint(4) NOT NULL default 0,
`karma_bad` smallint(5) unsigned NOT NULL default 0,
`karma_good` smallint(5) unsigned NOT NULL default 0,
`usertitle` varchar(255) NOT NULL default '',
`notify_announcements` tinyint(4) NOT NULL default 1,
`notify_regularity` tinyint(4) NOT NULL default 1,
`notify_send_body` tinyint(4) NOT NULL default 0,
`notify_types` tinyint(4) NOT NULL default 2,
`member_ip` varchar(255) NOT NULL default '',
`member_ip2` varchar(255) NOT NULL default '',
`secret_question` varchar(255) NOT NULL default '',
`secret_answer` varchar(64) NOT NULL default '',
`id_theme` tinyint(4) unsigned NOT NULL default 0,
`is_activated` tinyint(3) unsigned NOT NULL default 1,
`validation_code` varchar(10) NOT NULL default '',
`id_msg_last_visit` int(10) unsigned NOT NULL default 0,
`additional_groups` varchar(255) NOT NULL default '',
`smiley_set` varchar(48) NOT NULL default '',
`id_post_group` smallint(5) unsigned NOT NULL default 0,
`total_time_logged_in` int(10) unsigned NOT NULL default 0,
`password_salt` varchar(255) NOT NULL default '',
`ignore_boards` text NOT NULL,
`warning` tinyint(4) NOT NULL default 0,
`passwd_flood` varchar(12) NOT NULL default '',
`pm_receive_from` tinyint(4) unsigned NOT NULL default 1,
`lastactivity` int(11),
`aeva_items` int(11) NOT NULL default 0,
`aeva_comments` int(11) NOT NULL default 0,
`aeva_unseen` int(11) NOT NULL default -1,
`aeva` varchar(255) NOT NULL default '',
`longitude` decimal(18,15) default 0.000000000000000,
`latitude` decimal(18,15) default 0.000000000000000,
`pindate` int(10) NOT NULL default 0,
`profilestatuses` int(1) NOT NULL,
`has_agreed` tinyint(1) NOT NULL default 1,
`annoyuser` tinyint(3) NOT NULL default 0,
`referrals_no` mediumint(8) NOT NULL default 0,
`referred_by` mediumint(8) NOT NULL default 0,
`referrals_hits` int(11) NOT NULL default 0,
`profile_views` int(10) NOT NULL,
`is_spammer` tinyint(3) NOT NULL default 0,
PRIMARY KEY (`id_member`),
KEY `member_name` (`member_name`),
KEY `real_name` (`real_name`),
KEY `date_registered` (`date_registered`),
KEY `id_group` (`id_group`),
KEY `birthdate` (`birthdate`),
KEY `posts` (`posts`),
KEY `last_login` (`last_login`),
KEY `lngfile` (`lngfile`(30)),
KEY `id_post_group` (`id_post_group`),
KEY `warning` (`warning`),
KEY `total_time_logged_in` (`total_time_logged_in`),
KEY `id_theme` (`id_theme`)
) ENGINE=MyISAM;

--
-- Dumping data in `smf_members`
--

INSERT INTO `smf_members`
(`id_member`, `member_name`, `date_registered`, `posts`, `id_group`, `lngfile`, `last_login`, `real_name`, `instant_messages`, `unread_messages`, `new_pm`, `buddy_list`, `pm_ignore_list`, `pm_prefs`, `mod_prefs`, `message_labels`, `passwd`, `openid_uri`, `email_address`, `personal_text`, `gender`, `birthdate`, `website_title`, `website_url`, `location`, `icq`, `aim`, `yim`, `msn`, `hide_Email`, `show_online`, `time_format`, `signature`, `time_offset`, `avatar`, `pm_email_notify`, `karma_bad`, `karma_good`, `usertitle`, `notify_announcements`, `notify_regularity`, `notify_send_body`, `notify_types`, `member_ip`, `member_ip2`, `secret_question`, `secret_answer`, `id_theme`, `is_activated`, `validation_code`, `id_msg_last_visit`, `additional_groups`, `smiley_set`, `id_post_group`, `total_time_logged_in`, `password_salt`, `ignore_boards`, `warning`, `passwd_flood`, `pm_receive_from`, `lastactivity`, `aeva_items`, `aeva_comments`, `aeva_unseen`, `aeva`, `longitude`, `latitude`, `pindate`, `profilestatuses`, `has_agreed`, `annoyuser`, `referrals_no`, `referred_by`, `referrals_hits`, `profile_views`, `is_spammer`)
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Spuds on April 05, 2016, 04:54:06 pm
Are you saying that your SMF 2.0.11 install does not have a  pm_receive_from column in the members table ?
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 04:56:11 pm
The opposite I think.

Caused the error:

    Unknown column 'pm_receive_from' in 'field list'



The import failed with that message.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 04:58:21 pm
I tried removing it to see if the importer would continue but ran into problems. I do not know the sql query to delete it properly and remove the value from each users data.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Spuds on April 05, 2016, 05:02:14 pm
I'm going to run a quick test on a 2.0.11 to see if I get the error.  Looking quickly that error should mean the importer can't find the    pm_receive_from column in your SMF members table.  Since importing members is mandatory, it stops at that point.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 05:06:42 pm
I have a sneaky feeling that a 'pm' column was dropped or emptied (after some trouble). Maybe the column with the pm texts. I'm not sure as it happened quite a long time ago and cannot get hold of the person now. Maybe you can see straight away if one is missing, for me I would need to set up a new install and compare.

Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Spuds on April 05, 2016, 05:15:01 pm
If its missing, you should be able to add it back with phpmyadmin or adminer. 

Just add a new tinyint column named pm_receive_from with a default value of 1 to your smf_members table.  That should be easier than trying to adjust the importer code
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 05:17:09 pm
I think that is what it is - will give it ago tomorrow as its late here.
Thanks,
Andy
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: TE on April 05, 2016, 05:20:07 pm
depending on the version of the importer you're using somewhere  there should be a file named smf2_importer.xml..

please edit that file an remove
Code: [Select]
, pm_receive_from AS receive_from
Probably there's  a linebreak between the , and the  "pm_receive_from AS receive_from".

Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 05:42:07 pm
Had to get it done... it worked, but... didn’t import any members. Maybe there is something else. There were no error messages.

I can't log in but the new install and the SMF admin account were the same name. Now neither password works.
Will try again tomorrow.

http://paddington-school.com/elk/


I didnt see the above post so will try that tomorrow.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: TE on April 05, 2016, 06:48:41 pm
Are you sure your SMF is still healthy? I feel there's a problem with the smf_members table.
Just checked: http://paddington-school.com/smf/index.php?action=profile;u=2
Quote
The user whose profile you are trying to view does not exist.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: emanuele on April 05, 2016, 11:06:57 pm
Caused the error:

    Unknown column 'pm_receive_from' in 'field list'
This means your SMF installation is missing the column "pm_receive_from" in the members table.
And as TE noted, it looks like something happened to your SMF install, something similar to the members table being emptied.
I hope you have a backup handy just in case it becomes necessary.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 06, 2016, 05:51:49 am
It is only a test site so not important. After deleting the line in xml 2.0 importer file I got another error about 'like' posts. So I went back to the advanced options and unchecked several  things that I thought were not essential ('like' posts, anything about PMs etc), and it worked.

Its all working now....
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 06, 2016, 06:05:36 am
There was the password problem - first time failed but 2nd time logged in. I can see a few people complaining about that  :P
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Spuds on April 06, 2016, 02:55:31 pm
There was a discussion about that not long ago .... 

The system requires you to enter your password twice so that it can properly convert the "old" password hash to the hashing requirements of the new system.   

Should have a better message displayed when you enter the second one, like please enter your password again to finish the migration to the new system, or some such thing.  Only happens the first time a user logs on after a conversion.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on May 21, 2017, 05:18:53 pm
depending on the version of the importer you're using somewhere  there should be a file named smf2_importer.xml..

please edit that file an remove
Code: [Select]
, pm_receive_from AS receive_from
Probably there's  a linebreak between the , and the  "pm_receive_from AS receive_from".

I have exactly the same error as this, importing from exactly the same version of SMF.

I have edited the .xml for the importer, as suggested, and I get exactly the same error after.  I am completely stuck.

Any suggestions appreciated.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: Spuds on May 21, 2017, 06:25:12 pm
I just want to make sure you are using the version of open importer from here: https://github.com/OpenImporter/openimporter/zipball/master

There are a few versions floating around on the site, but the maintained version is from the above link.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on May 21, 2017, 07:01:32 pm
It does not look like that's the version i was using.  Bear with me, I'll try that.

......right.  Tried that version, from that direct link.  Had exactly the same error as the original post. Edited the .xml file to take out the line break in the line of code mentioned earlier in the thread. Exactly the same error.

So to confirm: I am seeing exactly this error, with the version in your link.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 01:50:38 pm
Right....I've not been able to spend much time on this earlier, but I've gone back and had a look at this importer, and installed the forums in easier to access places. I've put the smf board (source) in the domain root directory, and I've put elkarte in a sub.domain ('forum.domainname'). I have put the two boards in the same database (same database user, same password) but with different prefix (smf_ and elkarte_ respectively). I resolved an issue that I had with the php version not working with my smf board version (2.0.11) by setting the domainname hosting to use php 5.6. The two board locations verify, and the importer runs. I now get the following output:

Importing...

Importing settings... ✔
Importing members...

Unsuccessful!
This query:

INSERT IGNORE INTO `database_name`.elkarte_members
(id_member, member_name, date_registered, posts, id_group, lngfile, last_login, real_name, unread_messages, new_pm, buddy_list, pm_ignore_list, pm_prefs, mod_prefs, message_labels, passwd, email_address, personal_text, gender, birthdate, website_url, website_title, location, hide_email, show_online, time_format, signature, time_offset, avatar, pm_email_notify, usertitle, notify_announcements, notify_regularity, notify_send_body, notify_types, member_ip, member_ip2, secret_question, secret_answer, id_theme, is_activated, validation_code, id_msg_last_visit, additional_groups, smiley_set, id_post_group, total_time_logged_in, password_salt, ignore_boards, warning, passwd_flood, receive_from)
VALUES

 ****list of members and associated fields****

Caused the error:
Unknown column 'personal_text' in 'field list'

Line: 117
File: local_path/OpenImporter-openimporter-199d92d/importer/OpenImporter/Database.php
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 02:25:48 pm
I've upgraded smf to 2.0.14, and I've also made sure that both the domain, and sub-domain are running php 7.0.19.

Same error as in my previous post.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: emanuele on June 04, 2017, 02:41:38 pm
Question: did you install Elk 1.0 or 1.1?
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 02:46:17 pm
The attempt documented most recently above was elk 1.1 (RC 1).  The previous failed attempt was 1.0 - but I didn't try that with the current configuration of the board locations and php update.

I'm happy to try anything on the elk side - but it is more problematic on the smf side, as that is my current active board.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: emanuele on June 04, 2017, 03:12:19 pm
1.1 surely will fail, it has several differences with 1.0 and the importer will not be able to handle them (we need to take care of that "soon").

I'll give a try as soon as possible to the importer, I left it in a terrible state... :-[
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 03:14:23 pm
Obviously it's tempting to think the *my problem* is the most important problem....but it's clearly not.  I'll put 1.0 into my forum sub-domain, and run the importer again, and report any issues.

I do appreciate your help.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: emanuele on June 04, 2017, 03:54:16 pm
Well, it *is* important because it used to work and now there is a problem.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 05:12:57 pm
Okay. I have now been able to successfully (bar a few catches) been able to import.

php 7.0.19; MySQL 5.1.73; Centos 6; Plesk Onyx 17.5.3 Update #7

Both boards on the same domain, but the target (elkarte) in a sub-domain.  Both databases in the same database, but with different prefixes.

Success - except it didn't import karma (flagged an error, and all came across as zero values)...and there was an issue with group icons that I needed to manually change (theme related). Not all avatars work, but that might have been true on the smf installation that I imported from (didn't fully test before the import, because it was primarily only for import purposes).

Essentially, it looks good enough at this point.  I, and my users, will test.

At this point, it seems with elkarte 1.0, and smf 2.0.14, I have broad success.

Thank you for your help.
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: emanuele on June 04, 2017, 05:21:40 pm
You are welcome!
Karma not imported I'd call it a bug.
Avatars, could you check if they are by chance attachments? And if the attachments are fine?
Title: Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: tetricky on June 04, 2017, 06:11:23 pm
You are welcome!
Karma not imported I'd call it a bug.
Avatars, could you check if they are by chance attachments? And if the attachments are fine?

It's carried across the avatars that were correct on the source (SMF) board, so I suspect it's my error, not that of the importer. I'm going to just tell my users to set new avatars...I don't have time to track through what is (to me) a minor error almost certainly of my own making.

The karma is an issue, but a minor one.  I'm not too tense about it.