Error when trying to reorder pages/chapters of a book #5595

lucasplcorrea · 2025-04-30T13:17:49Z

Describe the Bug

Ao clicar em ordenar, uma mensagem de erro é exibida

Steps to Reproduce

Go to a shelve
Select a book
Try to sort a page/chapter

Expected Behaviour

Reordering book pages

Screenshots or Additional Context

When analyzing the logs in /app/www/storage/logs, searching for "sort", I received the following record:

[2025-04-30 13:01:50] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'books.sort_rule_id' in 'WHERE' (Connection: mysql, SQL: select sort_rules.*, (select count(*) from books where sort_rules.id = books.sort_rule_id and books.deleted_at is null) as books_count from sort_rules order by name asc) (View: /app/www/resources/views/books/sort.blade.php) {"userId":5,"exception":"[object] (Illuminate\\View\\ViewException(code: 0): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'books.sort_rule_id' in 'WHERE' (Connection: mysql, SQL: select sort_rules.*, (select count(*) from books where sort_rules.id = books.sort_rule_id and books.deleted_at is null) as books_count from sort_rules order by name asc) (View: /app/www/resources/views/books/sort.blade.php) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'books.sort_rule_id' in 'WHERE' (Connection: mysql, SQL: select sort_rules.*, (select count(*) from books where sort_rules.id = books.sort_rule_id and books.deleted_at is null) as books_count from sort_rules order by name asc) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'books.sort_rule_id' in 'WHERE' at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:407)

My environment runs on docker, BookStack version: v25.02.1
DB: Maria DB 11.4.5

Browser Details

No response

Exact BookStack Version

v25.02.1

The text was updated successfully, but these errors were encountered:

ssddanbrown · 2025-04-30T14:32:17Z

Hi @lucasplcorrea,
This error indicates that the database migrations were not run, or failed during run, when perform the upgrade to BookStack v25.02. Is this the default/standard (without modification) linuxserver docker image? If so, restart the bookstack container and it should show the migration status via the container logs during startup. Check those logs, and feel free to share them back here for further assistance if needed.

lucasplcorrea · 2025-04-30T14:39:08Z

Yes, is a default linuxserver image, the startup log:

Illuminate\Database\QueryException 

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'sort_rules' already exists (Connection: mysql, SQL: create table `sort_rules` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `sequence` text not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
    821▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    822▕                 );
    823▕             }
    824▕ 
  ➜ 825▕             throw new QueryException(
    826▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    827▕             );
    828▕         }
    829▕     }

      +9 vendor frames 

  10  /app/www/database/migrations/2025_01_29_180933_create_sort_rules_table.php:14
      Illuminate\Support\Facades\Facade::__callStatic()
      +25 vendor frames 

  36  /app/www/artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

[custom-init] No custom files found, skipping...
[ls.io-init] done.
[migrations] started
[migrations] 01-nginx-site-confs-default: skipped
[migrations] 02-default-location: skipped
[migrations] done
usermod: no changes
───────────────────────────────────────

      ██╗     ███████╗██╗ ██████╗
      ██║     ██╔════╝██║██╔═══██╗
      ██║     ███████╗██║██║   ██║
      ██║     ╚════██║██║██║   ██║
      ███████╗███████║██║╚██████╔╝
      ╚══════╝╚══════╝╚═╝ ╚═════╝

   Brought to you by linuxserver.io
───────────────────────────────────────

To support LSIO projects visit:
https://www.linuxserver.io/donate/

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    1000
User GID:    1000
───────────────────────────────────────
Linuxserver.io version: v25.02.1-ls198
Build-date: 2025-03-17T18:31:08+00:00
───────────────────────────────────────
    
using keys found in /config/keys
**** The following active confs have different version dates than the samples that are shipped. ****
**** This may be due to user customization or an update to the samples. ****
**** You should compare the following files to the samples in the same folder and update them. ****
**** Use the link at the top of the file to view the changelog. ****
┌────────────┬────────────┬────────────────────────────────────────────────────────────────────────┐
│  old date  │  new date  │ path                                                                   │
├────────────┼────────────┼────────────────────────────────────────────────────────────────────────┤
│ 2024-05-27 │ 2024-12-17 │ /config/nginx/nginx.conf                                               │
│ 2023-08-13 │ 2024-12-06 │ /config/nginx/ssl.conf                                                 │
└────────────┴────────────┴────────────────────────────────────────────────────────────────────────┘
Waiting for DB to be available

   INFO  Running migrations.  

  2025_01_29_180933_create_sort_rules_table ..................... 18.27ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'sort_rules' already exists (Connection: mysql, SQL: create table `sort_rules` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `sequence` text not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
    821▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    822▕                 );
    823▕             }
    824▕ 
  ➜ 825▕             throw new QueryException(
    826▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    827▕             );
    828▕         }
    829▕     }

      +9 vendor frames 

  10  /app/www/database/migrations/2025_01_29_180933_create_sort_rules_table.php:14
      Illuminate\Support\Facades\Facade::__callStatic()
      +25 vendor frames 

  36  /app/www/artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

[custom-init] No custom files found, skipping...
[ls.io-init] done.

ssddanbrown · 2025-04-30T15:26:17Z

Okay, so there's a clear issue.

Did you perform a database migration/import at any point recently?
Have you created/used any "Sort Rules" (Settings > Sort Rules) in the system yet?

lucasplcorrea · 2025-04-30T16:39:15Z

I haven't done any migration recently, but some time ago, I moved the system behind a domain, changing the URL; it was local and I published it on the internet.

That was the last major change I made to the system. On the other hand, I don't remember having reordered any pages since then.

In the system logs, I see records of book_sort already done after this migration, so I don't believe that's specifically the problem.

In addition, when I try to access the path you suggested: "Sort Rules" (Settings > Sort Rules), the same error occurs.

I searched the system logs for sort_rule_create/update/dele, but I didn't find any records of rules in that sense (I imagined that another user could have created something, but I didn't find anything).

ssddanbrown · 2025-04-30T17:01:44Z

Okay, thanks for the extra info.
Not sure what's occurred here. Either way, we'll need to align the database state so the database migrations can continue.

Are you using something to automatically update the container images used?
Are you familiar with managing/accessing a database like mysql?
Are you familiar with running commands inside a docker image?

lucasplcorrea · 2025-04-30T20:52:41Z

1 - Yes, I use Watchtower, which automatically updates my containers
2 - Yes, I am. I use Dbeaver to connect to Bookstack's MariaDB when I need to. I've even done backup and restore tests directly from the Bookstack database a few times
3 - Yes, I am. They're the same ones used in Linux. To make things easier, I use 1Panel, which allows me to run commands inside the container.

ssddanbrown · 2025-04-30T21:33:57Z

Alrighty, thanks for the answers.

In regards to watchtower, I often hear of these kinds of errors occur in watch-tower based environments. Based on a quick previous look, i assume some kind of timeout can occur during the upgrade leaving things in a bad state.
I'd advise using it for notification of updates only (if possible), and make the updates manually.

Before anything else, backup your data. Especially the database.

Now, can you enter the database and drop the sort_rules table?
Once done, restart the BookStack container, watching the first start-up logs for any errors or, hopefully if things work, non-error migration notices. Report back any errors, otherwise you should see the sort_rules table get re-created, and you should no longer have errors.

lucasplcorrea · 2025-05-02T13:59:25Z

I apologize for the delay in responding, yesterday was a holiday in my city and I couldn't work on it.

I did what you asked, dropped the table, restarted the container, and adjusted the watchtower only for notifications. During the restart, I even saw that it executes the table creation migrations in question, but in Dbeaver they are not displayed, and the error persists when accessing the page reordering section.

I will send you some prints and logs for analysis.

[ls.io-init] done.
[migrations] started
[migrations] 01-nginx-site-confs-default: skipped
[migrations] 02-default-location: skipped
[migrations] done
usermod: no changes
───────────────────────────────────────

      ██╗     ███████╗██╗ ██████╗
      ██║     ██╔════╝██║██╔═══██╗
      ██║     ███████╗██║██║   ██║
      ██║     ╚════██║██║██║   ██║
      ███████╗███████║██║╚██████╔╝
      ╚══════╝╚══════╝╚═╝ ╚═════╝

   Brought to you by linuxserver.io
───────────────────────────────────────

To support LSIO projects visit:
https://www.linuxserver.io/donate/

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    1000
User GID:    1000
───────────────────────────────────────
Linuxserver.io version: v25.02.1-ls198
Build-date: 2025-03-17T18:31:08+00:00
───────────────────────────────────────
    
using keys found in /config/keys
**** The following active confs have different version dates than the samples that are shipped. ****
**** This may be due to user customization or an update to the samples. ****
**** You should compare the following files to the samples in the same folder and update them. ****
**** Use the link at the top of the file to view the changelog. ****
┌────────────┬──────────   INFO  Running migrations.  

  2025_01_29_180933_create_sort_rules_table ..................... 49.51ms DONE
  2025_02_05_150842_add_sort_rule_id_to_books ................... 89.20ms DONE

[custom-init] No custom files found, skipping...
[ls.io-init] done.
[migrations] started
[migrations] 01-nginx-site-confs-default: skipped
[migrations] 02-default-location: skipped
[migrations] done
usermod: no changes
───────────────────────────────────────

      ██╗     ███████╗██╗ ██████╗
      ██║     ██╔════╝██║██╔═══██╗
      ██║     ███████╗██║██║   ██║
      ██║     ╚════██║██║██║   ██║
      ███████╗███████║██║╚██████╔╝
      ╚══════╝╚══════╝╚═╝ ╚═════╝

   Brought to you by linuxserver.io
───────────────────────────────────────

To support LSIO projects visit:
https://www.linuxserver.io/donate/

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    1000
User GID:    1000
───────────────────────────────────────
Linuxserver.io version: v25.02.1-ls198
Build-date: 2025-03-17T18:31:08+00:00
───────────────────────────────────────
    
using keys found in /config/keys
**** The following active confs have different version dates than the samples that are shipped. ****
**** This may be due to user customization or an update to the samples. ****
**** You should compare the following files to the samples in the same folder and update them. ****
**** Use the link at the top of the file to view the changelog. ****
┌────────────┬────────────┬────────────────────────────────────────────────────────────────────────┐
│  old date  │  new date  │ path                                                                   │
├────────────┼────────────┼────────────────────────────────────────────────────────────────────────┤
│ 2024-05-27 │ 2024-12-17 │ /config/nginx/nginx.conf                                               │
│ 2023-08-13 │ 2024-12-06 │ /config/nginx/ssl.conf                                                 │
└────────────┴────────────┴────────────────────────────────────────────────────────────────────────┘
Waiting for DB to be available

   INFO  Nothing to migrate.  

[custom-init] No custom files found, skipping...
[ls.io-init] done.

if there is any way to perform this migration manually, maybe I could try it

before performing the action, I made a backup as suggested.

lucasplcorrea · 2025-05-05T18:36:13Z

Any news regarding this case?

ssddanbrown · 2025-05-08T14:38:24Z

@lucasplcorrea no, not really.
It's odd that the migrations are running, but you are not seeing changes.
Do these migrations lines (which you highlighted with a red border in your screenshot) re-appear when restarting the container? Or does something else show?

and the error persists when accessing the page reordering section.

What exactly is this error? What are you seeing? What are the logged messages?
Just wondering if it's actually the same error or something else at play.

lucasplcorrea · 2025-05-08T20:29:32Z

No, it only appeared once!
A new version of bookstack was recently released, is it safe to update? If I update, will these migrations run again?

In the logs I find the following error filtering by | grep sort

[2025-05-02 13:53:25] production.ERROR: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) (View: /app/www/resources/views/books/sort.blade.php) {"userId":5,"exception":"[object] (Illuminate\\View\\ViewException(code: 0): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) (View: /app/www/resources/views/books/sort.blade.php) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (Illuminate\\Database\\QueryException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:407)

[2025-05-07 14:17:56] production.ERROR: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) (View: /app/www/resources/views/books/sort.blade.php) {"userId":5,"exception":"[object] (Illuminate\\View\\ViewException(code: 0): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) (View: /app/www/resources/views/books/sort.blade.php) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (Illuminate\\Database\\QueryException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist (Connection: mysql, SQL: select `sort_rules`.*, (select count(*) from `books` where `sort_rules`.`id` = `books`.`sort_rule_id` and `books`.`deleted_at` is null) as `books_count` from `sort_rules` order by `name` asc) at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[previous exception] [object] (PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:407)

ssddanbrown · 2025-05-08T21:50:47Z

No, it only appeared once!

Okay, that's what I'd expect from a working setup. Means the database state is stable.

If I update, will these migrations run again?

They shouldn't do, since they've already ran (and there's no further migrations in the recent patch releases).

2025-05-07 14:17:56] production.ERROR: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookstack.sort_rules' doesn't exist

This is so strange. I can't see how the table does not exist, since you saw the migrations run in the log, which would have had to create this table.
The most likely thing to me would be that there's maybe multiple instances getting mixed up here? Unlikely but it's something I've seen before when supporting people.

Otherwise, you could access a shell in the active BookStack container, then enter the /app/www folder in the container, and run: php artisan migrate:status | tail -n 5, just to help confirm which migrations have been run.
It's important that this is ran in the running container to be sure it's the instance seen via the browser.
You can maybe test this by running php artisan down via the same way. Access via the browser should then show a maintenance message. You could restore the instance via php artisan up.

lucasplcorrea · 2025-05-09T13:40:03Z

When I hit php artisan migrate:status | tail -n 5

When I hit php artisan down

And up

ssddanbrown · 2025-05-09T13:48:44Z

That all looks as expected.

What about this from the BookStack container (ran like before)?

php artisan tinker --execute='dd(array_filter(Schema::getTables(), fn($table) => str_contains($table["name"], "sort")));'

This should tell us if the table exists from the PHP code perspective.

lucasplcorrea · 2025-05-09T18:32:22Z

root@0bf709c923f8:/app/www# php artisan tinker --execute='dd(array_filter(Schema::getTables(), fn($table) => str_contains($table["name"]
, "sort")));'
[] // vendor/psy/psysh/src/ExecutionClosure.php(40) : eval()'d code:1
root@0bf709c923f8:/app/www#

ssddanbrown · 2025-05-09T22:01:46Z

Okay, that's super odd.
Maybe we just have to accept the table wasn't created here and create it manually.

Before that though, can you check your books table in the database and confirm if there's a sort_rule_id column (might be at the very end of the column list). Need to know if that column creation has also gone wrong.

lucasplcorrea · 2025-05-12T12:11:37Z

Yes, it is a very strange thing to happen, especially since there are logs indicating that the migrations have been completed. If you can help me create it manually, I would be grateful.

there is also no sort_rule_id table that you mentioned.

ssddanbrown · 2025-05-13T10:28:58Z

@lucasplcorrea Okay, lets try allowing the migrations to re-run.

Before anything, I'd strongly advise a full backup of the instance data, especially the database data.

In the database, look at the migrations tables.
Delete the latest two rows (with the highest IDs). The batch column value for these should both be 4.
These should be the 2025_01_29_180933_create_sort_rules_table and 2025_02_05_150842_add_sort_rule_id_to_books named migrations.

With that done, restart the BookStack container, watching first start-up logs again.
You should see these migrations run again, and this time hopefully you'll see the relevant database changes, with the sort_rules table appearing.

lucasplcorrea · 2025-05-16T12:53:12Z

Works like a charm. I deleted the rows as suggested, and on reboot the tables were recreated, I can order the pages again.

Thanks for the support. Now I will run the container update.

ssddanbrown · 2025-05-16T22:23:32Z

Great news, Good to hear that finally worked! Not sure why it didn't before, very strange!

lucasplcorrea added the 🐛 Bug label Apr 30, 2025

ssddanbrown added 🐕 Support and removed 🐛 Bug labels Apr 30, 2025

lucasplcorrea closed this as completed May 16, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error when trying to reorder pages/chapters of a book #5595

Error when trying to reorder pages/chapters of a book #5595

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented May 2, 2025

lucasplcorrea commented May 5, 2025

ssddanbrown commented May 8, 2025

lucasplcorrea commented May 8, 2025

ssddanbrown commented May 8, 2025

lucasplcorrea commented May 9, 2025

ssddanbrown commented May 9, 2025

lucasplcorrea commented May 9, 2025

ssddanbrown commented May 9, 2025

lucasplcorrea commented May 12, 2025

ssddanbrown commented May 13, 2025

lucasplcorrea commented May 16, 2025

ssddanbrown commented May 16, 2025

Error when trying to reorder pages/chapters of a book #5595

Error when trying to reorder pages/chapters of a book #5595

Comments

lucasplcorrea commented Apr 30, 2025

Describe the Bug

Steps to Reproduce

Expected Behaviour

Screenshots or Additional Context

Browser Details

Exact BookStack Version

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented Apr 30, 2025

ssddanbrown commented Apr 30, 2025

lucasplcorrea commented May 2, 2025

lucasplcorrea commented May 5, 2025

ssddanbrown commented May 8, 2025

lucasplcorrea commented May 8, 2025

ssddanbrown commented May 8, 2025

lucasplcorrea commented May 9, 2025

ssddanbrown commented May 9, 2025

lucasplcorrea commented May 9, 2025

ssddanbrown commented May 9, 2025

lucasplcorrea commented May 12, 2025

ssddanbrown commented May 13, 2025

lucasplcorrea commented May 16, 2025

ssddanbrown commented May 16, 2025