Have any of you ever seen this?

[chaos40]

when attempting to convert from phpBB3.3 to smf 2.0.19 via the converter program (convert.php) I get the following

Converting ranks... Successful.
Converting groups... Successful.
Converting members... Unsuccessful!
This query:
SELECT
u.user_id AS id_member, SUBSTRING(u.username, 1, 80) AS member_name,
SUBSTRING(u.username, 1, 255) AS real_name,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS last_login,
u.user_regdate AS date_registered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_rank = 1, 1, IFNULL(mg.id_group, 0)) AS id_group,
u.user_new_privmsg AS instant_messages,
SUBSTRING(u.user_email, 1, 255) AS email_address,
u.user_unread_privmsg AS unread_messages,
SUBSTRING(u.user_msnm, 1, 255) AS msn,
SUBSTRING(u.user_aim, 1, 16) AS aim,
SUBSTRING(u.user_icq, 1, 255) AS icq,
SUBSTRING(u.user_yim, 1, 32) AS yim,
SUBSTRING(u.user_website, 1, 255) AS website_title,
SUBSTRING(u.user_website, 1, 255) AS website_url,
u.user_allow_viewonline AS show_online, u.user_timezone AS time_offset,
IF(u.user_allow_viewemail = 1, 0, 1) AS hide_email, u.user_avatar AS avatar,
REPLACE(u.user_sig, '\n', '<br />') AS signature,
u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
u.user_notify_pm AS pm_email_notify,
CASE u.user_inactive_reason WHEN 0 THEN 1 ELSE 0 END AS is_activated,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle, u.user_ip AS member_ip,
'' AS secret_question, '' AS secret_answer, '' AS validation_code,
'' AS additional_groups, '' AS smiley_set, '' AS password_salt, '' as openid_uri, '' AS ignore_boards,
u.user_ip AS member_ip2
FROM `theblvb2_bluecashew`.phpbb_users AS u
LEFT JOIN `theblvb2_bluecashew`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `theblue_smf`.koi_membergroups AS mg ON (mg.group_name = CONCAT('phpBB ', r.rank_title))
WHERE u.group_id NOT IN (1, 6)
GROUP BY u.user_id
LIMIT 0, 500;
Caused the error:
1054

thanks for any assistance you can provide

[vbgamer45]

 In short, ERROR 1054 means that MySQL can't find the column name that you specified in your SQL statements.


Try to run that query manually and see what error pops back

[chaos40]

Completely lost

what do you mean?

[vbgamer45]

Take the whole query and run the sql in a tool such as phpmyadmin

Code Select Expand

SELECT
u.user_id AS id_member, SUBSTRING(u.username, 1, 80) AS member_name,
SUBSTRING(u.username, 1, 255) AS real_name,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS last_login,
u.user_regdate AS date_registered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_rank = 1, 1, IFNULL(mg.id_group, 0)) AS id_group,
u.user_new_privmsg AS instant_messages,
SUBSTRING(u.user_email, 1, 255) AS email_address,
u.user_unread_privmsg AS unread_messages,
SUBSTRING(u.user_msnm, 1, 255) AS msn,
SUBSTRING(u.user_aim, 1, 16) AS aim,
SUBSTRING(u.user_icq, 1, 255) AS icq,
SUBSTRING(u.user_yim, 1, 32) AS yim,
SUBSTRING(u.user_website, 1, 255) AS website_title,
SUBSTRING(u.user_website, 1, 255) AS website_url,
u.user_allow_viewonline AS show_online, u.user_timezone AS time_offset,
IF(u.user_allow_viewemail = 1, 0, 1) AS hide_email, u.user_avatar AS avatar,
REPLACE(u.user_sig, '\n', '<br />') AS signature,
u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
u.user_notify_pm AS pm_email_notify,
CASE u.user_inactive_reason WHEN 0 THEN 1 ELSE 0 END AS is_activated,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle, u.user_ip AS member_ip,
'' AS secret_question, '' AS secret_answer, '' AS validation_code,
'' AS additional_groups, '' AS smiley_set, '' AS password_salt, '' as openid_uri, '' AS ignore_boards,
u.user_ip AS member_ip2
FROM `theblvb2_bluecashew`.phpbb_users AS u
LEFT JOIN `theblvb2_bluecashew`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `theblue_smf`.koi_membergroups AS mg ON (mg.group_name = CONCAT('phpBB ', r.rank_title))
WHERE u.group_id NOT IN (1, 6)
GROUP BY u.user_id
LIMIT 0, 500;


[chaos40]

this is what I get

Failed to execute SQL : SQL SELECT u.user_id AS id_member, SUBSTRING(u.username, 1, 80) AS member_name, SUBSTRING(u.username, 1, 255) AS real_name, SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS last_login, u.user_regdate AS date_registered, u.user_posts AS posts, IF(u.user_rank = 1, 1, IFNULL(mg.id_group, 0)) AS id_group, u.user_new_privmsg AS instant_messages, SUBSTRING(u.user_email, 1, 255) AS email_address, u.user_unread_privmsg AS unread_messages, u.user_allow_viewonline AS show_online, u.user_timezone AS time_offset, IF(u.user_allow_viewemail = 1, 0, 1) AS hide_email, u.user_avatar AS avatar, REPLACE(u.user_sig, '\n', '<br />') AS signature, u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type, u.user_notify_pm AS pm_email_notify, CASE u.user_inactive_reason WHEN 0 THEN 1 ELSE 0 END AS is_activated, '' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels, '' AS personal_text, '' AS time_format, '' AS usertitle, u.user_ip AS member_ip, '' AS secret_question, '' AS secret_answer, '' AS validation_code, '' AS additional_groups, '' AS smiley_set, '' AS password_salt, '' as openid_uri, '' AS ignore_boards, u.user_ip AS member_ip2 FROM `theblvb2_bluecashew`.phpbb_users AS u LEFT JOIN `theblvb2_bluecashew`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1) LEFT JOIN `theblue_smf`.koi_membergroups AS mg ON (mg.group_name = CONCAT('phpBB ', r.rank_title)) WHERE u.group_id NOT IN (1, 6) GROUP BY u.user_id LIMIT 0, 500; failed : Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'theblue_smf.mg.id_group' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[vbgamer45]

Do you have access to your mysql configuration?
If so in my.cnf set
sql_mode = ""

[chaos40]

I did that and got the same error 

[vbgamer45]

Did you restart mysql after editing the file?

[chaos40]

Yes sir.

[vbgamer45]

Very strange does it stills say this error?
 clause and contains nonaggregated column 'theblue_smf.mg.id_group' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[chaos40]

Very strange does it stills say this error?
 clause and contains nonaggregated column 'theblue_smf.mg.id_group' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I'm technically inclined, but now you're speaking German 

This PHPBB software doesn't play nice at all. I ported the whole forum off an existing platform onto a new linux box with all software versions and settings identical to the original mysql version, php version etc etc - I use my local hosts file to do the name mapping since this forum is on the internet and my box is a replica

I did the database back up and restore via the ssh command line as the new server does not have phpmyadmin installed ( I use webmin on this box instead) and the entire site loads just fine but I cannot login no matter what I try. (clearing cookies, browser cache etc etc)

Could this be the issue? I didn't think they were related but now I am not so sure.

thanks for your time, buddy

[Doug Heffernan]

If you disabled only_full_group_by at the my.cnf file, that error shouldn't have happened again.

The other solutuin imo is to add the columns names mentioned in the error in the GROUP BY clause at the query.

[chaos40]

/etc/my.cnf looks like this

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
autocommit = 0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode = ""


afterward did a service mysqld stop and then start

and still getting the following when I run convert.php

Converting ranks... Successful.
Converting groups... Successful.
Converting members... Unsuccessful!
This query:
SELECT
u.user_id AS id_member, SUBSTRING(u.username, 1, 80) AS member_name,
SUBSTRING(u.username, 1, 255) AS real_name,
SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS last_login,
u.user_regdate AS date_registered,
SUBSTRING(u.user_from, 1, 255) AS location,
u.user_posts AS posts, IF(u.user_rank = 1, 1, IFNULL(mg.id_group, 0)) AS id_group,
u.user_new_privmsg AS instant_messages,
SUBSTRING(u.user_email, 1, 255) AS email_address,
u.user_unread_privmsg AS unread_messages,
SUBSTRING(u.user_msnm, 1, 255) AS msn,
SUBSTRING(u.user_aim, 1, 16) AS aim,
SUBSTRING(u.user_icq, 1, 255) AS icq,
SUBSTRING(u.user_yim, 1, 32) AS yim,
SUBSTRING(u.user_website, 1, 255) AS website_title,
SUBSTRING(u.user_website, 1, 255) AS website_url,
u.user_allow_viewonline AS show_online, u.user_timezone AS time_offset,
IF(u.user_allow_viewemail = 1, 0, 1) AS hide_email, u.user_avatar AS avatar,
REPLACE(u.user_sig, '\n', '<br />') AS signature,
u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
u.user_notify_pm AS pm_email_notify,
CASE u.user_inactive_reason WHEN 0 THEN 1 ELSE 0 END AS is_activated,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle, u.user_ip AS member_ip,
'' AS secret_question, '' AS secret_answer, '' AS validation_code,
'' AS additional_groups, '' AS smiley_set, '' AS password_salt, '' as openid_uri, '' AS ignore_boards,
u.user_ip AS member_ip2
FROM `theblvb2_bluecashew`.phpbb_users AS u
LEFT JOIN `theblvb2_bluecashew`.phpbb_ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
LEFT JOIN `smf_blue`.smf_membergroups AS mg ON (mg.group_name = CONCAT('phpBB ', r.rank_title))
WHERE u.group_id NOT IN (1, 6)
GROUP BY u.user_id
LIMIT 0, 500;
Caused the error:
1054

path to SMF: /home/customer/www/thebluecashew.net/public_html/smf

path to phpbb3: /home/customer/www/thebluecashew.net/public_html

both forums are up and running with their own databases on locally installed instance of mysql

[vbgamer45]

It could in a different file location then /etc/my.cnf some systems check /etc/mysql /etc/mysqld folders are something named liked that..

[chaos40]

I'm using centos

the only other directory was /etc/my.cnf.d/ so I copied my.cnf there

same result

[vbgamer45]

I run the same
And is it under "[mysqld]" section that you put that line


You can also run this mysql query
set global sql_mode=""

[chaos40]

Yes sir.

I verified what you asked and I ran that query

same result

[vbgamer45]

Out of ideas other than trying another host/service
Or editing the .sql file and add
set session sql_mode= "";

[chaos40]

thanks for Trying... I really appreciate your time

This may have something to do with the error on the PHPBB side where I cannot login with any account after the restore.

I'm haunting them on their forum as well right now 

[chaos40]

Okay new update

I was able to get the script to run by ripping out all references to rows that were not present in the original php database ... for the users only (at the moment)

end result looks like this in the .sql file


I see all of my users in the new smf database

however when I attempt to login I get the following message

Password security has recently been upgraded. Please enter your password again.

when I enter it again I get the same message returned. I see the hashed password in the database field as well but not sure if it's properly salted?

any help would be very appreciated. I feel like I'm almost there