1267 Illegal mix of collations

[jasperf]

Describe the bug Running mIgrations we get into a collation issue.

To Reproduce Steps to reproduce the behavior:

1. php artisan migrate:generate
2. Choose to log in migrations table
3. 3. default batch humber
4. See error as mentioned below

Expected behavior Migrations are run without issues.

Details (please complete the following information):

• DB: MySQL
• DB Version: Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))
• Laravel Version: 9.52.16
• PHP Version: 8.2
• Migrations Generator Version:7.0.3

Additional context Error we had on

SQLSTATE [HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,COERCIBLE) and (utf8mb3_unicode_ci, COERCIBLE) for operation '=' (SQL: SELECT GENERATION EXPRESSION
FROM information_schema. COLUMNS WHERE TABLE_NAME - 'companies'
AND COLUMN_NAME = "id'
AND EXTRA = 'VIRTUAL GENERATED')
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:760
...
// If an exception occurs when attempting to run a query, we'll format the error
// message to include the bindings with SQL, which will make this exception a
// lot more helpful to the developer instead of just the database's errors.
catch (Exception $e) {
throw new QueryException(
squery, Sthis-›prepareBindings($bindings), $e
38
+37 vendor frames
artisan: 35
...

and

mysql> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8mb3_unicode_ci   |
+----------------------+
1 row in set (0.00 sec)

plus

mysql> SHOW TABLE STATUS WHERE Name = 'companies';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| companies | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        16384 |         0 |              7 | 2023-03-22 08:21:17 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)

and

mysql> SHOW FULL COLUMNS FROM companies;
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field               | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id                  | int unsigned | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| contact_info_id     | int unsigned | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| name                | varchar(191) | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| created_at          | timestamp    | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at          | timestamp    | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| analytics_client_id | varchar(191) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
6 rows in set (0.02 sec)

We seem to use utf8mb3_ci for collation for most of our table columns. But of id we have null. And this now causes an issue. Perhaps because the tool then uses collation set by database? No, cause that is also utf8mb3_unicode_ci Looking into solutions here. Could decide to update all collations but that would be quite a change so need to make sure all then is fine. But also need to understand why this is an issue now. Why?

[kitloong]

Hi @jasperf , thank you for reporting the issue.

I am not sure if this is related to the generator, here's what I suspect:

1. If you run this raw query with your MySQL cli, will you get the same error?

SELECT GENERATION EXPRESSION
FROM information_schema. COLUMNS WHERE TABLE_NAME - 'companies'
AND COLUMN_NAME = "id'
AND EXTRA = 'VIRTUAL GENERATED'

2. Your DB collation is utf8mb3_unicode_ci, but your companies table is utf8mb4_unicode_ci, would it be conflict?

This sounds similar to issue #186 , and I would like to suggest you try this steps.

[jasperf]

Running that command

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

from Sequel Ace on staging it runs without issues. Did have to adjust your code and replace " by ':

mysql> use forge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT GENERATION_EXPRESSION
    -> FROM information_schema.COLUMNS
    -> WHERE TABLE_NAME = 'companies'
    -> AND COLUMN_NAME = 'id'
    -> AND EXTRA = 'VIRTUAL GENERATED';
Empty set (0.01 sec)

Do see issue too with utf8mb3_unicode_ci vs utf8mb4_unicode_ci. Will read issues and check steps suggested. Thanks!

fyi

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.03 sec)

mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.01 sec)
mysql> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8mb3_unicode_ci   |
+----------------------+
1 row in set (0.01 sec)
SHOW TABLE STATUS WHERE Name = 'companies';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| companies | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        16384 |         0 |              7 | 2023-03-22 08:21:17 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)
mysql> SHOW FULL COLUMNS FROM companies;
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field               | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id                  | int unsigned | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| contact_info_id     | int unsigned | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| name                | varchar(191) | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| created_at          | timestamp    | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at          | timestamp    | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| analytics_client_id | varchar(191) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+---------------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
6 rows in set (0.01 sec)

[jasperf]

Reading https://dev.mysql.com/blog-archive/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ , https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html#:%7E:text=utf8mb3%20supports%20only%20characters%20in,of%20four%20bytes%20per%20character , and https://dba.stackexchange.com/questions/334759/upgrading-mysql-utf8mb3-to-utf8mb4-how-to-replicate-documentation-behaviour show that upgrade from utf8mb3_unicode_ci to utf8mb4_unicode_ci should not really be a large deal and should work

Depends on the character. The characters in utf8mb3 occupies from 1 to 3 bytes, accordingly in utf8mb4 they may occupy from 1 to 4 bytes. So 85-character string value may take from 86 to 256 bytes in UTF8MB3 and from 86 to 341 bytes in UTF8MB4. And if your value takes less than 256 bytes after character set convertion then converted value won't be truncated, and you won't loose the information.

As for initial error on other server with utf8mb3_general_ci vs utf8mb3_ci, not sure yet, but

utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

[kitloong]

Hi @jasperf

How is the investigation going?

To eliminate the possibility of conflict with your existing packages, you could consider to create a new Laravel project and generate migration with this package.

[jasperf]

@siddharthghedia did try an update

imported the DB Dump provided by and tried the migration generation, its still showing the same error. I also tried changing the collation to other types for one particular table also, but the result was same

[kitloong]

Please let me close this issue due to stale status.