Catalogue check fails immediately after creation via ImportTableInfo

[rkm]

Describe the bug

After running ImportTableInfo with true to create a corresponding catalogue, running Check results in an error as the wrong database is being specified.

To Reproduce

1. Start a MySQL container for the Logging server and create a database:

   mysql_id=$(docker run --rm -d -p3306:3306 -e MYSQL_ROOT_PASSWORD='mySql1.' mysql:latest)
   # Wait for MySQL to start
   docker exec -it $mysql_id mysql -p'mySql1.' -e 'CREATE DATABASE rdmp_logging;'

2. Start a PostgreSQL server for the catalogue and create the example table:

   postgres_id=$(docker run --rm -d -p5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:14)
   # Wait for Postgres to start
   docker exec -it $postgres_id psql -U postgres -c 'CREATE DATABASE SMI;'
   docker exec -it $postgres_id psql -U postgres --db smi -c 'CREATE SCHEMA dicom; CREATE TABLE dicom."IO_StudyTable" ("StudyInstanceUID" varchar(255));'

3. Download rdmp CLI v8.1.5. Run:

   ./rdmp -- -f /dev/stdin --dir yaml <<EOF
   Commands:
     - CreateNewExternalDatabaseServer LiveLoggingServer_ID "DatabaseType:MySQL:Server=localhost;Uid=root;Pwd=mySql1.;Database=rdmp_logging"
     - CreateNewExternalDatabaseServer None "DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi"
     - ImportTableInfo "Table:IO_StudyTable:Schema:dicom:DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi" True
     # IDs may need adjusted
     - Check TableInfo:3
     - Check Catalogue:6
   EOF

Expected behavior

All checks pass.

RDMP Version

$ ./rdmp --version
2024-05-17 18:21:39.3017 INFO Dotnet Version:8.0.3 . 
2024-05-17 18:21:39.3170 INFO RDMP Version:8.1.5.0 . 
rdmp 8.1.5
2024-05-17 18:21:39.3256 INFO Exiting with code 0 . 

Error with Stack Trace

...

2024-05-21 16:00:06.7153 TRACE Running Command 'ExecuteCommandCheck' .
2024-05-21 16:00:06.7740 INFO Command Completed .
2024-05-21 16:00:06.7740 TRACE Running Command 'ExecuteCommandCheck' .
2024-05-21 16:00:06.7740 INFO Catalogue name IO_StudyTable follows naming conventions  .
2024-05-21 16:00:06.7919 INFO Found 1 ExtractionInformation(s), preparing to validate SQL with QueryBuilder .
2024-05-21 16:00:06.8127 INFO Query Builder assembled the following SQL:

SELECT

"smi"."dicom"."IO_StudyTable"."StudyInstanceUID"
FROM
"smi"."dicom"."IO_StudyTable"
fetch first 1 rows only .
2024-05-21 16:00:06.8158 ERROR Extraction SQL Checking failed for Catalogue IO_StudyTable make sure that you can access the underlying server under DataAccessContext.InternalDataProcessing and that the SQL generated runs correctly (see internal exception for details) . Npgsql.PostgresException (0x80004005): 0A000: cross-database references are not implemented: "smi.dicom.IO_StudyTable"

POSITION: 66
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Rdmp.Core.Curation.Data.Catalogue.Check(ICheckNotifier notifier) in D:\a\RDMP\RDMP\Rdmp.Core\Curation\Data\Catalogue.cs:line 925
  Exception data:
    Severity: ERROR
    SqlState: 0A000
    MessageText: cross-database references are not implemented: "smi.dicom.IO_StudyTable"
    Position: 66
    File: namespace.c
    Line: 257
    Routine: RangeVarGetRelidExtended
2024-05-21 16:00:06.8158 INFO Command Completed .
2024-05-21 16:00:06.8200 INFO Exiting with code -1 .

Database Engine

Postgres.

Additional context

N/A

[rkm]

@JFriel I've updated the steps above to more easily reproduce the error. The actual error turns out to be cross-database references are not implemented: "smi.dicom.IO_StudyTable" so I suspect the default postgres DB is being assumed somewhere again.

[JFriel]

@rkm Congratulations on finding a 7year old issue 🎉 Have a fix going through the review process now.
It won't make the 8.1.6 release as it's due to be released in a couple days, but will release a small v8.1.7 release with this fix shortly after

[rkm]

Just an update to say that I've tested with v8.1.6 and it gives a slightly different error message:

...

2024-06-15 19:39:02.8271 TRACE Running Command 'ExecuteCommandImportTableInfo' .
2024-06-15 19:39:02.9488 ERROR Error executing script.  Problem line was 'ImportTableInfo "Table:IO_StudyTable:Schema:dicom:DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi" True':**Could not find any columns in table IO_StudyTable .**
2024-06-15 19:39:02.9488 INFO Fatal error occurred so returning -1 . System.Exception: Error executing script.  Problem line was 'ImportTableInfo "Table:IO_StudyTable:Schema:dicom:DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi" True':Could not find any columns in table IO_StudyTable
 ---> System.Exception: Could not find any columns in table IO_StudyTable
   at FAnsi.Implementations.PostgreSql.PostgreSqlTableHelper.DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, String database)
   at FAnsi.Discovery.DiscoveredTable.DiscoverColumns(IManagedTransaction managedTransaction)                                                                  at Rdmp.Core.Curation.TableInfoImporter.DoImport(ITableInfo& tableInfoCreated, ColumnInfo[]& columnInfosCreated) in D:\a\RDMP\RDMP\Rdmp.Core\Curation\TableInfoImporter.cs:line 137
   at Rdmp.Core.CommandExecution.AtomicCommands.ExecuteCommandImportTableInfo.Execute() in D:\a\RDMP\RDMP\Rdmp.Core\CommandExecution\AtomicCommands\ExecuteCommandImportTableInfo.cs:line 57
   at Rdmp.Core.CommandExecution.CommandInvoker.ExecuteCommand(ConstructorInfo constructorInfo, CommandLineObjectPicker picker) in D:\a\RDMP\RDMP\Rdmp.Core\CommandExecution\CommandInvoker.cs:line 298
   at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.RunCommand(String command) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 104
   at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.RunScript(RdmpScript script, IRDMPPlatformRepositoryServiceLocator repositoryLocator) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 199
   --- End of inner exception stack trace ---
   at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.RunScript(RdmpScript script, IRDMPPlatformRepositoryServiceLocator repositoryLocator) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 204                                                                                            at Rdmp.Core.CommandLine.Runners.ExecuteCommandRunner.Run(IRDMPPlatformRepositoryServiceLocator repositoryLocator, IDataLoadEventListener listener, ICheckNotifier checkNotifier, GracefulCancellationToken token) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\Runners\ExecuteCommandRunner.cs:line 90
   at Rdmp.Core.CommandLine.RdmpCommandLineBootStrapper.Run(RDMPCommandLineOptions opts, IRunner explicitRunner, IRDMPPlatformRepositoryServiceLocator existingLocator) in D:\a\RDMP\RDMP\Rdmp.Core\CommandLine\RdmpCommandLineBootStrapper.cs:line 145
   at Rdmp.Core.Program.HandleArguments(String[] args, Logger logger) in D:\a\RDMP\RDMP\Tools\rdmp\Program.cs:line 83

[rkm]

Testing on the latest develop commit 87eba6 also produces the same error in my previous comment.

[JFriel]

@rkm the fix is currently in the 'release/8.1.7' branch, but will re-test with the updated error message you received in v8.1.6

[rkm]

Looks like this is still broken (with Could not find any columns in table IO_StudyTable) on the release/8.2.0 branch, but the postgres logging server seems to work now!

[rkm]

Actually, my bad. This works fully now on the release/8.2.0 branch!

A full reproduction:

postgres_id=$(docker run --rm -d -p5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:14)
docker exec -it $postgres_id psql -U postgres -c 'CREATE DATABASE rdmp_logging;'
docker exec -it $postgres_id psql -U postgres -c 'CREATE DATABASE smi;'
docker exec -it $postgres_id psql -U postgres --db smi -c 'CREATE SCHEMA dicom; CREATE TABLE dicom."IO_StudyTable" ("StudyInstanceUID" varchar(255)); INSERT INTO dicom."IO_StudyTable" VALUES ('"'"'1.2.3'"'"');'

rm -rf yaml
../../HicServices/RDMP/Tools/rdmp/bin/Debug/net8.0/rdmp -- -f /dev/stdin --dir yaml <<EOF
Commands:
  - CreateNewExternalDatabaseServer LiveLoggingServer_ID "DatabaseType:PostgreSql:Server=127.0.0.1;Uid=postgres;Database=postgres"
  - CreateNewExternalDatabaseServer None "DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi"
  - ImportTableInfo "Table:IO_StudyTable:Schema:dicom:DatabaseType:PostgreSql:Server=localhost;Uid=postgres;Database=smi" True
  # IDs may need adjusted
  - Check TableInfo:3
  - Check Catalogue:6
EOF