2023_01_21_225350_add_eol_date_on_assets_table - migration fails to add eol_explicit column

[crane476]

Debug mode

• I have enabled debug mode
• I have read checked the Common Issues page

Describe the bug

I am migrating my install to a new server and upgrading from SnipeIT v6.0.2 to v6.2.2. After installing SnipeIT and restoring my database backup, running php artisan migrate results in the following error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'eol_explicit' in 'field list' in [Snipe-IT Install directory]\vendor\laravel\framework\src\Illuminate\Database\Connection.php:522

The migration that is failing is 2023_01_21_225350_add_eol_date_on_assets_table

Reproduction steps

1. Install prerequisites (PHP, MariaDB, PHP Manager, composer, URL Rewrite)
2. Run git clone to grab the latest version (6.2.2 at the time of this issue)
3. Configure the .env file
4. Run composer install
5. Create database
6. Run php artisan snipeit:backup on old server
7. Restore database backup on new server
8. Run php artisan migrate

Expected behavior

php artisan migrate should update the database schema with any changes made since previous version was installed.

Screenshots

No response

Snipe-IT Version

6.2.2

Operating System

Windows Server 2022

Web Server

IIS

PHP Version

8.0.30

Operating System

No response

Browser

No response

Version

No response

Device

No response

Operating System

No response

Browser

No response

Version

No response

Error messages

No response

Additional context

I'm migrating our SnipeIT data to a new server. This is a fresh install of 6.2.2 on a new server, however I'm restoring a backup of our database from when we were on 6.0.2.

What I've done so far:

1. Install IIS
2. Install URL Rewrite for IIS
3. Downloaded and installed PHP 8
4. Configured the PHP.ini file to enable the required extensions
5. Installed PHP manager for IIS and registered my PHP version
6. Installed composer
7. Installed Git
8. Ran git clone to download the latest version of SnipeIT
9. Copied the .env-example file, renamed to .env, and filled in values from the old .env file
10. Copied over data from public/uploads and storage/private_uploads, as well as my oauth keys
11. Gave IUSR appropriate folder permissions as described in the documentation
12. Installed MariaDB
13. Created snipeit database
14. Created snipe_user and gave it full permissions for the snipeit database
15. Ran the composer install command
16. Ran the php artisan snipeit:backup command on the old server
17. Executed the .sql file generated by the previous step on the new server to restore the database
18. Ran the php artisan migrate command

I have not manually edited any data directly in the database.

Downgrading to version 6.2.1 resolves the issue. I followed the steps described above and did not experience any errors after downgrading.

[welcome]

👋 Thanks for opening your first issue here! If you're reporting a 🐞 bug, please make sure you include steps to reproduce it. We get a lot of issues on this repo, so please be patient and we will get back to you as soon as we can.

[snipe]

What's the result of the migration through?

[crane476]

The migration never completed, because of the error. It attempts to set a default value of 0 for eol_explicit, but fails because the column was never added.

[timw2016]

I have the same problem. This was to output while migrationg:

--------------------------------------------------------
STEP 9: Migrating database:
--------------------------------------------------------

Migrating: 2022_08_25_213308_adds_ldap_default_group_to_settings_table
Migrated:  2022_08_25_213308_adds_ldap_default_group_to_settings_table (9.40ms)
Migrating: 2022_10_25_215520_add_label2_settings
Migrated:  2022_10_25_215520_add_label2_settings (4.66ms)
Migrating: 2022_11_15_232525_adds_should_autoassign_bool_to_users_table
Migrated:  2022_11_15_232525_adds_should_autoassign_bool_to_users_table (5.70ms)
Migrating: 2022_12_20_171851_fix_nullable_migration_for_settings
Migrated:  2022_12_20_171851_fix_nullable_migration_for_settings (37.93ms)
Migrating: 2023_01_18_122534_add_byod_to_assets
Migrated:  2023_01_18_122534_add_byod_to_assets (4.32ms)
Migrating: 2023_01_21_225350_add_eol_date_on_assets_table

   Illuminate\Database\QueryException

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'eol_explicit' in 'field list' (SQL: update `assets` set `asset_eol_date` = 2017-12-15 00:00:00, `eol_explicit` = 0, `assets`.`updated_at` = 2023-10-10 09:49:12 where `id` = 174)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

  • A column was not found: You might have forgotten to run your migrations. You can run your migrations using `php artisan migrate`.
    https://laravel.com/docs/master/migrations#running-migrations

      +10 vendor frames
  11  app/Models/Asset.php:226
      Illuminate\Database\Eloquent\Model::save()

  12  database/migrations/2023_01_21_225350_add_eol_date_on_assets_table.php:32
      App\Models\Asset::save()

[timw2016]

I have found a workaround. First update from v6.0.2 to v6.1.1 and then to v6.2.2. The migrations will then apply succesfully.

[ghost]

I am having the exact same issue. Here is the output of php artisan migrate:

Migrating: 2023_01_21_225350_add_eol_date_on_assets_table

   Illuminate\Database\QueryException 

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'eol_explicit' in 'field list' (SQL: update `assets` set `asset_eol_date` = 2021-12-14 00:00:00, `eol_explicit` = 0, `assets`.`updated_at` = 202
3-10-11 10:19:54 where `id` = 14)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

  • A column was not found: You might have forgotten to run your migrations. You can run your migrations using `php artisan migrate`. 
    https://laravel.com/docs/master/migrations#running-migrations

      +10 vendor frames 
  11  app/Models/Asset.php:226
      Illuminate\Database\Eloquent\Model::save()

  12  database/migrations/2023_01_21_225350_add_eol_date_on_assets_table.php:32
      App\Models\Asset::save()

@snipe I am using php upgrade.php to go from 6.0.10 to latest. Is there a way to specify an intermediate version like @timw2016 suggested or is there a fix in progress?

Thanks

[timw2016]

I have manually checked out the v6.1.1 tag.

php artisan snipeit:backup
git checkout tags/v6.1.1

php composer.phar install --no-dev --prefer-source
php composer.phar dump-autoload
php artisan migrate
php artisan config:clear
php artisan config:cache

After that I have reseted the git repo to HEAD and run php upgrade.php

[snipe]

I think @spencerrlongg figured out what the issue is - he should hopefully have a fix out soon. In the meantime, incremental updates are the workaround.

[snipe]

We see what's happening here and why now - it's just a tough problem to solve for future-proofing.

The issue here is that we added some code to the asset observer to dynamically determine whether the EOL date was explicitly added (i.e. by picking one via the date picker or API) versus the EOL date that gets auto calculated based on the model/warranty information we already have.

It's failing on that old migration because that code in the observer fires any time we save the asset, and when that earlier migration fires, we do save the asset:

snipe-it/database/migrations/2023_01_21_225350_add_eol_date_on_assets_table.php

Lines 30 to 33 in 2a9ed09

	if ($asset->purchase_date!='') {
	$asset->asset_eol_date = $asset->present()->eol_date();
	$asset->save();
	}

which triggers the save() method on the asset observer.

snipe-it/app/Observers/AssetObserver.php

Lines 132 to 145 in 2a9ed09

	//determine if explicit and set eol_explit to true
	if(!is_null($asset->asset_eol_date) && !is_null($asset->purchase_date)) {
	if($asset->model->eol) {
	$months = Carbon::parse($asset->asset_eol_date)->diffInMonths($asset->purchase_date);
	if($months != $asset->model->eol) {
	$asset->eol_explicit = true;
	}
	}
	} elseif (!is_null($asset->asset_eol_date) && is_null($asset->purchase_date)) {
	$asset->eol_explicit = true;
	}
	if ((!is_null($asset->asset_eol_date)) && (!is_null($asset->purchase_date)) && (is_null($asset->model->eol))) {
	$asset->eol_explicit = true;
	}

When that fires, the eol_explicit field hasn't been created yet but the asset observer doesn't know that, so it's a bit of a race condition.

We either have to muck back-in-time with the migrations to create a temp field and then drop it later, before we actually add it in the later migration, or we have to junk up the asset observer forever to check if that field exists yet.

Both of these choices are terrible, but we're figuring out which one will be the least awful. :(

[snipe]

I have a PR up for this (#13748) that will hopefully resolve this issue moving forward.