https://github.com/osquery/osquery logo
Join Slack
Powered by
Hi, I am upgrading v4.26 to 4.54.1 and migrations ...
# fleet
s
Hi, I am upgrading v4.26 to 4.54.1 and migrations have failed on: 
024/08/12 16:15:26 FAIL 20230315104937_EnsureUniformCollation.go
DB version: MySQL 5.7.44 Name kolide Collation utf8mb4_unicode_ci Character set utf8mb4 Managed to restore from a backup and run the following statements, based of the following thread: https://osquery.slack.com/archives/C01DXJL16D8/p1681413560533379?thread_ts=1681410730.438379&cid=C01DXJL16D8
Copy code
ALTER TABLE software CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE host_users CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE operating_systems CONVERT TO CHARACTER SET utf8mb4;
however the migrations still hang on 
2024/08/12 16:58:23 [2023-03-15] Ensure Uniform Collation
Looking to see if there are any pointers on what else to look at?
Copy code
2024/08/12 17:09:18 FAIL 20230315104937_EnsureUniformCollation.go (fixing host_users table: iterating rows: Error 9 (HY000): Unexpected EOF found when reading file '/mysql/tmp/MY84IoFf' (Errcode: 175 - File to short; Expected more data in file)), quitting migration
k
I haven't seen that particular error before. can you confirm that there are no Fleet servers online during the migration process?
s
Yep I delete the deployment for the api first
im thinking of recreating the host_users table, since from what I can understand is where fleet stores users it gathers from endpoints
Ok deleted the table and recreated it with (we have a staging env running 4.54.1 which I was able to see create command with):
Copy code
CREATE TABLE
  `host_users` ( `host_id` int(10) unsigned NOT NULL,
    `uid` int(10) unsigned NOT NULL,
    `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `groupname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `removed_at` timestamp NULL DEFAULT NULL,
    `user_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `shell` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY
    (`host_id`,
      `uid`,
      `username`),
    UNIQUE KEY `idx_uid_username` (`host_id`,
      `uid`,
      `username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Allowing for the migrations to complete
k
That seems like a totally reasonable solution. It sounds like the root issue there may have been that the database is a little low on disk space, so that's something I'd recommend checking up on.
s
Thank you!
Looks like im hitting some issues with this recreated table
Copy code
"level=error ts=2024-08-12T18:48:06.861129962Z component=http method=POST uri=/api/v1/osquery/distributed/write took=11.648247862s ip_addr=REDACTED x_for_ip_addr="REDACTED" ingestion-err="ingesting query users: update host users: insert users: Error 1390 (HY000): Prepared statement contains too many placeholders" err="error in query ingestion""
Also getting a lot of 
msg="querying name and team ID from result" err="unknown format:
messages
Copy code
level=debug ts=2024-08-12T19:02:00.312927482Z msg="querying name and team ID from result" err="unknown format: \"pack/Access Mechanisms/Known Hosts\""
From the look of the message it seems like packs arent really supported or at least causing some sort of issues
k
Odd... there was a bug related to packs not functioning properly when data caching was enabled, but that was resolved in 4.41.1: https://github.com/fleetdm/fleet/issues/15168
Can you confirm the version of Fleet currently running?
s
I was running 4.54.1, but I have now rolled back 4.26.0 since I cant have prod down any longer
z
@Shend Saliaga Unrelated to this thread, but I want to make sure you didn't miss earlier communications about this. Fleet will only be supporting MySQL 8.0+ starting with v4.55.0 (I see you're upgrading to 4.54.1 so all good for now - just future planning)
s
Thanks for bringing that up, yeah i'm looking on standing up a parallel deployment with a separate database, so i'll use 8.0+ for that
k
when you rolled back, did you roll the database back as well?
s
Yep! I had backups before I made any changes
k
Good deal. It's hard to diagnose why that may have been happening now that Fleet is rolled back, but I'd be happy to hop on a call with you next week to run another upgrade and try to troubleshoot it live if that's an option for you.
s
Sure sounds good, whats your availability like next week?
k
I'll DM you my Calendly link 🙂
11 Views
Open in Slack
PreviousNext
Powered by