/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::Diff::Table

NAME

DBIO::MySQL::Diff::Table - Diff operations for MySQL/MariaDB tables

VERSION

version 0.900004

DESCRIPTION

Represents a table-level diff operation in MySQL/MariaDB: CREATE TABLE, DROP TABLE or, for a table whose collation changes, ALTER TABLE ... CONVERT TO (see "The collation of an existing table"). create ops carry the target columns and FKs so as_sql can render the full inline definition (parallel to DBIO::SQLite::Diff::Table).

The foreign keys a create op carries are the ones its CREATE TABLE is to have, and DBIO::MySQL::Diff chooses them: a foreign key that needs an index the same diff creates (a unique key of a table it creates, say) is left out and added after the index, where the server would refuse it here; and so is one whose columns an index the diff creates on the table covers, where the server would make a second index over them. The CREATE TABLE carries no index but the primary key, and a DROP TABLE comes after the DROP FOREIGN KEY of every constraint that references the table.

The one exception to the primary key being the only key of a CREATE TABLE: an AUTO_INCREMENT column that is not in the primary key has to be a key at the moment the table is created (the server refuses it otherwise, "there can be only one auto column and it must be defined as a key"). DBIO::MySQL::Diff hands such a table the index the target has over the column (indexes, hashrefs { index_name, index_info }), and it stands in the CREATE TABLE as UNIQUE KEY or KEY, not as a CREATE INDEX after it, so the table that an upgrade creates is the table install_ddl creates.

The collation of an existing table

The character set and collation of a table are part of the target model (mysql_charset and mysql_collate of the result class, table_collation of DBIO::MySQL::Introspect::Tables); the CREATE TABLE of a new table writes them. A table that exists with another collation is changed with

   ALTER TABLE `t` CONVERT TO CHARACTER SET x COLLATE y;

-- but only where the data provably survives. The statement
rewrites every text column of the table, and what it does to the data was
measured on MySQL 8.0.30 and MariaDB 11.8.9 (both behave alike unless said):

  • Same character set, other collation (utf8mb4_unicode_ci to utf8mb4_bin and back): the bytes stay, nothing is re-encoded. Umlauts, CJK and 4-byte characters were identical afterwards; FULLTEXT indexes kept matching; BLOB, VARBINARY and BINARY columns are not touched; a column with a collation of its own gets the table's (the model has no per-column collation); TINYTEXT/TEXT keep their type. A unique key whose values collide under the new collation (abc, ABC from _bin to _ci) is refused by the server (1062, "Duplicate entry"), atomically -- the table was unchanged afterwards; that error is the server's, a diff cannot know it in advance.

  • Another character set, target utf8mb4: widening, the characters are kept (latin1 with umlauts and utf8mb3 with Japanese text were identical afterwards). Two things are not safe and are refused instead: TINYTEXT, TEXT and MEDIUMTEXT columns are silently promoted to TEXT, MEDIUMTEXT and LONGTEXT (the type changes, so a later diff would turn it back and cut), and ENUM / SET columns of latin1 lose their non-ASCII members (MySQL 8.0.30 left the definition enum('','b') and emptied the data without an error; MariaDB refused with "Data truncated"). An index over a long VARCHAR can exceed the key length; MySQL refuses (1071, atomically), MariaDB keeps the statement and shortens the key to a prefix.

  • Any other target character set (latin1, utf8mb3, ...) for a column that has another one: narrowing. Characters outside the target are lost (with strict mode the server refuses, "Incorrect string value", and the table stays; a session without it wrote ? for each of them and the data differed afterwards). Refused.

  • A foreign key that has a text column of the table on either side: the server refuses the conversion of either table of the pair on its own (MySQL: "Referencing column ... and referenced column ... are incompatible", MariaDB: "Cannot change column ... used in a foreign key constraint"), and one statement cannot convert both. Refused.

A refusal is a DBIO::Exception that names the table, both collations and the column or constraint, thrown while the diff is built, before any statement ran. The columns of a converted table are not compared for character set and collation: the conversion gives them the collation of the target.

METHODS

diff

my @ops = DBIO::MySQL::Diff::Table->diff(
    $source_tables, $target_tables,
    $target_columns, $target_fks,
    $target_indexes, $source_columns, $source_fks,
);

The last two are the columns and foreign keys of the source, by table; they are what the refusals of a collation change look at.

refuse_unprovable_conversion

DBIO::MySQL::Diff::Table::refuse_unprovable_conversion(
    $table, $old_collation, $new_collation, $source_columns, $source_fks);

Throws a DBIO::Exception when CONVERT TO would not keep the data of the table, see "The collation of an existing table". Returns nothing otherwise.

as_sql

The CREATE TABLE, DROP TABLE or ALTER TABLE ... CONVERT TO statement. Every name in it is quoted with "quote_ident" in DBIO::MySQL::Identifier. A foreign key with a SET DEFAULT action throws ("refuse_set_default" in DBIO::MySQL::Diff::ForeignKey).

summary

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.