issue after updating 2.5.0.3 -> v2.8.1

Self-Hosted | v4/Legacy
1

Hi,

After updating with zip file and running the /update command it gives me this error:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘logo’ (SQL: alter table accounts add logo varchar(255) null, add logo_width int unsigned not null, add logo_height int unsigned not null, add logo_size int unsigned not null, add invoice_embed_documents tinyint(1) not null default ‘0’, add document_email_attachment tinyint(1) not null default ‘0’)

Which is correct, column name ‘logo’ already exists in table ‘accounts’.

2

It looks like there was a problem running the database migrations.

Can you check storage/logs/laravel-error.log for the past few errors.

3

This is the error in that file (masked my hostname with *****):

[2016-11-29 13:07:51] production.ERROR: Illuminate\Database\QueryException [HY000] : /vendor/laravel/framework/src/Illuminate/Database/Connection.php [Line 729] => SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ninja.#sql-4a2_12020e’ (errno: 150) (SQL: alter table documents add constraint documents_account_id_foreign foreign key (account_id) references accounts (id) on delete cascade) {“context”:“PHP”,“user_id”:0,“account_id”:0,“user_name”:"",“method”:“GET”,“url”:"/public/update",“user_agent”:“Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.98 Safari/537.36”,“ip”:“82.173.37.225”,“count”:1} []

4

i tried again fresh, no error in the browser this time just ‘Whoops, looks like something went wrong.’.

in laravel log:

[2016-11-29 14:06:33] production.ERROR: exception ‘PDOException’ with message ‘SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘migrations’ already exists’ in *****facturen/vendor/laravel/framework/src/Illuminate/Database/Connection.php:457 Stack trace: #0 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Connection.php(457): PDOStatement->execute(Array) #1 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Connection.php(722): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\MySqlConnection), ‘create table m…’, Array) #2 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Connection.php(685): Illuminate\Database\Connection->runQueryCallback(‘create tablem…’, Array, Object(Closure)) #3 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Connection.php(458): Illuminate\Database\Connection->run(‘create table m…’, Array, Object(Closure)) #4 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(83): Illuminate\Database\Connection->statement(‘create tablem…’) #5 *****facturen/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(229):

Should i remove the ‘migrations’ table?

5

To start fresh you’ll want to recreate the database.

6

And export invoices etc from old version so i can import in the new version? Will the import work in 2.8.1 from ‘old’ 2.5.0.3 export files?

7

No, to transfer your data you’d need to export to our latest version.

We’re working on a new minor release later today, we’ll include a fix which may help.

Note: you’ll need to comment out lines 13 to to 22 in database/migrations/2016_03_22_168362_add_documents.php

8

Okay, thank you for your support. I’ll wait for the new release.

p.s. tried to comment out the lines but got another error:

SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ninja.#sql-4a2_120a92’ (errno: 150) (SQL: alter table documents add constraint documents_account_id_foreign foreign key (account_id) references accounts (id) on delete cascade)

9

You’ll need to comment out those line using the next release.

10

We’ve released v2.8.2, hopefully it will help with this problem.

https://dl.dropboxusercontent.com/u/2909575/ninja-v2.8.2.zip

11

I have tried but I can’t get it to work.
Tried with original database from 2.5.0.3 and unzipped the 2.8.2 version, commented out lines 13 to to 22 in database/migrations/2016_03_22_168362_add_documents.php

But the same error appears:

SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ninja4.#sql-4a2_124faf’ (errno: 150) (SQL: alter table documents add constraint documents_account_id_foreign foreign key (account_id) references accounts (id) on delete cascade)

12

Can you check that your migration file is up to date, in particular line 44

https://github.com/invoiceninja/invoiceninja/blob/master/database/migrations/2016_03_22_168362_add_documents.php#L44

13

yes it is up to date, to be sure i copied the file but still the same error. If i try to access (without /update) I get:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘ninja4.companies’ doesn’t exist (SQL: select * from companies where companies.id is null and companies.deleted_at is null limit 1

with /update i get:

SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ninja4.#sql-4a2_12abf7’ (errno: 150) (SQL: alter table documents add constraint documents_account_id_foreign foreign key (account_id) references accounts (id) on delete cascade)

14

Can you check the documents table exists by running select * from documents

15

the table exists but it is empty, table structure (if that helps) is:
public_id int(10) unsigned DEFAULT NULL,
account_id int(10) unsigned NOT NULL,
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(10) unsigned NOT NULL,
invoice_id int(10) unsigned DEFAULT NULL,
expense_id int(10) unsigned DEFAULT NULL,
path varchar(255) COLLATE utf8_unicode_ci NOT NULL,
preview varchar(255) COLLATE utf8_unicode_ci NOT NULL,
name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
type varchar(255) COLLATE utf8_unicode_ci NOT NULL,
disk varchar(255) COLLATE utf8_unicode_ci NOT NULL,
hash varchar(40) COLLATE utf8_unicode_ci NOT NULL,
size int(10) unsigned NOT NULL,
width int(10) unsigned DEFAULT NULL,
height int(10) unsigned DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)

16

Try commenting table creation from the migration file.

17

I did and documents table is gone now. new error is:

SQLSTATE[HY000]: General error: 1005 Can’t create table ‘ninja4.#sql-4a2_12adc4’ (errno: 150) (SQL: alter table companies add constraint companies_payment_id_foreign foreign key (payment_id) references payments (id))

Seems like there is something wrong with my database or maybe permission problem or something? Is there a query i can try in phpmyadmin to check if that works to find the problem?

18

Maybe the foreign keys were already added, you can run this query to check

show create table documents;

19

im going from error to error, i feel like im doing something stupid. Maybe my update workflow is wrong.

  • I have 2.5.0.3 in folder A
  • I unzip 2.8.2 in folder B
  • Move files from B to A and overwrite
  • run example.com/folderA

Should i try another approach? edit a file in folderb and run /folderb for example?

sorry to bother you so much…

20

That should work. I think the next step is trying to comment out the foreign keys in the migration file.