database query fails in astor & jive after a fresh installation

tango version - 9.4.1, used create-db script from version 9.2.2 because I install tango through conda generally (all of astor, jive & pytango). The download from tango-controls website is still 9.2.2.

I am trying to setup a new host & I do not have newer create-db (& related) scripts. Is it different for versions above 9.2.2? I get the following error while opening jive:

Desc -> Failed to query TANGO database (error=Expression #1 of ORDER BY clause is not in SELECT list, references column 'tango.attribute_alias.attribute' which is not in SELECT list; this is incompatible with DISTINCT)
The query was: SELECT DISTINCT alias FROM attribute_alias WHERE alias LIKE "%" ORDER BY attribute
Reason -> DB_SQLError
Origin -> DataBase::db_get_attribute_alias_list()
Desc -> Cannot execute command DbGetAttributeAliasList on sys/database/2
Reason -> TangoApi_CANNOT_EXECUTE_COMMAND
Origin -> Connection.command_inout()

The error makes sense because the data returned by 'SELECT DISTINCT alias FROM attribute_alias WHERE alias LIKE "%"' is empty.

A similar error was found in property_device_hist while creating a new host:

Failed to query TANGO database (error=Expression #1 of ORDER BY clause is not in SELECT list, references column 'tango.property_device_hist.date' which is not in SELECT list; this is incompatible with DISTINCT)
The query was: SELECT DISTINCT id FROM property_device_hist WHERE device="tango/admin/gar-nb-medsch19" AND name="StartDsPath" ORDER by date

I removed the setting "STRICT_TRANS_TABLES" in MySQL but it does not seem to have an effect. The returned data from this table also contains only one column (the distinct ID column), so it makes sense it cannot order by date.

How to fix this?
Hi,

Could you please tell us what DB version you are using?
MySQL, MariaDB? What version?

It looks like the behavior changed in MySQL at some point but there seems to be some work-arounds: https://bugs.mysql.com/bug.php?id=106813

I don't remember anyone reporting this issue yet.

Kind regards,
Reynald
Rosenberg's Law: Software is easy to make, except when you want it to do something new.
Corollary: The only software that's worth making is software that does something new.
I installed MySQL server 5.7, "mysql Ver 14.14 Distrib 5.7.44, for Win64 (x86_64)".

Thanks for the link.
Edited 2 months ago
It looks like this issue has been fixed a while ago in the Tango Database device server.
See https://gitlab.com/tango-controls/TangoDatabase/-/commit/c5915b3a2c5133e6f4e3416a8d2f293fc64b97f2

So my recommendation is to either update the Tango Database server you are using or to use one of the work-around listed here with the sql_mode: https://bugs.mysql.com/bug.php?id=106813

Please also note that our CI/CD is now generating Windows installers for recent Tango versions.
You can get a link to the Windows installer here: https://gitlab.com/tango-controls/TangoSourceDistribution/-/releases

Sadly, the Tango documentation still requires an update to advertise and guide better our users toward this new Windows installer.

Hoping this helps,
Reynald
Rosenberg's Law: Software is easy to make, except when you want it to do something new.
Corollary: The only software that's worth making is software that does something new.
Please also note that you can install a recent version of the Tango Database server via conda too: https://anaconda.org/conda-forge/tango-database
Rosenberg's Law: Software is easy to make, except when you want it to do something new.
Corollary: The only software that's worth making is software that does something new.
Thanks, based on suggestions from the link, I modified the sql_mode setting to remove 'ONLY_FULL_GROUP_BY' & left "STRICT_TRANS_TABLES" back inside.

previously it was:
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Now it is:
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

One can find this in %PROGRAMDATA%/MySQL/MySQL Server 5.7, in my.ini
Thanks for the links on the updated versions. Yes, its best to simply update.
 
Register or login to create to post a reply.