Database error when updating Assets 1.1.4 to latest (2.2.4)

Question

EE v2.4 but also tried updating first to EE 2.8 with same results as per below.

Christiaan

When trying to update I receive the following:

A Database Error Occurred
Error Number: 1265

Data truncated for column 'folder_id' at row 2

ALTER TABLE exp_assets_files MODIFY COLUMN `folder_id` INT(10) NOT NULL AFTER `file_id`

Filename: /Library/WebServer/sites/disegno/_ee/_third_party/assets/upd.assets.php

Line Number: 551

And then if I try again I get the following:

A Database Error Occurred
Error Number: 1146

Table 'disegno_com_au.exp_assets_entries' doesn't exist

SHOW COLUMNS FROM `exp_assets_entries` LIKE "var_id"

Filename: /Library/WebServer/sites/disegno/_ee/_third_party/assets/upd.assets.php

Line Number: 341

Answer 1

Follow-up and fix from P&T support... 'about to be' released v2.2.5 solved the issue.

the most common error for this is because some of the files that are registered for Assets fields are missing and during the update to Assets 2.x, Assets does not know where in folder structure the file belongs, so it gets a folder_id of NULL. After that, there's a migration that sets the folder_id column to NOT NULL, so that produces the error you're having about data being truncated.

Then, when you run it again, some tables have been modified already, but Assets begins the process anew and that produces the second error :)

Try the attached 2.2.5 version which is not yet released - it should work for you. You will have to revert the DB to the state it was before the failed update, though.

Answer 2

Here's how I fixed this issue, I've had it on a couple of different sites.

Basically it's because of corrupted data in the exp_assets or the exp_assets_files table.

If you're on an earlier version of Assets you'll only have the exp_assets table.

Check the data in that table. If there is a row in that table that contains a file that doesn't start with {filedir_1} (or {filedir_2} etc.) you have a problem. Delete or fix any row that has a filename without {filedir_1}.

Then, if you have an newer version of Assets, you'll have the exp_assets_files table instead. If there are any entries in this that has folder_id NULL those need to be set to any valid folder id (e.g. 1). Or just deleted.