Unknown column 'b.member_groups' in 'where clause'

[jsx]

Hi.

A friend a few years ago had a forum that ran on SMF 1.1.11 and his friend upgraded the forum to SMF 2.0.12. Later when his friend tried to upgrade to a higher version it probably failed or something else happened that the forum stopped working. In the Packages directory there are the following packages: smf_patch_2.0.13, smf_patch_2.0.14, smf_patch_2.0.15, smf_patch_2.0.16, smf_patch_2.0.17 - but the man who updated the forum said that the last time the forum worked well was in SMF 2.0.12.

I want to run this forum. After uploading the forum files to the server and importing the database, the following information from the database occurs.

The first information is:

Unknown column 'add_deny' in 'field list'

I opened the database file and changed addDeny to add_deny in these places:

Code Select Expand

CREATE TABLE `backup_board_permissions` (
  `ID_GROUP` smallint(5) NOT NULL DEFAULT 0,
  `ID_BOARD` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `permission` varchar(30) NOT NULL DEFAULT '',
  `addDeny` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `backup_board_permissions` (`ID_GROUP`, `ID_BOARD`, `permission`, `addDeny`) VALUES

Code Select Expand

CREATE TABLE `backup_permissions` (
  `ID_GROUP` smallint(5) NOT NULL DEFAULT 0,
  `permission` varchar(30) NOT NULL DEFAULT '',
  `addDeny` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `backup_permissions` (`ID_GROUP`, `permission`, `addDeny`) VALUES
I uploaded the database again and now I get this information:

Unknown column 'b.member_groups' in 'where clause'

Does anyone know what's going on? How to fix it?

Reference to this information is in this post:

https://www.simplemachines.org/community/index.php?topic=331662.msg2393766#msg2393766

But I don't know if this information is related to the Topic Count in Profiles modification

Another reference to this information is in this post:

https://www.simplemachines.org/community/index.php?topic=487050.0

[Arantor]

Well, if you're modifying backup_ tables, which have 1.1 columns, presumably that means you're fixing the backup taken before the upgrade to 2.0 which is something of a dead-end...

The usual advice is to take another backup then roll the 2.0.19 files over the top and then reinstall mods/themes.

[Doug Heffernan]

I want to run this forum. After uploading the forum files to the server and importing the database, the following information from the database occurs.

Just to clarify, the forum is at 2.0.12... version, right? The table that you altered is prefaced with the backup_ prefix, which usually is a backup made by the upgrader before it runs. In this case it belongs to the 1.1.x versions.

You must make sure to edit the table for the right version. Can you check those tables in the 2.0.x database?

What is the database prefix set to in the Settings.php file? The tables prefaced with that prefix are the ones that you need.
 

[Oldiesmann]

Prior to 2.0 the column was "memberGroups" - it was changed to "member_groups" for technical reasons relating to differences in how MySQL and PostgreSQL handle capitalization in column names.

[jsx]

This is what the database looks like. Almost all tables are backup_table_name



There are several tables in the database that do not have a backup_ prefix

Just to clarify, the forum is at 2.0.12... version, right? The table that you altered is prefaced with the backup_ prefix, which usually is a backup made by the upgrader before it runs. In this case it belongs to the 1.1.x versions.

I got a message that the forum has been updated from SMF 1.1.11 to SMF 2.0.12

The Settings.php file has this information: Software Version: SMF 1.1

There is information about SMF 2.0.13 in the index.php file

There is information about SMF 2.0.10 in the SSI.php file

There is information about SMF 2.0.12 in Sources/Admin.php

As can see, there is a lot of confusion here.

You must make sure to edit the table for the right version. Can you check those tables in the 2.0.x database?

I have access to this database.

What is the database prefix set to in the Settings.php file? The tables prefaced with that prefix are the ones that you need.

There is information about SMF 1.1 in the Settings.php file, and the prefix looks like this:

$db_prefix = '';

[Doug Heffernan]

There is information about SMF 1.1 in the Settings.php file, and the prefix looks like this:

$db_prefix = '';

That means that the tables without the prefix are the one that you need. The one with backup_ prefix are from Smf 1.1.x that were backed up by the upgrader before the upgrade.

Can you find the settings table and see what the value for smfVersion is set to? That is to see if the database has been upgraded to 2.0.x as well. Just to make sure.

[Arantor]

Can you find the settings table and see what the value for smfVersion is set to?

The screenshot would suggest there isn't a full 2.0 database there. The list showed 55 tables; a 1.1 backup would be 41 tables, and the last table listed was custom_fields. (phpMyAdmin doesn't paginate at 55 rows.)

[jsx]

Can you find the settings table and see what the value for smfVersion is set to? That is to see if the database has been upgraded to 2.0.x as well. Just to make sure.

I opened the database file and found this:

Code Select Expand

--
-- Table structure for the table `settings`
--

CREATE TABLE `settings` (
  `variable` varchar(255) NOT NULL DEFAULT '',
  `value` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table data dump `settings`
--

INSERT INTO `settings` (`variable`, `value`) VALUES
('smfVersion', '2.0'),
I pasted this little fragment here because the dump of the 'settings' table has more data.

Should I clean up this database file and delete all the backup_ tables in it?

[Doug Heffernan]

Can you find the settings table and see what the value for smfVersion is set to? That is to see if the database has been upgraded to 2.0.x as well. Just to make sure.

I opened the database file and found this:

Code Select Expand

--
-- Table structure for the table `settings`
--

CREATE TABLE `settings` (
  `variable` varchar(255) NOT NULL DEFAULT '',
  `value` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table data dump `settings`
--

INSERT INTO `settings` (`variable`, `value`) VALUES
('smfVersion', '2.0'),
I pasted this little fragment here because the dump of the 'settings' table has more data.

Should I clean up this database file and delete all the backup_ tables in it?

Can you try something first? Overwrite your forum files with those from the large 2.0.19 upgrade package and then run the upgrader. Hopefully this will sort out the issues. If you will run into errors again, post them here.

Regarding the backup_ tables, I would leave them be for the time being. After you have your forum up and running then you can delete them.

[jsx]

I uploaded SMF 2.0.19 Large upgrade files and two clean files Settings.php, Settings_bak.php from SMF 2.0.19 package, because these two files in the file copy are from SMF 1.1.x

And after trying to enter the forum, this information appeared:

Table 'xx.settings' doesn't exist

This is weird because this table exists in the database file. But after uploading in phpMyAdmin, this table cannot be seen.

[@rjen]

Did you actually run the upgrader?

With a clean settings.php file you may have removed the database credentials...

[Doug Heffernan]

I uploaded SMF 2.0.19 Large upgrade files and two clean files Settings.php, Settings_bak.php from SMF 2.0.19 package, because these two files in the file copy are from SMF 1.1.x

And after trying to enter the forum, this information appeared:

Table 'xx.settings' doesn't exist

This is weird because this table exists in the database file. But after uploading in phpMyAdmin, this table cannot be seen.

You should not have overwritten the Settings.php file because that file holds all the database info. You can run the repair_settings.php tool to recreate it.

Did you make a backup of that file before overwritting it?

[jsx]

Did you actually run the upgrader?

With a clean settings.php file you may have removed the database credentials...

I ran the update via upgrade.php and it looks like this:



The login and password field is inactive.

You should not have overwritten the Settings.php file because that file holds all the database info. You can run the repair_settings.php tool to recreate it.

Did you make a backup of that file before overwritting it?

I understand. Yes, I restored the original Settings.php file

In the repair_settings tool it looks like this:

[Doug Heffernan]

The login and password field is inactive.

You can disable it and run the upgrade again. The instructions on how to do it are mentioned in the upgrade page.

[jsx]

Note: If necessary the above security check can be bypassed for users who may administrate a server but not have admin rights on the forum. In order to bypass the above check simply open "upgrade.php" in a text editor and replace "$disable_security = 0;" with "$disable_security = 1;" and refresh this page.

I entered 1 and can proceed to the next update step.

In the Upgrade Options step, only this option is selected:

Put the forum into maintenance mode during upgrade. (Customize)

Should I select this option?

Backup tables in your database with the prefix "backup_". (recommended!)

And should I select this option?

Empty error log before upgrading

[Doug Heffernan]

In the Upgrade Options step, only this option is selected:

Put the forum into maintenance mode during upgrade. (Customize)

Should I select this option?

Yes. It is best to have the forum closed off during the upgrade.

Should I select this option?

Backup tables in your database with the prefix "backup_". (recommended!)

And should I select this option?

You have already tables with the backup_ prefix in the database from the 1.1.x upgrade. Imo it would be better to make a manual backup of the database and then you can skip the backup option by the upgrader.

Empty error log before upgrading

Yes, you can check this option imo.

[jsx]

When I want to take the next step, this information appeared:

Unable to find members table!

I opened the database file and it is in it:

Code Select Expand

Table structure for the table `members`

CREATE TABLE `members` (
  `id_member` mediumint(8) UNSIGNED NOT NULL,
  `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',
  `buddy_list` mediumtext NOT NULL,
  `pm_ignore_list` mediumtext NOT NULL,
  `message_labels` mediumtext NOT NULL,
  `passwd` varchar(64) NOT NULL DEFAULT '',
  `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 '0',
  `show_online` tinyint(4) NOT NULL DEFAULT '1',
  `time_format` varchar(80) NOT NULL DEFAULT '',
  `signature` mediumtext 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) UNSIGNED 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 '',
  `mod_prefs` varchar(20) NOT NULL DEFAULT '',
  `warning` tinyint(4) NOT NULL DEFAULT '0',
  `ignore_boards` mediumtext NOT NULL,
  `passwd_flood` varchar(12) NOT NULL DEFAULT '',
  `new_pm` tinyint(3) NOT NULL DEFAULT '0',
  `pm_prefs` mediumint(8) NOT NULL DEFAULT '0',
  `openid_uri` mediumtext NOT NULL,
  `pm_receive_from` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  `facebook` varchar(45) NOT NULL DEFAULT '',
  `myspace` varchar(45) NOT NULL DEFAULT '',
  `twitter` varchar(45) NOT NULL DEFAULT '',
  `youtube` varchar(45) NOT NULL DEFAULT '',
  `deviantart` varchar(45) NOT NULL DEFAULT '',
  `googleplus` varchar(45) NOT NULL DEFAULT '',
  `linkedin` varchar(65) NOT NULL DEFAULT '',
  `pinterest` varchar(45) NOT NULL DEFAULT '',
  `fbname` varchar(255) NOT NULL DEFAULT '0',
  `fbid` varchar(255) NOT NULL DEFAULT '0',
  `fbpw` varchar(255) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Code Select Expand

Table data dump `members`

INSERT INTO `members` (`id_member`, `member_name`, `date_registered`, `posts`, `id_group`, `lngfile`, `last_login`, `real_name`, `instant_messages`, `unread_messages`, `buddy_list`, `pm_ignore_list`, `message_labels`, `passwd`, `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`, `mod_prefs`, `warning`, `ignore_boards`, `passwd_flood`, `new_pm`, `pm_prefs`, `openid_uri`, `pm_receive_from`, `facebook`, `myspace`, `twitter`, `youtube`, `deviantart`, `googleplus`, `linkedin`, `pinterest`, `fbname`, `fbid`, `fbpw`) VALUES
But these tables are not visible in phpmyAdmin.

I deleted all the backup_ tables in the database copy, I uploaded this database to phpmyAdmin and now the members table is visible. And I was able to run the forum and update to SMF 2.0.19

Thank you all for your help!

[Doug Heffernan]

I deleted all the backup_ tables in the database copy, I uploaded this database to phpmyAdmin and now the members table is visible. And I was able to run the forum and update to SMF 2.0.19

Thank you all for your help!

Happy to see that you upgraded the forum successfully. Marking this as solved.

[jsx]

I have one more question. Can I enter my own prefix for the database in the Server Settings in the Database and Paths tab in the Database Tables Prefix field? Is it safe?

[Doug Heffernan]

I have one more question. Can I enter my own prefix for the database in the Server Settings in the Database and Paths tab in the Database Tables Prefix field? Is it safe?

If you are going to change the prefix for the database tables, make sure to update the prefix in the Settings.php file to match the tables prefix, otherwise you will get a connection error.

[jsx]

It cannot be done that way. After changing database prefix in admin panel in Server Settings > Database and Paths > Database Tables Prefix and in Settings.php file this information appears:

Table 'xxx_test.smf2_themes' doesn't exist

I just thought that after changing the database prefix it would automatically change in all tables, but it doesn't work that way.

[Doug Heffernan]

It cannot be done that way. After changing database prefix in admin panel in Server Settings > Database and Paths > Database Tables Prefix and in Settings.php file this information appears:

Table 'xxx_test.smf2_themes' doesn't exist

I just thought that after changing the database prefix it would automatically change in all tables, but it doesn't work that way.

Did you change the prefix for all database tables as well? After you do that, you must update the Settings.php table with the same prefix that the database tables have.

[jsx]

Did you change the prefix for all database tables as well?

Where exactly does the prefix change for all tables?

[Arantor]

You have to go rename the tables themselves. So if the table is smf_settings, the prefix is smf_ and if you change the prefix to mytable_ you need to go rename each of the tables to match, e.g. mytable_admin_info_files, mytable_boards, mytable_categories and so on.

[Doug Heffernan]

Did you change the prefix for all database tables as well?

Where exactly does the prefix change for all tables?

You can change it for all the smf tables at once from phpmyadmin. To do so, first make a backup of your database, and then follow these steps:

1) Select the database that you have used to install your smf forum.

2) Select all the smf forum tables, if you have other tables for other scripts in that database. Otherwise you can select all the tables.

3) Click the 'With Slected' button at the bottom.

4) From the drop down options, Under Prefix, select the Add prefix to table and enter the desired prefix in the pop up screen that will be displayed. This is because from your previous posts, your tables do not have a prefix. If you had a prefix and wanted to change it, you should select the Replace table prefix option instead.

5) Enter the new prefix in the Settings.php file.

Hope it helps.

[jsx]

@Doug Heffernan

I did not think and did not even check that in phpMyAdmin I can change it so quickly. Thanks for the tip. Thanks for the help.

[Doug Heffernan]

@Doug Heffernan

I did not think and did not even check that in phpMyAdmin I can change it so quickly. Thanks for the tip. Thanks for the help.

You are welcome