FAIL 20220330100659_AddJobsTable.go (create table: Error 1050: Table 'jobs' already exists), quitting migration

[ducna96]

FAIL 20220330100659_AddJobsTable.go (create table: Error 1050: Table 'jobs' already exists), quitting migration i update fleetdm from 4.11 -> 4.23 and i create backup db and import to mysql, and run fleet prepare db with new db and have proplem

how do i fix that? mysql 5.17.29 thanks

[ducna96]

hi @noahtalerman

[roperzh]

hi @ducna96, before running migrations we require that you stop all Fleet instances connected to the database, and start the instances again only after the migrations were run. Could you please confirm if you're doing this?

another thing you could try is running FLUSH TABLES to make sure there isn't any temporary metadata messing up the migration.

[ducna96]

hi @ducna96, before running migrations we require that you stop all Fleet instances connected to the database, and start the instances again only after the migrations were run. Could you please confirm if you're doing this?

another thing you could try is running FLUSH TABLES to make sure there isn't any temporary metadata messing up the migration.

i create backup of main fleet database running, and import that to mysql, and run migrate with backupdb,(main fleet are running) is it wrong ? bcs, i backuped before and got error when migrate, so i want to test with that way.

[roperzh]

@ducna96 if you're running the migrations in the back-up, then I believe you shouldn't have to shut down the fleet server (as you mention.)

Could you double check if the back-up you create (or your main database) has a table named jobs? You can do that by running SHOW TABLES LIKE 'jobs';

[ducna96]

@ducna96 if you're running the migrations in the back-up, then I believe you shouldn't have to shut down the fleet server (as you mention.)

Could you double check if the back-up you create (or your main database) has a table named jobs? You can do that by running SHOW TABLES LIKE 'jobs';

yep, it have tables jobs.

[roperzh]

can you think of a reason for that table to be there before the migration is run? Is your database shared with another application? did the migration run in the main database (not the back-up) previously?

for the migration to succeed the jobs table shouldn't be present. From what I can see, I believe this is a specific problem with your database, not a bug in Fleet, but I'm happy to help you find out the root cause.

[macEar]

Hi there! @roperzh I've run into the same issue.

Check version of Fleet:

# fleet version
fleet version 4.27.0

The version of my MySQL database:

/usr/sbin/mysqld, Version: 8.0.29-21 (Percona Server (GPL), Release 21, Revision c59f87d2854)

Create new database fleet:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> create database fleet;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| fleet              |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> use fleet;
Database changed

mysql> show tables;
Empty set (0.00 sec)

Then, I started the migration process and got the beforementioned issue:

# sudo -u fleet /usr/bin/fleet prepare db --config /opt/fleet/conf/fleet.yml
Using config file: /opt/fleet/conf/fleet.yml
2023/02/06 12:26:39 [2016-11-18] Create Table App Configs
2023/02/06 12:26:39 FAIL 20161118193812_CreateTableAppConfigs.go (Error 1050: Table 'app_configs' already exists), quitting migration.

In MySQL logs I can see the following statements:

2023-02-06T09:26:39.451572Z 9508918 Query   START TRANSACTION
2023-02-06T09:26:39.453366Z 9508918 Query   CREATE TABLE `app_configs` (`id` INT(10) UNSIGNED NOT NULL DEFAULT 1,`org_name` VARCHAR(255) NOT NULL DEFAULT '',`org_logo_url` VARCHAR(255) NOT NULL DEFAULT '',`kolide_server_url` VARCHAR(255) NOT NULL DEFAULT '',`smtp_configured` TINYINT(1) NOT NULL DEFAULT FALSE,`smtp_sender_address` VARCHAR(255) NOT NULL DEFAULT '',`smtp_server` VARCHAR(255) NOT NULL DEFAULT '',`smtp_port` INT UNSIGNED NOT NULL DEFAULT 587,`smtp_authentication_type` INT NOT NULL DEFAULT 0,`smtp_enable_ssl_tls` TINYINT(1) NOT NULL DEFAULT TRUE,`smtp_authentication_method` INT NOT NULL DEFAULT 0,`smtp_domain` VARCHAR(255) NOT NULL DEFAULT '',`smtp_user_name` VARCHAR(255) NOT NULL DEFAULT '',`smtp_password` VARCHAR(255) NOT NULL DEFAULT '',`smtp_verify_ssl_certs` TINYINT(1) NOT NULL DEFAULT TRUE, `smtp_enable_start_tls` TINYINT(1) NOT NULL DEFAULT TRUE, `smtp_enabled` TINYINT(1) NOT NULL DEFAULT FALSE, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
2023-02-06T09:26:39.478835Z 9508918 Query   INSERT INTO app_configs VALUES ()
2023-02-06T09:26:39.488718Z 9508918 Execute INSERT INTO migration_status_tables (version_id, is_applied) VALUES (20161118193812, 1)
2023-02-06T09:26:39.504299Z 9508918 Query   START TRANSACTION
2023-02-06T09:26:39.506140Z 9508918 Query   CREATE TABLE `app_configs` (`id` INT(10) UNSIGNED NOT NULL DEFAULT 1,`org_name` VARCHAR(255) NOT NULL DEFAULT '',`org_logo_url` VARCHAR(255) NOT NULL DEFAULT '',`kolide_server_url` VARCHAR(255) NOT NULL DEFAULT '',`smtp_configured` TINYINT(1) NOT NULL DEFAULT FALSE,`smtp_sender_address` VARCHAR(255) NOT NULL DEFAULT '',`smtp_server` VARCHAR(255) NOT NULL DEFAULT '',`smtp_port` INT UNSIGNED NOT NULL DEFAULT 587,`smtp_authentication_type` INT NOT NULL DEFAULT 0,`smtp_enable_ssl_tls` TINYINT(1) NOT NULL DEFAULT TRUE,`smtp_authentication_method` INT NOT NULL DEFAULT 0,`smtp_domain` VARCHAR(255) NOT NULL DEFAULT '',`smtp_user_name` VARCHAR(255) NOT NULL DEFAULT '',`smtp_password` VARCHAR(255) NOT NULL DEFAULT '',`smtp_verify_ssl_certs` TINYINT(1) NOT NULL DEFAULT TRUE, `smtp_enable_start_tls` TINYINT(1) NOT NULL DEFAULT TRUE, `smtp_enabled` TINYINT(1) NOT NULL DEFAULT FALSE, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
2023-02-06T09:26:39.507971Z 9508918 Query   ROLLBACK

For some reason, Fleet tried to create the table twice, and without IF NOT EXISTS directive in the CREATE TABLE statement.

Each time I run fleet prepare db command, I get some errors. The full list of errors I've received while running that command is attached, you can get familiar with it. The same issue is present in v4.26.0 version of Fleet also.

fleet_v4.27.0_db_migration.log

[macEar]

@roperzh do you have any ideas on why CREATE TABLE statements could be run twice? Could we use CREATE TABLE IF NOT EXISTS queries instead?

[macEar]

Hi there again! It turned out that the issue was about MySQL load balancer. As only we changed connection to direct access, the problem was solved.

@ducna96 do you use load balancers? If so, try to set up direct connection.