BoldMinded

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: Publish entry updates wrong column on publisher_titles table

Status Resolved
Add-on / Version Publisher Lite 2.6.4
Severity
EE Version 3.5.11

David Clough

Oct 13, 2017

There are instances where the primary key for an entry do not match on publisher_titles and publisher_data. This appears to cause an issue where, when I publish an entry with an updated title, it updates the publisher_title table row with using the key for publisher_data, effectively overwriting the wrong entry’s data.

Looking at lines 141-153 of system/user/addons/publisher/Service/Entry/Entry.php indicates you are familiar with the problem to some degree, it perhaps just needs to be revisited.

This also creates a more a minor issue where if you return to edit the entry, the “A more recent draft is available.” message persists despite no more recent draft created for the entry.

Steps to reproduce the issue:
1. Create two channel entries, edit the database so that the id columns in channel_title match keys in channel_data, but refer to different entries
2. Edit the channel entry title and publish
3. Entry title is updated in the entries list, but if you return to the entry editor the title has not changed.

#1

BoldMinded (Brian)

Hi, David. This is very peculiar because as you saw by the comments in that file I thought this issue was fixed some time ago. This is the first such report since I’ve made that change.

What other add-ons do you have installed?

#2

BoldMinded (Brian)

I made that change back in April, and since then this is the first time someone has reported an issue with it. Not saying you’re not seeing the issue, just confused as to what is happening and why its suddenly happening again.

If you change the primary key id on both of those tables for an entry (manually set them to 99999 or something ridiculous), does it save correctly?

#3

BoldMinded (Brian)

Also, thanks for the detailed report. It sounds like you did a little snooping around and narrowed down the issue. I appreciate that 😊

This is a bit of a shot in the dark, and I don’t know if it will help fix your currently mis-matched rows, but I think it might prevent future issues. In that file try changing the join statement to this and see what happens.

->join('exp_publisher_data AS d', 't.entry_id = d.entry_id AND t.status = d.status AND t.lang_id = d.lang_id')
#4

David Clough

I tested and manually changed the ids in the tables to match and the title update was fine.

I have tried the updated join and it doesn’t help with this specific issue. I have done some more digging and it may be an envionmental problem that could be the cause if you cannot recreate. I am using a MariaDB 10.1.28 database server on PHP 7.1.10.

This issue appears to be the result of $query->first_row(). Authough the raw SQL query will return two columns labelled ‘id’ with two different value, through Expression Engine’s DB class they return an object with only one id value (the data id value seems to be preferred).

#5

BoldMinded (Brian)

I’m at the EE conf right now so I don’t have much time to dig into this, but your last statement about the query pulling both id columns gave me an idea. I tested this locally by manually changing the id value in the publisher_data table for an entry so it does not match the id in publisher_titles and loaded and re-saved an entry and it seemed to do the trick. Try making this change instead (remove that last join statement change)

$query = ee()->db->select('t.*, d.*, d.id as data_id')
#6

BoldMinded (Brian)

Actually never mind, that doesn’t seem to fix it.

#7

BoldMinded (Brian)

So, this is obviously a problem, but I’m wondering how it came to exist because there should always be a row in the data table that matches the title table. The only way for them to get out of sync is if something (or someone) else removes a row. What other add-ons do you have installed? Are you doing some sort of custom import or anything else that may be directly touching the publisher_titles or publisher_data tables?

#8

BoldMinded (Brian)

How many of these mismatched entries do you have? This query should return problematic rows:

select *, t.id as title_id, d.id as data_id from exp_publisher_titles as t
join exp_publisher_data as d on t.entry_id = d.entry_id and t.lang_id = d.lang_id and t.status = d.status
where t.id != d.id;
#9

David Clough

I have 600+ mismatched entries. I tried tried uninstalling and reinstalling to re-populate the tables but that still produced a high number of mismatches.

The site also uses the Structure add-on, there is no custom importer, and until this point no manual edits or removal of rows on either the publisher_title publisher_data tables were made.

Ultimately, I have manually synchronised the IDs between the two tables and saving titles and data now behaves as expected. I am still concerned that future entries will fall out of sync and have the same issue, is there any way to ensure this doesn’t happen?

#10

BoldMinded (Brian)

That seems like a lot of mismatches. Just out of curiosity, have you tried, or willing to try, the un-install and re-install with MySQL to see if there may be something specific in MariaDB? At this point I don’t have a definitive cause, so its hard to find the fix. Those tables should always have rows that match, so in theory it should always be entering the same auto-incrementing db value into each. One thought I had was to add a new column called data_id that is the auto-incrementor/primary key, then change id to be just a normal int column since EE’s data model really wants to seem to use that value since its the same id that is in publisher_titles. As I was thinking about this more I wondered how this isn’t an issue with EE’s core tables b/c instead of id being the column name, its entry_id, and entry_id is the primary key on both tables. I basically copied their data model for my models, but maybe its an underlying issue with the inability to support composite indexes. I may talk to Derek at EllisLab about this to see if he has any issues, but until then if you wouldn’t mind trying to replicate it with MySQL maybe at least it could be pinpointed to a MariaDB issue if that is the only place it happens, then it may be easier to fix.

#11

David Clough

I have looked further into the issue and it is not MariaDB specific. It seems to come from the data migration which takes place during Publisher Lite installation

The publisher_titles and publisher_data have an auto incrementing id column, but when migrating in data from channel_titles and channel_data in 50 post chunks it assumes the database queries for those tables will return the entries in identical order. In the site i am developing this is not the case causing a mismatch in ids in the new tables which breaks Publisher functionality for the duration of it’s installation.

Below is a patch to try and tackle the issue

diff --git a/system/user/addons/publisher/legacy/models/publisher_entry.php b/system/user/addons/publisher/legacy/models/publisher_entry.php
index a9de458..99d7bed 100644
--- a/system/user/addons/publisher/legacy/models/publisher_entry.php
+++ b/system/user/addons/publisher/legacy/models/publisher_entry.php
@@ -97,6 +97,8 @@ class Publisher_entry extends Publisher_model
 
         if ($entryCount) {
             $insertColumns = null;
+            $insertDataColumns = "INSERT INTO `". ee()->db->dbprefix ."publisher_data` (entry_id, site_id, channel_id, lang_id, status)";
+            $insertDataData = [];
             $insertData = [];
             $entryIds = [];
             $existingEntryIds = [];
@@ -171,12 +173,14 @@ class Publisher_entry extends Publisher_model
                     if (!$insertColumns) {
                         $insertColumns = "INSERT INTO `". ee()->db->dbprefix ."publisher_titles` (".implode(', ', array_keys($data)).")";
                     }
+                    $insertDataData[] = "(".implode(', ', [$data['entry_id'], $data['site_id'], $data['channel_id'], $data['lang_id'], $data['status']]).")";
                     $insertData[] = "(".implode(', ', array_values($data)).")";
                 }
             }
 
             if (!empty($insertData)) {
                 ee()->db->query($insertColumns . ' VALUES ' . implode(',', $insertData));
+                ee()->db->query($insertDataColumns . ' VALUES ' . implode(',', $insertDataData));
             }
         }
 
@@ -184,11 +188,11 @@ class Publisher_entry extends Publisher_model
             $custom_fields = [];
             $insertColumns = null;
             $insertData = [];
-            $entryIds = [];
 
             // Now handle the channel_data
-            $qry = ee()->db->limit(self::MAX_IMPORT_ENTRIES, $offset)->get('channel_data');
+            $qry = ee()->db->where_in('entry_id', $entryIds)->get('channel_data');
 
+            $entryIds = [];
             $existingEntryIds = [];
 
             foreach ($qry->result_array() as $row) {
@@ -232,17 +236,13 @@ class Publisher_entry extends Publisher_model
                     }
                 }
 
-                if ($updating) {
-                    $where = array_merge([
-                        'site_id' => $row['site_id'],
-                        'channel_id' => $row['channel_id'],
-                        'entry_id' => $row['entry_id']
-                    ], $where);
+                $where = array_merge([
+                    'site_id' => $row['site_id'],
+                    'channel_id' => $row['channel_id'],
+                    'entry_id' => $row['entry_id']
+                ], $where);
 
-                    $this->insert_or_update('publisher_data', $data, $where);
-                } else {
-                    $insertData[] = "INSERT INTO `". ee()->db->dbprefix ."publisher_data` (".implode(', ', array_keys($data)).") VALUES (".implode(', ', array_values($data)).")";
-                }
+                $this->insert_or_update('publisher_data', $data, $where);
             }
 
             if (!empty($insertData)) {
#12

BoldMinded (Brian)

Ah, this makes a lot more sense now. That code is super old and apparently it was fine in EE2 for those primary keys to not match, but EE3’s new model class seems to handle it differently, which is why the issue just now became noticeable. So is this patch resolving the issue for you?

Also, its not very often that someone sends me a patch like this. Thanks for looking into the issue and sending this.

#13

BoldMinded (Brian)

I’m a little confused how this patch fixes the issue. I applied the patch and ran a fresh install in my dev environment and it created the rows in each table, but it also did not import the values into exp_publisher_data for the field_id_N columns and it threw a sql error when it finished the install.

The current migrate() function was tested against 10,000 entries, and in my test just now I had 45 entries, and each of them had the correct id column value.

#14

BoldMinded (Brian)

David, just a bit of an update here. I’m able to replicate this issue consistently in EE4. I think I know how to fix it, but it could be a week or two.

#15

BoldMinded (Brian)

Comment has been marked private.

#16

BoldMinded (Brian)

This has been resolved with the 2.7 release.

Login to reply