Publisher does not support the Fluid field type. Please do not contact asking when support will be available.
If you purchased an add-on from expressionengine.com, be sure to visit boldminded.com/claim to add the license to your account here on boldminded.com.
Ticket: Database error when upgrading 1.6.11 -> 1.7.4 -> 2.4.0
Status | Resolved |
Add-on / Version | Publisher 2.4.0 |
Severity | |
EE Version | 3.5.4 |
Elli Pratt
Apr 07, 2017
Description:
We get the following database error when upgrading through the version path noted in the title. It seems that along the way something should have created a column named “short_name_segment” in the “exp_publisher_languages” table. I’ve looked through the code for both the 1.7.4 and 2.4.0 upgrades and can find no database operations which would add this column.
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publisher_m_Language_publisher_languages.short_name_segment' in 'field list':
SELECT publisher_m_Language_publisher_languages.id as publisher_m_Language__id, publisher_m_Language_publisher_languages.short_name as publisher_m_Language__short_name, publisher_m_Language_publisher_languages.short_name_segment as publisher_m_Language__short_name_segment, publisher_m_Language_publisher_languages.long_name as publisher_m_Language__long_name, publisher_m_Language_publisher_languages.locale as publisher_m_Language__locale, publisher_m_Language_publisher_languages.language_pack as publisher_m_Language__language_pack, publisher_m_Language_publisher_languages.cat_url_indicator as publisher_m_Language__cat_url_indicator, publisher_m_Language_publisher_languages.is_default as publisher_m_Language__is_default, publisher_m_Language_publisher_languages.is_enabled as publisher_m_Language__is_enabled, publisher_m_Language_publisher_languages.direction as publisher_m_Language__direction, publisher_m_Language_publisher_languages.sites as publisher_m_Language__sites FROM (`exp_publisher_languages` as publisher_m_Language_publisher_languages) LIMIT 18446744073709551615
Detailed steps to reproduce the issue:
1. Overwrite Publisher 1.6.11 plugin install with 1.7.4
2. Run module updates
3. Perform EE 3.5.4 upgrade, which includes upgrade to Publisher 2.4.0
BoldMinded (Brian)
Yep, already working on this. I’ll send you a new build shortly. Note that it will require upgrading to Publisher 1.7.5 in EE 2 first, or running this query before you start upgrading to EE 3.
BoldMinded (Brian)
Comment has been marked private.
Elli Pratt
Thanks for the rapid response! Where can we obtain a copy of Publisher 1.7.5? Incidentally, is there an even more recent version of 1.x that would serve us better?
BoldMinded (Brian)
Its included in the zip file in the private message just before your last comment 😊
Elli Pratt
Indeed it was. Sorry I missed that!
We have a new error now. This comes at the same point. I upgraded 1.6.11 -> 1.7.5 -> EE3/Pub2.4.1 and there’s another missing column:
<pre><code> SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘lang_id’ in ‘field list’: SELECT
site_pages
,lang_id
,status
FROM (exp_publisher_site_pages
) WHEREsite_id
= 1 ANDstatus
= ‘open’ [/cpde]BoldMinded (Brian)
Anyway you can provide the full stack trace so I know what line number thats from?
Elli Pratt
BoldMinded (Brian)
That helps a lot, thank you!
BoldMinded (Brian)
This is a little hacky, but it might work. In the Session.php file add this conditional.
https://www.dropbox.com/s/qr32btmqqwcrm4r/Screenshot 2017-04-07 14.17.19.png?dl=0
Elli Pratt
No such luck. After inserting this conditional I get the same error with an identical stack trace.
BoldMinded (Brian)
What if you change it to <= instead of <?
BoldMinded (Brian)
Elli, I think I understand what is happening now. I’ll try to have a fix for you by Monday or Tuesday.
BoldMinded (Brian)
Ok, it might be a quick fix. Try this:
https://www.dropbox.com/s/400dlltiuy0r3rx/Screenshot 2017-04-07 16.59.10.png?dl=0
BoldMinded (Brian)
Comment has been marked private.
BoldMinded (Brian)
Comment has been marked private.
BoldMinded (Brian)
A more recent 2.4.1 build has been uploaded and is available in your account.
BoldMinded (Brian)
The latest 2.4.2 release should fix these errors, so long as you’re fully upgrading Publisher in EE2 to 1.7.5 before going to EE3.
Elli Pratt
We thought we had this upgraded. We worked through the issues with 1.7.x with your help (thanks very much!) but ran into trouble upgrading into 2.4.x. We’ve run the addon update in the EE3 control panel, can access the Publisher control panel, and can even view channel entries with Publisher content, but when we save it seems we’re missing database fields. The table “exp_publisher_draft_previews” is missing the “entry_id” column. I can see where this should have been added as part of an update, but those updates seem to have not been run. We’ve done some experimentation but have had no luck in getting those updates to run.
BoldMinded (Brian)
Yeah I’ve got this fixed for the next release. For now you should be able to run these queries:
Elli Pratt
Thanks, Brian. This seems to have gotten us further. I believe now we’re running into an issue with some migrated Playa & Matrix fields. Publisher is holding on to old field IDs that no longer exist because they were Playa/Matrix field types which were deleted before upgrading to EE 3. What’s the best way to migrate this data within Publisher? We do have the capability to rollback to points previous in this process if it needs to happen prior to uninstalling Playa & Matrix.
BoldMinded (Brian)
Did you use this before you upgraded? https://github.com/EllisLab/PlayaMatrixImporter
If so then you shouldn’t have any old IDs.
BoldMinded (Brian)
I’ve run an EE2 to 3 upgrade using that importer and it migrates the fields correctly.
Elli Pratt
Here’s the error we get:
Field 3 was a Matrix multi_select_content field which no longer exists in the system. The content for field 3 is now in field 60. The stack trace indicates that the error originates from Publisher’s Entry.php:
BoldMinded (Brian)
Is field_id_3 the only one that is throwing the error? If Publisher was truly holding onto data as you suggest it would be a widespread issue, not specific to one column.
Did you clear all caches?
Did you delete the old Matrix fields in EE 2 before upgrading?
Do you see a field_id_60 in your exp_publisher_data table? What about field_id_3?
Did everything work fine in EE2 before you upgraded to EE3?
Do you mind sharing the content of field_id_3/60?
Elli Pratt
Actually while trying to update another entry, we received another column error
We cleared all caches.
After doing the Matrix/Playa migration, we uninstall the matrix and playa after plugins.
I do see both of those fields in the table.
Everything works okay in EE2 before the upgrade.
If it comes down to it, we might be able to work something out about sharing the content.
BoldMinded (Brian)
Would you be able to send me an export of your exp_channel_titles, exp_publisher_titles, exp_channel_data, and exp_publisher_data tables?
So if I’m reading this correctly the converter does everything correctly, saving an entry in EE 2 before upgrading does not yield any errors, but when you upgrade to EE 3 saving an entry yields errors?
Just displaying an entry on the front-end or CP shows all the correct data, but also does not yield any errors?
Elli Pratt
Okay, I got cleared to send you the database information. How should go about sending you that information?
Were you asking if while still on EE2 and after the migration of the fields, entries were still able to be updated correctly?
Yes, displaying an entry both on the front-end and the CP shows the correct information.
BoldMinded (Brian)
Just do a dump of those tables as .sql files. You can email them to support at boldminded dot com or share them on Dropbox, just mark your comment as private.
“Were you asking if while still on EE2 and after the migration of the fields, entries were still able to be updated correctly?” - Yes
Do you know which are the old columns and which are the new? You could try just deleting those old columns from the DB and see what happens. FWIW I’m going to be performing an upgrade this week using the converter, so I’ll be able to see if I encounter the same issue on a real site. If I see the issue and its just a matter of deleting those old columns I’ll add to Publisher’s upgrade scripts to delete the columns.
Elli Pratt
I’ve sent an export of the sql tables to the email above.
I would have to run through the upgrade process again, but as far as I’m aware, it still worked.
I do not know which are the old and new. I would have to run through the process again.
I can let you know a little later.
BoldMinded (Brian)
I got the sql but have not had a chance to look at it. FWIW, I just finished upgrading a site from 2 to 3 that was using Matrix and Playa, and I used the converter. In EE 3 I saved an entry that now has Grid fields replacing the Matrix fields and did not get any errors.
Elli Pratt
We think we found the issue. It appears that Playa was only being partially uninstalled. After fully uninstalling Playa, it fixed the database error on entry save.
Thank you.
BoldMinded (Brian)
Ah, that makes sense 😊 Glad you figured it out.
Elli Pratt
Hi Brian,
It seems we spoke too soon. We still get errors when saving most entries from the Detail Page channel. The stack trace is the same as was in this previous comment:
https://boldminded.com/support/ticket/1434#comment23
I did some debugging this morning and found that data coming into the save and cleanup functions included an empty string for field_id_3, even though this field has been deleted from the system. The exp_publisher_data table still contains a column of that name, but I don’t know if this is related.
BoldMinded (Brian)
Elli, I can see stray columns in my upgrade too, but for some reason I don’t get any errors. I’ll get an update to you tomorrow that will contain a cleanup script to drop the old/unused columns.
BoldMinded (Brian)
Comment has been marked private.
BoldMinded (Brian)
Comment has been marked private.
Elli Pratt
I’m getting an odd error when I click “yes drop these columns”.
BoldMinded (Brian)
Are you logged in as super admin? Might have to add this to your config for now.
$config[‘disable_csrf_protection’] = “y”;
Elli Pratt
It looks like dropping the tables fixes the error we were having! Is the publisher build you gave us able to be used in production? or will there be a public release soon?
Elli Pratt
Do we need to do anything to retain or migrate the data in those columns?
BoldMinded (Brian)
No. What the script does is look at the exp_channel_data table and compares its columns to exp_publisher_data, specifically the “field_id_*” columns, which are what EE adds for each custom field. So if field_id_123 was removed from exp_channel_data for whatever reason it should also be removed from the exp_publisher_data table. Publisher does what it can to listen to certain events via EE hooks or form posts to see if a column should be removed or changed, but sometimes it can’t listen for everything, thus some columns get stranded.
BoldMinded (Brian)
You should be able to use it in production, but with any other release test it locally or in dev first before going straight to prod.