database SQL error: no such column: A,DeviceRowID

[mongijs]

Hi,

After I upgraded a few days ago, I get the following message frequently in the log.

no such column: A,DeviceRowID

See also the attachmed JPEG

Any advice?

A restart of the raspberry did not help.
A back-up and restore of the database dit not help
Possibly, it started after I used events (LUA / Blockly), but I am not certain of this

Version: 2023-2 (build 15642)
Build Hash: ae4549c1f
Compile Date: 2023-11-09 13:54:20
dzVents Version: 3-1-8
Python Version: 3-9-2 (default, Mar 12 2021, 04:06:34) [GCC 10-2-1 20210110]
Motherboard: System: ODroid/Raspberry
RFXCOM: Using serial port: /dev/ttyUSB0

[willemd]

The A in the SQL query is an alias for the pushlink table, so it looks like there is something wrong with that table.
I think that is the table used for the list of devices that is pushed to for example the Influx dbase.
(in setup, more options, data push, influxdb for example)

You can check the table as follows:
1) login to the system to get a command line
2) cd domoticz
3) sqlite3 domoticz.db
4) .tables , this shows all tables in the database, pushlink should be one of them
5) select * from pushlink , this shows the devices
6).quit

Consdering the error message, I expect the output of action 4 or 5 above will not be as expected.

[jvdz]

I had the same when testing the upgrade: viewtopic.php?t=40962

This issue is fixed in this commit: https://github.com/domoticz/domoticz/co ... a89be5beef

[mongijs]

hmm, I had a slightly different situation as DeviceRowID column did not exist. But my db could be based on different version of courrse.
I solved it by dropping PushLink table and creating it with latest DDL from SQLhelper filter.
A bit tricky operation, as there can be more differences , but for now the error messages are gone.

CREATE TABLE IF NOT EXISTS [PushLink] ([ID] INTEGER PRIMARY KEY, [PushType] INTEGER, [DeviceRowID] BIGINT NOT NULL, [DelimitedValue] INTEGER DEFAULT 0, [TargetType] INTEGER DEFAULT 0, [TargetVariable] VARCHAR(100), [TargetDeviceID] INTEGER, [TargetProperty] VARCHAR(100), [Enabled] INTEGER DEFAULT 1, [IncludeUnit] INTEGER default 0);

[AndriesWasHere]

A similar problem occurred to me, but I found another solution to fix the DB.

After my Rpi system crashed, I have rebuild the Rpi system and restored an old Domoticz database.
However, since then I was facing the following messages:

Code: Select all

2024-01-12 12:19:30.951 Error: SQL Query("SELECT A.DeviceRowID, A.DelimitedValue, B.ID, B.Name, B.Type, B.SubType, B.SwitchType FROM PushLink as A, DeviceStatus as B WHERE (A.PushType==1 AND A.Enabled==1 AND A.DeviceRowID == B.ID)") : no such column: A.DeviceRowID
2024-01-12 12:19:30.952 Error: SQL Query("SELECT A.DeviceRowID, A.DelimitedValue, B.ID, B.Name, B.Type, B.SubType, B.SwitchType FROM PushLink as A, DeviceStatus as B WHERE (A.PushType==3 AND A.Enabled==1 AND A.DeviceRowID == B.ID)") : no such column: A.DeviceRowID
2024-01-12 12:19:30.953 Error: SQL Query("SELECT A.DeviceRowID, A.DelimitedValue, B.ID, B.Name, B.Type, B.SubType, B.SwitchType FROM PushLink as A, DeviceStatus as B WHERE (A.PushType==4 AND A.Enabled==1 AND A.DeviceRowID == B.ID)") : no such column: A.DeviceRowID
2024-01-12 12:19:30.955 Error: SQL Query("SELECT A.DeviceRowID, A.DelimitedValue, B.ID, B.Name, B.Type, B.SubType, B.SwitchType FROM PushLink as A, DeviceStatus as B WHERE (A.PushType==6 AND A.Enabled==1 AND A.DeviceRowID == B.ID)") : no such column: A.DeviceRowID
2024-01-12 12:19:30.956 Error: SQL Query("SELECT A.DeviceRowID, A.DelimitedValue, B.ID, B.Name, B.Type, B.SubType, B.SwitchType FROM PushLink as A, DeviceStatus as B WHERE (A.PushType==2 AND A.Enabled==1 AND A.DeviceRowID == B.ID)") : no such column: A.DeviceRowID

The post on this topic on 20231114T1144 mentioned a commit that would resolve the issue, but that commit is for an issue related to the SharedDevices TABLE.

The issue as shown above is related to the PushLink TABLE.

The DB Browser (SQLite) was used to debug the issue.
The problem domoticz.db was compared with a fresh domoticz.db (2024.2). See attachments.

Where in the problem domoticz.db TABLE PushLink includes COLUMN DeviceID,
the fresh domoticz.db TABLE PushLink includes COLUMN DeviceRowID.
The COLUMN names differed but the attributes were the same.

The DB Browser (SQLite) was used to modify the PushLink TABLE and rename the COLUMN.
Using the patched domoticz.db, the message "Error: SQL Query ...." was reported no more.