'
12: from /var/www/discourse/script/import_scripts/base.rb:47:in `perform'
11: from script/import_scripts/vbulletin5.rb:46:in `execute'
10: from script/import_scripts/vbulletin5.rb:79:in `import_users'
9: from /var/www/discourse/script/import_scripts/base.rb:916:in `batches'
8: from /var/www/discourse/script/import_scripts/base.rb:916:in `loop'
7: from /var/www/discourse/script/import_scripts/base.rb:917:in `block in batches'
6: from script/import_scripts/vbulletin5.rb:80:in `block in import_users'
5: from script/import_scripts/vbulletin5.rb:723:in `mysql_query'
4: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:22:in `query'
3: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/mysql2-0.5.4/lib/mysql2/client.rb:147:in `query'
2: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/mysql2-0.5.4/lib/mysql2/client.rb:147:in `handle_interrupt'
1: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/mysql2-0.5.4/lib/mysql2/client.rb:148:in `block in query'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/mysql2-0.5.4/lib/mysql2/client.rb:148:in `_query': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN u.scheme='blowfish:10' THEN token (Mysql2::Error)
WHEN u.scheme='lega' at line 2
```
At this point I’m a little lost. Can someone help?
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 20, 2022, 5:29pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/2 "2022-12-20T17:29:25Z")
paging @Canapin as he’s been in “Migration Vietnam” and might know more :heart:
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 20, 2022, 5:50pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/3 "2022-12-20T17:50:47Z")
Turn out working on something 8+ hours straight is bad.
When adding `u.lastvisit` at the end of `SELECT u.userid, u.username, u.homepage, u.usertitle, u.usergroupid, u.joindate, u.email,` I forgot to add a `,` after that.
Sorry Canapin for the ping :frowning:
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 20, 2022, 6:31pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/4 "2022-12-20T18:31:02Z")
Alright, follow up issue. The guide I was following stated that the registration process could be restarted if it slows down.
But when restarted I get errors about users already existing in the postgres db.
Silly me, I went and deleted all users with `id > 1` in the db (leaving the discobot, system and admin basically) and restarting. That makes the import continue but all previously created users’s email are marked as “already used” somewhere.
What can I do to clean it up and shouldn’t this process instead skip insertion if there is already a username matching?
Edit: Alright, found out that I need to clear `users`, `email_tokens` and `user_emails`.
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [December 20, 2022, 7:30pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/5 "2022-12-20T19:30:59Z")
> [@Crius](#):
>
> paging @Canapin as he’s been in “Migration Vietnam”
Unfortunately, I didn’t keep track of what I’ve been tweaking in my previous imports so I have little knowledge. I now have a personal Discourse instance where I write this kind of stuff… I should have done that before!
I’m more skilled in import stuff when I’m actually working on one.
As for destroying users, you may have wanted to use `UserDestroyer` through the rails console:
> [@Remove all users except admins or a few select users whichever is easier](https://meta.discourse.org/t/remove-all-users-except-admins-or-a-few-select-users-whichever-is-easier/249199/5):
>
> UserDestroyer is what you should use. It will delete other data associated with the user. Don’t know why you’d get that error. You might restore your backup to before you started the delete and try the destroyer instead.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 20, 2022, 8:22pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/6 "2022-12-20T20:22:56Z")
> [@Canapin](#):
>
> As for destroying users, you may have wanted to use `UserDestroyer`
Great stuff, I’ll keep it in mind for the “real” migration. For now i’m just doing a test run of the whole process while writing a runbook :slight_smile:
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 20, 2022, 10:28pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/7 "2022-12-20T22:28:48Z")
> [@Crius](#):
>
> me, I went and deleted all users with `id > 1` in the db (leaving the discobot, system and admin basically) and restartin
Why did you do that? Unless you changed something in the script that would import those differently then you should just restart and have it import those out didn’t import already.
If you do need to start over, it’s much easier to restore a backup or drop and create a new database.
It should find the import ids in the UserCustomFields table. I’m not quite sure how you’d get that error.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 20, 2022, 11:02pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/8 "2022-12-20T23:02:50Z")
> [@pfaffman](#):
>
> Why did you do that?
The changes are the one I listed. I am as surprised as you are but the script would not progress and simply error and stop.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 10:06am UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/9 "2022-12-21T10:06:19Z")
Is there any way to speed up the import?
I’ve over 90k users in this community and the speed of import degrade over time for some reason and I can’t imagine why.
It run all night and just for the users we are at `25123 / 95635 ( 26.3%) [42 items/min]`
There are several order of magnitude more posts. How long should I expect a migration to happen? Days? weeks?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 21, 2022, 11:40am UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/11 "2022-12-21T11:40:56Z")
How much ram? That’s likely the issue. You can stop and restart.
I’ve had them take weeks. That’s why the bulk importers exist.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 1:54pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/12 "2022-12-21T13:54:12Z")
> [@pfaffman](#):
>
> That’s why the bulk importers exist.
It has just 2GB ram. It’s a test machine. I could run it locally instead of a vm (16GB of ram would be that much better?) and then package everything and upload eventually I guess.
Can you elaborate on bulk importers? first time I hear about it and it definitely should have poppup up when I was searching for “migrate vbulletin to discourse” on google :angry:
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 21, 2022, 2:13pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/13 "2022-12-21T14:13:27Z")
>
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 2:22pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/14 "2022-12-21T14:22:57Z")
Can I run this even if the other importer already processed some users or should I clean up?
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 2:33pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/15 "2022-12-21T14:33:11Z")
Went ahead and tried, at worst it won’t work. I’m getting spammed with
```plaintext
ERROR: no implicit conversion of nil into String
/var/www/discourse/script/bulk_import/base.rb:861:in `encode'
/var/www/discourse/script/bulk_import/base.rb:861:in `normalize_charset'
/var/www/discourse/script/bulk_import/base.rb:856:in `normalize_text'
script/bulk_import/vbulletin5.rb:123:in `block in import_users'
/var/www/discourse/script/bulk_import/base.rb:725:in `block (2 levels) in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/script/bulk_import/base.rb:723:in `block in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/pg-1.4.5/lib/pg/connection.rb:196:in `copy_data'
/var/www/discourse/script/bulk_import/base.rb:722:in `create_records'
/var/www/discourse/script/bulk_import/base.rb:340:in `create_users'
script/bulk_import/vbulletin5.rb:120:in `import_users'
script/bulk_import/vbulletin5.rb:63:in `execute'
/var/www/discourse/script/bulk_import/base.rb:100:in `run'
script/bulk_import/vbulletin5.rb:781:in `'
```
I assume I should clean up users and groups which are the only things the other importer created/started to create.
Before I go mess in the db again, is there some ruby command I can run that will take care of that in a clean way?
For this time I’ll just go ahead and destroy and recreate the discourse installation. Thanks heaven for Docker.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 3:36pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/16 "2022-12-21T15:36:47Z")
Nope, clean installation, the script still fail with this generic
```plaintext
ERROR: no implicit conversion of nil into String
/var/www/discourse/script/bulk_import/base.rb:861:in `encode'
/var/www/discourse/script/bulk_import/base.rb:861:in `normalize_charset'
/var/www/discourse/script/bulk_import/base.rb:856:in `normalize_text'
script/bulk_import/vbulletin5.rb:123:in `block in import_users'
/var/www/discourse/script/bulk_import/base.rb:725:in `block (2 levels) in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/script/bulk_import/base.rb:723:in `block in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/pg-1.4.5/lib/pg/connection.rb:196:in `copy_data'
/var/www/discourse/script/bulk_import/base.rb:722:in `create_records'
/var/www/discourse/script/bulk_import/base.rb:340:in `create_users'
script/bulk_import/vbulletin5.rb:120:in `import_users'
script/bulk_import/vbulletin5.rb:63:in `execute'
/var/www/discourse/script/bulk_import/base.rb:100:in `run'
script/bulk_import/vbulletin5.rb:781:in `'
```
Is there any way to debug this so that at least I can understand which value is being `nil` and is expected to be something else?
Ok, error seems to be related to the encoding.
In the `bulk_import/vbulletin5.rb` I can specify the encoding (in our case, `UTF8mb4` but in the `base.rb` file it doesn’t seems to map to anything in the charset map
>
---
### Author:  [@leonardo](https://meta.discourse.org/u/leonardo)
#### Post date: [December 21, 2022, 3:50pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/17 "2022-12-21T15:50:37Z")
Hi!
Some general tips:
1. if you do changes to the script, it is generally advisable to start from scratch or at least a known good point. You can most easily do that by restoring a backup taken just before you run the migration like @pfaffman said.
2. bulk importers will generally take a lot less time, but this particular one has the potential to consume YUGE amounts of RAM because it caches stuff in memory. For one particular vBulletin bulk migration I did from a 2GB SQL file uncompressed, the process needed 22GB RAM (double checked, not a typo)
3. if you do changes to the script, I suggest you create a test version of the input with say 100 or 1000 records for each table (but watch out for referential integrity - ie. don’t truncate tables referenced by other tables). Testing changes with a \>8hs process will erode your sanity very quickly
A more specific tip about stack traces: look for lines mentioning the specific file you ran. In this case yes it’s an encoding issue, but more relevant is the fact that it’s about usernames:
>
You said you had to sanitize usernames so I would double check that you encoded them as expected by the script.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 21, 2022, 3:52pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/18 "2022-12-21T15:52:39Z")
> [@Crius](#):
>
> I’ll just go ahead and destroy and recreate the discourse installation.
You can just drop , create, migrate the database rather than recreate Discourse. It’s a bit tricky, though, as you have to
```
sv stop unicorn
```
and then
```
rake db:drop db:create db:migrate
```
It’ll complain and tell you to set and ENV variable that you need to put on the line before the rake task.
You can also restore a backup, which can be more convenient.
FWIW, I don’t think I’ve ever used a bulk migration script.
---
### Author:  [@Crius](https://meta.discourse.org/u/Crius)
#### Post date: [December 21, 2022, 3:54pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/19 "2022-12-21T15:54:27Z")
That’s even weirder as the usernames have been all sanitized to follow discorse guidelines, basically they have all been changed to be letters, numbers or \_, nothing else.
In any case, changing the charset from `utf8mb4` to `utf8` as it was the default made it go through but then I’m getting errors for invalid emails
```plaintext
ERROR: can't modify frozen String: "24ef401b30f5161e5a0bb27ec49ed921@email.invalid"
/var/www/discourse/script/bulk_import/base.rb:457:in `downcase!'
/var/www/discourse/script/bulk_import/base.rb:457:in `process_user_email'
/var/www/discourse/script/bulk_import/base.rb:726:in `block (2 levels) in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/mysql2/alias_method.rb:8:in `each'
/var/www/discourse/script/bulk_import/base.rb:723:in `block in create_records'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/pg-1.4.5/lib/pg/connection.rb:196:in `copy_data'
/var/www/discourse/script/bulk_import/base.rb:722:in `create_records'
/var/www/discourse/script/bulk_import/base.rb:351:in `create_user_emails'
script/bulk_import/vbulletin5.rb:151:in `import_user_emails'
script/bulk_import/vbulletin5.rb:66:in `execute'
/var/www/discourse/script/bulk_import/base.rb:100:in `run'
script/bulk_import/vbulletin5.rb:781:in `'
```
Going now to discover what is this about as the “non-bulk” imported was detecting some malformed email but replacing them automatically.
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [December 21, 2022, 4:03pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/20 "2022-12-21T16:03:04Z")
> [@Crius](#):
>
> For this time I’ll just go ahead and destroy and recreate the discourse installation.
After you have done so, just create a backup of your empty pristine Discourse installation.
You can always restore that very quickly and start fresh again.
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [December 21, 2022, 4:28pm UTC](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495/21 "2022-12-21T16:28:19Z")
To add to that, if you have trouble with for instance the import of posts, you could also exit the script after the user import has completed satisfactory and create another backup. Then you can restart the script and you can resume at the moment where you have already imported the users.
[Next page](https://meta.discourse.org/t/migrating-vbulletin-5-database-import-script-errors/249495.md?page=2)