Restore statement fails with request error

not-night-but commented 1 year ago

Running a db restore fails with a request error with no error message

Expected behaviour:

The database restore should execute (especially since database backup works fine).

Actual behaviour:

RESTORE DATABASE test FROM DISK = "/var/opt/mssql/data/2023-07-22_021403.bak" WITH REPLACE;

The above query fails with this error: https://dpaste.com/GTRTTUQ4S

stack trace

``` 18:08:29.034 (sql-server) › RUNNING {query: 'RESTORE DATABASE test FROM DISK = "/var/opt/mssql/data/2023-07-22_021403.bak" WITH REPLACE;', multiple: true} BaseCommandClient.ts?c8ca:359 RequestError at Request.eval [as userCallback] (request.js?d185:447:1) at Request.callback (request.js?6c7c:205:1) at Parser.onEndOfMessage (connection.js?244d:2915:1) at Object.onceWrapper (node:events:509:28) at Parser.emit (node:events:390:28) at Readable.eval (token-stream-parser.js?80fa:32:1) at Readable.emit (node:events:390:28) at endReadableNT (node:internal/streams/readable:1343:12) at processTicksAndRejections (node:internal/process/task_queues:83:21) eval @ BaseCommandClient.ts?c8ca:359 processTicksAndRejections @ node:internal/process/task_queues:96 Promise.catch (async) eval @ BaseCommandClient.ts?c8ca:357 _callee7$ @ BaseCommandClient.ts?c8ca:353 tryCatch @ regeneratorRuntime.js?3a6d:86 eval @ regeneratorRuntime.js?3a6d:66 eval @ regeneratorRuntime.js?3a6d:117 asyncGeneratorStep @ asyncToGenerator.js?8cf9:3 _next @ asyncToGenerator.js?8cf9:25 eval @ asyncToGenerator.js?8cf9:32 eval @ asyncToGenerator.js?8cf9:21 _runCommand @ BaseCommandClient.ts?c8ca:9 _callee3$ @ BaseCommandClient.ts?c8ca:299 tryCatch @ regeneratorRuntime.js?3a6d:86 eval @ regeneratorRuntime.js?3a6d:66 eval @ regeneratorRuntime.js?3a6d:117 asyncGeneratorStep @ asyncToGenerator.js?8cf9:3 _next @ asyncToGenerator.js?8cf9:25 eval @ asyncToGenerator.js?8cf9:32 eval @ asyncToGenerator.js?8cf9:21 runCommand @ BaseCommandClient.ts?c8ca:9 _callee$ @ BackupModule.ts?02b2:127 tryCatch @ regeneratorRuntime.js?3a6d:86 eval @ regeneratorRuntime.js?3a6d:66 eval @ regeneratorRuntime.js?3a6d:117 asyncGeneratorStep @ asyncToGenerator.js?8cf9:3 _next @ asyncToGenerator.js?8cf9:25 eval @ asyncToGenerator.js?8cf9:32 eval @ asyncToGenerator.js?8cf9:21 execute @ BackupModule.ts?02b2:127 wrappedActionHandler @ vuex.esm.js?ab2e:851 dispatch @ vuex.esm.js?ab2e:516 boundDispatch @ vuex.esm.js?ab2e:406 runBackup @ TabDatabaseBackup.vue?9835:95 invokeWithErrorHandling @ vue.runtime.esm.js?8eb8:2987 invoker @ vue.runtime.esm.js?8eb8:1785 invokeWithErrorHandling @ vue.runtime.esm.js?8eb8:2987 Vue.$emit @ vue.runtime.esm.js?8eb8:3685 _callee2$ @ Stepper.vue?3bb8:62 tryCatch @ regeneratorRuntime.js?3a6d:86 eval @ regeneratorRuntime.js?3a6d:66 eval @ regeneratorRuntime.js?3a6d:117 asyncGeneratorStep @ asyncToGenerator.js?8cf9:3 _next @ asyncToGenerator.js?8cf9:25 eval @ asyncToGenerator.js?8cf9:32 eval @ asyncToGenerator.js?8cf9:21 nextStep @ Stepper.vue?3bb8:56 invokeWithErrorHandling @ vue.runtime.esm.js?8eb8:2987 invoker @ vue.runtime.esm.js?8eb8:1785 original_1._wrapper @ vue.runtime.esm.js?8eb8:7428 ```

The exact same query runs fine when using sqlcmd

Configuration:

requestTimeout: Infinity,
pool: {
    max: 10
}

Let me know if you need more info, or if I'm just being dumb and have missed something obvious. If the latter is the case, so sorry for wasting your time.

Software versions

NodeJS: 16.19.1
node-mssql: ^8.0.2
SQL Server: Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64) May 26 2023 12:52:08 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS)

dhensby commented 1 year ago

Are you using the tedious driver?
The error you've shared doesn't appear to show all the data I'd expect to see (there's no actual database error displayed) - usually the database will respond with some kind of error message. Are you able to see any more errors in your console/logs?
The version of node-mssql you're using is out of date, have you tried v9?

not-night-but commented 1 year ago

@dhensby Yeah I believe we're using the tedious driver (not currently at my computer, can give more detailed information tomorrow).

Unfortunately there aren't any other errors or/logs, which is why I found this issue so peculiar. It doesn't actually seem to emit any error other than the stack trace.

I'll look into updating node-mssql tomorrow and get back to you

dhensby commented 1 year ago

OK, if you could also supply some example code of how the query is being executed, that'd be helpful too. 👍

not-night-but commented 1 year ago

@dhensby Okay! So I looked into upgrading node-mssql, but webpack is yelling at me about module parse errors.

Here's how we're executing the query:

const runQuery = async (connection) => {
    const request = connection.request();
    request.arrayRowMode = true;
    const data = await request.query(query);
    const rowsAffected = _.sum(data.rowsAffected);
    return { request, data, rowsAffected };
};

The query is captured from the upper scope. I have also tried this with arrayRowMode off, as well as with batch. It always almost instantaneously fails on the request.query line.

dhensby commented 1 year ago

Ok, without a bit more error detail we're a bit stuck.

I know there are no inherent problems with restore backup commands with the library as I use it in my own projects.

To debug further you can try to use the DEBUG env var, or you can attach a debugger to the error handler, or even add listeners to the debug events on tedious itself.

not-night-but commented 1 year ago

@dhensby Are you aware of any permissions I may have to enable on the db to allow the app to execute a restore? I am logged in as SA so I don't think that's the issue, but thought I'd ask. If you've got it working then it's got to be something I'm doing wrong (I hope lol)

dhensby commented 1 year ago

The most important thing is to find out what the error message is.

The sa user should be all that's needed.

The only thing I can think of is if you're trying to restore to a database that the connection is attached to. Are you connecting with a database name in the connection (and is it the same one you're trying to restore)?

not-night-but commented 1 year ago

@dhensby lmao well that would be it 🤦. Do I have to drop the database entirely or can I just use master and then restore? I'm so sorry to trouble you with this, kinda pissed that it seems like that's not in the SQL server docs anywhere 😒. Either that or I'm blind in which case double apologies.

dhensby commented 1 year ago

Glad you got to the bottom of it!

Either you can create a connection to the database server without a database specified (I think that's best) or you can try to switch database.

The risk with switching is that your database pool is likely to get in an inconsistent state with some connections attached to one database and some connected to another.

not-night-but commented 1 year ago

@dhensby ~~did some more testing. Neither of the above solutions actually work unfortunately. Connecting with no database specified or switching the database results in the same error.~~ Ignore me, it was multiple issues. It is fixed now

tediousjs / node-mssql