search

BookStackApp / BookStack

A platform to create documentation/wiki content built with PHP & Laravel
https://www.bookstackapp.com/
MIT License
15.22k stars 1.9k forks source link

Exception - column already exist #2710

Closed steakhutzeee closed 3 years ago

steakhutzeee commented 3 years ago

Describe the bug Hi, lately I start seeing these errors in my logs:

y at 01:03:15  -------------------------------------
today at 01:03:15            _         ()
today at 01:03:15           | |  ___   _    __
today at 01:03:15           | | / __| | |  /  \ 
today at 01:03:15           | | \__ \ | | | () |
today at 01:03:15           |_| |___/ |_|  \__/
today at 01:03:15  
today at 01:03:15  
today at 01:03:15  Brought to you by linuxserver.io
today at 01:03:15  -------------------------------------
today at 01:03:15  
today at 01:03:15  To support LSIO projects visit:
today at 01:03:15  https://www.linuxserver.io/donate/
today at 01:03:15  -------------------------------------
today at 01:03:15  GID/UID
today at 01:03:15  -------------------------------------
today at 01:03:15  
today at 01:03:15  User uid:    1000
today at 01:03:15  User gid:    100
today at 01:03:15  -------------------------------------
today at 01:03:15  
today at 01:03:15  [cont-init.d] 10-adduser: exited 0.
today at 01:03:15  [cont-init.d] 20-config: executing... 
today at 01:03:15  [cont-init.d] 20-config: exited 0.
today at 01:03:15  [cont-init.d] 30-keygen: executing... 
today at 01:03:15  using keys found in /config/keys
today at 01:03:15  [cont-init.d] 30-keygen: exited 0.
today at 01:03:15  [cont-init.d] 50-config: executing... 
today at 01:03:15  App Key found - setting variable for seds
today at 01:03:15  Running config - db_user set
today at 01:03:15  /var/run/s6/etc/cont-init.d/50-config: line 86: warning: command substitution: ignored null byte in input
today at 01:03:18  /var/run/s6/etc/cont-init.d/50-config: line 86: warning: command substitution: ignored null byte in input
today at 01:03:36  Migrating: 2021_03_08_215138_add_user_slug
today at 01:03:38  
today at 01:03:38     Illuminate\Database\QueryException  : SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'slug' (SQL: alter table `users` add `slug` varchar(250) not null)
today at 01:03:38  
today at 01:03:38    at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
today at 01:03:38      665|         // If an exception occurs when attempting to run a query, we'll format the error
today at 01:03:38      666|         // message to include the bindings with SQL, which will make this exception a
today at 01:03:38      667|         // lot more helpful to the developer instead of just the database's errors.
today at 01:03:38      668|         catch (Exception $e) {
today at 01:03:38    > 669|             throw new QueryException(
today at 01:03:38      670|                 $query, $this->prepareBindings($bindings), $e
today at 01:03:38      671|             );
today at 01:03:38      672|         }
today at 01:03:38      673|
today at 01:03:38  
today at 01:03:38    Exception trace:
today at 01:03:38  
today at 01:03:38    1   Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'slug'")
today at 01:03:38        /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
today at 01:03:38  
today at 01:03:38    2   Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
today at 01:03:38        /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:114
today at 01:03:38  
today at 01:03:38    Please use the argument -v to see more details.
today at 01:03:50  [cont-init.d] 50-config: exited 0.
today at 01:03:51  [cont-init.d] 99-custom-files: executing... 
today at 01:03:52  [custom-init] no custom files found exiting...
today at 01:03:52  [cont-init.d] 99-custom-files: exited 0.
today at 01:03:52  [cont-init.d] done.
today at 01:03:52  [services.d] starting services
today at 01:03:53  [services.d] done.

Your Configuration (please complete the following information):

Any idea why this is happening?

Thanks in advance!

ssddanbrown commented 3 years ago

Hi @steakhutzeee, This indicates that was likely an issue upgrading your BookStack database on a previous run, and now it can no longer attempt to run the migration.

Is it possible for you to get database access? If so, you could drop the slug column from the users table only, then restart the container and note down any new errors that appear. If you have the original logs from when you upgraded the container, then providing those could be an easier alternative.

steakhutzeee commented 3 years ago

Hi @steakhutzeee, This indicates that was likely an issue upgrading your BookStack database on a previous run, and now it can no longer attempt to run the migration.

Is it possible for you to get database access? If so, you could drop the slug column from the users table only, then restart the container and note down any new errors that appear. If you have the original logs from when you upgraded the container, then providing those could be an easier alternative.

Unfortunately i do not have the logs but i've root access to the db. Could you point on the commands to drop the slug column?

Thanks!

ssddanbrown commented 3 years ago

Hi @steakhutzeee, It'll be something like this:

USE bookstack; # Replace with database name
ALTER TABLE users DROP COLUMN slug;
steakhutzeee commented 3 years ago

Hi @steakhutzeee, It'll be something like this:

USE bookstack; # Replace with database name
ALTER TABLE users DROP COLUMN slug;

I did 

MariaDB [bookstackapp]> ALTER TABLE users
    -> DROP COLUMN slug;
Query OK, 0 rows affected (0.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

but after reboot the issue i have these errors:

today at 01:15:37  -------------------------------------
today at 01:15:37            _         ()
today at 01:15:37           | |  ___   _    __
today at 01:15:37           | | / __| | |  /  \ 
today at 01:15:37           | | \__ \ | | | () |
today at 01:15:37           |_| |___/ |_|  \__/
today at 01:15:37  
today at 01:15:37  
today at 01:15:37  Brought to you by linuxserver.io
today at 01:15:37  -------------------------------------
today at 01:15:37  
today at 01:15:37  To support LSIO projects visit:
today at 01:15:37  https://www.linuxserver.io/donate/
today at 01:15:37  -------------------------------------
today at 01:15:37  GID/UID
today at 01:15:37  -------------------------------------
today at 01:15:37  
today at 01:15:37  User uid:    1000
today at 01:15:37  User gid:    100
today at 01:15:37  -------------------------------------
today at 01:15:37  
today at 01:15:37  [cont-init.d] 10-adduser: exited 0.
today at 01:15:37  [cont-init.d] 20-config: executing... 
today at 01:15:38  [cont-init.d] 20-config: exited 0.
today at 01:15:38  [cont-init.d] 30-keygen: executing... 
today at 01:15:38  using keys found in /config/keys
today at 01:15:38  [cont-init.d] 30-keygen: exited 0.
today at 01:15:38  [cont-init.d] 50-config: executing... 
today at 01:15:38  App Key found - setting variable for seds
today at 01:15:38  Running config - db_user set
today at 01:15:38  /var/run/s6/etc/cont-init.d/50-config: line 86: warning: command substitution: ignored null byte in input
today at 01:15:40  /var/run/s6/etc/cont-init.d/50-config: line 86: warning: command substitution: ignored null byte in input
today at 01:15:46  Migrating: 2021_03_08_215138_add_user_slug
today at 01:15:48  
today at 01:15:48     Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `users` add unique `users_slug_unique`(`slug`))
today at 01:15:48  
today at 01:15:48    at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
today at 01:15:48      665|         // If an exception occurs when attempting to run a query, we'll format the error
today at 01:15:48      666|         // message to include the bindings with SQL, which will make this exception a
today at 01:15:48      667|         // lot more helpful to the developer instead of just the database's errors.
today at 01:15:48      668|         catch (Exception $e) {
today at 01:15:48    > 669|             throw new QueryException(
today at 01:15:48      670|                 $query, $this->prepareBindings($bindings), $e
today at 01:15:48      671|             );
today at 01:15:48      672|         }
today at 01:15:48      673|
today at 01:15:48  
today at 01:15:48    Exception trace:
today at 01:15:48  
today at 01:15:48    1   Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes")
today at 01:15:48        /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
today at 01:15:48  
today at 01:15:48    2   Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
today at 01:15:48        /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:114
today at 01:15:48  
today at 01:15:48    Please use the argument -v to see more details.
today at 01:15:54  [cont-init.d] 50-config: exited 0.
today at 01:15:54  [cont-init.d] 99-custom-files: executing... 
today at 01:15:54  [custom-init] no custom files found exiting...
today at 01:15:54  [cont-init.d] 99-custom-files: exited 0.
today at 01:15:54  [cont-init.d] done.
today at 01:15:54  [services.d] starting services
today at 01:15:55  [services.d] done.
ssddanbrown commented 3 years ago

Ah, Thanks, That tells me what the error is, I'll assign for the next patch release.

Setting the row format for the table might get you past this. Ensure you have a backup first. So something like:

USE bookstack; # Replace with database name
ALTER TABLE users DROP COLUMN slug;
ALTER TABLE users ROW_FORMAT=DYNAMIC;

# Then re-start container again.
steakhutzeee commented 3 years ago

Ah, Thanks, That tells me what the error is, I'll assign for the next patch release.

Setting the row format for the table might get you past this. Ensure you have a backup first. So something like:

USE bookstack; # Replace with database name
ALTER TABLE users DROP COLUMN slug;
ALTER TABLE users ROW_FORMAT=DYNAMIC;

# Then re-start container again.

Same error, i tried these commands also:

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
SET GLOBAL innodb_default_row_format=DYNAMIC;
ssddanbrown commented 3 years ago

Hi @steakhutzeee, I've just released v21.04.3: https://github.com/BookStackApp/BookStack/releases/tag/v21.04.3 (Linuxserver.io images are usually auto-built about an hour afterwards).

Within that I've altered our migrations to a lower sting length which I think should help avoid this.You'll need to drop the column again before upgrading but hopefully things should go smoothly for you now. Let me know how it goes.

steakhutzeee commented 3 years ago

Hi @steakhutzeee, I've just released v21.04.3: https://github.com/BookStackApp/BookStack/releases/tag/v21.04.3 (Linuxserver.io images are usually auto-built about an hour afterwards).

Within that I've altered our migrations to a lower sting length which I think should help avoid this.You'll need to drop the column again before upgrading but hopefully things should go smoothly for you now. Let me know how it goes.

Sure, thank you!

You think the 3 commands I issued could have done any harm?

ssddanbrown commented 3 years ago

@steakhutzeee Don't think they'd do harm, They'd often be the defaults you'd expect on a modern install anyway I think. I'm no DB pro though.

steakhutzeee commented 3 years ago

@ssddanbrown Yep! Dropped the column and updated, no issue so far. Thank you!!

ssddanbrown commented 3 years ago

@steakhutzeee Awesome! Will therefore close this off.