Migration error (possibly related to Japanese language)

[darylknight]

Describe the bug or issue you're experiencing

I just tried to update from Craft 3.7.51 to 4.2.1.1, Freeform 3.13.17 to 4.0.0 and had a migration error, output below.

*** applying m220330_111857_SplitSubmissionsTable
    > create table {{%freeform_submissions_radial_3_6_0_contact_1}} ... done (time: 0.008s)
    > add primary key PK on {{%freeform_submissions_radial_3_6_0_contact_1}} (id) ... done (time: 0.011s)
    > add foreign key fk_msyjobtqosgglagnlfuaoumqlceqlzhkoksz: {{%freeform_submissions_radial_3_6_0_contact_1}} (id) references {{%freeform_submissions}} (id) ... done (time: 0.022s)
    > insert into {{%freeform_submissions_radial_3_6_0_contact_1}} ... done (time: 0.010s)
    > create table {{%freeform_submissions_na_rain_contact_2}} ... done (time: 0.007s)
    > add primary key PK on {{%freeform_submissions_na_rain_contact_2}} (id) ... done (time: 0.012s)
    > add foreign key fk_iiedzlftcobkashxpwtaxcwfjmrwthmywaew: {{%freeform_submissions_na_rain_contact_2}} (id) references {{%freeform_submissions}} (id) ... done (time: 0.023s)
    > insert into {{%freeform_submissions_na_rain_contact_2}} ... done (time: 0.008s)
    > create table {{%freeform_submissions_questionnaire_for_china_6}} ... done (time: 0.006s)
    > add primary key PK on {{%freeform_submissions_questionnaire_for_china_6}} (id) ... done (time: 0.015s)
    > add foreign key fk_eokzbjichvtjjynhqviccbwnmnyxblquvyro: {{%freeform_submissions_questionnaire_for_china_6}} (id) references {{%freeform_submissions}} (id) ... done (time: 0.023s)
    > create table {{%freeform_submissions_radianz_contact_na_7}} ... done (time: 0.006s)
    > add primary key PK on {{%freeform_submissions_radianz_contact_na_7}} (id) ... done (time: 0.017s)
    > add foreign key fk_vfxlevsyxbjydcvwoxdrnlbngqaajvnkbegn: {{%freeform_submissions_radianz_contact_na_7}} (id) references {{%freeform_submissions}} (id) ... done (time: 0.019s)
    > create table {{%freeform_submissions_お問い合わせ_8}} ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{{%freeform_submissions_お問い合わせ_8}} (
    `id` int(11) NOT NULL,
    `first' at line 1
The SQL being executed was: CREATE TABLE {{%freeform_submissions_お問い合わせ_8}} (
    `id` int(11) NOT NULL,
    `first_name_1` text,
    `last_name_2` text,
    `institution_16` text,
    `department_25` text,
    `message_12` text,
    `what_are_you_interested_in_18` text,
    `permission_to_communicate_19` text,
    `email_40` text
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci (/Users/username/Sites/[removed]/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE {{...')
#1 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE {{...')
#2 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /Users/username/Sites/[removed]/vendor/solspace/craft-freeform/packages/plugin/src/migrations/m220330_111857_SplitSubmissionsTable.php(118): yii\db\Migration->createTable('{{%freeform_sub...', Array)
#4 /Users/username/Sites/[removed]/vendor/solspace/craft-freeform/packages/plugin/src/migrations/m220330_111857_SplitSubmissionsTable.php(85): Solspace\Freeform\migrations\m220330_111857_SplitSubmissionsTable->createFormTable(8, '\xE3\x81\x8A\xE5\x95\x8F\xE3\x81\x84\xE5\x90\x88\xE3\x82\x8F...', Array)
#5 /Users/username/Sites/[removed]/vendor/craftcms/cms/src/db/Migration.php(49): Solspace\Freeform\migrations\m220330_111857_SplitSubmissionsTable->safeUp()
#6 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#7 /Users/username/Sites/[removed]/vendor/craftcms/cms/src/console/controllers/MigrateController.php(360): yii\console\controllers\BaseMigrateController->migrateUp('m220330_111857_...')
#8 [internal function]: craft\console\controllers\MigrateController->actionAll()
#9 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#10 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#11 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/console/Controller.php(182): yii\base\Controller->runAction('all', Array)
#12 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/base/Module.php(552): yii\console\Controller->runAction('all', Array)
#13 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#14 /Users/username/Sites/[removed]/vendor/craftcms/cms/src/console/Application.php(90): yii\console\Application->runAction('migrate/all', Array)
#15 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#16 /Users/username/Sites/[removed]/vendor/craftcms/cms/src/console/Application.php(121): yii\console\Application->handleRequest(Object(craft\console\Request))
#17 /Users/username/Sites/[removed]/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#18 /Users/username/Sites/[removed]/craft(27): yii\base\Application->run()
#19 {main}
*** failed to apply m220330_111857_SplitSubmissionsTable (time: 0.209s)

25 from 32 migrations were applied.

Migration failed. The rest of the migrations are canceled.

With my limited knowledge, I would guess this could be related to the Japanese content, also mentioned in https://github.com/solspace/craft-freeform/issues/408

[kjmartens]

Sorry about this @darylknight. Where we've gone wrong is not having complete validation on form handles during auto-generation. Form handles do not allow you to type in any foreign characters, but the auto-generation of the handle when creating a new form will generate those characters, which are not supposed to be allowed. We've also noticed that field handles will incorrectly allow foreign characters, which doesn't seem to be a problem in Freeform 3, but will become a problem in Freeform 4.

What we'll do is fix Freeform to no longer allow this. However, to correct this on your end, you'll need to update the forms with handles (and fields' handles as well) that contain characters like these to include alpha-numeric handles instead. Then try the migration again. 🙂

Sorry for the trouble!

[darylknight]

That makes perfect sense, thank you!

[kjmartens]

This has now been resolved in Freeform 3.13.18 and 4.0.1. 🙂

[darylknight]

Great, thank you