Import from SMF 2.011 failed (Unknown column 'pm_receive

Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list') Started by andy · April 05, 2016, 10:16:12 am · Read 32612 times 0 Members and 1 Guest are viewing this topic. previous topic - next topic

Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

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'

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #1 – April 05, 2016, 10:20:43 am

Could you also post the original query that you modified?

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #2 – 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(

unsigned NOT NULL auto_increment,
member_name varchar(80) NOT NULL default '',
date_registered int(10) unsigned NOT NULL default 0,
posts mediumint(

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(

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(

NOT NULL default 0,
referred_by mediumint(

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)

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #3 – 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 ?

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #4 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #5 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #6 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #7 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #8 – 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

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #9 – 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

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #10 – 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".

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #11 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #12 – 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #13 – April 05, 2016, 05:06:57 pm

Quote from: andy – April 05, 2016, 10:16:12 amCaused 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.

Re: Import from SMF 2.011 failed (Unknown column 'pm_receive_from' in 'field list')

Reply #14 – 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....