Default coluns in core_members

[Gabriel Torres]

Hello,

I am in the process of cleaning-up our database, and as we run IPS for for ages now, I believe our core_members table has several columns that were used in previous versions and are no longer needed.

I compared the current structure of our core_members table with the default columns listed in the /applications/core/data/schema.json file, and I ended up with the list below of columns that exist in core_members but aren't listed in the schema.json file.

| misc                      | varchar(128)         | YES  |     | NULL    |
| member_login_key_expire   | int(10)              | NO   |     | 0       |
| has_blog                  | text                 | YES  |     | NULL    |
| blogs_recache             | tinyint(1)           | YES  |     | NULL    |
| has_gallery               | tinyint(1)           | NO   |     | 0       |
| conv_password             | varchar(128)         | YES  |     | NULL    |
| allow_multiple_accounts   | tinyint(1)           | NO   |     | 0       |
| access_attempts           | tinyint(1)           | NO   |     | 0       |
| registration_attempts     | text                 | YES  |     | NULL    |
| unique_machines           | text                 | YES  |     | NULL    |
| google_access_token       | varchar(255)         | YES  |     | NULL    |
| google_access_expires     | int(10)              | YES  |     | 0       |
| google_refresh_token      | varchar(255)         | YES  |     | NULL    |
| google_pass_reset         | int(1)               | YES  |     | 0       |
| google_uid                | varchar(255)         | YES  |     | NULL    |
| referred_by               | int(11)              | NO   |     | 0       |
| cm_no_sev                 | tinyint(1)           | YES  |     | 0       |
| cim_profile_id            | varchar(32)          | YES  |     |         |
| cim_payment_id            | int(10)              | YES  |     | 0       |
| cim_method                | int(5)               | NO   |     | 0       |
| cm_return_group           | smallint(3)          | YES  |     | 0       |
| cm_credits                | text                 | YES  |     | NULL    |
| cm_reg                    | int(11)              | NO   |     | 0       |
| gallery_perms             | varchar(10)          | NO   |     | 1:1:1   |
| pp_setting_count_visitors | int(2)               | NO   |     | 1       |

Please let me know which columns are safe to drop.

Thank you in advance,

Gabe

[Gabriel Torres]

Hi,

What I discovered so far:

member_login_key_expire

This was removed in a previous IPS version and it is safe to be dropped. See core/setup/upg_101100/queries.json.

allow_multiple_accounts
access_attempts
registration_attempts
unique_machines

These belong to an app that we removed in the past, so they are safe to drop. App: CJ Duplicate Member Logger.

cim_profile_id
cim_payment_id
cim_method

These belong to Nexus and should be kept as per applications/nexus/setup/upg_11100/queries.json.

cm_no_sev
cm_return_group
cm_credits

These belong to Nexus and should be kept as per applications/nexus/setup/upg_10102/queries.json.

However, there is another related column that from what I could understand, was replaced with a cookie. I couldn't find a reference to this column in the source files. I don't know if it safe to drop it from core_members:

cm_reg

This other column seems to have been moved to the core_referrals table according to applications/core/data/schema.json. I don't know if it is safe to drop it from core_members:

referred_by 

These two seem to have became bitoptions. I don't know if it is safe to drop them from core_members:

gallery_perms
pp_setting_count_visitors

The columns below I couldn't find any reference in the source files. I don't know if it is safe to drop them from core_members:

google_access_token
google_access_expires
google_refresh_token
google_pass_reset
google_uid
has_blog
has_gallery
conv_password
misc

If someone could review these I'd appreciate it!

Thank you in advance,

Gabe.

[Adriano Faria]

Quote

has_blog
has_gallery

These two were used by IPS on IP.Board 3.X. Tried to find the step they were removed but couldn't find it; probably on 4.0.0.

[Martin A.]

'has_blog' and 'has_gallery' is from IPB 3. Was used as a way to check if a member had a blog or gallery before displaying links to manage them

<if test="hasblog:|:$author['has_blog'] AND IPSLib::appIsInstalled( 'blog' )">
	<li><a href='{parse url="app=blog&amp;module=display&amp;section=blog&amp;show_members_blogs={$author['member_id']}" base="public"}' title='{$this->lang->words['view_blog']}'>{parse replacement="blog_link"}</a></li>
</if>
<if test="hasgallery:|:$author['has_gallery'] AND IPSLib::appIsInstalled( 'gallery' )">
	<li><a href='{parse url="app=gallery&amp;module=user&amp;section=user&amp;user={$author['member_id']}" base="public"}' title='{$this->lang->words['view_gallery']}'>{parse replacement="gallery_link"}</a></li>
</if>

 'conv_password' and 'misc' is also IPB3. Looks like this was the password hash from whatever software you converted from.

/**
	 * Joomla!
	 */
	private function joomla ( $username, $email, $password )
	{
		if ( $this->parent->_memberData['conv_password'] == md5 ( $password . $this->parent->_memberData['misc'] ) )
		{
			return true;
		}
		else
		{
			return false;
		}
	}

All those google_* columns must be from a 3rd party

Edited July 17, 2023 by Martin A.

[Miss_B]

Also misc is not part of the core_members table by default a.f.a.i.k.

[Ryan Ashbrook]

These are a part of the converters application.

conv_password
misc

 

[Miss_B]

40 minutes ago, Ryan Ashbrook said:

These are a part of the converters application.

I haven't used it myself, hence why I was not aware of that. Thank you for the clarification.

[Gabriel Torres]

Thanks for the help, guys!

@Ryan Ashbrook Are the conv_password and misc columns safe to remove? We moved from vBulletin to IPS 10+ years ago... LOL

I also need some advice if it is safe to remove these:

cm_reg
referred_by
gallery_perms
pp_setting_count_visitors

Thanks!

6 hours ago, Martin A. said:

All those google_* columns must be from a 3rd party

Thanks, I was able to find their origin here in an old spreadsheet. They were from a plugin called "Sign in through Google", available in the 3.4 era, before this functionality was incorporated in the core software.