MySQL Error: Illegal mix of collations error when executing generating command

[nabeeljavaid]

Describe the bug I encountered an error while executing generating command in my Laravel application. The error message states "SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,COERCIBLE) and (utf8mb3_unicode_ci,COERCIBLE) for operation '='". This error occurs when trying to run the following query:

SELECT GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'activity_log'
    AND COLUMN_NAME = 'id'
    AND EXTRA = 'VIRTUAL GENERATED'

The error message indicates that there is an illegal mix of collations between utf8mb3_general_ci and utf8mb3_unicode_ci.

To Reproduce Steps to reproduce the behavior:

1. The tables already exist in the database.
2. Run php artisan migrate:generate --tables="activity_log"
3. See error

Expected behavior The SQL query should execute without any collation-related errors.

Screenshots

Details (please complete the following information):

• DB: MySQL
• DB Version: 8.0
• Laravel Version: 9.43.0
• PHP Version: 8.1
• Migrations Generator Version: 0

Additional context N/A

[kitloong]

Hi @nabeeljavaid , I am sorry for not replying sooner.

I am sorry for your trouble. Looks to me like it might be an existing collation issue in your table/database but I am not so sure.

Could you export the table DDL statement with sensitive information removed for me?

This will allow me to easily reproduce your issue.

[nabeeljavaid]

Hi @kitloong , Please see the following database schema. Please review it and identify any potential issues or areas that may require improvement.
Thank you in advance for your assistance.

-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `id` int UNSIGNED NOT NULL,
  `log_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `subject_id` int UNSIGNED DEFAULT NULL,
  `subject_type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `event` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `causer_id` int UNSIGNED DEFAULT NULL,
  `causer_type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `properties` json DEFAULT NULL,
  `batch_uuid` char(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_log`
--
ALTER TABLE `activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `activity_log_log_name_index` (`log_name`),
  ADD KEY `subject` (`subject_id`,`subject_type`),
  ADD KEY `causer` (`causer_id`,`causer_type`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_log`
--
ALTER TABLE `activity_log`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;```

[kitloong]

Hi @nabeeljavaid

I have run the following steps:

1. Create a new Laravel project
2. Install the migrations generator
3. Create activity_log table as shared by you.
4. Run php artisan migrate:generate --tables="activity_log"

However, I failed to reproduce your issue.

If you get the error message, which your should not, by running the following command directly from your MySQL client

SELECT GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'activity_log'
    AND COLUMN_NAME = 'id'
    AND EXTRA = 'VIRTUAL GENERATED'

It may indicate the error does not cause by the generator.

Thank you for providing more details for investigation purposes.

[nabeeljavaid]

@kitloong I successfully executed the migration generation process with the new Laravel installation, and I'm pleased to inform you that it was completed without any issues. I appreciate your assistance and guidance throughout the process.