Problems when migrating with the sqldump procedure

[lucaato]

Describe the issue Hi, I've dumped my legacy db as described in https://docs.standardnotes.com/self-hosting/legacy-migration/. After doing so I've created my stack by following a similar procedure to the one described on the website. If I create a new db everything works fine, Instead if I place my dbdump.sql in the import folder I get this error in the auth container.

Service Versions:

• syncing-server-js:1.37.2
• auth:1.15.0
• api-gateway:1.21.3

Logs Logs of the auth container which seems to be the one that's causing the problem:

db (172.21.0.2:3306) open
db:3306 is up - executing command
cache (172.21.0.3:6379) open
cache:6379 is up - executing command
syncing-server-js (172.21.0.4:3000) open
syncing-server-js:3000 is up - executing command
Starting Web...
yarn run v1.22.5
$ node dist/bin/server.js
{"v":0,"level":30,"name":"newrelic_bootstrap","hostname":"0aaede4cac41","pid":31,"time":"2021-08-08T15:22:54.484Z","msg":"NEW_RELIC_NO_CONFIG_FILE set, deferring to environment variables."}
{"v":0,"level":30,"name":"newrelic_bootstrap","hostname":"0aaede4cac41","pid":31,"time":"2021-08-08T15:22:55.220Z","msg":"NEW_RELIC_NO_CONFIG_FILE set, deferring to environment variables."}
/var/www/node_modules/typeorm/error/QueryFailedError.js:11
        var _this = _super.call(this) || this;
                           ^

QueryFailedError: Column 'created_at' cannot be null
    at new QueryFailedError (/var/www/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.onResult (/var/www/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:216:45)
    at Query.execute (/var/www/node_modules/mysql2/lib/commands/command.js:30:14)
    at PoolConnection.handlePacket (/var/www/node_modules/mysql2/lib/connection.js:425:32)
    at PacketParser.onPacket (/var/www/node_modules/mysql2/lib/connection.js:75:12)
    at PacketParser.executeStart (/var/www/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/www/node_modules/mysql2/lib/connection.js:82:25)
    at Socket.emit (node:events:378:20)
    at Socket.EventEmitter.emit (node:domain:470:12)
    at addChunk (node:internal/streams/readable:313:12) {
  code: 'ER_BAD_NULL_ERROR',
  errno: 1048,
  sqlState: '23000',
  sqlMessage: "Column 'created_at' cannot be null",
  query: 'INSERT INTO `settings`(`uuid`, `name`, `value`, `server_encryption_version`, `created_at`, `updated_at`, `user_uuid`) VALUES (?, ?, ?, ?, ?, ?, ?)',
  parameters: [
    'stringParameter',
    'stringParameter',
    'stringParameter',
    0,
    ,
    ,
    'stringParameter',
  ]
}
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

I've replaced string parameters of the query with the string 'stringParameter' since I don't think that's the problem and also because I don't want to risk leaking relevant account data. 😁

To me it looks like there is an error in the not null constraints of the table or maybe the software is passing a null parameter where it shouldn't.

I hope someone can help me figure it out. Thanks in advance.

If needed I can give more info about the setup I'm using.

[caturday]

Seconding this. I had the same issue doing a clean install of a standalone instance, then backing up my notes from a previous instance via the webapp and restoring them.

[lucaato]

Hi, a simple workaround if you have just one user is to use the export feature in the standardnotes app (on windows is under Account>Data Backups). You have to start with exporting the notes from the app, after that create the new standardnotes stack with compose, recreate an empty account by registering and then reimport all the notes from the app. After that you will have to set the DISABLE_USER_REGISTRATION property to true if you don't want anyone else to register.

However this is not doable with more users.

[caturday]

I believe that's the exact process I went through. I was running an older version of the syncing server and had to do some work on the database to get the newest version to import from my dump. Eventually I got a mostly-working version and was able to use app.standardnotes.org to export via the process you described. I ran server.sh cleanup on the new instance, removed dbdump.sql from the import directory, and started a fresh server. I tried importing the exported file and it mostly worked but resulted in the same error described by the OP. I'll be interested to see whether this workaround works for OP...

On Thu, Aug 12, 2021 at 11:37 AM Luca @.***> wrote:

Hi, a simple workaround if you have just one user is to use the export feature in the standardnotes app (on windows is under Account>Data Backups). You have to start with exporting the notes from the app, after that create the new standardnotes stack with compose, recreate an empty account by registering and then reimport all the notes from the app. After that you will have to set the DISABLE_USER_REGISTRATION property to true if you don't want anyone else to register.

However this is not doable with more users.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/standardnotes/standalone/issues/38#issuecomment-897742007, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAKQOS3SXRECQCRGUZJL56LT4PTDFANCNFSM5BYTJG6A . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

[caturday]

Wait! Sorry. I didn't realize the workaround was suggested by the OP! I'm surprised that worked for you!

Maybe if I try exporting again from the imported data it'll work better?

On Fri, Aug 13, 2021 at 2:06 PM Zack Panitzke < @.***> wrote:

I believe that's the exact process I went through. I was running an older version of the syncing server and had to do some work on the database to get the newest version to import from my dump. Eventually I got a mostly-working version and was able to use app.standardnotes.org to export via the process you described. I ran server.sh cleanup on the new instance, removed dbdump.sql from the import directory, and started a fresh server. I tried importing the exported file and it mostly worked but resulted in the same error described by the OP. I'll be interested to see whether this workaround works for OP...

On Thu, Aug 12, 2021 at 11:37 AM Luca @.***> wrote:

Hi, a simple workaround if you have just one user is to use the export feature in the standardnotes app (on windows is under Account>Data Backups). You have to start with exporting the notes from the app, after that create the new standardnotes stack with compose, recreate an empty account by registering and then reimport all the notes from the app. After that you will have to set the DISABLE_USER_REGISTRATION property to true if you don't want anyone else to register.

However this is not doable with more users.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/standardnotes/standalone/issues/38#issuecomment-897742007, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAKQOS3SXRECQCRGUZJL56LT4PTDFANCNFSM5BYTJG6A . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

[karolsojko]

@luca-a it seems like the issue is cause by invalid SQL data in the dump.

seems like you have some odd records in the settings table.

This is the faulting query:

INSERT INTO `settings`(`uuid`, `name`, `value`, `server_encryption_version`, `created_at`, `updated_at`, `user_uuid`) VALUES (?, ?, ?, ?, ?, ?, ?)',
  parameters: [
    'stringParameter',
    'stringParameter',
    'stringParameter',
    0,
    ,
    ,
    'stringParameter',
  ]

Would it be possible for you to edit the sqldump file and remove that settings entry from there and trying to reimport the database backup?