LDAP login does not work after v4.8.0 to v5.1.4 update (migration failed during and after update process)

[arkeyme]

Please confirm you have done the following before posting your bug report:

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

Describe the bug I was tried to update snip-it from v4.8.0 to v5.1.4 using this guide: https://snipe-it.readme.io/docs/switching-to-a-git-install

cd /var/www/snipeit
php artisan snipeit:backup
php artisan down

cd ..
mv snipeit snipeit_bak

git clone https://github.com/snipe/snipe-it snipeit 

cp -R snipeit_bak/public/uploads/* snipeit/public/uploads
cp -R snipeit_bak/storage/private_uploads/* snipeit/storage/private_uploads
cp -R snipeit_bak/storage/app/backups/* snipeit/storage/app/backups
cp -R snipeit_bak/.env snipeit/
cp -R snipeit_bak/storage/oauth-private.key snipeit/storage/oauth-private.key
cp -R snipeit_bak/storage/oauth-public.key snipeit/storage/oauth-public.key

chown snipeitapp:www-data -R snipeit
cd snipeit
chmod g+w -R ./

sudo -u snipeitapp php upgrade.php

After that all is seems to be fine except LDAP setting which has not to be working. I cant login using my LDAP account, which obviously worked before upgrade. I tried to change and save settings on LDAP config page and got 500 error. After I enabled Debug mode I see this:

/var/www/snipeit/vendor/laravel/framework/src/Illuminate/Database/Connection.php
     * @param  array  $bindings
     * @param  \Closure  $callback
     * @return mixed
     *
     * @throws \Illuminate\Database\QueryException
     */
    protected function runQueryCallback($query, $bindings, Closure $callback)
    {
        // To execute the statement, we'll simply call the callback, which will actually
        // run the SQL against the PDO connection. Then we can calculate the time it
        // took to execute and log the query SQL, bindings and time in our memory.
        try {
            $result = $callback($query, $bindings);
        }

        // If an exception occurs when attempting to run a query, we'll format the error
        // message to include the bindings with SQL, which will make this exception a
        // lot more helpful to the developer instead of just the database's errors.
        catch (Exception $e) {
            throw new QueryException(
                $query, $this->prepareBindings($bindings), $e
            );
        }

        return $result;
    }

    /**
     * Log a query in the connection's query log.
     *
     * @param  string  $query
     * @param  array  $bindings
     * @param  float|null  $time
     * @return void
     */
    public function logQuery($query, $bindings, $time = null)
    {
        $this->event(new QueryExecuted($query, $bindings, $time, $this));

        if ($this->loggingQueries) {
Arguments
"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ad_append_domain' in 'field list' (SQL: update `settings` set `ad_append_domain` = 0, `settings`.`update ▶"

I saved this page to the snip-it-error.zip file

Also, migration is failed too:

root@host_test:/var/www/snipeit# php artisan down
Application is now in maintenance mode.
root@host_test:/var/www/snipeit# php artisan migrate
**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

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\PDOException::("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/PDOStatement.php:131

  2   PDOException::("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/PDOStatement.php:129

  Please use the argument -v to see more details.

Expected behavior Is to able log in using my LDAP account, and to save LDAP settings. Screenshots But it obviously enabled:

If applicable, add screenshots to help explain your problem.

Server (please complete the following information):

• Snipe-IT Version v5.1.4
• OS: Ubuntu 20.04
• Web Server: Apache2
• PHP Version PHP 7.4.3 (cli)

Error Messages

• WITH DEBUG TURNED ON, if you're getting an error in your browser, include that error

• If a stacktrace is provided in the error, include that too.

• Any errors that appear in your browser's error console.

• Include any additional information you can find in storage/logs and your webserver's logs.

Only one Log

[2021-03-23 10:05:12] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ad_append_domain' in 'field list' (SQL: update `settings` set `ad_append_domain` = 0, `settings`.`updated_at` = 2021-03-23 10:05:12 where `id` = 1) {"userId":273,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ad_append_domain' in 'field list' (SQL: update `settings` set `ad_append_domain` = 0, `settings`.`updated_at` = 2021-03-23 10:05:12 where `id` = 1) at /var/www/snipeit/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669)
[stacktrace]

• Include the output from php -m (this should display what modules you have enabled.)

  
  root@teclas04test:/var/www/snipeit# php --version
  PHP 7.4.3 (cli) (built: Oct  6 2020 15:47:56) ( NTS )
  Copyright (c) The PHP Group
  Zend Engine v3.4.0, Copyright (c) Zend Technologies
  with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies
  root@teclas04test:/var/www/snipeit# php -m
  [PHP Modules]
  bcmath
  calendar
  Core
  ctype
  curl
  date
  dom
  exif
  FFI
  fileinfo
  filter
  ftp
  gd
  gettext
  hash
  iconv
  imagick
  json
  ldap
  libxml
  mbstring
  mysqli
  mysqlnd
  openssl
  pcntl
  pcre
  PDO
  pdo_mysql
  Phar
  posix
  readline
  Reflection
  session
  shmop
  SimpleXML
  sockets
  sodium
  SPL
  standard
  sysvmsg
  sysvsem
  sysvshm
  tokenizer
  xml
  xmlreader
  xmlwriter
  xsl
  Zend OPcache
  zip
  zlib

[Zend Modules] Zend OPcache

**Additional context**
- Is this a fresh install or an upgrade? 
Upgrade

[uberbrady]

You need to run migrations, which will add that missing column for you - php artisan migrate

[arkeyme]

Unfortunetely, I already did it, but it failed too, with this exception:

root@host_test:/var/www/snipeit# php artisan down
Application is now in maintenance mode.
root@host_test:/var/www/snipeit# php artisan migrate
**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

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\PDOException::("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/PDOStatement.php:131

  2   PDOException::("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/PDOStatement.php:129

  Please use the argument -v to see more details.

[uberbrady]

We've seen that one before - there's a (terrible) article on MariaDB's website here about it. This points to the recommended solution - changing the table's ROW_FORMAT to DYNAMIC - https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/#converting-the-table-to-the-dynamic-row-format

So you'll want to run this at a SQL prompt:

ALTER TABLE settings ROW_FORMAT=DYNAMIC;

Then, you should be able to run your migrations again. You may also find that some other migrations have a similar issue - find out which table they're referring to, and make the same alteration - then you'll be able 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."

[arkeyme]

Thank you!

mysql
use snipeit;
ALTER TABLE settings ROW_FORMAT=DYNAMIC;

Helps me

[perezalvarezhi]

Thank you!

mysql
use snipeit;
ALTER TABLE settings ROW_FORMAT=DYNAMIC;

Helps me

Hello Arkeyme im having a similar problem with you while migrating, I have the same error :

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 When I try the commands you mention on mysql ( using phpmyadmin window) I get the following error:

Table mydatabasename.settings doesn't exists

Did this worked for you? I see you reopened the issue 6 days ago.

Note: Im trying to upgrade from 4.7.4 to current stable 5.0.12