default_dashboard unknown column

hi All,

We have just upgraded our instance from 7.9.5 to 8.0.2 , upgrade failed at post_upgrade checks

we are witnessing following error on all module pages - Unknown column 'dashboards.default_dashboard'

HTTP: 500 Internal Server Error

An exception occurred while executing 'SELECT dashboards.id, dashboards.date_modified dashboards__date_modified FROM dashboards LEFT JOIN sugarfavorites sf_dashboards ON (sf_dashboards.module = ?) AND (sf_dashboards.record_id = dashboards.id) AND (sf_dashboards.assigned_user_id = ?) AND (sf_dashboards.deleted = ?) WHERE ((dashboards.dashboard_module = ?) AND ((sf_dashboards.id IS NOT NULL) OR (dashboards.default_dashboard = ?)) AND (dashboards.view_name = ?)) AND (dashboards.deleted = ?) ORDER BY dashboards.date_modified DESC, dashboards.id DESC LIMIT 18446744073709551615 OFFSET 0' with params ["Dashboards", "1", 0, "pmse_Business_Rules", 1, "records", 0]: Unknown column 'dashboards.default_dashboard' in 'where clause'

could someone provide me with dashboards table create query please post 8.0.2 upgrade.

  • I just ran your query in my 8.0.2 and it runs fine:

    SELECT dashboards.id, dashboards.date_modified dashboards__date_modified
    FROM dashboards
    LEFT JOIN sugarfavorites sf_dashboards ON (sf_dashboards.module = 'Dashboards')
    AND (sf_dashboards.record_id = dashboards.id) AND (sf_dashboards.assigned_user_id = 1)
    AND (sf_dashboards.deleted = 0) WHERE ((dashboards.dashboard_module = 'pmse_Business_Rules') AND ((sf_dashboards.id IS NOT NULL)
    OR (dashboards.default_dashboard = 1)) AND (dashboards.view_name = 'records')) AND (dashboards.deleted = '0')
    ORDER BY dashboards.date_modified DESC, dashboards.id DESC LIMIT 18446744073709551615 OFFSET 0

    Here is my create table:

    CREATE TABLE `dashboards` (
    `id` char(36) NOT NULL,
    `name` varchar(255) DEFAULT NULL,
    `date_entered` datetime DEFAULT NULL,
    `date_modified` datetime DEFAULT NULL,
    `modified_user_id` char(36) DEFAULT NULL,
    `created_by` char(36) DEFAULT NULL,
    `description` text,
    `deleted` tinyint(1) DEFAULT '0',
    `assigned_user_id` char(36) DEFAULT NULL,
    `dashboard_module` varchar(100) DEFAULT NULL,
    `view_name` varchar(100) DEFAULT NULL,
    `metadata` text,
    `default_dashboard` tinyint(1) DEFAULT '0',
    `team_id` char(36) DEFAULT NULL,
    `team_set_id` char(36) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_dashboards_date_modfied` (`date_modified`),
    KEY `idx_dashboards_id_del` (`id`,`deleted`),
    KEY `idx_dashboards_date_entered` (`date_entered`),
    KEY `user_module_view` (`assigned_user_id`,`dashboard_module`,`view_name`),
    KEY `idx_dashboards_name_del` (`name`,`deleted`),
    KEY `idx_dashboards_assigned_del` (`assigned_user_id`,`deleted`),
    KEY `idx_dashboards_tmst_id` (`team_set_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Hope that helps,

    Francesca

  • BTW did you try a QRR to see if there were any pending SQL queries that needed to be executed?

  • Francesca Shiekh its a strange one for me , QRR is non responsive from front end , nothing happens, when i run via backend i am getting database error, basically what has happened is we are trying to upgrade our instance from 7.9.5 to 8.0.2 , upgrade failed at post_upgrade checks. now we are trying to investigate what went wrong as logs are not very helpful. we are also missing data_privacy table. when i look via studio data privacy module exists but no entry in db, i guess QRR should have resolved the issue by giving option to create if i would have been able to run it. so basically none of 8.0.2 tables are updated which is causing me greif also i am able to see records in list view but unable to create any , it says permission denied but all files and folders are set as owner : apache:apache and permissions 755. any ideas ?

  • Yes, it sounds like your upgrade did not finish.

    I assume you are on site since you are doing your own upgrade.

    When I have such issues I start with setting permissions from root down using:

    sudo chown -R  apache:apache *

    sudo find . -type d -exec chmod 775 {} \;

    sudo find . -type f -exec chmod 664 {} \;

     

    I re-run the upgrade with stage continue:
    see: https://support.sugarcrm.com/Documentation/Sugar_Versions/8.0/Pro/Installation_and_Upgrade_Guide/#Upgrading_via_Silent_Upgrader

    • <stage> : Instructs the upgrader to begin at a specific stage; "healthcheck" will cause only the health check portion to run while "continue" will cause it to start where it stopped on the previous run.

    Check your upgrade log again and see what if anything it is telling you.

    An alternative, though I don't like to do this - proceed at your own risk - is to open the upgrade package, go to upgrade->scripts->sql

    analyze those files and see if you can find the sql that should have executed on your instance but did not. Run it from your RDBMS Then try the QRR again.

  • One more thing, set your log level to debug. 

    I was looking at my notes and I had a similar issue from 7.9.x to 8.0

    In my case it was an error in one of my custom files that prevented the upgrade from completing.

    When you put your log level to debug and continue the upgrade you should get a better indication of where - or at least in what module - the error is happening that is preventing your upgrade from completing.

    See if that helps.

    This was the suggestion that support sent me:

    After lowering your logger level to debug and rerunning the upgrader, your sugarcrm.log should have an entry that prints something similar to this:

    UpgradeSidecarGridLayoutMetaDataParser->__construct( recordview , <MODULENAME> , )

    The modulename in this case will be key. From there you should be able to go into either of these two paths and verify the structure of the view defs that might be there:

    /custom/<MODULENAME>/clients/base/views/record/record.php (This might not exist)

    /<MODULENAME>/clients/base/views/record/record.php

    If the custom view file exists, start with this one. Otherwise look in your OOTB view file. What you are looking for is a PHP array that has an initial structure of:

    $viewdefs['<MODULENAME>']['base']['view']['record'] = array(

    If the structure is anything other than that you will encounter the errors you are seeing on the upgrader.

    I hope this helps to identify the issue you were seeing where the upgrader was dying before completion. Please note that while it might appear that your instance is functional after upgrade when the upgrader has stopped midway it actually is in a broken state.

    As it relates to this issue, to “fix” this issue, you will need to identify the suspect files on your pre-upgrade instance and remove them from your system. You can do this by renaming the file from record.php to something like record.php.old, or simply deleting the file altogether. After doing this, and rerunning the upgrader, you should be able to complete the process.

  • During the uprade the database is changed, esp. the table dashboards is extendend by some new columns. These new columns are not in your upgraded installation.

    Possible reasons: the sql upgrade script did not run or the system runs with an old version eg. a manually upgraded database.

    I would discard the update result and start the upgrade again with the old version.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH