Question "Migrating" from 1.7.20 to 2.2.1

I'm in the final steps of "migrating" my much neglected 1.7.20 server to 2.2.1.  My understanding is that the database format is largely the same between versions, but I'm running into and error when I try to connect the new 2.2.1 installation to my 1.7.20 mysql database.

Steps Performed:

1. On a newly provisioned Linux VM with Apache/MySQL installed, I installed web trees 2.2.1 as a fresh installation but pointed to my old 1.7.20 database.
2. I got the below error when I connected to webtrees.
3. I then created a new "webtrees2" database owned by the same webtrees dbuser. 
4. Walked through the "new site config wizard" and imported the GED file from my old site and copied over my "data/media" directory
5. The tree was back up and functioning!  BUT, all my site specific configurations were missing (news, users, etc.)
6. I change the webtrees/data/config.ini.php file and pointed the site to the old 1.7.20 database....got the same error as originally (see below)

Question:

1. From the error message, it looks like the database structure may have changed betwen 1.7.20 and 2.2.1.  Can someone confirm this.  Specifically, that I can't just point 2.2.1 at a 1.7.20 database.
2. Does anyone know how to dump just the users and news from the 1.7.20 database and then load that into 2.2.1 database?  Are there any format changes that are needed?

I'm really hoping to not have to hand migrate all 150+ users from one install to the other and then have them all reset their passwords.

Error Message received in browser when 2.2.1 install is connecting to 1.7.20 database:

PDOException: SQLSTATE[HY000]: General error: 3780 Referencing column 'module_name' and referenced column 'module_name' in foreign key constraint 'wt_module_privacy_module_name_foreign' are incompatible. in /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php:571
Stack trace:
#0 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(571): PDOStatement->execute()
#1 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(812): Illuminate\Database\Connection->Illuminate\Database\{closure}()
#2 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#3 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(560): Illuminate\Database\Connection->run()
#4 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Blueprint.php(117): Illuminate\Database\Connection->statement()
#5 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#6 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Builder.php(418): Illuminate\Database\Schema\Builder->build()
#7 /var/www/familytree.latoga.com/app/Schema/Migration42.php(56): Illuminate\Database\Schema\Builder->create()
#8 /var/www/familytree.latoga.com/app/Services/MigrationService.php(61): Fisharebest\Webtrees\Schema\Migration42->upgrade()
#9 /var/www/familytree.latoga.com/app/Http/Middleware/UpdateDatabaseSchema.php(55): Fisharebest\Webtrees\Services\MigrationService->updateSchema()
#10 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UpdateDatabaseSchema->process()
#11 /var/www/familytree.latoga.com/app/Http/Middleware/UseDatabase.php(60): Middleland\Dispatcher->handle()
#12 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDatabase->process()
#13 /var/www/familytree.latoga.com/app/Http/Middleware/BadBotBlocker.php(308): Middleland\Dispatcher->handle()
#14 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BadBotBlocker->process()
#15 /var/www/familytree.latoga.com/app/Http/Middleware/CompressResponse.php(71): Middleland\Dispatcher->handle()
#16 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CompressResponse->process()
#17 /var/www/familytree.latoga.com/app/Http/Middleware/ContentLength.php(40): Middleland\Dispatcher->handle()
#18 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ContentLength->process()
#19 /var/www/familytree.latoga.com/vendor/middlewares/client-ip/src/ClientIp.php(65): Middleland\Dispatcher->handle()
#20 /var/www/familytree.latoga.com/app/Http/Middleware/ClientIp.php(47): Middlewares\ClientIp->process()
#21 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ClientIp->process()
#22 /var/www/familytree.latoga.com/app/Http/Middleware/PublicFiles.php(62): Middleland\Dispatcher->handle()
#23 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\PublicFiles->process()
#24 /var/www/familytree.latoga.com/app/Http/Middleware/HandleExceptions.php(89): Middleland\Dispatcher->handle()
#25 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\HandleExceptions->process()
#26 /var/www/familytree.latoga.com/app/Http/Middleware/SecurityHeaders.php(49): Middleland\Dispatcher->handle()
#27 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\SecurityHeaders->process()
#28 /var/www/familytree.latoga.com/app/Http/Middleware/BaseUrl.php(73): Middleland\Dispatcher->handle()
#29 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BaseUrl->process()
#30 /var/www/familytree.latoga.com/app/Http/Middleware/ReadConfigIni.php(68): Middleland\Dispatcher->handle()
#31 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ReadConfigIni->process()
#32 /var/www/familytree.latoga.com/app/Http/Middleware/EmitResponse.php(57): Middleland\Dispatcher->handle()
#33 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\EmitResponse->process()
#34 /var/www/familytree.latoga.com/app/Http/Middleware/ErrorHandler.php(38): Middleland\Dispatcher->handle()
#35 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ErrorHandler->process()
#36 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle()
#37 /var/www/familytree.latoga.com/app/Webtrees.php(280): Middleland\Dispatcher->dispatch()
#38 /var/www/familytree.latoga.com/app/Webtrees.php(251): Fisharebest\Webtrees\Webtrees->httpRequest()
#39 /var/www/familytree.latoga.com/index.php(24): Fisharebest\Webtrees\Webtrees->run()
#40 {main}

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 3780 Referencing column 'module_name' and referenced column 'module_name' in foreign key constraint 'wt_module_privacy_module_name_foreign' are incompatible. (Connection: default, SQL: alter table `wt_module_privacy` add constraint `wt_module_privacy_module_name_foreign` foreign key (`module_name`) references `wt_module` (`module_name`) on delete cascade) in /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php:825
Stack trace:
#0 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#1 /var/www/familytree.latoga.com/vendor/illuminate/database/Connection.php(560): Illuminate\Database\Connection->run()
#2 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Blueprint.php(117): Illuminate\Database\Connection->statement()
#3 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#4 /var/www/familytree.latoga.com/vendor/illuminate/database/Schema/Builder.php(418): Illuminate\Database\Schema\Builder->build()
#5 /var/www/familytree.latoga.com/app/Schema/Migration42.php(56): Illuminate\Database\Schema\Builder->create()
#6 /var/www/familytree.latoga.com/app/Services/MigrationService.php(61): Fisharebest\Webtrees\Schema\Migration42->upgrade()
#7 /var/www/familytree.latoga.com/app/Http/Middleware/UpdateDatabaseSchema.php(55): Fisharebest\Webtrees\Services\MigrationService->updateSchema()
#8 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UpdateDatabaseSchema->process()
#9 /var/www/familytree.latoga.com/app/Http/Middleware/UseDatabase.php(60): Middleland\Dispatcher->handle()
#10 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\UseDatabase->process()
#11 /var/www/familytree.latoga.com/app/Http/Middleware/BadBotBlocker.php(308): Middleland\Dispatcher->handle()
#12 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BadBotBlocker->process()
#13 /var/www/familytree.latoga.com/app/Http/Middleware/CompressResponse.php(71): Middleland\Dispatcher->handle()
#14 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\CompressResponse->process()
#15 /var/www/familytree.latoga.com/app/Http/Middleware/ContentLength.php(40): Middleland\Dispatcher->handle()
#16 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ContentLength->process()
#17 /var/www/familytree.latoga.com/vendor/middlewares/client-ip/src/ClientIp.php(65): Middleland\Dispatcher->handle()
#18 /var/www/familytree.latoga.com/app/Http/Middleware/ClientIp.php(47): Middlewares\ClientIp->process()
#19 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ClientIp->process()
#20 /var/www/familytree.latoga.com/app/Http/Middleware/PublicFiles.php(62): Middleland\Dispatcher->handle()
#21 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\PublicFiles->process()
#22 /var/www/familytree.latoga.com/app/Http/Middleware/HandleExceptions.php(89): Middleland\Dispatcher->handle()
#23 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\HandleExceptions->process()
#24 /var/www/familytree.latoga.com/app/Http/Middleware/SecurityHeaders.php(49): Middleland\Dispatcher->handle()
#25 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\SecurityHeaders->process()
#26 /var/www/familytree.latoga.com/app/Http/Middleware/BaseUrl.php(73): Middleland\Dispatcher->handle()
#27 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\BaseUrl->process()
#28 /var/www/familytree.latoga.com/app/Http/Middleware/ReadConfigIni.php(68): Middleland\Dispatcher->handle()
#29 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ReadConfigIni->process()
#30 /var/www/familytree.latoga.com/app/Http/Middleware/EmitResponse.php(57): Middleland\Dispatcher->handle()
#31 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\EmitResponse->process()
#32 /var/www/familytree.latoga.com/app/Http/Middleware/ErrorHandler.php(38): Middleland\Dispatcher->handle()
#33 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(136): Fisharebest\Webtrees\Http\Middleware\ErrorHandler->process()
#34 /var/www/familytree.latoga.com/vendor/oscarotero/middleland/src/Dispatcher.php(118): Middleland\Dispatcher->handle()
#35 /var/www/familytree.latoga.com/app/Webtrees.php(280): Middleland\Dispatcher->dispatch()
#36 /var/www/familytree.latoga.com/app/Webtrees.php(251): Fisharebest\Webtrees\Webtrees->httpRequest()
#37 /var/www/familytree.latoga.com/index.php(24): Fisharebest\Webtrees\Webtrees->run()
#38 {main}

Your database most likely has an incompatible mix of collations. e.g. utf8mb3 on some columns and utf8m4 on others.

This could happen if you have upgraded your database since you installed webtrees 1.7, as the default collation changed around MySQL 5.6 / 5.7

To fix? Well, you can fix your database by manually converting the columns from utf8mb3 to utf8mb4.

Tools such as phpMyAdmin should let you do this with a GUI interface. If you prefer the command line, use something like this.

ALTER TABLE table MODIFY COLUMN column VARCHAR(...) COLLATE utf8mb4_unicode_ci;

Don't forget any not-null constraints or other column definitions.

Upgrading from 1.7.20 to 2.2 needs several activities how to configure your server settings and even modifications in your genealogical data. There is a special chapter in the German webtrees manual for that (1.7.20->2.0->2.1->2.2). Maybe you can use the translation in your browser to verify all the necessary steps. Don't forget to update all your custom modules.

Thanks @fisharebest and @hermann for the guidance.  Using phpmyadmin I was able to determine the database table fields that were set to utf8mb3 and upgrade them all to utf8mb4.  This then fixed the problem and I was able to use the old database with a fresh installation of 2.2.1,  There was a number of other minor tweaks I had to chase down (file upload limits, email sending, etc) but everything is back up and running.  I appreciate the pointers! 

Community, I think I'm still running into what I think are DB related issue post upgrade.  There are a few strange behaviors that I'm seeing, One is I can't upload files, I just get an error but nothing is logged into the website log file  (I was able to upload files before the migration and DB collation fixes, see below).  Another is I can't edit shared notes that were in my tree pre-upgrade.  When the edit screen comes up, the note appears in a drop down instead of a text field.


When I went thru the database and updated all the Table Collations from utf8mb3_unicode_ci to utf8mb4_unicode_ci, except all the primary and foreign key fields as the DB won't let you update those.  

Question: It's been a while since I've worked at this level.  Will having all the table keys set to utf8mb3 make a difference in any way? 
 

If yes, I'm assuming the best way to fix this is to create a new webtrees DB instance, take the sqldump file that is a backup of my databases and change all the "COLLATE utf8_unicode_ci" statements to "COLLATE utf8mb4_unicode_ci" to force the latest version.  Or is there a way to force this within mysql when I create the DB?  (as it seems the default is to use the utf8mb3 version.)

I realize I'm going deep here, so I appreciate anyone who can provide me guidance here!  

 

> Will having all the table keys set to utf8mb3 make a difference in any way?


No.

But if you want to change them, you need to drop the foreign keys before, and recreate them afterwards.