failed to lex SQL query

[andrey-lizunov]

We have this error failed to lex SQL query for some SQL queries.
Not sure why they occur and how to fix them.
You can find the query in the example below.
Database Postgresql 9.5, Rails 4.2.7, ruby 2.2.3p173, gem version 0.10.3

[SKYLIGHT] internal error: failed to lex SQL query
ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 25 }; sql=SELECT "roaming_packages".* FROM "roaming_packages" WHERE "roaming_packages"."roaming_zone_id" = $1 AND "roaming_packages"."availability_type" IN ('all', 'business') AND ("roaming_packages"."id" != 1) AND (available_customers_ids = ARRAY[]::integer[] OR available_customers_ids @> ARRAY[2]::integer[])

[wagenet]

Thanks for sharing this. Our SQL parser has had some difficulties with arrays so it may be related to that, or possibly the @> syntax.

[andrey-lizunov]

@wagenet Am I right, that if I want to turn off sql analysis, I should set my own list of probes without sequel probe and it will help to avoid this error messages in log?
We use Skylight with Rails.

[wagenet]

@andrey-lizunov if you'd like to silence the SQL parse error logging for now you can upgrade to 1.0.1 and set SKYLIGHT_SILENCE_SQL_FAILURES=true in your ENV. Long term, I'd still like to resolve the actual parsing issue.

[gkop]

Here's another one:

ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=LOCK TABLE agencies IN EXCLUSIVE MODE

[wagenet]

@gkop thanks! I'm working on improving the lexer at this very moment :)

[andriytyurnikov]

any updates?

[wagenet]

@andriytyurnikov improving this requires rewriting much of our lexer. It's a project I'm actively working on as I have time for it. Unfortunately, I don't have an ETA yet.

[andriytyurnikov]

thanks, @wagenet
could you please share reasoning behind choice to perform lexical analysis on the client's side?
(i collect stories of decision making in programming)

[gkop]

@andriytyurnikov I believe one major goal is to filter out sensitive bits of the SQL before shipping it off to Skylight.

[wagenet]

@andriytyurnikov @gkop is correct. Furthermore, we do lots of aggregation based on similar queries which we're unable to do if we don't strip out variables.

[andriytyurnikov]

with all due respect to your effort - we already using new relic and getsentry to monitor our exceptions and performance, and I believe they not so carefull
while client-side lexing vs server-side lexing and privacy is a tradeoff that is 100% yours,
I would like to highlight, that for me problem was in having exception that avoided Sentry

[andriytyurnikov]

by the way - that particular SQL maybe was invalid (something like pg json operators applied to pg hash), but problem was with app exception that was not processed with Sentry (which may be more related to sentry itself)... I think I'll just mention cross-mention this issue in their repo

[wagenet]

@andriytyurnikov failure to parse isn't an exception. It doesn't stop application operation, it just means we can't report as much data to the Skylight servers.

[wodka]

got another one (version 1.4.1)

err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 1 }; sql=SHOW VARIABLES like 'max_allowed_packet';

[wagenet]

I've made some good progress on the new SQL lexer. I still don't have an ETA, but wanted to let you all know that we're still working on it.

[rhuanbarreto]

Follow a log of queries to help:

2017-12-06T16:03:14.754047742Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:03:14.782474816Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:15.206023445Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Event registry].[Event Discovered 1 Des] FROM [Odda Technology AS$Event registry]
2017-12-06T16:03:15.206063245Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:15.246031285Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Event registry].[Item No_ (assy)] FROM [Odda Technology AS$Event registry]
2017-12-06T16:03:15.246087485Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:15.287118413Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Event registry].[Item No_ (detail)] FROM [Odda Technology AS$Event registry]
2017-12-06T16:03:15.287149712Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:16.075290550Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:03:16.075330949Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:16.152772159Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Customer].* FROM [Odda Technology AS$Customer]
2017-12-06T16:03:16.152812058Z [SKYLIGHT] internal error: failed to lex SQL query

2017-12-06 16:03:22.922 INFO  - Container logs
2017-12-06T16:03:18.574588711Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:03:18.574628211Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:18.653640202Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Work Center].* FROM [Odda Technology AS$Work Center]  ORDER BY [Odda Technology AS$Work Center].[Name] ASC
2017-12-06T16:03:18.653689501Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:03:19.849904946Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Event registry].* FROM [Odda Technology AS$Event registry] WHERE ([Create date]<= '12-06-2017') AND ([Create date]>= '11-06-2017')  ORDER BY [Create date] DESC
2017-12-06T16:03:19.849966146Z [SKYLIGHT] internal error: failed to lex SQL query

2017-12-06 16:05:22.925 INFO  - Container logs
2017-12-06T16:05:18.169354973Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:05:18.169406872Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:05:19.958952396Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 7 }; sql=SELECT [Odda Technology AS$Customer].* FROM [Odda Technology AS$Customer] WHERE [Odda Technology AS$Customer].[No_] IN (N'K34572', N'K34488', N'K34506', N'K34577', N'K34251', N'K34560', N'K34563', N'K34544', N'K34590', N'K34589', N'K32641', N'K34408', N'K33264', N'K34594', N'K34597', N'K33198', N'K33284', N'K34593', N'K34422', N'K34600', N'K34569', N'K34608', N'K34606', N'K34609', N'K34588', N'K34612', N'K34613', N'K34615', N'K34011', N'K34616', N'K34425')
2017-12-06T16:05:19.958995896Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:05:20.749185210Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:05:20.749256309Z [SKYLIGHT] internal error: failed to lex SQL query
2017-12-06T16:05:22.839493676Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:05:22.839538276Z [SKYLIGHT] internal error: failed to lex SQL query

2017-12-06 16:05:32.923 INFO  - Container logs
2017-12-06T16:05:31.991359550Z ERROR:skylight::c_api: failed to lex SQL query; err=LexError { kind: UnknownLexError, desc: "unknown sql lex error", pos: 3 }; sql=USE [NAV_ODS_2016]
2017-12-06T16:05:31.991415949Z [SKYLIGHT] internal error: failed to lex SQL query

[rhuanbarreto]

@wagenet You could disable these errors in production. This is a kind of error that is important for Skylight, not for the people that is managing the rails app.

[wagenet]

@rhuanbarreto Thanks for sharing these! Given that most people only run Skylight in production, it was thought that if we wanted to gather information on failed queries, that would be the only place to do so. You can disable these warnings by setting SKYLIGHT_SILENCE_SQL_FAILURES=true in your ENV.

[wagenet]

For anyone still following along, I'm also fixing these parse failures in the new version of the SQL lexer. I expect a beta to be out in the coming weeks.

[wagenet]

So I've now got the new lexer successfully handling everything except for @rhuanbarreto's queries. @rhuanbarreto what SQL variant are you using?

[rhuanbarreto]

It's Microsoft SQL Server queries generated by tiny_tds gem together with free_tds driver for Linux.

[rhuanbarreto]

@wagenet you can use our public image on docker easysubsea/ruby-freetds:latest to have it running