Most database issues are database-version related, or migrations related. Running migrations is always necessary to make sure your schema is up to date with the database schema the code is expecting.

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 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:

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

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:

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.)

# 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.