database query fails in astor & jive after a fresh installation

# 1 year ago
VigneshV	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?

# 1 year ago

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?

# 1 year ago
Reynald	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.

# 1 year ago

Reynald

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.

# 1 year ago
VigneshV	I installed MySQL server 5.7, "mysql Ver 14.14 Distrib 5.7.44, for Win64 (x86_64)". Thanks for the link. Edited 1 year ago

# 1 year ago
Reynald	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.

# 1 year ago

Reynald

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.

# 1 year ago
Reynald	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.

# 1 year ago
VigneshV	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

# 1 year ago

VigneshV

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

# 1 year ago
VigneshV	Thanks for the links on the updated versions. Yes, its best to simply update.