https://slingdata.io logo
Powered by
Good Morning! We have started
# sling-help
m
Good Morning! We have started experimenting with Transforms and have seemingly hit a stumbling block. We are attempting to create a "true change at" key by the following.
Copy code
~ could not evaluate expression (greatest(record.DateAdded,record.LastChanged,record._sling_deleted_at)), with value=<nil> for column true_changed_at
DateAdded,LageChanged and _sling_deleted_at all do exist as columns.
Copy code
prod_<company>.zm_locs:
    transforms:
      - true_changed_at: 'greatest(record.DateAdded,record.LastChanged,record._sling_deleted_at)'
    target_options:
      format: parquet
Any help with this would be greatly appreciated <3
f
hey @MOBY , do you mind running in debug mode and sharing the full error stack? run with 
--debug
or env var 
DEBUG=true
Also, do this happen immediately? as in the very first row, or some later row?
m
Hi! it seems to be happening immediately.
Copy code
fatal:
--- proc.go:283 main ---
--- sling_cli.go:525 main ---
--- sling_cli.go:561 cliInit ---
--- cli.go:287 CliProcess ---
--- sling_run.go:269 processRun ---
--- sling_run.go:688 runPipeline ---
~ failure running pipeline (see docs @ https://docs.slingdata.io)
--- pipeline.go:185 Execute ---
~ error executing step: ingest
--- pipeline.go:301 Execute ---
~ error running replication
--- hooks..go:2171 Execute ---

--------------------------- prod_<company>.zm_locs ---------------------------
--- task_run.go:142 func2 ---
~ Could not ReadFromDB
--- task_run.go:837 runDbToDb ---
~ Could not BulkExportFlow
--- task_run_read.go:244 ReadFromDB ---
--- database.go:2501 BulkExportFlow ---
~ dataflow error while waiting for ready state
--- dataflow.go:700 WaitReady ---
--- database.go:2482 func1 ---
--- database_mysql.go:376 BulkExportStream ---
~ Error running query
--- database.go:834 StreamRows ---
~ could start datastream
--- database.go:983 StreamRowsContext ---
~ error in getting rows
--- datastream.go:787 Start ---
--- database.go:2482 func1 ---
--- database_mysql.go:376 BulkExportStream ---
--- database.go:834 StreamRows ---
--- database.go:983 StreamRowsContext ---
--- datastream.go:727 Start ---
~ could not evaluate expression (greatest(record.DateAdded,record.LastChanged,record._sling_deleted_at)), with value=<nil> for column true_changed_at
--- transform_stage..go:300 Evaluate ---
var error: object has no member "DateAdded"
Where 
DateAdded
very much exists and has a value set for every record. Thank you!
f
hi @MOBY , I was able to reproduce your issue. The field names are being lowercased for the transforms. This is done to normalize across all kinds of column casings. Can you try below?
Copy code
greatest(record.dateadded,record.lastchanged,record._sling_deleted_at)
I'll add some notes in the docs about this, thanks for raising
m
Will give it a go right now
Doesnt seem to like the 
_sling_deleted_at
Copy code
~ could not evaluate expression (greatest(record.dateadded,record.lastchanged,record._sling_deleted_at)), with value=<nil> for column true_changed_at
--- transform_stage..go:300 Evaluate ---
var error: object has no member "_sling_deleted_at"
I can ignore it for now, but it is something we would like to have working IF the record doesnt have that value
Okay, we are now seeing
Copy code
--- database_bigquery.go:260 ExecContext ---
googleapi: Error 400: Bad int64 value: 2025-09-04 16:31:31 +00, invalidQuery
That one seems fairly self explanitory though!
f
Interesting, 
_sling_deleted_at
works for me. Maybe that one doesn't exist? You can also try 
record["_sling_deleted_at"]
What's the source DB type?
m
Source DB Type is mysql, not all records have 
_sling_delted_at
Copy code
--- database_bigquery.go:260 ExecContext ---
googleapi: Error 400: Bad int64 value: 2025-09-04 16:31:31 +00, invalidQuery
This is being quite difficult now. Are there any known issues with creating new datetime columns?
f
so 
dateadded
and 
lastchanged
are both datetime/timestamp types? try 
cast(greatest(record.dateadded, record.lastchanged), "timestamp")
strange that the column type is int64 at DDL
m
Copy code
function error: "cast" - parsing time "\"2025-09-04T16:31:31Z\"" as "Mon, 02 Jan 2006 15:04:05 -0700": cannot parse "\"2025-09-04T16:31:31Z\"" as "Mon"
f
yeah, I'm seeing that too. Looking into why.
Ok, found a weird bug that introduced quotes before/after the timestamp value when using 
greatest
. This is already fixed in the next release. this below works for now as a work-around:
Copy code
transforms:
      - true_changed_at: >
          greatest(
            date_parse(replace(record.dateadded, "\"", "")),
            date_parse(replace(record.lastchanged, "\"", "")), 
            date_parse(replace(record._sling_deleted_at, "\"", ""))
          )
in next release (1.5.5), you'll just do this:
Copy code
- true_changed_at: >
          greatest(
            date_parse(record.dateadded), 
            date_parse(record.lastchanged), 
            date_parse(record._sling_deleted_at)
          )
m
I'll give this a go later tonight/tomorrow morning. Is this expecting to fix 
var error: object has no member "_sling_deleted_at"
also?
f
_sling_deleted_at
is working fine for me, this test file [here](https://github.com/slingdata-io/sling-cli/blob/v1.5.5/cmd/sling/tests/replications/r.87.record_key_casing_bigquery.yaml) is passing fine with the latest code changes. Not too sure what's going on on your side. Could you share the mysql source table DDL (at least for those 3 columns)? Even if the value is 
null
, that shouldn't cause it to fail.
m
I'll have a go now and let you know 👍
I'm an idiot. Ignore this, not a bug
I was trying to use it on the first transfer... where it doesnt exist
New error! Looks like there is some spooky casting going on
Copy code
--- database_bigquery.go:260 ExecContext ---
googleapi: Error 400: Invalid cast from TIMESTAMP to INT64 at [3:1153], invalidQuery
This is going into Google BigQuery
f
hmm, can you share the debug logs? need more details. I wondering if one of the timestamp columns is created as int64 BQ side. sling will attempt to cast from timestamp to int64 (if you're running incremental mode)
m
Okay, Think I fixed the google issue!
f
Great, what was the issue?
m
The issue was that previously in my testing, the column was created as an int
I deleted the column and let sling re-create it and it's happy
Now a new issue! (Yay) - I am sorry
Copy code
--------------------------- prod_<company>.zm_locs ---------------------------
--- task_run.go:142 func2 ---
--- task_run.go:882 runDbToDb ---
~ could not delete missing records
--- task_run_delete..go:87 deleteMissingRecords ---
~ could not query source IDs
--- database.go:834 StreamRows ---
~ could start datastream
--- database.go:983 StreamRowsContext ---
~ error in getting rows
--- datastream.go:787 Start ---
--- task_run.go:142 func2 ---
--- task_run.go:882 runDbToDb ---
--- task_run_delete..go:87 deleteMissingRecords ---
--- database.go:834 StreamRows ---
--- database.go:983 StreamRowsContext ---
--- datastream.go:727 Start ---
~ could not evaluate expression (greatest(
  date_parse(replace(record.dateadded, "\"", "")),
  date_parse(replace(record.lastchanged, "\"", ""))
)
), with value=<nil> for column true_changed_at
--- transform_stage..go:300 Evaluate ---
var error: object has no member "dateadded"
Looks like the string replacement isnt quite working right
f
hmm, this is about the column missing in the record, could it be not in the source table?
m
The column 
DateAdded
exists
If I set the column names to be case-accurate, we get:
Copy code
--------------------------- prod_<company>.zm_locs ---------------------------
--- task_run.go:142 func2 ---
~ Could not ReadFromDB
--- task_run.go:837 runDbToDb ---
~ Could not BulkExportFlow
--- task_run_read.go:244 ReadFromDB ---
--- database.go:2501 BulkExportFlow ---
~ dataflow error while waiting for ready state
--- dataflow.go:700 WaitReady ---
--- database.go:2482 func1 ---
--- database_mysql.go:376 BulkExportStream ---
~ Error running query
--- database.go:834 StreamRows ---
~ could start datastream
--- database.go:983 StreamRowsContext ---
~ error in getting rows
--- datastream.go:787 Start ---
--- database.go:2482 func1 ---
--- database_mysql.go:376 BulkExportStream ---
--- database.go:834 StreamRows ---
--- database.go:983 StreamRowsContext ---
--- datastream.go:727 Start ---
~ could not evaluate expression (greatest(
  date_parse(replace(record.DateAdded, "\"", "")),
  date_parse(replace(record.LastChanged, "\"", ""))
)
), with value=<nil> for column true_changed_at
--- transform_stage..go:300 Evaluate ---
var error: object has no member "DateAdded"
if I do lower-case:
Copy code
--------------------------- prod_<company>.zm_locs ---------------------------
--- task_run.go:142 func2 ---
--- task_run.go:882 runDbToDb ---
~ could not delete missing records
--- task_run_delete..go:87 deleteMissingRecords ---
~ could not query source IDs
--- database.go:834 StreamRows ---
~ could start datastream
--- database.go:983 StreamRowsContext ---
~ error in getting rows
--- datastream.go:787 Start ---
--- task_run.go:142 func2 ---
--- task_run.go:882 runDbToDb ---
--- task_run_delete..go:87 deleteMissingRecords ---
--- database.go:834 StreamRows ---
--- database.go:983 StreamRowsContext ---
--- datastream.go:727 Start ---
~ could not evaluate expression (greatest(
  date_parse(replace(record.dateadded, "\"", "")),
  date_parse(replace(record.lastchanged, "\"", ""))
)
), with value=<nil> for column true_changed_at
--- transform_stage..go:300 Evaluate ---
var error: object has no member "dateadded"
Copy code
-- prod_<company>.zm_locs definition

CREATE TABLE `zm_locs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,

  `DateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastChanged` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loc_num_uk` (`LocNum`),
  KEY `FK_zm_locs_zm_location_category` (`location_category_id`),
  KEY `LastChanged` (`LastChanged`),
  KEY `postcode` (`Postcode`),
  KEY `party_number_index` (`party_number`),
  KEY `idx_locs_external_reference` (`party_number`,`external_id`),
  KEY `fk__locs__network_id` (`network_id`),
  KEY `ranking` (`ranking`),
  KEY `locale_search` (`CountryCode`,`PostTown`),
  SPATIAL KEY `coordinates` (`coordinates`),
  CONSTRAINT `fk__locs__network_id` FOREIGN KEY (`network_id`) REFERENCES `zm_network` (`id`),
  CONSTRAINT `FK_zm_locs_zm_location_category` FOREIGN KEY (`location_category_id`) REFERENCES `zm_location_category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5104071 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DDL
Did you manage to get anywhere with this? :)
f
Hey, no, I actually tried to run again an hour ago and it works fine for me, so I'm just not sure what's going on... below works fine:
Copy code
source: mysql
target: bigquery

hooks:
  start:
    - type: query
      connection: '{source.name}'
      query: |
        DROP TABLE IF EXISTS mysql.test_record_key_casing;
        CREATE TABLE mysql.test_record_key_casing (
          `id` int unsigned NOT NULL,
          `DateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `LastChanged` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          `_sling_deleted_at` TIMESTAMP NULL,
          value TEXT
        );
        INSERT INTO mysql.test_record_key_casing (id, `DateAdded`, `LastChanged`, `_sling_deleted_at`, value)
        VALUES
          (1, '2024-01-15 10:00:00', '2024-01-20 15:30:00', NULL, 'row1'),
          (2, '2024-02-10 08:00:00', '2024-02-25 12:00:00', '2024-03-01 09:00:00', NULL),
          (3, '2024-03-05 09:00:00', '2024-03-10 18:00:00', NULL, 'row3');


streams:
  mysql.test_record_key_casing:
    object: public.test_record_key_casing
    mode: full-refresh
    target_options:
      direct_insert: true

    transforms:
      - true_changed_at: >
          greatest(
            date_parse(replace(record.dateadded, "\"", "")),
            date_parse(replace(record.lastchanged, "\"", ""))
          )
can you try running that yourself? adjust the names as needed, just to test
m
of course, thank you!
Right! Update! It turns out it was running when lower-case. It looks like it was just failing at a spefific point
Just need to work out why it failed now
The error is actually during the delete detection stage. Does that change anything as faras you know?
Copy code
2026-01-16 12:18:57 INF inserted 279279 rows into `prod_<company>`.`zm_locs` in 61 secs [4,505 r/s] [96 MB]
2026-01-16 12:18:57 INF processing missing soft ID deletion for columns ["id"] into table `prod_<company>`.`zm_locs`
2026-01-16 12:18:58 DBG create table `prod_<company>`.`zm_locs_temp_ids` (`id` int64) [ingest-bigquery-prod-q2P]
2026-01-16 12:19:00 DBG select `id` from `prod_<company>`.`zm_locs` [doprod-general-01-kgO]
2026-01-16 12:19:00 DBG adding column via transform: true_changed_at
2026-01-16 12:19:00 DBG applying column casing (normalize) for target type (bigquery)
2026-01-16 12:19:00 DBG applying column casing (normalize) for target type (bigquery)
2026-01-16 12:19:00 DBG drop table if exists `prod_<company>`.`zm_locs_temp_ids` [ingest-bigquery-prod-q2P]
2026-01-16 12:19:00 DBG table `prod_<company>`.`zm_locs_temp_ids` dropped
2026-01-16 12:19:00 DBG drop table if exists `prod_<company>`.`zm_locs_tmp` [ingest-bigquery-prod-q2P]
2026-01-16 12:19:01 DBG table `prod_<company>`.`zm_locs_tmp` dropped
2026-01-16 12:19:01 DBG closed "bigquery" connection (conn-bigquery-ingest-bigquery-prod-q2P)
2026-01-16 12:19:01 DBG closed "mysql" connection (conn-mysql-doprod-general-01-kgO)
2026-01-16 12:19:01 INF execution failed

2026-01-16 12:19:01 WRN could not write pipeline store to file: open /home/sling/.sling/executions/38L5BQLDm95Z2JgzXipIopJ2Xvk/runtime/_pipeline_store_.json: no such file or directory
2026-01-16 12:19:01 DBG failure while executing step "ingest" (type: replication) =>
So we think we know what might be happening. We think that the transformations are being attempted during the delete detection stage where those columns are not present
f
OK thanks, will try to reproduce and get back to you
Hey @MOBY I was able to reproduce and it is a bug. This should be fixed in the next release. Targeting end of this week
m
Thank you! ❤️❤️
2 Views
PreviousNext
Powered by