Users/People broken, unable to see people or 500 error on specific user page

[ghost]

Hi, me again!

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

Describe the bug
Users page results in 500 error

To Reproduce
Steps to reproduce the behavior:

1. Main side nav > people: No users loaded

---

1. Dashboard > click any user > 500 error

Expected behavior
People page loads users
Specific user page loads correctly

Screenshots
If applicable, add screenshots to help explain your problem.

Server (please complete the following information):

• Snipe-IT Version: 5.1.8 (most recent branch as of 8/23/21)
• OS: Ubuntu 20
• Web Server: Apache
• PHP Version: 7.4.3

Desktop (please complete the following information):

• OS: Any
• Browser: Firefox, Chrome

Details:
Debug:
Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accessories_users.note' in 'field list' (SQL: select accessories.*, accessories_users.assigned_to as pivot_assigned_to, accessories_users.accessory_id as pivot_accessory_id, accessories_users.id as pivot_id, accessories_users.created_at as pivot_created_at, accessories_users.note as pivot_note from accessories inner join accessories_users on accessories.id = accessories_users.accessory_id where accessories_users.assigned_to in (1))

Previous exceptions

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accessories_users.note' in 'field list' (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accessories_users.note' in 'field list' (42S22)

[snipe]

Always nice to see you 😁

If you run php artisan migrate what is the output?

[ghost]

Migrating: 2018_08_20_204842_add_depreciation_option_to_settings

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs (SQL: alter table settings add depreciation_method char(10) null default 'default')

at /var/www/snipeit/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
665| // If an exception occurs when attempting to run a query, we'll format the error
666| // message to include the bindings with SQL, which will make this exception a
667| // lot more helpful to the developer instead of just the database's errors.
668| catch (Exception $e) {

669| throw new QueryException(
670| $query, $this->prepareBindings($bindings), $e
671| );
672| }
673|

Exception trace:

1 Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs")
/var/www/snipeit/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18

2 Doctrine\DBAL\Driver\PDO\Exception::new()
/var/www/snipeit/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:114

Please use the argument -v to see more details.

Let me know if you need the verbose flag.

[snipe]

Do you happen to have a buttload of custom fields or something? This is usually a DB thing. (The 500 on people is a red herring I think. It will fail until migrations have been able to run, but they're not currently able to run, so until we can get past this, it's going to be broken.)

Are you running MySQL or MariaDB? And which version? I feel like I've seen this before, but only in Maria.

[ghost]

Only one custom field for MAC addresses. But I went ahead and deleted it and any reference I could find in the UI.

Reran php artisan migrate and it spit out the same error.

Running MariaDB, 10.3.31-MariaDB-0ubuntu0.20.04.1

[snipe]

One custom field should definitely not be an issue. It's a weird row format issue that we only see in certain versions of maria DB. Hang tight, we have a command we can give you that can (hopefully?!) fix the issue for you. We definitely need to get your migrations running.

[uberbrady]

ALTER TABLE tab ROW_FORMAT=DYNAMIC; from here: https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ is what you want to do.

I still haven’t figured out exactly why yet, but for some strange reason certain versions of MariaDB will use a silly row format that’s a little too narrow for Snipe-IT. But if you change this row format setting, specifically on the Assets table if memory serves, you should find that migrations can continue.

The thing is we don’t ever see this on MySQL, ever. I think some specific version of MariaDB changed what the default row format was, and then later changed it to something far more reasonable, but if you started while running the wrong version, then you are kinda screwed?

But I’m not exactly sure. Regardless of the cause, the row format change seems to make this problem go away (and allows you to complete migrations).

MariaDB KnowledgeBase

Troubleshooting Row Size Too Large Errors with InnoDB

Fixing "Row size too large (> 8126). Changing some columns to TEXT or BLOB may help."

[ghost]

Okay after changing the row format to dynamic for the affected tables and rerunning php artisan migrate, everything seems to be working again!

Thank you both again for helping resolve this so quickly! 😎

[snipe]

Always glad to help!