Report module issue: MySQL error 1054

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.

I think this issue is already solved in newer versions…

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

Try this one:

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?).

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

BTW, what is your PHP version?

From the diagnostic tools of Suite I see 7.1.33

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

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

Anyway, thanks for any ideas…

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.

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

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…

1 Like

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.

1 Like