Most database issues are database-version related, or migrations related. # Run Migrations Running migrations is always necessary to make sure your schema is up to date with the database schema the code is expecting: {` # Run migrations $ php artisan migrate `} {` # Run migrations on docker $ docker exec -it snipeit-app php artisan migrate `} # Unknown column 'generation_expression' in 'field list' If you run into the error `Column not found: 1054 Unknown column 'generation_expression' in 'field list'`, you are running an incompatible version of MySQL. Please check the [requirements](docs:requirements) page for minimum versions. # Column already exists: 1060 Duplicate column name If you run into this issue while running migrations, it means something in your database got borked. The `migrations` table logs each completed migration and somehow you ended up with a migration that ran but didn't get logged, so it's attempting to be run again - but it can't, since the column or table already exists from a previous migration. We don't exactly know how folks get into this situation, and unfortunately the solution is quite tedious (and fraught). The only way around this is to manually enter the migrations into the migrations database table, re-run migrations, and rinse and repeat. The actual error should show you the migration file that it's getting caught on. You'll see something like: ```php Migrating: 2019_02_12_182750_add_actiondate_to_actionlog In Connection.php line 669: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'action_date' ``` in your error logs and/or output from running `php upgrade.php`. We'll use the above error for our example. From the MySQL command line, you'd run the following: {` # Manually add the migration record to the migrations table $ INSERT INTO migrations (migration, batch) VALUES('2019_02_12_182750_add_actiondate_to_actionlog', 10); `} This will add the `2019_02_12_182750_add_actiondate_to_actionlog` migration to the `migrations` table and allow you to continue with the next migrations. After inserting that record, try running migrations again: {` # Try running migrations again $ php artisan migrate `} If you get another similar error after running migrations, repeat the process with the next migration name, re-run migrations, etc. Repeat this until your migrations run smoothly. # SQLSTATE[42S22]: Column not found: 1054 Unknown column If you see an error like this, you somehow missed running migrations in-between updates, or the migrations failed and you might not have noticed. {` # Run migrations $ php artisan migrate `} {` # Run migrations on docker $ docker exec -it snipeit-app php artisan migrate `} # Base table or view not found: 1146 Table 'oauth_clients' doesn't exist Run migrations again to create the tables: {` # Run database migrations $ php artisan migrate `}
# SQLSTATE[HY000]: General error: 1005 Can't create table 'snipeit.#sql-3626_1c6' (errno: 150) This happens when your default table engine is set to MyISAM (which in general is weird. InnoDB has been the default table engine in MySQL for quite some time). If you see this error: ``` Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table 'snipeit.#sql-3626_1c6' (errno: 150) (SQL: alter table users add constraint users_company _id_foreign foreign key (company_id) references companies (id)) [PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table 'snipeit.#sql-3626_1c6' (errno: 150) ``` Try running: {` # Change table type to InnoDB $ ALTER TABLE companies ENGINE = InnoDB; `} on your MySQL database and then re-run migrations. (If you run into this on tables other than `companies`, you can safely run that command on each table that's giving you issues. # Duplicate key name 'action_logs_target_id_target_type_index' If your MySQL defaults to MyISAM as a storage engine (which it shouldn't, since InnoDB has been the default since MySQL 5.6), you will have to run the following on your database before running migrations: {` # Change table type to InnoDB $ DROP INDEX action_logs_target_id_target_type_index ON action_logs; $ DROP INDEX action_logs_created_at_index ON action_logs; $ DROP INDEX action_logs_item_type_item_id_action_type_index ON action_logs; $ DROP INDEX action_logs_target_type_target_id_action_type_index ON action_logs; $ ALTER TABLE action_logs ENGINE = InnoDB; $ ALTER TABLE assets ENGINE = InnoDB;`}
# SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large." If you're unable to run migrations because of this error, use the following settings in your `my.cnf` database configuration file: ```text my.cnf innodb_log_file_size=512M innodb_strict_mode=0 ``` If you're upgrading or restoring and run into this error, run: {` # Change row format to dynamic $ ALTER TABLE assets ROW_FORMAT=DYNAMIC; $ ALTER TABLE users ROW_FORMAT=DYNAMIC; `} # Max key length is 1000 bytes You would likely only see this is you're upgrading from a much older version of Snipe-IT (v3 or earlier). The default engine for new tables in MySQL was changed to InnoDB (from MyISAM) a long time ago, and newer installs specify InnoDB explicitly, however older tables may have been created as MyISAM if they're very old. (Upgrading Snipe-IT or MySQL itself won't automatically change the engine type.)

If your MySQL defaults to MyISAM as a storage engine (which it shouldn't, since InnoDB has been the default since MySQL 5.6), you will have to run the following on your database before running migrations: {` # Change table type to InnoDB $ ALTER TABLE action_logs ENGINE = InnoDB; $ ALTER TABLE assets ENGINE = InnoDB; `} If you miss this step and run migrations, you can run these commands and then re-run migrations. # Syntax error or access violation: 1142 REFERENCES command denied The error "REFERENCES command denied" during a database migration means the database user lacks the necessary privileges to create or modify foreign key constraints (which require the REFERENCES privilege in MySQL). To resolve this, you must grant the appropriate user the REFERENCES privilege on the relevant database and tables using a GRANT SQL command, such as GRANT REFERENCES ON your_database_name.* TO 'your_username'@'your_host';, then re-run your migrations.