Unkown PID returns "Unknown column 'k[PID]' in 'field list'"

[mayday175]

Most of the PIDs I would like to log are specific to my car (Subaru diesel) and not included in the default list of PIDs in the createtorque[log/keys]_table.sql scripts. Uploading data from the Torque App silently fails, with hundreds of logs repeatedly retrying to upload.

Manually entering the URL's I saw logged in my Apache logs, into a web browser, returns an error like this:

Unknown column 'k32111f' in 'field list'

Note: PID 32111f used here is not a real PID, rather a fake one just for the purpose of recreating this message.

I was able to edit the createtorque[log/keys]_table.sql scripts to include all the non-standard PIDs and their descriptions, units, etc, that I am interested in recording, and re-create the database tables with these modified scripts. Uploading data from my Torque App now works without error.

Perhaps this is more of a feature request... to either silently ignore unknown PIDs or automatically add them to the database.

[surfrock66]

I need to look into this more; the expected behavior is that it will add the PID if it hasn't seen it, and way back when I tested that.

[mayday175]

FYI... the PID example above was added to the torque_keys table with the following details: id=k32111f description=k32111f type=varchar(255) units= populated=1 favorite=0 min= max=

There is no reference to this PID in the raw_logs_2019 table.

I saw this same behaviour with other PIDs sent by my Torque app, which is what led me to try editing the createtorque[log/keys]_table.sql scripts.

[surfrock66]

Do you have access to your apache logs? I can try to recreate it here if I have the exact request from your data. In theory, in the upload_data.php file, there's a whole condition for if a value isn't found. If I can see your calls to upload_data.php for a session, I can debug here. I'll uncomment a lot of the php in the upload_data.php file, call to the url in my browser to simulate a datapoint upload, then instead of "OK!" I'll see some state conditions.

[mayday175]

Sure... I've attached the Apache error.log and the vhost log for the torque website, other_vhosts_access.log. Both logs were recorded on 8 Aug and show 3 sessions... starting at 08:28, 09:29 and 17:12, plus some interaction from me with the website itself at about 12:26. The error.log shows some "Undefined variable" messages... it has always done this (and mislead my troubleshooting for a while) and continues to do so now, even though log data is being recorded correctly.

Note: I have anonymised some of the data to remove personally identifiable information (ie. email, domain names, Torque App ID, GPS coordinates). All other data remains untouched.

Unfortunately, I cannot provide the logs of when "Unknown column..." error message initially occurred... I reset a few things along the way. However, the logs provided here show the same PIDs from the same app on the same phone, so nothing changed there... except now that I manually created all the custom PIDs in the database, I've started logging a lot more of them :)

I hope this helps you find what you are looking for.

error.log other_vhosts_access.log

[surfrock66]

I am sorry this took so long, I have gotten majorly sidetracked.

According to your "other_vhosts_access.log" the URL being accessed is this (a single line pulled out):

GET /torque/upload_data.php?eml=redacted@nowhere.com.au&v=8&session=1565220583662&id=6c7e23035029a1d6ca9f4e1234567890&time=1565221338600&kff1005=137.43576713&kff1006=-36.06292529&kff1001=62.46&kff1007=74.5&k22125b=0.0&k22111c=0.0&kc=866.75&k22114b=180.0&k22114c=240.0&kff1202=1.1091137

Translated a bit, that's this:

eml=redacted@nowhere.com.au
v=8
session=1565220583662
id=6c7e23035029a1d6ca9f4e1234567890
time=1565221338600
kff1005=137.43576713 
kff1006=-36.06292529
kff1001=62.46
kff1007=74.5
k22125b=0.0
k22111c=0.0
kc=866.75
k22114b=180.0
k22114c=240.0
kff1202=1.1091137

What's weird for me...the logic works correctly. it detects that specifically k22125b isn't in the field list for the raw log table, tries to create it, ADDS IT TO THE KEYS TABLE, then proceeds...when we make the call to insert the raw values, the raw table never got the new column.

I've got a patch working now to fix this, but I'm finishing testing it. 2 things, one is a performance improvement, one is a bug fix.

After line 116, add "$dbfields_per_table = array();" which just re-declares the array, which was appending and not replacing, so for a minute there were way too many variables in that array, making it huge.

On line 121 (or 122 after making the above change) change the submitval check to 2 instead of 1, just a dumb typo.

Since I have like 7 years of logs, adding a column takes an extravagantly long time in my test DB. If you wouldn't mind, tell me what the values are and I'll add them to my starting keys db.

[mayday175]

Hey, no problems with the delay... while this issue is not affecting me, I'm happy to help resolve it for the next person. The "values"..? Do you mean, the details for the unrecognised PIDs? Sure... I originally got the details from SubDiesel (http://web.archive.org/web/20151220002031/https://subdiesel.wordpress.com/generic/protocols/extended-obd-ii/), but the site has recently gone offline (hence the webarchive link). Of the 0x22 custom/Subaru PIDs, the ones listed above are... 22125b=DPF Active Regen Switch, units=none, min=0, max=255 22111c=EGR Valve Opening Angle, units=grad, min=0, max=90 22114b=Estimated Catalyst Temp, units=Celsius, min=0 max=1000 22114c=Estimated DPF Temp, units=Celcius, min=0, max=1000 Was this what you need? If not, let me know and I'll fetch some more info for you.

[surfrock66]

Ok, I changed my mind after seeing how many were; probably not efficient to add them to the default keys list for everybody. Still, after my testing, the behavior of "add a PID to the keys table and every log table when a new PID is encountered" seems to be working.

Side note, the way the logic works, it took a full 24 hours to add those PIDs to my testing installation. My DB is MASSIVE as I've been keeping it since 2014, have been monitoring every single PID my car throws at me, and have some legacy errors in my DB like a few cases where I was actually recording like 30 datapoints every second.

Solved in commit https://github.com/surfrock66/torque/commit/d592b0d8e2f70d6d84f875e8665236d8d607a2a7

[mayday175]

I agree, not a good idea to add my few PIDs as defaults... my understanding is that 0x22 type PIDs are custom to each manufacturer, and the few that I am using are specifically for diesel Subaru. If, by chance, another manufacturer used the same PID, its unlikely they would use it for the same purpose.

Well done on finding a solution :)