Database query fails in astor & jive after a fresh installation

General Installation
1

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?

2

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

3

I installed MySQL server 5.7, “mysql Ver 14.14 Distrib 5.7.44, for Win64 (x86_64)”.

Thanks for the link.

4

It looks like this issue has been fixed a while ago in the Tango Database device server.
See - Fix some incompatibility between MySQL releases. This all started with Ubuntu 16.04 (c5915b3a) · Commits · tango-controls / TangoDatabase · GitLab

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: Releases · tango-controls / TangoSourceDistribution · GitLab

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

Hoping this helps,
Reynald

5

Please also note that you can install a recent version of the Tango Database server via conda too: Tango Database | Anaconda.org

6

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

7

Thanks for the links on the updated versions. Yes, its best to simply update.