Report module issue: MySQL error 1054

2di7 · 23 June 2023 11:30

HI,
I’m using version 7.11.2.

I created a report on on the line items (Quote module) which works great for Admin but doesn’t work for users with limited permissions.

If the user has set the permissions to “owner” for AOS_Quote and to “owner” for the Report, no results are shown (even if there are).

So, I found this error (but of course the column exists in the database):

MySQL error 1054: Unknown column 'aos_quotes.assigned_user_id' in 'on clause'

Quick repair already done.

Anyone have suggestions? This is a serious limitation for me and I have to find a solution.

Thanks in advance.

pgr · 23 June 2023 13:59

I think this issue is already solved in newer versions…

2di7 · 23 June 2023 14:38

Thanks pgr.
Actually, I would like to find a fix… I don’t think an update is allowed for me at this time.

pgr · 23 June 2023 18:01

Try this one:

github.com/salesagility/SuiteCRM-Core

Fix #154 - [Legacy] MySQL Error 1054: missing config.deleted columns

salesagility:hotfix ← pgorod:patch-1

opened 10:39AM - 30 Mar 23 UTC

pgorod

+3 -1

## Description Some modules don't have an SQL column that the code below expect…s. ## Motivation and Context See related Issue #154 Note that I am not sure which modules to add to the **`$excludedModules`** list I created. I know that the **Administration** module is the one causing the FATAL. Other modules could be added here later. I added a few that are "special" modules, without a DB table and a **deleted** column there. ## How To Test This I am not sure how to trigger it. I came across this in [this Forum thread](https://community.suitecrm.com/t/databse-error-sending-campaign-mails-from-the-email-queue/88564/6). It seems to happen somewhere along the way in the Campaign wizard. Perhaps the best thing is to first try and reproduce the error, trigger it, and only then try the fix. ## Types of changes - [X] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality to change) ### Final checklist - [X] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [X] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

2di7 · 27 June 2023 09:19

Hi pgr,
Thanks for the tip but to be honest, I’m freaking out!

In particular, I can’t identify the path to the file to edit.
i.e. public/legacy/include/portability/Services/Favorites/FavoritesManagerPort.php

In fact, there is nothing in my “public” folder. It probably depends on the installation that was done some time ago in this company (or can it depend on my version of Suite?).

pgr · 27 June 2023 09:32

That’s on v8, sorry. You wrote above that you have v7 but I got confused.

BTW, what is your PHP version?

2di7 · 27 June 2023 09:46

From the diagnostic tools of Suite I see 7.1.33

pgr · 27 June 2023 09:54

Is your Report a scheduled report, or is it running from within the app.

If it is scheduled, it used CLI php which might have a different version, and different settings.

I am just making things up, trying to find clues. I don’t have anything specific in mind

2di7 · 27 June 2023 10:08

No, it’s not scheduled. The user only needs to be enabled to consult it from the module.

Anyway, thanks for any ideas…

pgr · 27 June 2023 10:29

The path forward in terms of diagnosis would be to get a stack trace of that error so you can understand where it’s being called from. Or, ideally, stepping through the code with a debugger so you could see the values of the variables, etc.

2di7 · 28 June 2023 11:39

Hi pgr,
I compared two queries generated while I’m viewing the report results.
The first is with Admin, the second is with a user with restricted privileges (however he has access to his offers and reports).

SELECT count(*) c FROM (SELECT `aos_products_quotes:aos_products_cstm`.cod_prod_c AS 'Codice_Cabur0', `aos_products_quotes`.description AS 'Descrizione1', `aos_products_quotes`.item_description AS 'Descrizione2', `aos_products_quotes_cstm`.codice_competitor_c AS 'Cod._Competitor3', `aos_products_quotes_cstm`.qta_conf_c AS 'Q.tà_per_conf.4', `aos_products_quotes`.product_qty AS 'Quantità5', `aos_products_quotes_cstm`.product_stampa_somma_sconto_c AS 'product_stampa_somma_sconto6', `aos_products_quotes`.currency_id AS 'aos_products_quotes_currency_id', `aos_products_quotes`.product_unit_price AS 'Prezzo_Unitario7', `aos_products_quotes_cstm`.product_ricarica_c AS 'product_ricarica8', `aos_products_quotes_cstm`.product_margine_c AS 'product_margine9', `aos_products_quotes_cstm`.prodotto_prezzo_unit_grossis_c AS 'prodotto_prezzo_unit_grossis10', `aos_products_quotes`.id AS 'aos_products_quotes_id', `aos_products_quotes:aos_products`.id AS 'aos_products_quotes:aos_products_id', `aos_products_quotes:aos_quotes`.id AS 'aos_products_quotes:aos_quotes_id'
FROM `aos_products_quotes`
LEFT JOIN aos_products `aos_products_quotes:aos_products`
ON `aos_products_quotes`.product_id=`aos_products_quotes:aos_products`.id AND `aos_products_quotes:aos_products`.deleted=0
LEFT JOIN `aos_products_cstm` `aos_products_quotes:aos_products_cstm` ON `aos_products_quotes:aos_products`.id = `aos_products_quotes:aos_products_cstm`.id_c
LEFT JOIN `aos_products_quotes_cstm` `aos_products_quotes_cstm` ON `aos_products_quotes`.id = `aos_products_quotes_cstm`.id_c
LEFT JOIN aos_quotes `aos_products_quotes:aos_quotes`
ON `aos_products_quotes`.parent_id=`aos_products_quotes:aos_quotes`.id AND `aos_products_quotes:aos_quotes`.deleted=0
WHERE ( `aos_products_quotes:aos_quotes`.number = '12' ) AND  aos_products_quotes.deleted = 0 ) as n

SELECT count(*) c FROM (SELECT `aos_products_quotes:aos_products_cstm`.cod_prod_c AS 'Codice_Cabur0', `aos_products_quotes`.description AS 'Descrizione1', `aos_products_quotes`.item_description AS 'Descrizione2', `aos_products_quotes_cstm`.codice_competitor_c AS 'Cod._Competitor3', `aos_products_quotes_cstm`.qta_conf_c AS 'Q.tà_per_conf.4', `aos_products_quotes`.product_qty AS 'Quantità5', `aos_products_quotes_cstm`.product_stampa_somma_sconto_c AS 'product_stampa_somma_sconto6', `aos_products_quotes`.currency_id AS 'aos_products_quotes_currency_id', `aos_products_quotes`.product_unit_price AS 'Prezzo_Unitario7', `aos_products_quotes_cstm`.product_ricarica_c AS 'product_ricarica8', `aos_products_quotes_cstm`.product_margine_c AS 'product_margine9', `aos_products_quotes_cstm`.prodotto_prezzo_unit_grossis_c AS 'prodotto_prezzo_unit_grossis10', `aos_products_quotes`.id AS 'aos_products_quotes_id', `aos_products_quotes:aos_products`.id AS 'aos_products_quotes:aos_products_id', `aos_products_quotes:aos_quotes`.id AS 'aos_products_quotes:aos_quotes_id'
FROM `aos_products_quotes`
LEFT JOIN aos_products `aos_products_quotes:aos_products`
ON `aos_products_quotes`.product_id=`aos_products_quotes:aos_products`.id AND `aos_products_quotes:aos_products`.deleted=0
LEFT JOIN `aos_products_cstm` `aos_products_quotes:aos_products_cstm` ON `aos_products_quotes:aos_products`.id = `aos_products_quotes:aos_products_cstm`.id_c
LEFT JOIN `aos_products_quotes_cstm` `aos_products_quotes_cstm` ON `aos_products_quotes`.id = `aos_products_quotes_cstm`.id_c
LEFT JOIN aos_quotes `aos_products_quotes:aos_quotes`
ON `aos_products_quotes`.parent_id=`aos_products_quotes:aos_quotes`.id AND `aos_products_quotes:aos_quotes`.deleted=0

 AND ( aos_quotes.assigned_user_id ='1f510b7f-8633-9652-01fb-5d307f6422f3'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '1f510b7f-8633-9652-01fb-5d307f6422f3'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'AOS_Quotes'
                       WHERE   secr.record_id = `aos_products_quotes:aos_quotes`.id
                               AND secg.deleted = 0) )

WHERE ( `aos_products_quotes:aos_quotes`.number = '1714' ) AND  aos_products_quotes.deleted = 0 ) as n

Well, the only difference I find in the ON clause are quotes.

aos_quotes.assigned_user_id
instead of

`aos_quotes`.assigned_user_id

as are all the other columns.
Please note that securitygroups has the same anomaly.
However, in the other queries launched in debug I don’t have the columns delimited with these quotes…

Could this be the problem?

If so, where should I look?
Right now I’m checking AOR_Report.php but so far all the fixes I’ve found are already applied.

EDIT:
Ok: “Backticks round alias name are necessary as alias contains a : in the middle of it which would otherwise cause an error.”
But actually… maybe this

aos_quotes.assigned_user_id

should become

`aos_products_quotes:aos_quotes`.assigned_user_id

2di7 · 30 June 2023 13:39

Hi pgr, hi all,
I worked around it.

Finally, I identified the problem and now it works.
But what I wrote to see the result is not clean and must be corrected in the SuiteCRM’s language. Maybe I need help…

The change is in the build_report_access_query function of aor_report.php

if ($module->bean_implements('ACL') && ACLController::requireOwner($module->module_dir, 'list')) {
            global $current_user;
			
			$user_id= $current_user->id;
			
			//original
            //$owner_where = $module->getOwnerWhere($current_user->id);
			
			$owner_where =  $db->quoteIdentifier($alias).'.assigned_user_id=\''.$current_user->id.'\'';
            //necessary because without $db->quoteIdentifier it doesn't insert ` but with $db->quoteIdentifier it inserts two
			$owner_where = str_replace('``','`',$owner_where);
            $where = ' AND ' . $owner_where;
        }

Result

SELECT count(*) c FROM (SELECT `aos_products_quotes:aos_quotes`.number AS 'Prevetivo_Numero0', `aos_products_quotes`.currency_id AS 'aos_products_quotes_currency_id', `aos_products_quotes`.product_unit_price AS 'Prezzo_Unitario1', `aos_products_quotes`.product_id AS 'ID_Prodotto2', `aos_products_quotes`.product_qty AS 'Quantità3', `aos_products_quotes`.id AS 'aos_products_quotes_id', `aos_products_quotes:aos_quotes`.id AS 'aos_products_quotes:aos_quotes_id' FROM `aos_products_quotes` LEFT JOIN aos_quotes `aos_products_quotes:aos_quotes` ON `aos_products_quotes`.parent_id=`aos_products_quotes:aos_quotes`.id AND `aos_products_quotes:aos_quotes`.deleted=0

 AND `aos_products_quotes:aos_quotes`.assigned_user_id='a0c4363e-784a-3512-6cfd-646f4bdfceec' WHERE ( `aos_products_quotes:aos_quotes`.number = '12' ) AND  aos_products_quotes.deleted = 0 ) as n

I hope someone can suggest me the correct way to achieve this change.
In the meantime, I’ll continue to study about it…

2di7 · 3 July 2023 13:22

In the end, I chose to create a getOwnerWhere function for the report module only:

public function getOwnerWhere_fix ($module, $user_id, $alias)
	{
		if(isset($this->field_defs['assigned_user_id']))
        {
            return ' ' .$alias. '.assigned_user_id =\'' .$user_id. '\'';
        }
        if(isset($this->field_defs['created_by']))
        {
            return ' ' .$alias. '.created_by =\'' .$user_id. '\'';
        }
        return '';	
	}

This way, I can call it in build_report_access_query (in the two lines where necessary)

$owner_where = $this->getOwnerWhere_fix ($module, $user_id, $alias);

This solution works for me: it shows the report results even to users with restricted permissions or in security groups.