Upgrade from 0.27.5 to 0.30.2 causes error with php artisan migrate

[thelastpsion]

Describe the bug When upgrading from 0.27.5 to 0.30.2, I get the following error when running php artisan migrate: Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'id'; check that column/key exists (SQL: alter table `joint_permissions` drop `id`)

Steps To Reproduce Steps to reproduce the behavior:

1. Have installation of 0.27.5
2. Attempt upgrade using git pull origin release && composer install --no-dev && php artisan migrate

Expected behavior Completion of migration to 0.30.2

Screenshots

Your Configuration (please complete the following information):

• Exact BookStack Version (Found in settings): 0.30.2 (from 0.27.5)
• PHP Version: 7.2.33
• Hosting Method (Nginx/Apache/Docker): Apache

Additional context Add any other context about the problem here.

[ssddanbrown]

Hi @PocketNerdIO, Sorry to hear you're running into issues.

I suspect this is actually due to a different error thrown on original upgrade which has left the database in an awkward state. I'd suspect this table may be using an old data format.

Before proceeding, Ensure you have a backup of the database.

Coudl you try running the below commands on your database to see if they'd then enable you to migrate as intended?

use bookstack; # Rename to your own database. 
alter table joint_permissions add column id int;
alter table joint_permissions ENGINE=InnoDB;

[gramakri]

We hit this in the old Cloudron packages as well. I had to first run "alter table joint_permissions add column id int;". Even after that, we got several duplicate constraint errors. I had to manually remove several rows from the joint_permissions table by hand since there were duplicates of the primary key (which was a 4-tuple). So, I had

a) run alter table joint_permissions add column id int; b) run migration . it will fail with a constraint violation c) remove the duplicate row mentioned in step b). now go back to step a)

Unfortunately, I forgot to keep a history of the terminal session, sorry about that. Leaving a note here, in case someone else find it's useful.

[QuaverSager]

Hello! I'm completely new to doing a project like this. With no experience with programming. @ssddanbrown how would one write out those commands for Windows Command Line?

[ssddanbrown]

Hi @QuaverSager, Those commands are intended to be ran against the database (MySQL or MariaDB). How you access that will really depend on how you've installed BookStack and the database. I can try to help further if you can explain your setup/install process.

[QuaverSager]

@ssddanbrown Thank you! I've been using this video as a guide: https://youtu.be/lSaY4lK_RjM Naming and putting files in nearly the exact setup since I'm unfamiliar with this. And I've been running all the commands via the windows command-line interface, same as in the video.

[ssddanbrown]

@QuaverSager Okay, Following the guide at timestamp 10:44 it looks like you would have used PHPmyAdmin to set up the database for BookStack?

If so, Login as you would have in that guide at 10:44.

---

Before you go any further, Backup your database. Click the "Home" icon under the phpMyAdmin logo in the top left, Click on the "Export" tab at the top. On that page, The "Export Method" should be "quick" and "Format" should be "SQL". Click "Go" in the bottom right then store that sql file somewhere safely. If this process fails do not continue.

---

Now, to run the commands, Click your bookstack table in the left column to select it. At the top click on the "SQL" tab. This should show a big text box with "Run SQL query/queries on database bookstack" above it. You can paste the commands in there then press the "Go" button in the bottom right corner.

That should be it, now you can re-attempt the upgrade.

[ruben-rodriguez]

I was also getting this issue, after running

use bookstack; # Rename to your own database. 
alter table joint_permissions add column id int;
alter table joint_permissions ENGINE=InnoDB;

I was getting:

Starting Migration...
Migrating: 2020_08_04_111754_drop_joint_permissions_id
Illuminate\Database\QueryException  : SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-BookStack\Chapter-4-page-create' for key 'PRIMARY' (SQL: alter table `joint_permissions` add primary key `joint_primary`(`role_id`, `entity_type`, `entity_id`, `action`))

at /var/www/bookstack/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[23000]: Integrity constraint violation: 1062 Duplicate entry '4-BookStack\Chapter-4-page-create' for key 'PRIMARY'")
       /var/www/bookstack/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:131

  2   PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-BookStack\Chapter-4-page-create' for key 'PRIMARY'")
      /var/www/bookstack/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:129

Effectively, when trying to run such alter table to add that primary key it failed:

alter table `joint_permissions` add primary key `joint_primary`(`role_id`, `entity_type`, `entity_id`, `action`);
ERROR 1062 (23000): Duplicate entry '1-BookStack\Book-4-chapter-create' for key 'PRIMARY'

Finally made it work by: 1 - Adding ID column. 2 - Removing duplicates by running the primary key alter with IGNORE 3 - Dropping the primary key so Syntax error or access violation: 1068 Multiple primary key defined is not triggered. 4 - Trying again migration process.

use bookstack; # Rename to your own database. 
alter table joint_permissions add column id int;
alter table joint_permissions ENGINE=InnoDB;
alter ignore table `joint_permissions` add primary key `joint_primary`(`role_id`, `entity_type`, `entity_id`, `action`);
alter table `joint_permissions` drop primary key;

It is now working fine 😎

[QuaverSager]

@ssddanbrown Thank you so much! That helped me get past where I was stuck.

[ssddanbrown]

Due to the lack of response from the original issue author I will close this. Thanks to everyone above that's provided support or advice.

[tolaleng]

Migrating: 2020_08_04_111754_drop_joint_permissions_id

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'id'; check that column/key exists (SQL: alter table joint_permissions drop id)

at */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: 1091 Can't DROP 'id'; check that column/key exists") */vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:131

2 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'id'; check that column/key exists") */vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:129

Please use the argument -v to see more details.

[tolaleng]

I have run the command as above following @ssddanbrown but it still not fix and the error still exists.

[tolaleng]

The migration is successful, but when I log in with default admin it is shown below. I had tried to install with many times with a fresh server, but still getting this error. Anyone can help me to solve it?

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owned_by' in 'where clause' (SQL: select * from activities where (exists (select id from joint_permissions where joint_permissions.entity_id=activities.entity_id and joint_permissions.entity_type=activities.entity_type and action = view and role_id in (1) and (has_permission = 1 or (has_permission_own = 1 and owned_by = 1)))) order by created_at desc limit 10 offset 0)

[ssddanbrown]

Hi @tolaleng, This issue is closed so it's easy to miss things here.

Can you open a new issue for this? And in that issue please paste the output of running php artisan migrate:status.