Various issues with member_id surpassing mediumint(8)

David.. · December 25, 2022

On my community, member IDs now surpass the 8 digit value and because of this, there are numerous issues with new registrations.

It started with them being unable to create a new account, which thankfully was resolved here: https://invisioncommunity.com/forums/topic/471135-new-accounts-unable-to-be-created/#comment-2919659

After resolving the registrations, because of the member IDs surpassing 8 digits, it also caused issues with nexus, posts (both partially fixed but IPS complaining as seen below) and seems like I cannot start new PMs. When trying to make a new post or PM now, it double posts but the posts do not actually show which may be causing issues with the database. The same issue can also be seen via PM.

While changing to BIGINT(20) solves the issue, IPS support seems to be complaining about there being an issue with the database:

There are some problems with your database. Normally it is safe to try to fix these problems automatically however if your community is large, you may want to run the necessary queries manually. If so, the queries to run are:

UPDATE `core_validating` SET `member_id`=0 WHERE `member_id` IS NULL;
ALTER TABLE `core_validating` DROP INDEX `member_id`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member_id` (`member_id`);
UPDATE `core_pfields_content` SET `member_id`=0 WHERE `member_id` IS NULL;
CREATE TABLE `core_pfields_content_new` LIKE `core_pfields_content`;
ALTER TABLE `core_pfields_content_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`member_id`);
INSERT IGNORE INTO `core_pfields_content_new` SELECT * FROM `core_pfields_content`;
DROP TABLE `core_pfields_content`;
RENAME TABLE `core_pfields_content_new` TO `core_pfields_content`;
UPDATE `forums_posts` SET `author_id`=0 WHERE `author_id` IS NULL;
ALTER TABLE `forums_posts` DROP INDEX `author_id`, DROP INDEX `topic_id`, CHANGE COLUMN `author_id` `author_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `author_id` (`author_id`,`post_date`,`queued`), ADD KEY `topic_id` (`topic_id`,`queued`,`post_date`,`author_id`);
UPDATE `nexus_invoices` SET `i_id`='' WHERE `i_id` IS NULL;
ALTER TABLE `nexus_invoices` CHANGE COLUMN `i_id` `i_id` INT NOT NULL AUTO_INCREMENT ;

Hopefully someone from the IPS team ( @Matt, @Daniel F & others) can take some time to look into this or at least tell me where all the member ID columns that need to be changed to BIGINT are. This should be escalated as it seems to be a bug with the IPS software itself as pointed out by @teraßyte here: https://invisioncommunity.com/forums/topic/471135-new-accounts-unable-to-be-created/?do=findComment&comment=2919579

I have made sure the access information is up to date. Sorry this had to happen on Christmas day, but it is out of my control.

Thank you.

Edited December 25, 2022 by David..

Matt · December 25, 2022

You can ignore the support tool warning. We will revue member ID columns and increase to bigint where they are not already.

David.. · December 25, 2022

30 minutes ago, Matt said:

You can ignore the support tool warning. We will revue member ID columns and increase to bigint where they are not already.

Hi Matt! Thank you for replying and hope you're having a Merry Christmas!

Is this something that would require resolution on my live board the coming days, or would I have wait until the next IPS release? I can also upgrade them to bigint myself if you could get me a list of all the member_id columns.

Looking forward to your next reply.

Edited December 25, 2022 by David..

David.. · December 27, 2022

Hi again @Matt.

Just wanted to ask if there is an update to this since new members are unable to properly use the community, and it seems like they are creating topics (according to pending numbers in board index) but they are nowhere to be seen.

Similar issue for posts, PMs, commerce, and likely other parts of the community.

Looking forward to your reply.

Matt · December 27, 2022

Which version are you currently using? I've just checked most tables with a member ID, and they are already bigint(20).

David.. · December 27, 2022

3 hours ago, Matt said:

Which version are you currently using? I've just checked most tables with a member ID, and they are already bigint(20).

I’m on the latest, 4.7.5. Only have not applied the moderator bug patch.

teraßyte · December 27, 2022

@Matt When the tables where all updated from mediumint(8) to bigint(20) for member ids in a past version, the upgrade skipped this step and only new installation used bigint(20) from that point on.

If I recall correctly it was temporarily skipped because it could have caused issues altering big tables (mainly for big sites) in a minor (?) version. It was supposed to be added to the next major version, but it looks like it was never done until now.

That's also when the code I pointed out in the other topic was most likely added:

Quote

See file /system/Application/Application.php on line 2846:

/*  If the only difference is MEDIUMIT or INT should be BIGINT UNSIGNED - that's where we changed the member ID column. We don't need to flag it */

[...]

Edited December 27, 2022 by teraßyte

Stuart Silvester · December 27, 2022

3 hours ago, teraßyte said:

@Matt When the tables where all updated from mediumint(8) to bigint(20) for member ids in a past version, the upgrade skipped this step and only new installation used bigint(20) from that point on.

If I recall correctly it was temporarily skipped because it could have caused issues altering big tables (mainly for big sites) in a minor (?) version. It was supposed to be added to the next major version, but it looks like it was never done until now.

That's also when the code I pointed out in the other topic was most likely added:

Yes, that's the reason although it wasn't a forgotten temporary thing. For the majority of communities (even huge ones) they don't typically have enough members to get near the limit of MEDIUMINT and it wasn't worthwhile forcing a large number of database changes for those that wouldn't be affected.

We've got an internal issue for this so we'll make it easier to change these columns when appropriate.

For @David.. as a temporary work around, if you find the lines in the file @teraßyte mentioned ( /system/Application/Application.php on line 2846), temporarily delete the following and then go to the support page in the AdminCP.

							if ( isset( $differences['type'] ) and ( $differences['type']['is'] == 'MEDIUMINT' or $differences['type']['is'] == 'INT' ) and $differences['type']['shouldBe'] == 'BIGINT' )
							{
								unset( $differences['type'] );
								if ( isset( $differences['length'] ) )
								{
									unset( $differences['length'] );
								}
								if ( isset( $differences['unsigned'] ) and !$differences['unsigned']['is'] and $differences['unsigned']['shouldBe'] )
								{
									unset( $differences['unsigned'] );
								}
							}

When you're done running the queries it presents, you can restore that file back to default. As always, it's a good idea to make sure you have a good database backup before making changes to the structure.

David.. · December 28, 2022

Hi @Stuart Silvester!

These are the SQL queries the ACP support tool is telling me to run. I noticed there's a few DROPs in there so I thought I'd put them here before continuing to make sure.

UPDATE `core_sys_cp_sessions` SET `session_member_id`=0 WHERE `session_member_id` IS NULL;
ALTER TABLE `core_sys_cp_sessions` DROP INDEX `session_member_id`, CHANGE COLUMN `session_member_id` `session_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `session_member_id` (`session_member_id`);
UPDATE `core_validating` SET `member_id`=0 WHERE `member_id` IS NULL;
ALTER TABLE `core_validating` DROP INDEX `member_id`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member_id` (`member_id`);
UPDATE `core_pfields_content` SET `member_id`=0 WHERE `member_id` IS NULL;
CREATE TABLE `core_pfields_content_new` LIKE `core_pfields_content`;
ALTER TABLE `core_pfields_content_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`member_id`);
INSERT IGNORE INTO `core_pfields_content_new` SELECT * FROM `core_pfields_content`;
DROP TABLE `core_pfields_content`;
RENAME TABLE `core_pfields_content_new` TO `core_pfields_content`;
ALTER TABLE `core_admin_logs` DROP INDEX `member`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NULL DEFAULT 0 , ADD KEY `member` (`member_id`);
UPDATE `core_attachments` SET `attach_member_id`=0 WHERE `attach_member_id` IS NULL;
ALTER TABLE `core_attachments` DROP INDEX `attach_mid_size`, CHANGE COLUMN `attach_member_id` `attach_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `attach_mid_size` (`attach_member_id`,`attach_filesize`);
ALTER TABLE `core_sessions` DROP INDEX `login_type`, DROP INDEX `member_id`, DROP INDEX `ip_address`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NULL , ADD KEY `login_type` (`login_type`,`running_time`,`member_id`,`member_group`), ADD KEY `member_id` (`member_id`,`ip_address`), ADD KEY `ip_address` (`ip_address`,`member_id`);
UPDATE `core_message_topics` SET `mt_to_member_id`=0 WHERE `mt_to_member_id` IS NULL;
ALTER TABLE `core_message_topics` CHANGE COLUMN `mt_to_member_id` `mt_to_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
UPDATE `core_message_posts` SET `msg_author_id`=0 WHERE `msg_author_id` IS NULL;
ALTER TABLE `core_message_posts` DROP INDEX `msg_author_id`, CHANGE COLUMN `msg_author_id` `msg_author_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `msg_author_id` (`msg_author_id`);
UPDATE `core_message_topic_user_map` SET `map_user_id`=0 WHERE `map_user_id` IS NULL;
CREATE TABLE `core_message_topic_user_map_new` LIKE `core_message_topic_user_map`;
ALTER TABLE `core_message_topic_user_map_new` DROP INDEX `map_main`, DROP INDEX `map_user`, CHANGE COLUMN `map_user_id` `map_user_id` BIGINT NOT NULL DEFAULT 0 , ADD UNIQUE KEY `map_main` (`map_user_id`,`map_topic_id`), ADD KEY `map_user` (`map_user_id`,`map_user_active`,`map_last_topic_reply`);
INSERT IGNORE INTO `core_message_topic_user_map_new` SELECT * FROM `core_message_topic_user_map`;
DROP TABLE `core_message_topic_user_map`;
RENAME TABLE `core_message_topic_user_map_new` TO `core_message_topic_user_map`;
UPDATE `core_rc_comments` SET `comment_by`=0 WHERE `comment_by` IS NULL;
ALTER TABLE `core_rc_comments` DROP INDEX `member`, CHANGE COLUMN `comment_by` `comment_by` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`comment_by`);
UPDATE `core_rc_reports` SET `report_by`=0 WHERE `report_by` IS NULL;
ALTER TABLE `core_rc_reports` DROP INDEX `report_by`, CHANGE COLUMN `report_by` `report_by` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `report_by` (`report_by`);
UPDATE `core_item_markers` SET `item_member_id`=0 WHERE `item_member_id` IS NULL;
CREATE TABLE `core_item_markers_new` LIKE `core_item_markers`;
ALTER TABLE `core_item_markers_new` DROP INDEX `combo_key`, DROP INDEX `marker_index`, CHANGE COLUMN `item_member_id` `item_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD UNIQUE KEY `combo_key` (`item_key`,`item_member_id`,`item_app`), ADD KEY `marker_index` (`item_member_id`,`item_app`,`item_app_key_1`);
INSERT IGNORE INTO `core_item_markers_new` SELECT * FROM `core_item_markers`;
DROP TABLE `core_item_markers`;
RENAME TABLE `core_item_markers_new` TO `core_item_markers`;
UPDATE `core_upgrade_history` SET `upgrade_mid`=0 WHERE `upgrade_mid` IS NULL;
ALTER TABLE `core_upgrade_history` CHANGE COLUMN `upgrade_mid` `upgrade_mid` BIGINT NOT NULL DEFAULT 0 ;
ALTER TABLE `core_members_warn_logs` DROP INDEX `wl_date`, CHANGE COLUMN `wl_member` `wl_member` BIGINT UNSIGNED NULL , ADD KEY `wl_date` (`wl_member`,`wl_date`), DROP INDEX `wl_moderator`, CHANGE COLUMN `wl_moderator` `wl_moderator` BIGINT UNSIGNED NULL , ADD KEY `wl_moderator` (`wl_moderator`);
UPDATE `core_error_logs` SET `log_member`=0 WHERE `log_member` IS NULL;
ALTER TABLE `core_error_logs` DROP INDEX `member`, CHANGE COLUMN `log_member` `log_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`log_member`);
UPDATE `core_ignored_users` SET `ignore_owner_id`=0 WHERE `ignore_owner_id` IS NULL;
UPDATE `core_ignored_users` SET `ignore_ignore_id`=0 WHERE `ignore_ignore_id` IS NULL;
ALTER TABLE `core_ignored_users` DROP INDEX `ignore_owner_id`, CHANGE COLUMN `ignore_owner_id` `ignore_owner_id` BIGINT NOT NULL DEFAULT 0 , ADD KEY `ignore_owner_id` (`ignore_owner_id`), DROP INDEX `ignore_ignore_id`, CHANGE COLUMN `ignore_ignore_id` `ignore_ignore_id` BIGINT NOT NULL DEFAULT 0 , ADD KEY `ignore_ignore_id` (`ignore_ignore_id`);
UPDATE `core_announcements` SET `announce_member_id`=0 WHERE `announce_member_id` IS NULL;
ALTER TABLE `core_announcements` DROP INDEX `member`, CHANGE COLUMN `announce_member_id` `announce_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`announce_member_id`);
UPDATE `core_leaders` SET `leader_type_id`=0 WHERE `leader_type_id` IS NULL;
CREATE TABLE `core_leaders_new` LIKE `core_leaders`;
ALTER TABLE `core_leaders_new` DROP INDEX `leader_type`, CHANGE COLUMN `leader_type_id` `leader_type_id` BIGINT NOT NULL DEFAULT 0 COMMENT 'Member or group ID', ADD UNIQUE KEY `leader_type` (`leader_type`,`leader_type_id`);
INSERT IGNORE INTO `core_leaders_new` SELECT * FROM `core_leaders`;
DROP TABLE `core_leaders`;
RENAME TABLE `core_leaders_new` TO `core_leaders`;
ALTER TABLE `core_advertisements` DROP INDEX `member`, CHANGE COLUMN `ad_member` `ad_member` BIGINT UNSIGNED NULL COMMENT 'If created by Nexus, the customer ID', ADD KEY `member` (`ad_member`);
UPDATE `core_edit_history` SET `member`=0 WHERE `member` IS NULL;
ALTER TABLE `core_edit_history` DROP INDEX `member`, CHANGE COLUMN `member` `member` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The ID number of the member making the edit', ADD KEY `member` (`member`);
UPDATE `core_tags` SET `tag_member_id`=0 WHERE `tag_member_id` IS NULL;
ALTER TABLE `core_tags` DROP INDEX `tag_member_id`, CHANGE COLUMN `tag_member_id` `tag_member_id` BIGINT NOT NULL DEFAULT 0 , ADD KEY `tag_member_id` (`tag_member_id`);
UPDATE `core_reputation_index` SET `member_received`=0 WHERE `member_received` IS NULL;
ALTER TABLE `core_reputation_index` DROP INDEX `app`, DROP INDEX `member_received`, DROP INDEX `leaderboard`, CHANGE COLUMN `member_received` `member_received` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `app` (`app`,`type`,`type_id`,`member_id`,`member_received`), ADD KEY `member_received` (`member_received`,`rep_date`,`rep_class`,`rep_rating`), ADD KEY `leaderboard` (`rep_class`,`rep_date`,`member_received`,`rep_rating`);
UPDATE `core_follow` SET `follow_member_id`=0 WHERE `follow_member_id` IS NULL;
ALTER TABLE `core_follow` DROP INDEX `follow_member_id`, DROP INDEX `follow_stream`, DROP INDEX `follow_added_extra`, DROP INDEX `digest_task`, CHANGE COLUMN `follow_member_id` `follow_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `follow_member_id` (`follow_member_id`,`follow_visible`,`follow_added`), ADD KEY `follow_stream` (`follow_app`(73),`follow_area`(98),`follow_member_id`), ADD KEY `follow_added_extra` (`follow_added`,`follow_is_anon`,`follow_member_id`), ADD KEY `digest_task` (`follow_notify_do`,`follow_notify_freq`,`follow_notify_sent`,`follow_member_id`);
UPDATE `core_polls` SET `starter_id`=0 WHERE `starter_id` IS NULL;
ALTER TABLE `core_polls` DROP INDEX `member`, CHANGE COLUMN `starter_id` `starter_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`starter_id`);
UPDATE `core_voters` SET `member_id`=0 WHERE `member_id` IS NULL;
ALTER TABLE `core_voters` DROP INDEX `member`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`member_id`,`poll`);
UPDATE `core_member_status_updates` SET `status_member_id`=0 WHERE `status_member_id` IS NULL;
UPDATE `core_member_status_updates` SET `status_author_id`=0 WHERE `status_author_id` IS NULL;
ALTER TABLE `core_member_status_updates` DROP INDEX `s_hash`, DROP INDEX `status_author_lookup`, DROP INDEX `status_member_id`, CHANGE COLUMN `status_member_id` `status_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `s_hash` (`status_member_id`,`status_hash`,`status_date`), ADD KEY `status_author_lookup` (`status_author_id`,`status_member_id`,`status_date`), ADD KEY `status_member_id` (`status_member_id`,`status_approved`,`status_date`), CHANGE COLUMN `status_author_id` `status_author_id` BIGINT NOT NULL DEFAULT 0 ;
UPDATE `core_member_status_replies` SET `reply_member_id`=0 WHERE `reply_member_id` IS NULL;
ALTER TABLE `core_member_status_replies` DROP INDEX `reply_member_id`, DROP INDEX `reply_status_count`, CHANGE COLUMN `reply_member_id` `reply_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `reply_member_id` (`reply_member_id`), ADD KEY `reply_status_count` (`reply_status_id`,`reply_member_id`);
ALTER TABLE `core_sys_social_groups` DROP INDEX `owner`, CHANGE COLUMN `owner_id` `owner_id` BIGINT UNSIGNED NULL , ADD KEY `owner` (`owner_id`);
ALTER TABLE `core_sys_social_group_members` DROP INDEX `member`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NULL , ADD KEY `member` (`member_id`);
UPDATE `core_soft_delete_log` SET `sdl_obj_member_id`=0 WHERE `sdl_obj_member_id` IS NULL;
ALTER TABLE `core_soft_delete_log` DROP INDEX `member`, CHANGE COLUMN `sdl_obj_member_id` `sdl_obj_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `member` (`sdl_obj_member_id`);
UPDATE `core_search_index` SET `index_author`=0 WHERE `index_author` IS NULL;
ALTER TABLE `core_search_index` DROP INDEX `author_posted_in`, DROP INDEX `author_lookup`, CHANGE COLUMN `index_author` `index_author` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The object author id', ADD KEY `author_posted_in` (`index_author`,`index_item_index_id`), ADD KEY `author_lookup` (`index_author`,`index_class`(150),`index_hidden`,`index_date_updated`,`index_date_commented`), DROP INDEX `index_item_author`, CHANGE COLUMN `index_item_author` `index_item_author` BIGINT UNSIGNED NULL COMMENT 'The author of the item', ADD KEY `index_item_author` (`index_item_author`,`index_date_commented`);
ALTER TABLE `core_streams` DROP INDEX `member`, CHANGE COLUMN `member` `member` BIGINT NULL COMMENT 'The member who created the stream or null for a global stream', ADD KEY `member` (`member`);
UPDATE `core_log` SET `member_id`=0 WHERE `member_id` IS NULL;
ALTER TABLE `core_log` CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Member that triggered the error, if any';
UPDATE `core_search_index_item_map` SET `index_author_id`=0 WHERE `index_author_id` IS NULL;
CREATE TABLE `core_search_index_item_map_new` LIKE `core_search_index_item_map`;
ALTER TABLE `core_search_index_item_map_new` DROP INDEX `search_map`, CHANGE COLUMN `index_author_id` `index_author_id` BIGINT NOT NULL DEFAULT 0 , ADD UNIQUE KEY `search_map` (`index_item_id`,`index_class`,`index_author_id`);
INSERT IGNORE INTO `core_search_index_item_map_new` SELECT * FROM `core_search_index_item_map`;
DROP TABLE `core_search_index_item_map`;
RENAME TABLE `core_search_index_item_map_new` TO `core_search_index_item_map`;
UPDATE `core_security_answers` SET `answer_member_id`='' WHERE `answer_member_id` IS NULL;
CREATE TABLE `core_security_answers_new` LIKE `core_security_answers`;
ALTER TABLE `core_security_answers_new` DROP INDEX `question_and_member`, DROP INDEX `member`, CHANGE COLUMN `answer_member_id` `answer_member_id` BIGINT UNSIGNED NOT NULL COMMENT 'The member ID this answer is for', ADD UNIQUE KEY `question_and_member` (`answer_question_id`,`answer_member_id`), ADD KEY `member` (`answer_member_id`);
INSERT IGNORE INTO `core_security_answers_new` SELECT * FROM `core_security_answers`;
DROP TABLE `core_security_answers`;
RENAME TABLE `core_security_answers_new` TO `core_security_answers`;
UPDATE `core_reputation_leaderboard_history` SET `leader_member_id`=0 WHERE `leader_member_id` IS NULL;
CREATE TABLE `core_reputation_leaderboard_history_new` LIKE `core_reputation_leaderboard_history`;
ALTER TABLE `core_reputation_leaderboard_history_new` DROP INDEX `leader_date`, DROP INDEX `first_place`, CHANGE COLUMN `leader_member_id` `leader_member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD UNIQUE KEY `leader_date` (`leader_date`,`leader_member_id`), ADD KEY `first_place` (`leader_position`,`leader_member_id`);
INSERT IGNORE INTO `core_reputation_leaderboard_history_new` SELECT * FROM `core_reputation_leaderboard_history`;
DROP TABLE `core_reputation_leaderboard_history`;
RENAME TABLE `core_reputation_leaderboard_history_new` TO `core_reputation_leaderboard_history`;
UPDATE `forums_forums` SET `last_poster_id`=0 WHERE `last_poster_id` IS NULL;
ALTER TABLE `forums_forums` CHANGE COLUMN `last_poster_id` `last_poster_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
UPDATE `forums_topics` SET `starter_id`=0 WHERE `starter_id` IS NULL;
UPDATE `forums_topics` SET `last_poster_id`=0 WHERE `last_poster_id` IS NULL;
ALTER TABLE `forums_topics` DROP INDEX `starter_id`, CHANGE COLUMN `starter_id` `starter_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `starter_id` (`starter_id`,`forum_id`,`approved`,`start_date`), CHANGE COLUMN `last_poster_id` `last_poster_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
UPDATE `forums_posts` SET `author_id`=0 WHERE `author_id` IS NULL;
ALTER TABLE `forums_posts` DROP INDEX `author_id`, DROP INDEX `topic_id`, CHANGE COLUMN `author_id` `author_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `author_id` (`author_id`,`post_date`,`queued`), ADD KEY `topic_id` (`topic_id`,`queued`,`post_date`,`author_id`);
CREATE TABLE `forums_question_ratings_new` LIKE `forums_question_ratings`;
ALTER TABLE `forums_question_ratings_new` DROP INDEX `member_rating`, DROP INDEX `topic`, CHANGE COLUMN `member` `member` BIGINT UNSIGNED NULL COMMENT 'The member giving the rating', ADD UNIQUE KEY `member_rating` (`member`,`topic`), ADD KEY `topic` (`topic`,`member`);
INSERT IGNORE INTO `forums_question_ratings_new` SELECT * FROM `forums_question_ratings`;
DROP TABLE `forums_question_ratings`;
RENAME TABLE `forums_question_ratings_new` TO `forums_question_ratings`;
UPDATE `forums_archive_posts` SET `archive_author_id`=0 WHERE `archive_author_id` IS NULL;
ALTER TABLE `forums_archive_posts` DROP INDEX `archive_author_id`, CHANGE COLUMN `archive_author_id` `archive_author_id` BIGINT NOT NULL DEFAULT 0 , ADD KEY `archive_author_id` (`archive_author_id`);
UPDATE `nexus_invoices` SET `i_id`='' WHERE `i_id` IS NULL;
UPDATE `nexus_invoices` SET `i_member`=0 WHERE `i_member` IS NULL;
ALTER TABLE `nexus_invoices` CHANGE COLUMN `i_id` `i_id` INT NOT NULL AUTO_INCREMENT , DROP INDEX `i_member`, CHANGE COLUMN `i_member` `i_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `i_member` (`i_member`);
UPDATE `nexus_transactions` SET `t_member`=0 WHERE `t_member` IS NULL;
ALTER TABLE `nexus_transactions` DROP INDEX `t_member`, CHANGE COLUMN `t_member` `t_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `t_member` (`t_member`,`t_status`,`t_fraud_blocked`);
UPDATE `nexus_customers` SET `member_id`=0 WHERE `member_id` IS NULL;
CREATE TABLE `nexus_customers_new` LIKE `nexus_customers`;
ALTER TABLE `nexus_customers_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`member_id`);
INSERT IGNORE INTO `nexus_customers_new` SELECT * FROM `nexus_customers`;
DROP TABLE `nexus_customers`;
RENAME TABLE `nexus_customers_new` TO `nexus_customers`;
UPDATE `nexus_purchases` SET `ps_member`=0 WHERE `ps_member` IS NULL;
ALTER TABLE `nexus_purchases` DROP INDEX `ps_member`, CHANGE COLUMN `ps_member` `ps_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `ps_member` (`ps_member`), CHANGE COLUMN `ps_pay_to` `ps_pay_to` BIGINT UNSIGNED NULL ;
UPDATE `nexus_payouts` SET `po_member`=0 WHERE `po_member` IS NULL;
ALTER TABLE `nexus_payouts` DROP INDEX `po_member`, CHANGE COLUMN `po_member` `po_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `po_member` (`po_member`,`po_date`), CHANGE COLUMN `po_processed_by` `po_processed_by` BIGINT UNSIGNED NULL ;
UPDATE `nexus_support_replies` SET `reply_member`=0 WHERE `reply_member` IS NULL;
ALTER TABLE `nexus_support_replies` DROP INDEX `reply_member`, CHANGE COLUMN `reply_member` `reply_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `reply_member` (`reply_member`);
UPDATE `nexus_support_requests` SET `r_member`=0 WHERE `r_member` IS NULL;
UPDATE `nexus_support_requests` SET `r_last_reply_by`=0 WHERE `r_last_reply_by` IS NULL;
UPDATE `nexus_support_requests` SET `r_staff`=0 WHERE `r_staff` IS NULL;
ALTER TABLE `nexus_support_requests` DROP INDEX `r_member`, CHANGE COLUMN `r_member` `r_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `r_member` (`r_member`), DROP INDEX `r_last_reply_by`, CHANGE COLUMN `r_last_reply_by` `r_last_reply_by` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `r_last_reply_by` (`r_last_reply_by`), DROP INDEX `r_staff`, CHANGE COLUMN `r_staff` `r_staff` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `r_staff` (`r_staff`);
UPDATE `nexus_support_tracker` SET `member_id`=0 WHERE `member_id` IS NULL;
CREATE TABLE `nexus_support_tracker_new` LIKE `nexus_support_tracker`;
ALTER TABLE `nexus_support_tracker_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`member_id`,`request_id`);
INSERT IGNORE INTO `nexus_support_tracker_new` SELECT * FROM `nexus_support_tracker`;
DROP TABLE `nexus_support_tracker`;
RENAME TABLE `nexus_support_tracker_new` TO `nexus_support_tracker`;
UPDATE `nexus_support_views` SET `view_member`=0 WHERE `view_member` IS NULL;
CREATE TABLE `nexus_support_views_new` LIKE `nexus_support_views`;
ALTER TABLE `nexus_support_views_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `view_member` `view_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`view_rid`,`view_member`);
INSERT IGNORE INTO `nexus_support_views_new` SELECT * FROM `nexus_support_views`;
DROP TABLE `nexus_support_views`;
RENAME TABLE `nexus_support_views_new` TO `nexus_support_views`;
UPDATE `nexus_support_stock_actions` SET `action_staff`=0 WHERE `action_staff` IS NULL;
ALTER TABLE `nexus_support_stock_actions` CHANGE COLUMN `action_staff` `action_staff` BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
ALTER TABLE `nexus_support_ratings` DROP INDEX `rating_from`, CHANGE COLUMN `rating_from` `rating_from` BIGINT UNSIGNED NULL , ADD KEY `rating_from` (`rating_from`), DROP INDEX `rating_staff`, CHANGE COLUMN `rating_staff` `rating_staff` BIGINT UNSIGNED NULL , ADD KEY `rating_staff` (`rating_staff`);
ALTER TABLE `nexus_licensekeys` DROP INDEX `lkey_member`, CHANGE COLUMN `lkey_member` `lkey_member` BIGINT UNSIGNED NULL , ADD KEY `lkey_member` (`lkey_member`);
ALTER TABLE `nexus_reviews` DROP INDEX `review_author_id`, CHANGE COLUMN `review_author_id` `review_author_id` BIGINT UNSIGNED NULL , ADD KEY `review_author_id` (`review_author_id`), CHANGE COLUMN `review_edit_member_id` `review_edit_member_id` BIGINT UNSIGNED NULL ;
UPDATE `nexus_alternate_contacts` SET `main_id`=0 WHERE `main_id` IS NULL;
UPDATE `nexus_alternate_contacts` SET `alt_id`=0 WHERE `alt_id` IS NULL;
CREATE TABLE `nexus_alternate_contacts_new` LIKE `nexus_alternate_contacts`;
ALTER TABLE `nexus_alternate_contacts_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `main_id` `main_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`main_id`,`alt_id`), DROP INDEX `alt_id`, CHANGE COLUMN `alt_id` `alt_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `alt_id` (`alt_id`);
INSERT IGNORE INTO `nexus_alternate_contacts_new` SELECT * FROM `nexus_alternate_contacts`;
DROP TABLE `nexus_alternate_contacts`;
RENAME TABLE `nexus_alternate_contacts_new` TO `nexus_alternate_contacts`;
ALTER TABLE `nexus_donate_logs` CHANGE COLUMN `dl_member` `dl_member` BIGINT UNSIGNED NULL ;
UPDATE `nexus_notes` SET `note_member`=0 WHERE `note_member` IS NULL;
UPDATE `nexus_notes` SET `note_author`=0 WHERE `note_author` IS NULL;
ALTER TABLE `nexus_notes` DROP INDEX `note_member`, CHANGE COLUMN `note_member` `note_member` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD KEY `note_member` (`note_member`), CHANGE COLUMN `note_author` `note_author` BIGINT UNSIGNED NOT NULL DEFAULT 0 ;
UPDATE `nexus_support_request_log` SET `rlog_member`='' WHERE `rlog_member` IS NULL;
ALTER TABLE `nexus_support_request_log` DROP INDEX `rlog_member`, CHANGE COLUMN `rlog_member` `rlog_member` BIGINT UNSIGNED NOT NULL COMMENT 'The member ID of the staff performing the action', ADD KEY `rlog_member` (`rlog_member`);
ALTER TABLE `nexus_billing_agreements` DROP INDEX `member`, CHANGE COLUMN `ba_member` `ba_member` BIGINT UNSIGNED NULL COMMENT 'Customer ID', ADD KEY `member` (`ba_member`);
ALTER TABLE `nexus_support_streams` DROP INDEX `list`, CHANGE COLUMN `stream_owner` `stream_owner` BIGINT UNSIGNED NULL DEFAULT 0 COMMENT 'The ID number of the staff member who owns the stream', ADD KEY `list` (`stream_owner`,`stream_position`);
UPDATE `nexus_support_staff_preferences` SET `staff_id`='' WHERE `staff_id` IS NULL;
ALTER TABLE `nexus_support_staff_preferences` CHANGE COLUMN `staff_id` `staff_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID Number of staff member';
CREATE TABLE `nexus_support_staff_dpt_order_new` LIKE `nexus_support_staff_dpt_order`;
ALTER TABLE `nexus_support_staff_dpt_order_new` DROP INDEX `staff_and_department`, CHANGE COLUMN `staff_id` `staff_id` BIGINT UNSIGNED NULL DEFAULT 0 COMMENT 'The staff\'s member ID', ADD UNIQUE KEY `staff_and_department` (`staff_id`,`department_id`);
INSERT IGNORE INTO `nexus_support_staff_dpt_order_new` SELECT * FROM `nexus_support_staff_dpt_order`;
DROP TABLE `nexus_support_staff_dpt_order`;
RENAME TABLE `nexus_support_staff_dpt_order_new` TO `nexus_support_staff_dpt_order`;

Is anyone from IPS able to do this? I'd feel more comfortable if that were to be the case.

Randy Calvert · December 28, 2022

The drops are correct. You might notice most of them are part of creating new tables then deleting old ones and then renaming the newly created one.

26 minutes ago, David.. said:

CREATE TABLE `nexus_support_tracker_new` LIKE `nexus_support_tracker`;
ALTER TABLE `nexus_support_tracker_new` DROP INDEX `PRIMARY`, CHANGE COLUMN `member_id` `member_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 , ADD PRIMARY KEY (`member_id`,`request_id`);
INSERT IGNORE INTO `nexus_support_tracker_new` SELECT * FROM `nexus_support_tracker`;
DROP TABLE `nexus_support_tracker`;
RENAME TABLE `nexus_support_tracker_new` TO `nexus_support_tracker`;

These are things you should be doing yourself as a self hosted customer. If you are not comfortable doing them, you should contact your host. IPS does not have direct access to your database. These are BEST run via SSH. In many cases they can be run via phpMyAdmin but there are cases where queries might time out via a web browser. As a result, it’s something IPS notes for you to be prepared to do just to be safe.

David.. · December 28, 2022

30 minutes ago, Randy Calvert said:

The drops are correct. You might notice most of them are part of creating new tables then deleting old ones and then renaming the newly created one.

These are things you should be doing yourself as a self hosted customer. If you are not comfortable doing them, you should contact your host. IPS does not have direct access to your database. These are BEST run via SSH. In many cases they can be run via phpMyAdmin but there are cases where queries might time out via a web browser. As a result, it’s something IPS notes for you to be prepared to do just to be safe.

I understand, I guess just a review of all the SQL queries before proceeding would suffice.

Randy Calvert · December 28, 2022

These all look regular and safe. As a recommendation, take a backup of your database before running the commands. This will protect you should something go horribly wrong.

Edited December 28, 2022 by Randy Calvert

Marc Stridgen · December 28, 2022

Those would be correct. Ensure you run all those in order of course, as it inserts the data to another table, and then drops it. And of course, as mentioned above, back up

Various issues with member_id surpassing mediumint(8)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Recently Browsing 0 members

Upcoming Events

Trending Content