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.