[Bug]: Company assignment via SQL not possible anymore

[SW99PM]

Snipe-IT Version

v8.6.3 build 23252

PHP Version

8.5.3

Composer Version

2.8.1

MySQL/MariaDB version

MariaDB 12.2.2

How did you install Snipe-IT?

Git install

Is this a fresh install or an upgrade?

Upgrade

What happened?

Since the last update to Snipe-IT version v8.6.3 build 23252 it is not possible anymore to manually assign a company to a user via SQL. Before the update I used a SQL script which assigned the users automatically to their companies based on their country.
As I can see there is now an additional table called "company_user". If I manually change the company of a user via SQL in the table "users" the changes will not be duplicated in the "company_user" table. This causes the issue, that the user cannot accept an asset assigned to him. Error: "Checkout company and asset-company are not the same".
The user looks totally fine in Snipe-IT and the asset as well. But if I check the table "company_user" there is no entry with the user ID. After assigning a different company to the user via Snipe-IT website and after that assigning back to the correct company the user ID will be listed in the "company_user" table.

Is there any possibility to get both tables updated simultaneously?

What browsers are you seeing the problem on?

No response

Can you reproduce this on the public demo?

N/A

Do you have full multiple company support enabled?

Yes

If you have full multiple company support enabled, do you have location scoping to company enabled?

No

Application log output

Browser console output

Common Issues

• I have searched this repo for existing issues related to my issue (including closed issues)
• My APP_URL is set correctly in my .env file (including http or https and no trailing slash)
• I have searched the official Snipe-IT documentation and have checked the Common Issues documentation (where applicable)
• I have run database migrations (where applicable).
• I have attached screenshots and/or videos of the issue (where applicable)

Code of Conduct

• I agree to follow this project's Code of Conduct

[uberbrady]

You don't need to use the company_id in the users table any more at all; you can ignore that field. We'll probably mark it as deprecated soon, and then delete it later.

If you change your script to instead add to the company_users table, will that do what you want?

INSERT INTO company_users (user_id,company_id) VALUES ($user_id,$company_id);

Or if you are doing something in pure SQL, maybe something like -

INSERT INTO company_users (user_id,company_id) VALUES (SELECT id,CASE country WHEN 'us' THEN 1 WHEN 'jp' THEN 2 WHEN 'pt' THEN 3 ELSE 4 END FROM users WHERE /* something? */)

[SW99PM]

Thank you @uberbrady ! I'll try that. At the moment I am using this AssignCompany.sql script which will get started by a .bat script which will get started by a scheduled task once an hour 😄

SET company_id = 1 
WHERE activated = 1 AND country = "Germany" OR country = "Deutschland";

UPDATE users 
SET company_id = 3 
WHERE activated = 1 AND country = "Spain" OR country = "Spanien";

UPDATE users 
SET company_id = 4
WHERE activated = 1 AND country = "United States" OR country = "Vereinigte Staaten";

UPDATE users 
SET company_id = 22
WHERE activated = 1 AND country = "Brazil" OR country = "Brasilien";

UPDATE users 
SET company_id = 5 
WHERE activated = 1 AND country = "Turkey" OR country = "Türkei";

UPDATE users 
SET company_id = 6
WHERE activated = 1 AND country = "India" OR country = "Indien";

UPDATE users 
SET company_id = 9
WHERE activated = 1 AND country = "Australia" OR country = "Australien";

UPDATE users 
SET company_id = 10
WHERE activated = 1 AND country = "Slovenia" OR country = "Slowenien";

UPDATE users 
SET company_id = 11
WHERE activated = 1 AND country = "Japan";

UPDATE users 
SET company_id = 12
WHERE activated = 1 AND country = "France" OR country = "Frankreich";

UPDATE users 
SET company_id = 2 
WHERE activated = 1 AND country = "Germany" OR country = "Deutschland";

[snipe]

We really discourage folks from directly editing data via SQL versus using the API. The schema changes all the time, and the API handles those types of things for you. https://snipe-it.readme.io/docs/manually-editing-data

Snipe-IT Documentation

Manually Editing Data

The short version: ❗️ DON'T If you start deleting records from tables manually, you will almost assuredly mangle your data in a way that will cause real problems. This software uses a relational database system, which means that some tables exist just to establish relationships between other pieces…