mysql errors cropping up in established site

crush123

i have a site which has been live for over 2 years now , currently running with editor php 1.7.0 and it has some mysql errors cropping up on some pages which didn't beforehand, I suspect as a result of mysql recently being updated to 5.7.26

My question is, is there a quick way to generate the select statement generated by the ajax editor page. so i can use this in phpmyadmin to help me quickly debug the errors ?

colin

Hi @crush123 ,

This thread should help, it's asking the same thing. If not, let us know.

Cheers,

Colin

crush123

that works a treat !

However it highlights an error generated by my code, which works fine on prior versions of mySQL.

my query generates data using a linked table to an options list like so...

Field::inst( 'tblevent.EventType' )
        ->options( Options::inst()
        ->table( 'refeventtype' )
        ->value( 'EventTypeID' )
        ->label( 'EventTypeDescription' )
        ->order( 'ListOrder' )
        ),

the sql generated by this appears to be as follows...

SELECT DISTINCT `EventTypeID`
as 'EventTypeID', `EventTypeDescription` as 'EventTypeDescription' FROM `refeventtype` ORDER BY `ListOrder`

which works perfectly well before mySQL 5.7.x but now generates this error

    #3065 - Expression #1 of ORDER BY clause
is not in SELECT list, references column 'inchorus_members.refeventtype.ListOrder' which is not in SELECT list; this is incompatible with DISTINCT

if I remove the 'order by' clause, or add the 'refeventtype.ListOrder' to the select list, it works again

it may be something you have seen already ?

allan

Are you still using 1.7.0 of our PHP libraries? If so, updating to 1.9.0 should resolve that. This is the code that should handle what you are seeing.

Allan

crush123

perfect !

i will update the site.

thanks for all your help