SQLite to MySQL migration [Sep 16]

Discord-Discussions
1

:raising_hand_man: suroh asked

Hello, I am hoping someone can help me out - I’m trying to upgrade an existing installation from sqlite to mysql and I have successfully dumped the contents into mysql, and nocodb is loading, but when I try and login I’m getting a refresh key error being too long. Has anyone tried changing their database post install?

2

:person_tipping_hand: o1lab replied

Which tool did you use to load MySQL from SQLite

3

:raising_hand_man: suroh replied

:confounded: I used an online conversion in the end : Convert SQLite to MySQL online - RebaseData

4

:raising_hand_man: suroh replied

then I did a dump on an empty database sudo mysql nocodb < dump.sql

5

:person_tipping_hand: o1lab replied

will help if u can paste the error in your docker logs

6

:raising_hand_man: suroh replied

Sure I’ll get them to you. I’m using a node install not docker - I guess I can get the logs from the stdout?

7

:person_tipping_hand: o1lab replied

Yep

8

:raising_hand_man: suroh replied

```

Error: update nc_users_v2 set email = ‘mafra@hr.nl’, refresh_token = ‘9c8c223f43161bb38106602e4a14323ac00f80d62c4d9acaa368ab49284e595943005486801163fe’, token_version = ‘48d135e72dedaf34ed76ecfd9ce2ed2281d3f72af50ead22b00bc7352adbba753d7596c3339ef7c5’, updated_at =

at Packet.asError (/var/www/nocodb/node_modules/mysql2/lib/packets/packet.js:728:17)

at Query.execute (/var/www/nocodb/node_modules/mysql2/lib/commands/command.js:29:26)

at Connection.handlePacket (/var/www/nocodb/node_modules/mysql2/lib/connection.js:456:32)

at PacketParser.onPacket (/var/www/nocodb/node_modules/mysql2/lib/connection.js:85:12)

at PacketParser.executeStart (/var/www/nocodb/node_modules/mysql2/lib/packet_parser.js:75:16)

at Socket. (/var/www/nocodb/node_modules/mysql2/lib/connection.js:92:25)

at Socket.emit (node:events:527:28)

at Socket.emit (node:domain:537:15)

at addChunk (node:internal/streams/readable:324:12)

at readableAddChunk (node:internal/streams/readable:297:9) {

code: ‘ER_DATA_TOO_LONG’,

errno: 1406,

sqlState: ‘22001’,

sqlMessage: “Data too long for column ‘updated_at’ at row 1”,

sql: "update nc_users_v2 set email = ‘mafra@hr.nl’, refresh_token = ‘9c8c223f43161bb38106602e4a14323ac00f80d62c4d9acaa368ab49284e595943005486801163fe’, token_version = ‘48d135e72dedaf34ed76ecfd9ce2ed2281d3f72af50ead22b00bc7352adbba753d7596c3339ef7c5’, updated_at

}

```

9

:raising_hand_man: suroh replied

above is the dump from the stdout when I try and login, and this is the error message in the UI :

```

update nc_users_v2 set email = ‘mafra@hr.nl’, refresh_token = ‘9c8c223f43161bb38106602e4a14323ac00f80d62c4d9acaa368ab49284e595943005486801163fe’, token_version = ‘48d135e72dedaf34ed76ecfd9ce2ed2281d3f72af50ead22b00bc7352adbba753d7596c3339ef7c5’, updated_at = CURRENT_TIMESTAMP where id = ‘us_lpd97kncojpgus’ - Data too long for column ‘updated_at’ at row 1

```

10

:person_tipping_hand: o1lab replied

its complaining for data getting updated as too long

11

:person_tipping_hand: o1lab replied

check if updated_at is fine for that table

12

:raising_hand_man: suroh replied

I’m not super db savvy - when you say fine, I guess I should be checking the max-chars for the field, and the type?

13

:person_tipping_hand: o1lab replied

yep that is correct

14

:person_tipping_hand: o1lab replied

I wonder if the updated_at field is datetime or not

15

:raising_hand_man: suroh replied

hm yeah looking at the schema it very weird, everything is varchar and many of them are varchar(0)… .

16

:raising_hand_man: suroh replied

maybe the db conversion tool was not the best option? do you have a suggestion on how to go from sqlite to mysql?

17

Autogenerated from discord

Join NocoDB’s community

Discord • Twitter • Reddit • Docs