Page tree

Since translate5 is working with foreign key constraints, it can happen that the DB returns the above mentioned error on creating a table or modifying a column related to a foreign key.

Basicly the SQL command

SHOW INNODB STATUS;

called directly after the above error, gives a little bit more information as the error message itself.

For a whole list of possible reasons for the error see http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

Reasons for this error can using datatype A on column one and datatype B using on foreign key column. This can be recognized fast by comparing the table and field definitions.

Not so obviously is the fact, that if your are trying to use a varchar field (or string type in general) as a foreign key field, the charsets and collations have also to be the same on both fields.

That led to the convention, that table create statements must have a charset!

To check and modify table and database charsets, the following commands can be used:

Get database default character_set:
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "<DATABASE_NAME>";	
Get all columns not configured as utf8mb4:
SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME,CHARACTER_SET_NAME from information_schema.columns where CHARACTER_SET_NAME != 'utf8mb4';
Change the default character set and collation of a database:
ALTER DATABASE <database_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Additional Errors produced due the missing charset:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=', query was:
  • No labels