Free and open source schema versioning and database migration made natively with .NET/6. NEW THIS MAY 2022! v1.3.15 released!
417
stars
63
forks
source link
syntax error at or near "CONNECT" PostgreSQL unable to deploy script on DB #283
Open
Naimishah opened 2 years ago
2022-09-06T10:46:21.9993631Z ##[section]Starting: Verify Yuniql 2022-09-06T10:46:22.0358555Z ============================================================================== 2022-09-06T10:46:22.0358905Z Task : Verify yuniql 2022-09-06T10:46:22.0359255Z Description : Runs an uncommitted database migrations with Yuniql CLI using version acquired from Use Yuniql task. 2022-09-06T10:46:22.0359610Z Version : 1.190.0 2022-09-06T10:46:22.0359837Z Author : Rodel E. Dagumampan 2022-09-06T10:46:22.0360131Z Help : https://yuniql.io/docs/migrate-via-azure-devops-pipelines/ 2022-09-06T10:46:22.0360493Z ============================================================================== 2022-09-06T10:46:22.7889326Z yuniql/input_version: latest 2022-09-06T10:46:22.7889950Z yuniql/input_workspacePath: _PBM_DataManagement_DB_CI/Database 2022-09-06T10:46:22.7890602Z yuniql/input_connectionString: Server=10.55.127.8;Port=5432;Database=tpm; User Id=TP_DEV@udtppg;Password=4UDTPPG!; 2022-09-06T10:46:22.7891255Z yuniql/input_targetPlatform: postgresql 2022-09-06T10:46:22.7891951Z yuniql/input_autoCreateDatabase: false 2022-09-06T10:46:22.7892440Z yuniql/input_targetVersion: latest 2022-09-06T10:46:22.7892886Z yuniql/input_tokenKeyValuePair: undefined 2022-09-06T10:46:22.7893323Z yuniql/input_delimiter: , 2022-09-06T10:46:22.7893739Z yuniql/input_additionalArguments: --debug 2022-09-06T10:46:22.7894166Z yuniql/var_osPlat: win32 2022-09-06T10:46:22.7894578Z yuniql/var_osArch: x64 2022-09-06T10:46:22.7898401Z Found tool in cache: yuniql 0.0.0 x64 2022-09-06T10:46:22.7898978Z yuniql/var_yuniqlBasePath: F:\Softwares\vsts-agent-win-x64-2.165.0_work_tool\yuniql\0.0.0\x64 2022-09-06T10:46:22.7899666Z yuniql/var_yuniqlExecFilePath: F:\Softwares\vsts-agent-win-x64-2.165.0_work_tool\yuniql\0.0.0\x64\yuniql.exe 2022-09-06T10:46:22.7901236Z yuniql/additionalArguments array is 2022-09-06T10:46:22.7901669Z arg#0: --debug 2022-09-06T10:46:22.7902526Z [command]F:\Softwares\vsts-agent-win-x64-2.165.0_work_tool\yuniql\0.0.0\x64\yuniql.exe verify -p _PBM_DataManagement_DB_CI/Database -c "Server=10.55.127.8;Port=5432;Database=tpm; User Id=TP_DEV@udtppg;Password=4UDTPPG!;" --platform postgresql --bulk-separator , --debug 2022-09-06T10:46:24.8716393Z Running yuniql v1.3.15 for windows-x64 2022-09-06T10:46:24.8756349Z Copyright 2019 (C) Rodel E. Dagumampan. Apache License v2.0 2022-09-06T10:46:24.8785438Z Visit https://yuniql.io for documentation and working samples 2022-09-06T10:46:24.8786565Z 2022-09-06T10:46:24.8793840Z WRN 2022-09-06 10:46:24Z Trace logs settings is set to silent (default) and no log files will be produced. To enable log file creation, pass parameter --trace-to-file or see our CLI command reference. 2022-09-06T10:46:25.1102849Z INF 2022-09-06 10:46:25Z Run configuration: 2022-09-06T10:46:25.1135532Z { 2022-09-06T10:46:25.1148728Z "isInitialized": true, 2022-09-06T10:46:25.1161427Z "workspace": "_PBM_DataManagement_DB_CI/Database", 2022-09-06T10:46:25.1165675Z "platform": "postgresql", 2022-09-06T10:46:25.1180094Z "connectionString": "",
2022-09-06T10:46:25.1180834Z "isAutoCreateDatabase": false,
2022-09-06T10:46:25.1181383Z "targetVersion": null,
2022-09-06T10:46:25.1181870Z "tokens": [
2022-09-06T10:46:25.1198104Z {},
2022-09-06T10:46:25.1200725Z {}
2022-09-06T10:46:25.1215472Z ],
2022-09-06T10:46:25.1218191Z "isVerifyOnly": true,
2022-09-06T10:46:25.1244687Z "bulkSeparator": ",",
2022-09-06T10:46:25.1245149Z "bulkBatchSize": 0,
2022-09-06T10:46:25.1245682Z "commandTimeout": 30,
2022-09-06T10:46:25.1246065Z "isDebug": true,
2022-09-06T10:46:25.1246439Z "appliedByTool": "yuniql-cli",
2022-09-06T10:46:25.1246888Z "appliedByToolVersion": "1.3.15.0",
2022-09-06T10:46:25.1247343Z "environment": null,
2022-09-06T10:46:25.1247717Z "metaSchemaName": null,
2022-09-06T10:46:25.1249488Z "metaTableName": null,
2022-09-06T10:46:25.1249977Z "isContinueAfterFailure": false,
2022-09-06T10:46:25.1250420Z "transactionMode": "session",
2022-09-06T10:46:25.1295066Z "isRequiredClearedDraft": false,
2022-09-06T10:46:25.1295528Z "isForced": false
2022-09-06T10:46:25.1295738Z }
2022-09-06T10:46:25.3018305Z INF 2022-09-06 10:46:25Z No explicit target version requested. We'll use latest available locally v0.01 on _PBM_DataManagement_DB_CI/Database.
2022-09-06T10:46:25.5313302Z DBG 2022-09-06 10:46:25Z Replaced token YUNIQL_TABLE_NAME with yuniql_schema_version
2022-09-06T10:46:25.6235109Z DBG 2022-09-06 10:46:25Z Replaced token YUNIQL_SCHEMA_NAME with public
2022-09-06T10:46:25.6235670Z DBG 2022-09-06 10:46:25Z Executing statement BA15F9:
2022-09-06T10:46:25.6238274Z
2022-09-06T10:46:25.6258919Z SELECT 1 FROM pg_tables WHERE tablename = '__yuniql_schema_version' AND schemaname = 'public';
2022-09-06T10:46:25.6266701ZgReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
2022-09-06T10:46:26.6955688Z at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2022-09-06T10:46:26.6956160Z at Npgsql.NpgsqlDataReader.NextResult()
2022-09-06T10:46:26.6956657Z at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2022-09-06T10:46:26.6957273Z at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2022-09-06T10:46:26.6957859Z at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
2022-09-06T10:46:26.6958294Z at Npgsql.NpgsqlCommand.ExecuteNonQuery()
2022-09-06T10:46:26.6958897Z at Yuniql.Core.ConnectionExtensions.ExecuteNonQuery(IDbConnection connection, String commandText, Nullableb 3(String sqlStatement)
2022-09-06T10:46:26.6960937Z at System.Collections.Generic.Listb__1(String scriptFile)
2022-09-06T10:46:26.6961876Z at System.Collections.Generic.List DisplayClass12_2.b11(String scriptSubDirectory)
2022-09-06T10:46:26.6968755Z at System.Collections.Generic.ListgRunVersionDirectoriesInternal|8(IDbConnection connection, IDbTransaction transaction, List`1 scriptSubDirectories, String scriptDirectory, String versionDirectory, Stopwatch stopwatch)
2022-09-06T10:46:26.6970451Z at Yuniql.Core.MigrationService.<>cDisplayClass12_0.b7(<>fAnonymousType0
2022-09-06T10:46:26.4426448Z DBG 2022-09-06 10:46:26Z Statement BA15F9 executed in 891 ms 2022-09-06T10:46:26.4579709Z DBG 2022-09-06 10:46:26Z Executing statement E5CCD9: 2022-09-06T10:46:26.4583927Z 2022-09-06T10:46:26.4584433Z SELECT 'v1.1' FROM pg_tables WHERE tablename = 'yuniqldbversion'; 2022-09-06T10:46:26.4584784Z
2022-09-06T10:46:26.4639118Z DBG 2022-09-06 10:46:26Z Statement E5CCD9 executed in 6 ms 2022-09-06T10:46:26.4639997Z INF 2022-09-06 10:46:26Z The schema version tracking table is up to date for tpm on 10.55.127.8. 2022-09-06T10:46:26.4708801Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_SCHEMA_NAME with public 2022-09-06T10:46:26.4709568Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_TABLE_NAME with yuniql_schema_version 2022-09-06T10:46:26.4710123Z DBG 2022-09-06 10:46:26Z Executing statement: 2022-09-06T10:46:26.4710410Z 2022-09-06T10:46:26.4711024Z SELECT sequence_id, version, applied_on_utc, applied_by_user, applied_by_tool, applied_by_tool_version, status, duration_ms, checksum, failed_script_path, failed_script_error, additional_artifacts 2022-09-06T10:46:26.4711683Z FROM public.yuniql_schema_version ORDER BY version ASC; 2022-09-06T10:46:26.4712364Z
2022-09-06T10:46:26.4912457Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_SCHEMA_NAME with public 2022-09-06T10:46:26.4913141Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_TABLE_NAME with yuniql_schema_version 2022-09-06T10:46:26.4913743Z DBG 2022-09-06 10:46:26Z Executing statement: 2022-09-06T10:46:26.4913930Z 2022-09-06T10:46:26.4915448Z SELECT sequence_id, version, applied_on_utc, applied_by_user, applied_by_tool, applied_by_tool_version, status, duration_ms, checksum, failed_script_path, failed_script_error, additional_artifacts 2022-09-06T10:46:26.4916140Z FROM public.yuniql_schema_version ORDER BY version ASC; 2022-09-06T10:46:26.4916425Z
2022-09-06T10:46:26.4989304Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_SCHEMA_NAME with public 2022-09-06T10:46:26.4990027Z DBG 2022-09-06 10:46:26Z Replaced token YUNIQL_TABLE_NAME with yuniql_schema_version 2022-09-06T10:46:26.4990499Z DBG 2022-09-06 10:46:26Z Executing statement: 2022-09-06T10:46:26.4991060Z 2022-09-06T10:46:26.4991749Z SELECT sequence_id, version, applied_on_utc, applied_by_user, applied_by_tool, applied_by_tool_version, status, duration_ms, checksum, failed_script_path, failed_script_error, additional_artifacts 2022-09-06T10:46:26.4992651Z FROM public.yuniql_schema_version ORDER BY version ASC; 2022-09-06T10:46:26.4993283Z
2022-09-06T10:46:26.5114790Z INF 2022-09-06 10:46:26Z Transaction created for current session. This migration run will be executed in a shared connection and transaction context. 2022-09-06T10:46:26.5248341Z INF 2022-09-06 10:46:26Z Found 0 script files on _PBM_DataManagement_DB_CI/Database_pre 2022-09-06T10:46:26.5263286Z INF 2022-09-06 10:46:26Z Found 0 bulk files on _PBM_DataManagement_DB_CI/Database_pre 2022-09-06T10:46:26.5263873Z INF 2022-09-06 10:46:26Z Executed script files on _PBM_DataManagement_DB_CI/Database_pre 2022-09-06T10:46:26.5390907Z INF 2022-09-06 10:46:26Z Found 1 script files on _PBM_DataManagement_DB_CI/Database\v0.01\34-20220905 2022-09-06T10:46:26.5392834Z + 4372_Add_New_fuel.sql 2022-09-06T10:46:26.5557832Z DBG 2022-09-06 10:46:26Z Executing sql statement as part of : _PBM_DataManagement_DB_CI/Database\v0.01\34-20220905\4372_Add_New_fuel.sql 2022-09-06T10:46:26.5567536Z DBG 2022-09-06 10:46:26Z Executing statement 3FBFA4: 2022-09-06T10:46:26.5567931Z CONNECT TP_DEV 2022-09-06T10:46:26.5568149Z 2022-09-06T10:46:26.5568228Z 2022-09-06T10:46:26.5568404Z DECLARE 2022-09-06T10:46:26.5568759Z id_max_p INTEGER; 2022-09-06T10:46:26.5568984Z id_Market INTEGER; 2022-09-06T10:46:26.5569366Z id_max_master_data_change INTEGER;
2022-09-06T10:46:26.5569779Z rowCnt INTEGER := 0; 2022-09-06T10:46:26.5570036Z id_fueltype INTEGER;
2022-09-06T10:46:26.5570253Z BEGIN 2022-09-06T10:46:26.5570518Z --** Add New Fuel BIO1_MV3_CDS *** 2022-09-06T10:46:26.5570923Z --SELECT nvl((SELECT 1 FROM fuel WHERE name='BIO1_MV3_CDS'),0) INTO rowCnt FROM dual;
2022-09-06T10:46:26.5571405Z SELECT count(1) INTO rowCnt FROM fuel WHERE name='BIO1_MV3_CDS' ; 2022-09-06T10:46:26.5571699Z
2022-09-06T10:46:26.5571902Z IF rowCnt = 0 THEN 2022-09-06T10:46:26.5572234Z SELECT ID INTO id_Market FROM market_area WHERE name = 'NL'; 2022-09-06T10:46:26.5572802Z SELECT ID INTO id_fueltype FROM Fuel_Type where Name='Biomass'; 2022-09-06T10:46:26.5573285Z SELECT MAX (ID) + 1 INTO id_max_p FROM fuel; 2022-09-06T10:46:26.5573684Z SELECT MAX(ID) + 1 INTO id_max_master_data_change FROM master_data_change_upload; 2022-09-06T10:46:26.5573993Z
2022-09-06T10:46:26.5574155Z
2022-09-06T10:46:26.5574674Z INSERT INTO fuel (id, name, fuel_type_id, fuel_level, fuel_tax, fuel_tax_uom, cds_price_curve, first_trading_hour,traded_unit_scaling_factor, fuel_hedged, reporting_uom,lagging_structure) 2022-09-06T10:46:26.5575609Z VALUES (id_max_p, 'BIO1_MV3_CDS', id_fueltype, 'Nothing', null, null, 'BIOMASS_MV3', 0, 1, 1, 't',null); 2022-09-06T10:46:26.5576000Z
2022-09-06T10:46:26.5576449Z INSERT INTO master_data_change_upload(ID, action, asset_type, asset_id,upload_name,comments,file_name,kid,upload_timestamp,market_area_id) 2022-09-06T10:46:26.5577106Z VALUES (id_max_master_data_change, 'Add', 'Fuel', id_max_p,NULL,'BIO1_MV3_CDS',NULL,NULL,SYSDATE,id_Market); 2022-09-06T10:46:26.5577832Z
2022-09-06T10:46:26.5578446Z INSERT INTO fuel_audit (id, name, fuel_type_id, fuel_level,fuel_tax,fuel_tax_uom, cds_price_curve, first_trading_hour, traded_unit_scaling_factor, fuel_hedged, reporting_uom,lagging_structure, master_data_change_upload_id) 2022-09-06T10:46:26.5579306Z VALUES (id_max_p,'BIO1_MV3_CDS',id_fueltype,'Nothing',null,null,'BIOMASS_MV3',0,1,1,'t', NULL,id_max_master_data_change);
2022-09-06T10:46:26.5579738Z -- COMMIT; 2022-09-06T10:46:26.5579941Z END IF; 2022-09-06T10:46:26.5580146Z End 2022-09-06T10:46:26.5580327Z / 2022-09-06T10:46:26.6949437Z ERR 2022-09-06 10:46:26Z Failed to execute verification function. Target database will be rolled back to its previous state. Migration of version v0.01 was running in transaction and has failed while attempting to execute script file _PBM_DataManagement_DB_CI/Database\v0.01\34-20220905\4372_Add_New_fuel.sql due to error "(0x80004005) Error 42601: syntax error at or near "CONNECT" 2022-09-06T10:46:26.6950344Z 2022-09-06T10:46:26.6951186Z POSITION: 1. Exception data: Severity: ERROR, InvariantSeverity: ERROR, SqlState: 42601, MessageText: syntax error at or near "CONNECT", Position: 1, File: scan.l, Line: 1135, Routine: scanner_yyerror". The target database will initiate a rollback attempt to revert all applicable DML and DDL changes. The database auto-created will be kept but would be empty. 2022-09-06T10:46:26.6952155Z Diagnostics stack trace captured a Yuniql.Core.YuniqlMigrationException: 2022-09-06T10:46:26.6952671Z ---> Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "CONNECT" 2022-09-06T10:46:26.6953977Z 2022-09-06T10:46:26.6954239Z POSITION: 1 2022-09-06T10:46:26.6954861Z at Npgsql.Internal.NpgsqlConnector.
1 commandTimeout, IDbTransaction transaction, ITraceService traceService) 2022-09-06T10:46:26.6959755Z at Yuniql.Core.MetadataService.ExecuteSql(IDbConnection connection, String commandText, Nullable1 commandTimeout, IDbTransaction transaction, ITraceService traceService) 2022-09-06T10:46:26.6960451Z at Yuniql.Core.MigrationService.<>c__DisplayClass16_1.1.ForEach(Action1 ) 2022-09-06T10:46:26.6961410Z at Yuniql.Core.MigrationService.<>cDisplayClass16_0.1.ForEach(Action1 ) 2022-09-06T10:46:26.6962942Z at Yuniql.Core.MigrationService.RunVersionSqlScripts(IDbConnection connection, IDbTransaction transaction, TransactionContext transactionContext, Stopwatch stopwatch, String version, String workspace, String scriptDirectory, String metaSchemaName, String metaTableName, List1 tokens, Nullable1 commandTimeout, String environment, String appliedByTool, String appliedByToolVersion) 2022-09-06T10:46:26.6963881Z Exception data: 2022-09-06T10:46:26.6964115Z Severity: ERROR 2022-09-06T10:46:26.6964540Z SqlState: 42601 2022-09-06T10:46:26.6964848Z MessageText: syntax error at or near "CONNECT" 2022-09-06T10:46:26.6965125Z Position: 1 2022-09-06T10:46:26.6965343Z File: scan.l 2022-09-06T10:46:26.6965572Z Line: 1135 2022-09-06T10:46:26.6965850Z Routine: scanner_yyerror 2022-09-06T10:46:26.6966159Z --- End of inner exception stack trace --- 2022-09-06T10:46:26.6967138Z at Yuniql.Core.MigrationService.RunVersionSqlScripts(IDbConnection connection, IDbTransaction transaction, TransactionContext transactionContext, Stopwatch stopwatch, String version, String workspace, String scriptDirectory, String metaSchemaName, String metaTableName, List1 tokens, Nullable1 commandTimeout, String environment, String appliedByTool, String appliedByToolVersion) 2022-09-06T10:46:26.6968261Z at Yuniql.Core.MigrationService.<>c1.ForEach(Action1 ) 2022-09-06T10:46:26.6969562Z at Yuniql.Core.MigrationService.<>cDisplayClass12_0.2 version) 2022-09-06T10:46:26.6970956Z at System.Collections.Generic.List1.ForEach(Action1 ) 2022-09-06T10:46:26.6972074Z at Yuniql.Core.MigrationService.RunVersionDirectories(IDbConnection connection, IDbTransaction transaction, List1 appliedVersions, String workspace, String targetVersion, TransactionContext transactionContext, List1 tokens, String bulkSeparator, String metaSchemaName, String metaTableName, Nullable1 commandTimeout, Nullable1 bulkBatchSize, String appliedByTool, String appliedByToolVersion, String environment, String transactionMode) 2022-09-06T10:46:26.6973691Z at Yuniql.Core.MigrationService.<Run>g__RunAllInternal|11_3(IDbConnection connection, IDbTransaction transaction, Boolean isRequiredClearedDraft, <>c__DisplayClass11_0& ) 2022-09-06T10:46:26.6975017Z at Yuniql.Core.MigrationService.Run(String workspace, String targetVersion, Nullable1 isAutoCreateDatabase, List1 tokens, Nullable1 isVerifyOnly, String bulkSeparator, String metaSchemaName, String metaTableName, Nullable1 commandTimeout, Nullable1 bulkBatchSize, String appliedByTool, String appliedByToolVersion, String environment, Nullable`1 isContinueAfterFailure, String transactionMode, Boolean isRequiredClearedDraft) 2022-09-06T10:46:26.6976089Z at Yuniql.Core.MigrationService.Run() 2022-09-06T10:46:26.6976468Z at Yuniql.CLI.CommandLineService.RunVerifyOption(VerifyOption opts) 2022-09-06T10:46:26.6976967Z If you think this is a bug, please report an issue here https://github.com/rdagumampan/yuniql/issues. 2022-09-06T10:46:26.7150696Z 2022-09-06T10:46:26.7233107Z yuniql/error: The process 'F:\Softwares\vsts-agent-win-x64-2.165.0_work_tool\yuniql\0.0.0\x64\yuniql.exe' failed with exit code 1 2022-09-06T10:46:26.7272942Z ##[error]The process 'F:\Softwares\vsts-agent-win-x64-2.165.0_work_tool\yuniql\0.0.0\x64\yuniql.exe' failed with exit code 1