spdMerlin spdMerlin SQLite insert error when ServerName includes “(xxx & yyy)” — fix for ServerID/ServerName parsing

[cristian.ionica]

Hi all,
I recently reinstalled spdMerlin and noticed the following error in the log:

Oct 29 00:43:29 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:30 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:31 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:31 AsusRouter spdMerlin: SQLite process[spd21] reported error(s).
Oct 29 00:43:31 AsusRouter spdMerlin: Speedtest results - Download: 764.01 Mbps (data used: 859.2 MB, additional servers: 3) - Upload: 747.07 Mbps (data used: 915.3 MB)
Oct 29 00:43:31 AsusRouter spdMerlin: Connection quality - Idle Latency: 1.46 ms (jitter: 0.05ms, low: 1.36ms, high: 1.49ms) - Packet Loss: 0.0%

After logging more output, I found that ServerID and ServerName were not inserted correctly into the DB. Example insert that failed:

INSERT INTO spdstats_WAN ([Timestamp],[Download],[Upload],[Latency],[Jitter],[PktLoss],[ResultURL],[DataDownload],[DataUpload],[ServerID],[ServerName])
VALUES (1761727471,834.09,732.91,1.45,0.04,0.0,'https://www.speedtest.net/result/8%3A44+AM',978.2,888.9,&,'Digi');

I grabbed the parsed file and saw the issue occurs with this server display name:

Digi (RCS & RDS) - Timisoara (id: 11500)

During parsing, ServerID resolved to & and ServerName to Digi because of the " (RCS & RDS) " part.

Fix​

I adjusted the spdMerlin script where the two variables are populated.

Original:

serverName="$(grep "Server:" "$tmpfile" | awk 'BEGIN { FS = "\r" } ;{print $NF};' | cut -f1 -d'(' | cut -f2 -d':' | awk '{$1=$1;print}')"
serverid="$(grep "Server:" "$tmpfile" | awk 'BEGIN { FS = "\r" } ;{print $NF};' | cut -f2 -d'(' | awk '{print $2}' | tr -d ')')"

Updated:

serverName="$(grep "Server:" "$tmpfile" | awk 'BEGIN{FS="\r"};{print $NF}' | awk -F'Server:' '{print $2}' | awk -F' \\(id:' '{print $1}' | awk '{$1=$1; print}')"
serverid="$(grep "Server:" "$tmpfile" | awk 'BEGIN{FS="\r"};{print $NF}' | awk -F'\\(id: ' '{print $2}' | awk -F'\\)' '{print $1}')"

With this change, I now get:

• serverName = "Digi (RCS & RDS) - Timisoara"
• serverId = "11500"

The insert succeeds and no longer throws the SQLite insert error.

 

[Martinski]

Hi all,
I recently reinstalled spdMerlin and noticed the following error in the log:

Oct 29 00:43:29 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:30 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:31 AsusRouter spdMerlin: SQLite3 Failure[spd21]: Parse error near line 2: near "&": syntax error /www.speedtest.net/result/10%3A43+PM',859.2,915.3,&,'Digi'); error here ---^
Oct 29 00:43:31 AsusRouter spdMerlin: SQLite process[spd21] reported error(s).
Oct 29 00:43:31 AsusRouter spdMerlin: Speedtest results - Download: 764.01 Mbps (data used: 859.2 MB, additional servers: 3) - Upload: 747.07 Mbps (data used: 915.3 MB)
Oct 29 00:43:31 AsusRouter spdMerlin: Connection quality - Idle Latency: 1.46 ms (jitter: 0.05ms, low: 1.36ms, high: 1.49ms) - Packet Loss: 0.0%

After logging more output, I found that ServerID and ServerName were not inserted correctly into the DB. Example insert that failed:

INSERT INTO spdstats_WAN ([Timestamp],[Download],[Upload],[Latency],[Jitter],[PktLoss],[ResultURL],[DataDownload],[DataUpload],[ServerID],[ServerName])
VALUES (1761727471,834.09,732.91,1.45,0.04,0.0,'https://www.speedtest.net/result/8%3A44+AM',978.2,888.9,&,'Digi');

I grabbed the parsed file and saw the issue occurs with this server display name:

Digi (RCS & RDS) - Timisoara (id: 11500)

During parsing, ServerID resolved to & and ServerName to Digi because of the " (RCS & RDS) " part.

Fix​

I adjusted the spdMerlin script where the two variables are populated.

Original:

serverName="$(grep "Server:" "$tmpfile" | awk 'BEGIN { FS = "\r" } ;{print $NF};' | cut -f1 -d'(' | cut -f2 -d':' | awk '{$1=$1;print}')"
serverid="$(grep "Server:" "$tmpfile" | awk 'BEGIN { FS = "\r" } ;{print $NF};' | cut -f2 -d'(' | awk '{print $2}' | tr -d ')')"

Updated:

serverName="$(grep "Server:" "$tmpfile" | awk 'BEGIN{FS="\r"};{print $NF}' | awk -F'Server:' '{print $2}' | awk -F' \\(id:' '{print $1}' | awk '{$1=$1; print}')"
serverid="$(grep "Server:" "$tmpfile" | awk 'BEGIN{FS="\r"};{print $NF}' | awk -F'\\(id: ' '{print $2}' | awk -F'\\)' '{print $1}')"

With this change, I now get:

• serverName = "Digi (RCS & RDS) - Timisoara"
• serverId = "11500"

The insert succeeds and no longer throws the SQLite insert error.

That was a great catch!! Thank you for reporting it and providing a solution. The parentheses being part of the "Server name" field throw a wrench in the original code.

I'll implement a slightly different fix since the code has to be able to parse different forms of the "Server Name" line, which also contains the "Server ID" field. For example, "(id: NNNN)" vs "(id = NNNN)" generated by different Speedtest binary versions available.

Thanks again.