MySQL to Postgres Data Only with Truncate target table not truncating table before loading.

[hcpatel]

• pgloader --version

pgloader version "3.6.9"
compiled with SBCL 2.3.8

• did you test a fresh compile from the source tree?

No.

• did you search for other similar issues?

Yes.

• how can I reproduce the bug?

Load file with:

 LOAD DATABASE
    FROM mysql://<connection string>
    INTO postgresql://<connection string>


 WITH data only, truncate, disable triggers, downcase identifiers, reset sequences,
      workers = 8, concurrency = 1, prefetch rows = 500, batch rows = 500,
      single reader per thread, on error stop

• pgloader output you obtain

2024-06-26T12:12:52.888381-04:00 LOG report summary reset
                                         table name     errors       read   imported      bytes      total time       read      write
---------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                                    fetch meta data          0        109        109                     0.201s
                                  Drop Foreign Keys          0          0          0                     0.000s
                                           Truncate          0         53         53                     0.255s
---------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------

I see it indicated 'Truncate' , but I suspect table is not being truncated as subsequently the COPY fails with 'Duplicate Primary key constraint violation'.
After the run I still see the old rows in the table.

• data that is being loaded, if relevant

• How the data is different from what you expected, if relevant

I am looking to migrate data only, the script failed, on re-running was expecting it to 'detect there is data in the target table on postgres and expecting it to truncate the table prior to running the COPY of the data.

Is there a way to DUMP/Log the SQL pgloader runs for 'truncate'? so I can verify the '53 truncates' it indicates above?

[hcpatel]

I did a test with just this one table in MySQL. I am using a MATERIALIZE VIEWS to copy the data and finding it does not 'truncate' the data in target Postgres table when I use MATERIALIZE VIEWS.
My Load file has:

MATERIALIZE VIEWS view_name AS $$ <select from original_table_table> $$
EXCLUDING TABLE NAMES MATCHING 'original_table_name'
INCLUDING ONLY TABLE NAMES MATCHING 'view_name'
 ALTER TABLE NAMES MATCHING 'view_name' RENAME TO 'pg_table_name'

The result indicate no Truncate run:

2024-06-27T05:28:24.744574-04:00 LOG report summary reset
             table name     errors       read   imported      bytes      total time       read      write
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
        fetch meta data          0          1          1                     0.094s
      Drop Foreign Keys          0          0          0                     0.000s
               Truncate          0          0          0                     0.000s
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
   public.pg_table_name         1          2          0                     0.029s     0.012s     0.000s
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
COPY Threads Completion          0          8          8                     0.029s
        Reset Sequences          0        203        203                     0.394s
    Create Foreign Keys          0          0          0                     0.000s
       Install Comments          0          0          0                     0.000s
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
      Total import time          1          2          0                     0.423s

So how can I specify the 'target table should be truncated' (e.g. pg_table_name) when using MATERIALIZE VIEW with renaming tables from source to target?

[bobdaly]

I've encountered this issue as well, and now wondering if the WITH include drop construct would achieve the same thing.

[dimitri]

There are two separate issues here.

---

Issue A — main report: "53 truncates but data not actually truncated"

The 53 truncates DID run and commit. The prepare-pgsql-database transaction wraps all DDL — drop FKs, drop indexes, then TRUNCATE — and commits before any COPY operation starts. TRUNCATE in PostgreSQL is transactional, but here it commits successfully (it is the last thing in that transaction).

The duplicate PK errors during COPY come from the source MySQL data itself having duplicate primary key values, not from leftover data in PostgreSQL. MySQL with MyISAM or after dropping a PK constraint can store duplicate values that PostgreSQL then rejects. The tables that still show data after the failed run are tables whose COPY succeeded, not tables the TRUNCATE missed — the confusion is that the new rows are identical to the old ones.

Diagnostic: run this on the MySQL side to check:

SELECT pk_col, COUNT(*) FROM tbl GROUP BY pk_col HAVING COUNT(*) > 1;

---

Issue B — comment: MATERIALIZE VIEWS ... AS $$ ... $$ + RENAME gives "Truncate: 0"

This is a real v3 bug, now fixed in PR #1751.

When fetch-metadata processes a MATERIALIZE VIEWS view_name AS $$ SELECT ... $$ command, the view is fetched via fetch-columns with :table-type :view, so it lands in schema-view-list rather than schema-table-list. Meanwhile truncate-tables built its target list exclusively from (table-list catalog), which only returns schema-table-list entries — the view-list entries were invisible to it, so the TRUNCATE SQL was never generated for any materialized view.

The COPY phase already did the right thing — optimize-table-copy-ordering uses (nconc table-list view-list) — but the pre-COPY TRUNCATE step had not been updated to match. The fix is a two-line change in truncate-tables to include (view-list catalog) alongside (table-list catalog).

v4 status — not affected. Matviews with an explicit SQL definition are handled by materialize-views! which always runs DROP TABLE IF EXISTS + CREATE TABLE before COPY, so the target is always fresh. Matviews without an explicit SQL definition are merged into the regular catalog and go through the standard per-table TRUNCATE loop. Neither path has this miss.