[MDEV-21359] Changing "LONGTEXT" column's collation (using ALTER TABLE ... CHANGE) truncates data after 65535 chars Created: 2019-12-19  Updated: 2020-01-12  Resolved: 2020-01-12

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Data types
Affects Version/s: 10.0.38, 5.5.64, 10.4.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Bram Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS Linux 7.5.1804 (Core) (VPS Plesk environment), PHP 7.2.25


 Description   

Changing the collation of a LONGTEXT column (using ALTER TABLE ... CHANGE) truncates the data contained in it to exactly 65535 characters long (the max length of a TEXT type column?).

I've confirmed/reproduced this with MariaDB 5.5.64 and 10.0.38. MySQL 5.6.13 doesn't experience this behaviour.

The problematic query is in the form of:

ALTER TABLE <table_name> CHANGE <column_name> <column_name> LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

The following work fine/as expected:

ALTER TABLE <table_name> MODIFY <column_name> LONGTEXT CHARACTER SET utf8mb4

and:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4

(The specific collation or the type of data contained in the LONGTEXT column doesn't seem to matter; I'm able to reproduce it every time. Also both InnoDB and MyISAM storage engines experience the same behaviour.)

Side notes

I originally posted this issue on PhpMyAdmin's github page and got directed here: https://github.com/phpmyadmin/phpmyadmin/issues/15670

I hope I've included enough information.

 Comments   
Comment by Bram [ 2019-12-20 ]

I'm sorry if I wasted anyone's time, but I think this is a problem on PhpMyAdmin's side after all. That was also a common factor in all my test cases (should've used the terminal, yes).

In short: even though the "problematic" query I've posted above is shown as being used by phpmyadmin to update the column's collation (when using the graphical column editing interface)...I think it's doing something else internally, or in a different environment. As that same query runs without truncating anything when being run from the "SQL" query box. Really strange.

Either how, I think it's safe to say it's not a problem in MariaDB, and that this can be closed.

For those interested (or as reference) I've made a new post on pma's github: https://github.com/phpmyadmin/phpmyadmin/issues/15670#issuecomment-568069865

Generated at Thu Feb 08 09:06:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.