Conda Feed to SMB Share

[scott.wright_8356]

Hi Support,

We have an issue creating a Conda feed to an SMB share. The SQL Lite file cannot be created there; we are running a High Availability structure with no issues with NuGet, Npm, Python, and Maven feeds. With the Conda feed, we get

Is there a particular port that ProGet requires when communicating with SMB share for SQL Lite? We can get the feed working with local storage on one of the nodes but not with the shared storage. When we put the SQL lite file there manually and refresh the feed, it is deleted. It is like clearing the file and then attempting to write it there using a different process than the other feeds.

Regards Scott

[atripp]

Hi @scott-wright_8356 ,

ProGet doesn't "directly" work with SMB shares, but instead uses the File System API. As such there's no special configuration or ports required - that's all handled at the operating system level. And it sounds like it's working fine, since other feeds are working.

As for SQL Lite, a few feed types (including conda, debian, rpm, etc.) will use a SQL Lite to "cache" connector information. But a SQL Lite database is just a file stored on disk, as you've noticed - if ProGet can read/write files on an SMB share, there is no reason it wouldn't work.

We haven't had issues in HA configurations with SQL Lite connectors, but we have seen this issue come twice before. One time was a ticket, and it was some "malware" that was blocking the SQL Lite file for security reasons. A more recent thread is here: https://forums.inedo.com/topic/4128

I wonder if you can follow some of the same troubleshooting steps? In the end, this seemed to work:

I was finally able to try this with a mounted network drive (using SysInternals), and that works

We don't really have an alternate solution at this time, but if this continues to be an issue we can definitely consider a node-by-node / local file system configuration.

Thanks,
Alana

[scott.wright_8356]

Hi Alana,

We wrote a program to use the SQL Lite library and ran it from the ProGet Node.

This is from the ProGet node server to our ProGet SMB share, and there are no issues. It is just the ProGet application that is having a problem.

[atripp]

Hi @scott-wright_8356 ,

Thanks; it's hard to say what the issue is, and unfortunately we don't really have any good troubleshooting tools here. That error is coming from within SQL Lite, and we can't reproduce it when we do a SMB share.

I wonder if you can follow some of the same troubleshooting steps? Specifcally like using ProcMon or mounting the network drive with SysInternals?

We just don't really have any means to troubleshoot this, so we'd need to go back to the drawing board to figure out why it doesn't work for you. You're welcome to keep exploring C#, but let me share the code that's closer to ProGet.

Here is the code that ProGet is running. It's kind of similar, but a little different. Note we are using System.Data.SQLite.Core-1.0.118 and .NET6.

private static SQLiteConnection OpenOrCreateDatabase(string fileName, out Dictionary<string, string> config)
{
    var str = new SQLiteConnectionStringBuilder
    {
        DataSource = fileName,
        FailIfMissing = false
    }.ToString();

    if (FileEx.Exists(fileName))
    {
        var conn = new SQLiteConnection(str);
        try
        {
            conn.Open();

            config = ReadConfig(conn);
            if (config.TryGetValue("SchemaVersion", out var versionStr))
            {
                int version = int.Parse(versionStr);
                if (version == 1)
                {
                    using var cmd2 = new SQLiteCommand(GetScript("UpdateSchemaToV2"), conn);
                    cmd2.ExecuteNonQuery();
                    return conn;
                }
                else if (version == 2)
                {
                    return conn;
                }
            }
        }
        catch
        {
        }

        conn?.Dispose();
        FileEx.Delete(fileName);
    }
    else
    {
        DirectoryEx.Create(PathEx.GetDirectoryName(fileName));
    }

    var conn2 = new SQLiteConnection(str);
    conn2.Open();

    using var cmd = new SQLiteCommand(GetScript("CreateSchema"), conn2);
    cmd.ExecuteNonQuery();

    cmd.CommandText = GetScript("UpdateSchemaToV2");
    cmd.ExecuteNonQuery();

    config = ReadConfig(conn2);

    return conn2;
}
private static Dictionary<string, string> ReadConfig(SQLiteConnection conn)
{
    using var cmd = new SQLiteCommand("SELECT Key_Name, Value_Text FROM OtherData", conn);
    using var reader = cmd.ExecuteReader();

    var res = new Dictionary<string, string>();

    while (reader.Read())
    {
        var key = reader.GetString(0);
        var value = reader.IsDBNull(1) ? null : reader.GetString(1);
        res[key] = value;
    }

    return res;
}

Here is the CreateSchema script:

CREATE TABLE OtherData
(
    Key_Name TEXT PRIMARY KEY NOT NULL,
    Value_Text TEXT
);

INSERT INTO OtherData (Key_Name, Value_Text) VALUES ('SchemaVersion', '1');

CREATE TABLE FetchInfo
(
    Subdir_Name TEXT PRIMARY KEY NOT NULL,
    Modified_Date INTEGER,
    Fetch_Date INTEGER NOT NULL
);

CREATE TABLE ChannelData
(
    Package_Name TEXT PRIMARY KEY NOT NULL,
    Flags_Value INTEGER NOT NULL,
    Description_Text TEXT,
    DevUrl_Text TEXT,
    DocSourceUrl_Text TEXT,
    DocUrl_Text TEXT,
    Home_Text TEXT,
    Icon_Hash TEXT,
    Icon_Url TEXT,
    License_Text TEXT,
    RunExports_Json BLOB,
    SourceGitUrl_Text TEXT,
    SourceUrl_Text TEXT,
    Summary_Text TEXT,
    Timestamp_Value INTEGER,
    Version_Text TEXT NOT NULL,
    Subdirs_Csv TEXT NOT NULL
);

CREATE TABLE RepoData
(
    Package_Name TEXT NOT NULL,
    Subdir_Name TEXT NOT NULL,
    Version_Text TEXT NOT NULL,
    Build_Text TEXT NOT NULL,
    ArchiveType_Code INTEGER NOT NULL,
    Build_Number INTEGER NOT NULL,
    License_Text TEXT,
    LicenseFamily_Text TEXT,
    MD5_Hash BLOB,
    SHA256_Hash BLOB,
    Package_Size INTEGER NOT NULL,
    Timestamp_Value INTEGER,
    Dependencies_Json BLOB,
    AppEntry_Text TEXT,
    AppType_Text TEXT,
    Type_Text TEXT,
    Summary_Text TEXT,
    Icon_Hash BLOB,

    PRIMARY KEY (Package_Name, Version_Text, Subdir_Name, Build_Text, ArchiveType_Code)
);

Here is the UpdateSchemaToV2 script:

UPDATE OtherData
   SET Value_Text = '2'
 WHERE Key_Name = 'SchemaVersion';

ALTER TABLE RepoData
  ADD Constraints_Json BLOB;

From there, it's just a bunch of inserts into those tables. The "unable to open database file" error would probably be occurring in the middle of those, but it's hard to say.

The race condition occurs when multiple threads run OpenOrCreateDatabase at same time. It's rare as I mentioned.

Alana

[scott.wright_8356]

Hi Alana,

We have written many simple programs using the same SQL Lite package as above. We cannot find a scenario where SQL Lite works over an SMB share, and we have been able to find content that says not to use it over the network. We were able to confirm that the create database option works fine. It is the opening that fails every time. What happens with the OpenOrCreateDatabase is it rolls back the file created when the open fails, so you end up with nothing.

https://www.sqlite.org/useovernet.html

https://stackoverflow.com/questions/788517/sqlite-over-a-network-share

We are using the IIS setup. Has this been tested with IIS and SMB share for Conda? I assume the index information for the package repository is what is going into the SQL Lite DB? Would it not be faster to store this information locally on each node? Does the SQL Lite DB need to be shared?

Is there an option to set the Conda/Debian package storage on the network share and the SQL Lite DB locally for each node?

Regards Scott

[scott.wright_8356]

Hi Alana,

What we did find to get our small applications working was the following post. This mentioned that when opening the file the path should start with four backslashes. "\\"

https://stackoverflow.com/questions/41330660/sqlite-database-file-cant-be-opened-when-placed-in-network-folder

https://stackoverflow.com/questions/17303076/sqlite-cannot-open-network-file-programmatically-even-though-worked-before

This worked for our small applications and allowed the SQL lite package to work over the SMB share.

Regards Scott

[rhessinger]

Hi @scott-wright_8356,

Thanks for the additional information. We were able to recreate it and have a fix pending, PG-2639, that will be released this Friday in ProGet 2024.2.

[scott.wright_8356]

@rhessinger Wonderful, thank you very much and credit to Pawel Ostrowski who also did a lot of investigation on this