ElkArte Community

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

Title: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')
Post by: andy on April 05, 2016, 10:16:12 am
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, 10:20:43 am
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, 10:28:37 am
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, 10:54:06 am
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, 10:56:11 am
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, 10:58:21 am
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, 11:02:14 am
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, 11:06:42 am
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, 11:15:01 am
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, 11:17:09 am
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, 11:20:07 am
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, 11:42:07 am
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, 12: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
QuoteThe 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, 05:06:57 pm
Quote from: andy – 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 05, 2016, 11:51:49 pm
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, 12: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, 08:55:31 am
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, 11:18:53 am
Quote from: TE – 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, 12: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, 01: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, 07:50:38 am
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, 08:25:48 am
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, 08:41:38 am
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, 08:46:17 am
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, 09:12:19 am
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, 09:14:23 am
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, 09:54:16 am
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, 11:12:57 am
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, 11:21:40 am
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, 12:11:23 pm
Quote from: emanuele – 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.