Unable to install change data capture pipeline error="near \"index\": syntax error" node_id=126

[return2alfred]

Tested on:
[v0.8.7]
[v0.8.8-alpha.4]

root@e54bad2c22b0:/config/.marmot# ./run-com.plexapp.plugins.library.db.toml
8:27PM INF Starting nats-server from=nats node_id=126
8:27PM INF Version: 2.10.4 from=nats node_id=126
8:27PM INF Git: [not set] from=nats node_id=126
8:27PM INF Cluster: e-marmot from=nats node_id=126
8:27PM INF Name: marmot-node-126 from=nats node_id=126
8:27PM INF Node: DuRiCrQn from=nats node_id=126
8:27PM INF ID: NDEIP7SOVCLTRLVV5OK44HARDYZIJXXLBC4TF3WB2IRILAZPNJKSWMU3 from=nats node_id=126
8:27PM INF Starting JetStream from=nats node_id=126
8:27PM INF _ ___ _____ ___ _____ ___ ___ _ __ __ from=nats node_id=126
8:27PM INF _ | | | / | | _ \ __| /\ | / | from=nats node_id=126
8:27PM INF | || | | | | _ \ | | | / | / _ | |/| | from=nats node_id=126
8:27PM INF _/|| || |/ || ||___// __| |_| from=nats node_id=126
8:27PM INF from=nats node_id=126
8:27PM INF https://docs.nats.io/jetstream from=nats node_id=126
8:27PM INF from=nats node_id=126
8:27PM INF ---------------- JETSTREAM ---------------- from=nats node_id=126
8:27PM INF Max Memory: 47.03 GB from=nats node_id=126
8:27PM INF Max Storage: 114.37 GB from=nats node_id=126
8:27PM INF Store Directory: "/config/Library/Application Support/Plex Media Server/Plug-in Support/Databases/nats/marmot-node-126/jetstream" from=nats node_id=126
8:27PM INF ------------------------------------------- from=nats node_id=126
8:27PM INF Starting JetStream cluster from=nats node_id=126
8:27PM INF Creating JetStream metadata controller from=nats node_id=126
8:27PM INF JetStream cluster recovering state from=nats node_id=126
8:27PM INF Listening for client connections on 0.0.0.0:45249 from=nats node_id=126
8:27PM INF Server is ready from=nats node_id=126
8:27PM INF Cluster name is e-marmot from=nats node_id=126
8:27PM INF Listening for route connections on 172.17.0.6:4222 from=nats node_id=126
8:27PM WRN JetStream has not established contact with a meta leader from=nats node_id=126
8:27PM ERR Error trying to connect to route (attempt 1): dial tcp 172.17.0.7:4222: connect: connection refused from=nats node_id=126
8:27PM ERR Error trying to connect to route (attempt 1): dial tcp 172.17.0.7:4222: connect: connection refused from=nats node_id=126
8:27PM WRN Waiting for routing to be established... from=nats node_id=126
8:27PM ERR NATS client disconnected node_id=126
8:27PM ERR NATS client exiting node_id=126
8:27PM WRN Waiting for routing to be established... from=nats node_id=126
8:27PM INF 172.17.0.7:45442 - rid:9 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:45444 - rid:10 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:45456 - rid:11 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:45464 - rid:12 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:4222 - rid:13 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:4222 - rid:14 - Route connection created from=nats node_id=126
8:27PM INF 172.17.0.7:4222 - rid:13 - Router connection closed: Duplicate Route from=nats node_id=126
8:27PM INF 172.17.0.7:4222 - rid:14 - Router connection closed: Duplicate Route from=nats node_id=126
8:27PM ERR NATS client disconnected node_id=126
8:27PM ERR NATS client exiting node_id=126
8:27PM ERR NATS client disconnected node_id=126
8:27PM ERR NATS client exiting node_id=126
8:27PM ERR NATS client disconnected node_id=126
8:27PM ERR NATS client exiting node_id=126
8:27PM INF Self is new JetStream cluster metadata leader from=nats node_id=126
8:27PM INF Streaming ready... node_id=126
8:27PM INF Streaming ready... node_id=126
8:27PM INF JetStream cluster new stream leader for '$G > marmot-changes-c-1' from=nats node_id=126
8:27PM INF JetStream cluster new stream leader for '$G > KV_e-marmot' from=nats node_id=126
8:27PM INF Listing tables to watch... node_id=126
8:27PM INF Starting change data capture pipeline... node_id=126
8:27PM INF Creating global change log table node_id=126
8:27PM INF Creating trigger for metadata_relations node_id=126
8:27PM INF Creating trigger for statistics_media node_id=126
8:27PM INF Creating trigger for locatables node_id=126
8:27PM INF Creating trigger for statistics_resources node_id=126
8:27PM INF Creating trigger for fts4_metadata_titles_docsize node_id=126
8:27PM INF Creating trigger for fts4_tag_titles_docsize node_id=126
8:27PM INF Creating trigger for locations_parent node_id=126
8:27PM INF Creating trigger for media_stream_settings node_id=126
8:27PM INF Creating trigger for media_parts node_id=126
8:27PM ERR Unable to install change data capture pipeline error="near "index": syntax error" node_id=126

[return2alfred]

@maxpert Since this is only a syntax error, I think it should be easy to fix, however, this indeed impacts the creation of CDC.

[igor-buzzoola]

what: SQlite return (on execute cmd) error "near "index": syntax error"
why: field name - is sqlite keyword (above - 'index'; other example: 'default')
fix: use (in source) field name in quotes

1. on send: db/table_change_log.script.tmpl, line32:
   • old: {{$read_target}}.{{$col.Name}},
   • new: {{$read_target}}.'{{$col.Name}}',
2. on receive: db/change_log.go, line 595:
   • old: columnNames = append(columnNames, k)
   • new: columnNames = append(columnNames, "'" + k + "'")