Page tree
Skip to end of metadata
Go to start of metadata

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