yuniql: Error during bulk data import: 42601 syntax error at or near ")"

Hi,

I’m evaluating bulk import functionality. My database is Postgres 12.3 (running via docker).

I’ve created a version folder using vnext command, and put there two files:

test_data.csv (i tried to include id to the csv file - the result is the same)

"first_name","last_name"
"Bob","Green"
"Robert","Red"

script.sql

create table test_data(
  id SERIAL PRIMARY KEY,
  first_name varchar(20),
  last_last varchar(20)
)

When I run run command against the database I get this error:

....
INF   2020-06-19T16:41:56.2163740Z   PostgreSqlBulkImportService: Started copying data into destination table public.test_data
INF   2020-06-19T16:41:56.2254700Z   PostgreSqlBulkImportService: COPY public.test_data () FROM STDIN (FORMAT BINARY)
ERR   2020-06-19T16:41:56.2408861Z   Failed to execute run function. 
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near ")"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.ReadMessage(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlBinaryImporter..ctor(NpgsqlConnector connector, String copyFromCommand)
   at Npgsql.NpgsqlConnection.BeginBinaryImport(String copyFromCommand)
   at Yuniql.PostgreSql.PostgreSqlBulkImportService.BulkCopyWithDataTable(IDbConnection connection, IDbTransaction transaction, String schemaName, String tableName, DataTable dataTable) in C:\projects\yuniql\yuniql-platforms\postgresql\PostgreSqlBulkImportService.cs:line 113
   at Yuniql.PostgreSql.PostgreSqlBulkImportService.Run(IDbConnection connection, IDbTransaction transaction, String fileFullPath, String bulkSeparator, Nullable`1 bulkBatchSize, Nullable`1 commandTimeout) in C:\projects\yuniql\yuniql-platforms\postgresql\PostgreSqlBulkImportService.cs:line 56
   at Yuniql.Core.MigrationServiceBase.<>c__DisplayClass16_0.<RunBulkImport>b__1(String csvFile) in C:\projects\yuniql\yuniql-core\MigrationServiceBase.cs:line 181
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at Yuniql.Core.MigrationServiceBase.RunBulkImport(IDbConnection connection, IDbTransaction transaction, String workingPath, String scriptDirectory, String bulkSeparator, Nullable`1 bulkBatchSize, Nullable`1 commandTimeout, String environmentCode) in C:\projects\yuniql\yuniql-core\MigrationServiceBase.cs:line 183
   at Yuniql.Core.MigrationService.<>c__DisplayClass10_0.<RunVersionScripts>b__2(String versionDirectory) in C:\projects\yuniql\yuniql-core\MigrationService.cs:line 300
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at Yuniql.Core.MigrationService.RunVersionScripts(IDbConnection connection, IDbTransaction transaction, List`1 dbVersions, String workingPath, String targetVersion, NonTransactionalContext nonTransactionalContext, List`1 tokenKeyPairs, String bulkSeparator, String metaSchemaName, String metaTableName, Nullable`1 commandTimeout, Nullable`1 bulkBatchSize, String appliedByTool, String appliedByToolVersion, String environmentCode) in C:\projects\yuniql\yuniql-core\MigrationService.cs:line 315
   at Yuniql.Core.MigrationService.<Run>g__RunAllInternal|9_2(IDbConnection connection, IDbTransaction transaction, <>c__DisplayClass9_0& ) in C:\projects\yuniql\yuniql-core\MigrationService.cs:line 212
   at Yuniql.Core.MigrationService.Run(String workingPath, String targetVersion, Nullable`1 autoCreateDatabase, List`1 tokenKeyPairs, Nullable`1 verifyOnly, String bulkSeparator, String metaSchemaName, String metaTableName, Nullable`1 commandTimeout, Nullable`1 bulkBatchSize, String appliedByTool, String appliedByToolVersion, String environmentCode, Nullable`1 resumeFromFailure) in C:\projects\yuniql\yuniql-core\MigrationService.cs:line 149
   at Yuniql.CLI.CommandLineService.RunMigration(RunOption opts) in C:\projects\yuniql\yuniql-cli\CommandLineService.cs:line 146
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near ")"
    Position: 24
    File: scan.l
    Line: 1149
    Routine: scanner_yyerror

Please advise.

About this issue

Original URL
State: open
Created 4 years ago
Comments: 15 (8 by maintainers)

Most upvoted comments

Yes, I forsee different transaction modes full (default),per-version,none. The full is the default for all platforms supporting transactional DDL, the per-version best fit in your scenario where some scripts really depents on a committed state, the third none is for versions that require optimal execution without transaction.

rdagumampan on Jun 20, 2020

Each migration is one atomic transaction so far.

What you are saying is that for example if I have to update the database which for example has version v0.1 up to the version v.5 - all the versions in between (0.2, 0.3, 0.4) along with the final version - are all executed in the single transaction? Am I correct here?

Yes, they will be single transaction when you call yuniql run. It’s an all or nothing situation and this means they would be sharing the transaction and will cause the CSV import to fail.

The only way so far we can split them is to make targetted run such as we specify the target version such as yuniql run -t v0.01, yuniql run -t v0.05

rdagumampan on Jun 20, 2020

Hi @kirill-gerasimenko , thanks again for reaching out. The tool can only get better with valuable feedback like this.

I was able to reproduce your case. I found that this is because the schema definition of the destination table is not yet available when the creation of tables and import of CSV shared the same transaction. This mean putting the .sql file and .csv files in the same version is not supported in this build.

This describes the possible workaround here for now. I will investigate this further on how we can support DDL and bulk import in the same transaction.

Deploy local database container

docker run -d --name yuniql-postgresql -e POSTGRES_USER=sa -e POSTGRES_PASSWORD=P@ssw0rd! -e POSTGRES_DB=helloyuniql -p 5432:5432 postgres

Set environment variable

SETX YUNIQL_CONNECTION_STRING "Host=localhost;Port=5432;Username=sa;Password=P@ssw0rd!;Database=yuniqldb"

Prepare and run the baseline first

md c:\temp\issue-129
cd c:\temp\issue-129

yuniql init
code c:\temp\issue-129\v0.00\sample.sql

create table test_data(
  first_name varchar(20),
  last_name varchar(20)
)

yuniql run -a -t v0.00 --platform postgresql --debug

Prepare and run the csv import

yuniql vnext -f test_data.csv
code c:\temp\issue-129\v0.01\test_data.csv

"first_name","last_name"
"Bob","Green"
"Robert","Red"

yuniql run --platform postgresql --debug

rdagumampan on Jun 20, 2020