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 

[vbgamer45]

Try to do forgot your password. Yes it doesn't solve other users but can see if you can get in that way first.

[chaos40]

No dice

[vbgamer45]

Didn't get the email? or didn't work after resetting.

[chaos40]

Didn't get the email -- probably because I didn't set all that up on the new smf forum prior to converting

I can however register a new user manually

is there a way I can go into the database directly and make the user I just registered an admin?

[vbgamer45]

Yes there is just change the id_group = 1 where that member id/username

[chaos40]

Im in as an admin now with the new user I registered

[chaos40]

Is there anything I can do to get my users working without having to use the forgot password function?

thanks

[Aleksi "Lex" Kilpinen]

If you are logged in as admin, you should be able to just change the password for your earlier account. No problem.

[vbgamer45]

Yeah.. otherwise you have to see if you can find code to do it.
https://stackoverflow.com/questions/73270291/phpbb-3-3-8-verify-password-outside-source

Haven't found anything in google searches so far

SMF has function phpBB3_password_check  but I believe that is for 3.0 only.
Sources\LogInOut.php

[chaos40]

Interestingly enough the 33 converter did not work but the 32 converter did

I suspect this site was upgraded from older versions to this latest one

[chaos40]

SO now the conversion completed successfully.. mostly

but every post has a last edit by guest line and quotes have strike through lines

any way to get rid of that?

[chaos40]

I finished the conversion. looks pretty good

can I post a link to the board on this forum?

[Aleksi "Lex" Kilpinen]

If you want to show a succesful conversion in this topic, I'd say it's fine. Usually if it's related to the support topic it is allowed.

[GL700Wing]

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

I had the same issue with a forum I migrated a couple of years ago so (ie, users need to enter their password a second time) so I changed the message in '/Themes/default/languages/Login.english.php' to:

Code Select Expand

$txt['login_hash_error'] = '<b>Password security has recently been upgraded.<br />Please wait 5 seconds and then enter your password again.</b>';
This way the users know they need to try again after waiting a few seconds ...

[chaos40]

If you want to show a succesful conversion in this topic, I'd say it's fine. Usually if it's related to the support topic it is allowed.

thanks bud!

https://thebluecashew.net

The board named Blue Cashew Archive is a link to the php version of the board. I did that so that users have the option of logging into the old forum to alert me to the fact that they have difficulty with the reset process

[Doug Heffernan]

I finished the conversion. looks pretty good

It looks nice indeed. Welcome to Smf.

I am going to mark this topic as solved seeing the fact that your conversion was successfully finished.

[chaos40]

PHPBB was very difficult to navigate and lacked many of the features which are bundled with SMF by default or at least with the addition of mods

thanks for all your help guys!

[Antechinus]

phpBB has its good points and bad points, like anything else. Some aspects of it I like better than SMF. Others I don't. Overall I would prefer SMF, but with some good ideas nicked from other places.

[chaos40]

Hey Guys,

if you quickly needed to add about 500+ users to a group to give proper access to all areas of your board how would you do it?

is there an SQL query I can run or something like that?

thanks

Now I'm battling DMARC issues -- you'd think I would have thought of that BEFORE swinging the site over since the entire success of all the users hinges on being able to receive password reset emails

any quick help would be most appreciated! On the sql thing..

[chaos40]

Nevermind. I found a post which had the syntax for the query that needs to be run

[chaos40]

If you want to show a succesful conversion in this topic, I'd say it's fine. Usually if it's related to the support topic it is allowed.

thanks bud!

https://thebluecashew.net

The board named Blue Cashew Archive is a link to the php version of the board. I did that so that users have the option of logging into the old forum to alert me to the fact that they have difficulty with the reset process

updated