# Silent Column Changes

When a [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) or [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) command is issued, MariaDB silently changes a column specification in the following cases:

* [PRIMARY KEY](https://mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide#primary-key) columns are always NOT NULL.
* Any trailing spaces from [SET](https://mariadb.com/docs/server/reference/data-types/string-data-types/set-data-type) and [ENUM](https://mariadb.com/docs/server/reference/data-types/string-data-types/enum) values are discarded.
* [TIMESTAMP](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp) columns are always NOT NULL, and display sizes are discarded.
* A row-size limit of 65535 bytes applies.
* If [strict SQL mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#strict-mode) is not enabled (by default, it is), a [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar) column longer than 65535 become [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/text), and a [VARBINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/varbinary) columns longer than 65535 becomes a [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob). If strict mode is enabled the silent changes will not be made, and an error will occur.
* If a USING clause specifies an index that's not permitted by the storage engine, the engine will instead use another available index type that can be applied without affecting results.
* If the CHARACTER SET binary attribute is specified, the column is created as the matching binary data type. A TEXT becomes a BLOB, CHAR a BINARY and VARCHAR a VARBINARY. ENUMs and SETs are created as defined.

To ease imports from other RDBMSs, MariaDB also silently maps the following data types:

| Other Vendor Type    | MariaDB Type                                                                                    |
| -------------------- | ----------------------------------------------------------------------------------------------- |
| BOOL                 | [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint)      |
| BOOLEAN              | [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint)      |
| CHARACTER VARYING(M) | [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar)(M)    |
| FIXED                | [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal)      |
| FLOAT4               | [FLOAT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/float)          |
| FLOAT8               | [DOUBLE](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/double)        |
| INT1                 | [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint)      |
| INT2                 | [SMALLINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/smallint)    |
| INT3                 | [MEDIUMINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/mediumint)  |
| INT4                 | [INT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int)              |
| INT8                 | [BIGINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/bigint)        |
| LONG VARBINARY       | [MEDIUMBLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/mediumblob) |
| LONG VARCHAR         | [MEDIUMTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/mediumtext) |
| LONG                 | [MEDIUMTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/mediumtext) |
| MIDDLEINT            | [MEDIUMINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/mediumint)  |
| NUMERIC              | [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal)      |

Currently, all MySQL types are supported in MariaDB.

For type mapping between Cassandra and MariaDB, see [Cassandra storage engine](https://mariadb.com/docs/server/server-usage/storage-engines/legacy-storage-engines/cassandra/cassandra-storage-engine-overview).

## Example

Silent changes in action:

```sql
CREATE TABLE SilenceIsGolden
   (
    f1 TEXT CHARACTER SET BINARY,
    f2 VARCHAR(15) CHARACTER SET BINARY,
    f3 CHAR CHARACTER SET BINARY,
    f4 ENUM('x','y','z') CHARACTER SET BINARY,
    f5 VARCHAR (65536),
    f6 VARBINARY (65536),
    f7 INT1
   );
Query OK, 0 rows affected, 2 warnings (0.31 sec)

SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1246 | Converting column 'f5' from VARCHAR to TEXT   |
| Note  | 1246 | Converting column 'f6' from VARBINARY to BLOB |
+-------+------+-----------------------------------------------+

DESCRIBE SilenceIsGolden;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| f1    | blob              | YES  |     | NULL    |       |
| f2    | varbinary(15)     | YES  |     | NULL    |       |
| f3    | binary(1)         | YES  |     | NULL    |       |
| f4    | enum('x','y','z') | YES  |     | NULL    |       |
| f5    | mediumtext        | YES  |     | NULL    |       |
| f6    | mediumblob        | YES  |     | NULL    |       |
| f7    | tinyint(4)        | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}