Items missing after upgrading from 3.1.1.60 - 3.1.2.45

[deanbruhn]

Steps to reproduce

1. Upload and run update through web console, no errors appear.
2. After initial upgrade all data will be there.
3. Wait one day so overnight tasks can run
4. Items will no longer be accessible in TeamPass.
5. The items still appear in the database on my test system.
6. I have the item counts enabled on the directories and the counts still reflect the missing items.

Expected behaviour

Tell us what should happen
The Items should still be in the system and accessible.

Actual behaviour

Tell us what happens instead
They are no longer in the UI

Server configuration

Operating system:
Debian 11.7

Web server:
Server version: Apache/2.4.56 (Debian)
Server built: 2023-04-02T03:06:01

Database:
mariadb Ver 15.1 Distrib 10.7.8-MariaDB, for debian-linux-gnu (x86_64) using readline EditLine wrapper

PHP version:
PHP 8.2.14 (cli) (built: Dec 21 2023 20:18:00) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.2.14, Copyright (c) Zend Technologies
with Zend OPcache v8.2.14, Copyright (c), by Zend Technologies

Teampass version:
Original - 3.1.1.60
Upgraded - 3.1.2.45

Teampass configuration file:

'10', 'enable_favourites' => '1', 'show_last_items' => '1', 'enable_pf_feature' => '1', 'log_connections' => '0', 'log_accessed' => '1', 'time_format' => 'H:i:s', 'date_format' => 'm/d/Y', 'duplicate_folder' => '1', 'item_duplicate_in_same_folder' => '1', 'duplicate_item' => '1', 'number_of_used_pw' => '6', 'manager_edit' => '1', 'cpassman_dir' => '/var/www/html', 'cpassman_url' => 'https:///', 'favicon' => 'https:///index.php/s/yFEYxP8gXeLfFCs/download/favicon.ico', 'path_to_upload_folder' => '/var/www/html/upload', 'url_to_upload_folder' => 'https:///upload', 'path_to_files_folder' => '/var/www/html/files', 'url_to_files_folder' => 'https:///files', 'activate_expiration' => '0', 'pw_life_duration' => '0', 'maintenance_mode' => '0', 'enable_sts' => '0', 'encryptClientServer' => '1', 'cpassman_version' => '2.1.27', 'ldap_mode' => '0', 'ldap_type' => '0', 'ldap_suffix' => '0', 'ldap_domain_dn' => '0', 'ldap_domain_controler' => '0', 'ldap_user_attribute' => '0', 'ldap_ssl' => '0', 'ldap_tls' => '0', 'richtext' => '0', 'allow_print' => '1', 'roles_allowed_to_print' => '["["["["["1","2"]"]"]"]"]', 'show_description' => '1', 'anyone_can_modify' => '0', 'anyone_can_modify_bydefault' => '0', 'nb_bad_authentication' => '0', 'utf8_enabled' => '1', 'restricted_to' => '0', 'restricted_to_roles' => '0', 'enable_send_email_on_user_login' => '0', 'enable_user_can_create_folders' => '1', 'insert_manual_entry_item_history' => '0', 'enable_kb' => '0', 'enable_email_notification_on_item_shown' => '0', 'enable_email_notification_on_user_pw_change' => '0', 'custom_logo' => 'https:///index.php/s/K6xY9KgRcS7KpwM/download/Nested%20C%20600x600.png', 'custom_login_text' => '', 'default_language' => 'english', 'send_stats' => '1', 'get_tp_info' => '1', 'send_mail_on_user_login' => '0', 'nb_items_by_query' => 'auto', 'enable_delete_after_consultation' => '0', 'enable_personal_saltkey_cookie' => '0', 'personal_saltkey_cookie_duration' => '0', 'email_smtp_server' => 'smtp.office365.com', 'email_smtp_auth' => '1', 'email_auth_username' => '', 'email_auth_pwd' => '', 'email_port' => '587', 'email_security' => 'tls', 'email_server_url' => 'https://', 'email_from' => '', 'email_from_name' => '', 'pwd_maximum_length' => '40', 'delay_item_edition' => '1', 'allow_import' => '0', 'proxy_ip' => '', 'proxy_port' => '', 'upload_maxfilesize' => '10mb', 'upload_docext' => 'doc,docx,dotx,xls,xlsx,xltx,rtf,csv,txt,pdf,ppt,pptx,pot,dotx,xltx', 'upload_imagesext' => 'jpg,jpeg,gif,png', 'upload_pkgext' => '7z,rar,tar,zip', 'upload_otherext' => 'sql,xml', 'upload_imageresize_options' => '1', 'upload_imageresize_width' => '800', 'upload_imageresize_height' => '600', 'upload_imageresize_quality' => '90', 'use_md5_password_as_salt' => '0', 'ga_website_name' => '', 'api' => '0', 'subfolder_rights_as_parent' => '1', 'show_only_accessible_folders' => '0', 'enable_suggestion' => '1', 'otv_expiration_period' => '7', 'default_session_expiration_time' => '500', 'duo' => '0', 'send_stats_time' => '0', 'tree_counters' => '1', 'item_extra_fields' => '0', 'enable_attachment_encryption' => '1', 'copy_to_clipboard_small_icons' => '1', 'settings_offline_mode' => '0', 'offline_key_level' => '0', 'bck_script_filename' => 'bck_cpassman', 'bck_script_path' => '/var/www/html//backups', 'can_create_root_folder' => '0', 'encryption_protocol' => 'ctr', 'menu_type' => 'context', 'google_authentication' => '1', 'enable_server_password_change' => '0', 'syslog_enable' => '0', 'syslog_host' => 'localhost', 'syslog_port' => '514', 'saltkey_ante_2127' => '13', 'teampass_version' => '3.1.2', 'migration_to_2127' => 'done', 'manager_move_item' => '0', 'create_item_without_password' => '0', 'agses_authentication_enabled' => '0', 'timezone' => 'America/Chicago', 'personal_saltkey_security_level' => '0', 'ldap_new_user_is_administrated_by' => '0', 'ldap_port' => '389', 'enable_http_request_login' => '0', 'otv_is_enabled' => '0', 'ldap_and_local_authentication' => '0', 'secure_display_image' => '1', 'upload_zero_byte_file' => '0', 'upload_all_extensions_file' => '0', 'files_with_defuse' => 'done', 'send_statistics_items' => 'stat_country;stat_users;stat_items;stat_items_shared;stat_folders;stat_folders_shared;stat_admins;stat_managers;stat_ro;stat_mysqlversion;stat_phpversion;stat_teampassversion;stat_languages;stat_kb;stat_suggestion;stat_customfields;stat_api;stat_2fa;stat_agses;stat_duo;stat_ldap;stat_syslog;stat_stricthttps;stat_fav;stat_pf;', 'admin_2fa_required' => '0', 'password_overview_delay' => '10', 'roles_allowed_to_print_select' => '', 'clipboard_life_duration' => '0', 'mfa_for_roles' => '', 'settings_tree_counters' => '0', 'enable_massive_move_delete' => '0', 'email_debug_level' => '0', 'ga_reset_by_user' => '1', 'onthefly-backup-key' => '', 'onthefly-restore-key' => '', 'ldap_user_dn_attribute' => '', 'ldap_dn_additional_user_dn' => '', 'ldap_user_object_filter' => '', 'ldap_bdn' => '', 'ldap_hosts' => '', 'ldap_password' => '', 'ldap_username' => '', 'api_token_duration' => '60', 'enable_tasks_manager' => '1', 'task_maximum_run_time' => '300', 'maximum_number_of_items_to_treat' => '300', 'tasks_manager_refreshing_period' => '100', 'ldap_tls_certifacte_check' => 'LDAP_OPT_X_TLS_NEVER', 'enable_tasks_log' => '1', 'enable_ad_users_with_ad_groups' => '0', 'enable_ad_user_auto_creation' => '0', 'ldap_group_object_filter' => '', 'ldap_guid_attibute' => 'objectguid', 'sending_emails_job_frequency' => '1', 'user_keys_job_frequency' => '1', 'items_statistics_job_frequency' => '5', 'reload_cache_table_task' => 'hourly;04:00', 'rebuild_config_file' => '', 'purge_temporary_files_task' => 'daily;14:00', 'clean_orphan_objects_task' => 'daily;01:00', 'users_personal_folder_task' => 'hourly;17:00', 'maximum_session_expiration_time' => '60', 'items_ops_job_frequency' => '1', 'upgrade_timestamp' => '1718907872', 'enable_refresh_task_last_execution' => '1', 'ldap_group_objectclasses_attibute' => 'top,groupofuniquenames', 'max_last_items' => '20', 'rebuild_config_file_task' => 'daily;03:00', 'pwd_default_length' => '16', 'tasks_log_retention_delay' => '3650', 'oauth2_enabled' => '0', 'oauth2_client_appname' => 'Login with Azure', 'oauth2_client_scopes' => 'openid,profile,email', ); **Updated from an older Teampass or fresh install:** PLEASE attach to this issue the file `/includes/config/tp.config.php`. ### Client configuration **Browser:** Safari Firefox **Operating system:** macOS Sonoma 14.5 Debian 12.5 ### Logs #### Web server error log [Thu Jun 20 12:55:15.213479 2024] [mpm_prefork:notice] [pid 582] AH00163: Apache/2.4.56 (Debian) OpenSSL/1.1.1w configured -- resuming normal operations [Thu Jun 20 12:55:15.213562 2024] [core:notice] [pid 582] AH00094: Command line: '/usr/sbin/apache2' [Thu Jun 20 13:23:33.515139 2024] [proxy_fcgi:error] [pid 615] [client 10.10.200.12:51667] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92' [Thu Jun 20 13:23:45.562653 2024] [proxy_fcgi:error] [pid 616] [client 10.10.200.12:51675] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92', referer: https:///install/upgrade.php [Thu Jun 20 13:23:50.281598 2024] [proxy_fcgi:error] [pid 616] [client 10.10.200.12:51675] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92', referer: https:///install/upgrade.php [Thu Jun 20 13:23:59.195224 2024] [proxy_fcgi:error] [pid 613] [client 10.10.200.12:51676] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92', referer: https:///install/upgrade.php [Thu Jun 20 13:24:59.652044 2024] [proxy_fcgi:error] [pid 614] [client 10.10.200.12:51687] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92', referer: https:///install/upgrade.php [Thu Jun 20 13:25:03.682809 2024] [proxy_fcgi:error] [pid 614] [client 10.10.200.12:51687] AH01071: Got error 'PHP message: PHP Warning: Constant SECUREPATH already defined in /var/www/html/install/upgrade.php on line 92', referer: https:///install/upgrade.php [Fri Jun 21 00:00:02.037852 2024] [mpm_prefork:notice] [pid 582] AH00171: Graceful restart requested, doing restart AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message Insert your webserver log here ``` #### Log from the web-browser developer console (CTRL + SHIFT + i) All web consol logs are showing are 200 OK Insert the log here and especially the answer of the query that failed. ```

[deanbruhn]

I've added two screen shots. One is pre the upgrade and one is post the upgrade to show the difference in items specifically with the stuff in this directory. The items that are missing are still in the teampass_items table.

[deanbruhn]

Does anyone have any idea what data I can change in the DB so this information stops getting removed from the system? Or what I can do to get it back?

[deanbruhn]

I have been able to confirm the records are removed after the Clean orphan objects runs on schedule. Any one have any idea how to make is so I don't have orphan records?

[deanbruhn]

If I go into (webroot) ./scripts/task_maintenance_clean_orphan_objects.php and comment out these lines of code, the system stops getting rid of all of the records. That being said, obviously not a clean solution.

/*
// Delete all item keys for which no user exist
DB::query(
'DELETE k FROM ' . prefixTable('sharekeys_items') . ' k
LEFT JOIN ' . prefixTable('users') . ' u ON k.user_id = u.id
WHERE u.id IS NULL OR u.deleted_at IS NOT NULL'
);

// Delete all files keys for which no user exist
DB::query(
    'DELETE k FROM ' . prefixTable('sharekeys_files') . ' k
    LEFT JOIN ' . prefixTable('users') . ' u ON k.user_id = u.id
    WHERE u.id IS NULL OR u.deleted_at IS NOT NULL'
);

// Delete all fields keys for which no user exist
DB::query(
    'DELETE k FROM ' . prefixTable('sharekeys_fields') . ' k
    LEFT JOIN ' . prefixTable('users') . ' u ON k.user_id = u.id
    WHERE u.id IS NULL OR u.deleted_at IS NOT NULL'
);

// Delete all item logs for which no user exist
DB::query(
    'DELETE l FROM ' . prefixTable('log_items') . ' l
    LEFT JOIN ' . prefixTable('users') . ' u ON l.id_user = u.id
    WHERE u.id IS NULL OR u.deleted_at IS NOT NULL'
);

// Delete all system logs for which no user exist
DB::query(
    'DELETE l FROM ' . prefixTable('log_system') . ' l
    LEFT JOIN ' . prefixTable('users') . ' u ON l.qui = u.id
    WHERE i.id IS NULL OR u.deleted_at IS NOT NULL'
);

// Delete all item keys for which no object exist
DB::query(
    'DELETE k FROM ' . prefixTable('sharekeys_items') . ' k
    LEFT JOIN ' . prefixTable('items') . ' i ON k.object_id = i.id
    WHERE i.id IS NULL'
);

// Delete all files keys for which no object exist
DB::query(
    'DELETE k FROM ' . prefixTable('sharekeys_files') . ' k
    LEFT JOIN ' . prefixTable('items') . ' i ON k.object_id = i.id
    WHERE i.id IS NULL'
);

// Delete all fields keys for which no object exist
DB::query(
    'DELETE k FROM ' . prefixTable('sharekeys_fields') . ' k
    LEFT JOIN ' . prefixTable('items') . ' i ON k.object_id = i.id
    WHERE i.id IS NULL'
);

// Delete all item logs for which no object exist
DB::query(
    'DELETE l FROM ' . prefixTable('log_items') . ' l
    LEFT JOIN ' . prefixTable('items') . ' i ON k.id_item = i.id
    WHERE i.id IS NULL'
);

*/

[deanbruhn]

Ok. deeper dive. The issue was specifically related to this part of the code in html/scripts/task_maintenance_clean_orphan_objects.php

// Delete all item logs for which no user exist
DB::query(
    'DELETE l FROM ' . prefixTable('log_items') . ' l
    LEFT JOIN ' . prefixTable('users') . ' u ON l.id_user = u.id
    WHERE u.id IS NULL OR u.deleted_at IS NOT NULL'
);

I went through each loop individually to figure it out.

I went through and figured out what lines in the teampass_log_items table were referencing users that were no longer in the DB.

This query should output that info.
SELECT * FROM teampass_log_items WHERE (id_user) not in (SELECT id from teampass_users);

Then I took that list of users that were referenced in that table, and updated all of the lines with those users to be associated with a specific user. Basically you just need to change the numbers in this command with the users output in your previous statement, and a user you know is good.

UPDATE teampass_log_items SET id_user = 30 WHERE id_user = 13;

After that I went through all of the users still in the DB, and made their deleted_at fields null. Then I went into the UI and deactivated the users.

I am pretty sure the problem that I had here is that we've been upgrading this thing for 10+ years, and there must have been a change at some point in how deleted users were handled that were problematic with the new orphan cleanup task.

Leaving open for Nils review.