Region permissions not saving

    I've got a newly-updated-to-Runway site and it has changed some region permissions, which I didn't expect. The website owner (I've called the role 'Editor') can edit the content in the regions but they can't publish – it saves the changes as a draft but the 'Save as draft' checkbox is greyed out.


    They needed the content out here ASAP so I did the honours as I'm the Primary Admin and I can uncheck the 'Save as draft' checkbox.


    I then went to change their permissions and something strange is happening… I go to the region, then Region Options, and in the permissions section it looks like:


    May be edited by

    [x] Everyone

    [x] Admin

    [ ] Editor


    Drafts may be published by

    [ ] Everyone

    [x] Admin

    [ ] Editor


    The problem is, when I check 'Everyone' in 'Drafts may be published by' and hit Save, it says 'Successfully updated' at the top and the checkboxes look right when I scroll down to check, but when I reload the page and scroll down the checkboxes go back to the way they were.


    I've run the page with define('PERCH_DEBUG', true); in my config and I'm seeing a red line:


    Quote

    Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'regionPublishRoles' in 'field list'


    The full debug looks like this:


    0 recover(): ">[1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='eb76dda1f33b22833c5d923c2cbfccef' LIMIT 1
    0.0006 update(): ">UPDATE perch2_users SET userHash='74bfd0f4702436550b00fce46982b5f0' WHERE userID='1'
    0.0036 get(): ">[36] SELECT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
    0.0032 add_new_apps(): ">[8] SELECT itemValue FROM perch2_menu_items WHERE itemType='app'
    0.0107 find(): ">[1] SELECT * FROM perch2_content_regions WHERE regionID=45 LIMIT 1
    0.0102 all(): ">[2] SELECT * FROM perch2_user_roles ORDER BY roleTitle ASC
    0.0057 get_menu(): ">[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
    0.001 get_items(): ">[3] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
    0.0012 get_value(): ">[1] SELECT itemTitle FROM perch2_menu_items WHERE itemType='app' AND itemValue='content' LIMIT 1
    0.0004 get_menu(): ">[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
    0.0002 get_items(): ">[2] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
    0.0017 title_panel(): ">File: /core/apps/content/modes/region.options.post.php
    0.0018 __call(): ">Looking up missing property regionPublishRoles
    0 get_value(): ">SELECT regionPublishRoles FROM perch2_content_regions WHERE regionID='45'
    0.0022 get_value(): ">[0] Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'regionPublishRoles' in 'field list'
    0.0028 Queries: 12
    0.0001 Memory: 2.5148



    How do I fix that 'Invalid query' issue…? =O

    Couldn't post this in one go (I exceeded the 10k character limit) so here's the debug after changing the values and hitting Save:


    0 recover(): ">[1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='74bfd0f4702436550b00fce46982b5f0' LIMIT 1
    0.0008 update(): ">UPDATE perch2_users SET userHash='13c0a8642043763e50f94e7e09d70953' WHERE userID='1'
    0.002 get(): ">[36] SELECT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
    0.0031 add_new_apps(): ">[8] SELECT itemValue FROM perch2_menu_items WHERE itemType='app'
    0.0107 find(): ">[1] SELECT * FROM perch2_content_regions WHERE regionID=45 LIMIT 1
    0.0036 create_new_revision(): ">INSERT INTO perch2_content_items (itemID, regionID, pageID, itemRev, itemOrder, itemJSON, itemSearch, itemUpdatedBy) SELECT itemID, regionID, pageID, 12 AS itemRev, itemOrder, itemJSON, itemSearch, '1' AS itemUpdatedBy FROM perch2_content_items WHERE regionID=45 AND itemRev=11 ORDER BY itemOrder ASC
    0.0138 create_new_revision(): ">REPLACE INTO perch2_resource_log (appID, itemFK, itemRowID, resourceID) SELECT cr.appID, cr.itemFK, c2.itemRowID, cr.resourceID FROM perch2_resource_log cr, perch2_content_items c1, perch2_content_items c2 WHERE cr.appID='content' AND cr.itemFK='itemRowID' AND cr.itemRowID=c1.itemRowID AND c1.itemID = c2.itemID AND c1.regionID=45 AND c2.regionID=45 AND c1.itemRev = 11 AND c2.itemRev = 12
    0.0032 renumber_items(): ">[1] SELECT itemRowID FROM perch2_content_items WHERE regionID=45 AND itemRev=12 ORDER BY itemOrder ASC
    0.0004 update(): ">UPDATE perch2_content_items SET itemOrder=1000 WHERE itemRowID='519345'
    0.0003 update(): ">UPDATE perch2_content_regions SET regionLatestRev=12 WHERE regionID='45'
    0.0007 get_value(): ">[1] SELECT regionRev FROM perch2_content_regions WHERE regionID=45
    0.0002 delete_old_revisions(): ">DELETE FROM perch2_content_items WHERE regionID=45 AND itemRev!=11 AND itemRev IN (SELECT itemRev FROM (SELECT DISTINCT itemRev FROM perch2_content_items WHERE regionID=45 ORDER BY itemRev DESC LIMIT 8, 99999) AS t2)
    0.004 delete_old_revisions(): ">DELETE FROM perch2_resource_log WHERE appID='content' AND itemFK='itemRowID' AND itemRowID NOT IN (SELECT itemRowID FROM perch2_content_items)
    0.0011 truncate_for_region(): ">DELETE FROM perch2_content_items WHERE itemRowID IN (SELECT itemRowID FROM (SELECT itemRowID FROM perch2_content_items WHERE regionID=45 AND itemRev=12 ORDER BY itemOrder ASC LIMIT 1, 99999999 ) AS t2 )
    0.0584 update(): ">UPDATE perch2_content_regions SET regionSearchable='1', regionTemplate='tax_data_table.html', regionKey='Income Tax Allowances', regionMultiple=0, regionEditRoles='*', regionPublishRoles='*' WHERE regionID='45'
    0.0004 update(): ">Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'regionPublishRoles' in 'field list'
    0.0002 update(): ">UPDATE perch2_content_regions SET regionOptions='{\"edit_mode\":\"listdetail\",\"searchURL\":\"\",\"title_delimit\":\"\",\"adminOnly\":0,\"addToTop\":0,\"limit\":false,\"column_ids\":[]}' WHERE regionID='45'
    0.0004 get_flat_for_region(): ">[1] SELECT * FROM perch2_content_items c WHERE c.regionID=45 AND c.itemRev=12 ORDER BY c.itemOrder ASC
    0.0077 render(): ">[6] Using template: /templates/content/tax_data_table.html
    0.0091 update(): ">UPDATE perch2_content_regions SET regionHTML='<table cellspacing=\"0\">\n\n <thead>\n\n <tr>\n <th>Income Tax Allowances</th>\n <th>2020/21</th>\n <th>2019/20</th>\n </tr>\n\n </thead>\n\n <tbody>\n\n \n <tr>\n <td>Personal Allowance</td>\n <td>£12,500</td>\n <td>£12,500</td>\n </tr>\n \n <tr>\n <td>Transferable Marriage Allowance</td>\n <td>£1,250</td>\n <td>£1,250</td>\n </tr>\n \n <tr>\n <td>Blind Persons Allowance</td>\n <td>£2,500</td>\n <td>£2,450</td>\n </tr>\n \n <tr>\n <td>Dividend Allowance</td>\n <td>£2,000</td>\n <td>£2,000</td>\n </tr>\n \n <tr>\n <td>Personal Savings Allowance (Basic rate taxpayer)</td>\n <td>£1,000</td>\n <td>£1,000</td>\n </tr>\n \n <tr>\n <td>Personal Savings Allowance (Higher rate taxpayer)</td>\n <td>£500</td>\n <td>£500</td>\n </tr>\n \n\n </tbody>\n\n</table>\n', regionRev=12, regionLatestRev=12 WHERE regionID='45'
    0.0003 get_value(): ">[1] SELECT MIN(itemRev) FROM perch2_content_items WHERE regionID=45
    0.0013 index(): ">DELETE FROM perch2_content_index WHERE regionID=45 AND itemRev<5
    0.0206 get_for_region(): ">[1] SELECT * FROM perch2_content_items WHERE regionID=45 AND itemRev=12 ORDER BY itemOrder ASC
    0.0004 index(): ">DELETE FROM perch2_content_index WHERE regionID=45 AND itemRev=12
    0.0007 index(): ">Using template: /templates/content/tax_data_table.html
    0.0016 index(): ">INSERT INTO perch2_content_index (itemID, regionID, pageID, itemRev, indexKey, indexValue) VALUES (1160,45,9,12,'tax_type','Income Tax Allowances'),(1160,45,9,12,'current_tax_year','2020/21'),(1160,45,9,12,'previous_tax_year','2019/20'),(1160,45,9,12,'images.tax_category','Personal Allowance'),(1160,45,9,12,'images.tax_category','Transferable Marriage Allowance'),(1160,45,9,12,'images.tax_category','Blind Persons Allowance'),(1160,45,9,12,'images.tax_category','Dividend Allowance'),(1160,45,9,12,'images.tax_category','Personal Savings Allowance (Basic rate taxpayer)'),(1160,45,9,12,'images.tax_category','Personal Savings Allowance (Higher rate taxpayer)'),(1160,45,9,12,'images.current_year','£12,500'),(1160,45,9,12,'images.current_year','£1,250'),(1160,45,9,12,'images.current_year','£2,500'),(1160,45,9,12,'images.current_year','£2,000'),(1160,45,9,12,'images.current_year','£1,000'),(1160,45,9,12,'images.current_year','£500'),(1160,45,9,12,'images.previous_year','£12,500'),(1160,45,9,12,'images.previous_year','£1,250'),(1160,45,9,12,'images.previous_year','£2,450'),(1160,45,9,12,'images.previous_year','£2,000'),(1160,45,9,12,'images.previous_year','£1,000'),(1160,45,9,12,'images.previous_year','£500'),(1160,45,9,12,'_id',1160),(1160,45,9,12,'_order','1000')
    0.0028 index(): ">[1] OPTIMIZE TABLE perch2_content_index
    0.2398 set(): ">Setting alert: Successfully updated (success)
    0.0003 all(): ">[2] SELECT * FROM perch2_user_roles ORDER BY roleTitle ASC
    0.0037 get_menu(): ">[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
    0.0015 get_items(): ">[3] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
    0.001 get_value(): ">[1] SELECT itemTitle FROM perch2_menu_items WHERE itemType='app' AND itemValue='content' LIMIT 1
    0.0006 get_menu(): ">[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
    0.0003 get_items(): ">[2] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
    0.0023 title_panel(): ">File: /core/apps/content/modes/region.options.post.php
    0.0029 Queries: 40
    0 Memory: 3.5783



    And this is my diagnostic report:


    • Perch Runway: 3.1.5, PHP: 7.2.22, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $, with PDO
    • Server OS: Darwin, apache2handler
    • Installed apps: content (3.1.5), assets (3.1.5), categories (3.1.5), perch_forms (1.12), perch_backup
    • App runtimes: <?php $apps_list = [ 'perch_forms', ];
    • PERCH_LOGINPATH: /cms
    • PERCH_PATH: /Users/martin/Sites/grahamaccountants.com/cms
    • PERCH_CORE: /Users/martin/Sites/grahamaccountants.com/cms/core
    • PERCH_RESFILEPATH: /Users/martin/Sites/grahamaccountants.com/cms/resources
    • Image manipulation: GD
    • PHP limits: Max upload 32M, Max POST 8M, Memory: 128M, Total max file upload: 8M
    • F1: 3b606135b33e6a102526838f4152a807
    • Resource folder writeable: Yes
    • HTTP_HOST: grahamaccountants.local
    • DOCUMENT_ROOT: /Users/martin/Sites/grahamaccountants.com
    • REQUEST_URI: /cms/core/settings/diagnostics/
    • SCRIPT_NAME: /cms/core/settings/diagnostics/index.php