MySql 5.7 wrong (apparently) value of character_maximum_length
Hi
I am trying to analyze a table structure, to understand why ALTER TABLE operations take so long time.
My attention comes to longtext and mediumtext columns.
One of them, though, has a strange clue in the information_schema.COLUMNS view:
character_maximum_length reports a extremely high value, which does not seem to relate to reality:
this is the table structure:
CREATE TABLE `multiqueue` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uuid` varchar(36) NOT NULL,
`draft_uuid` varchar(36) DEFAULT NULL COMMENT 'Original draft',
`main_queue_uuid` varchar(36) DEFAULT NULL COMMENT 'Means this is the alternative channels queue of another queue',
`original_request` mediumtext COMMENT 'Original request transmitted from the client',
`action` varchar(191) DEFAULT NULL,
`author_id` bigint(20) NOT NULL,
`idaccount` bigint(20) DEFAULT NULL COMMENT 'this is the owner_id',
`author_uuid` varchar(36) NOT NULL,
`owner_uuid` varchar(36) NOT NULL,
`idaccount_parent` bigint(20) DEFAULT NULL COMMENT 'Deprecated: the information is obtainable from idaccount',
`sendcode` varchar(191) DEFAULT NULL,
`client_name` varchar(32) NOT NULL DEFAULT 'UNKNOWN',
`client_version` varchar(191) DEFAULT NULL,
`client_key` varchar(191) DEFAULT NULL,
`memo` varchar(256) DEFAULT NULL,
`delivery_time` datetime DEFAULT NULL,
`upload_session_uuid` varchar(36) DEFAULT NULL,
`files` mediumtext,
`attachments` mediumtext,
`custom` mediumtext,
`sender` text,
`multicerta_options` text,
`posta_options` text,
`mail_options` text,
`multibox_options` text,
`dateins` datetime DEFAULT NULL,
`deadline_at` datetime DEFAULT NULL COMMENT 'Deadline to read, for channels like MultiCerta',
`deadline_event` enum('RECEIVED-NOTIFIED','RECEIVED-OPENED','RECEIVED-READ') DEFAULT NULL,
`codicelistino` varchar(191) DEFAULT NULL,
`sqs` smallint(1) NOT NULL,
`stato` varchar(4) NOT NULL COMMENT 'Deprecated: see status',
`log` text,
`invoice_tag` varchar(100) DEFAULT NULL,
`postal_account` text,
`notify_email` varchar(191) DEFAULT NULL COMMENT 'Deprecated field, use notification_email_addresses, instead',
`notification_email_addresses` varchar(512) DEFAULT NULL COMMENT 'Comma separated values of valid emails',
`status` enum('WAITING-FOR-COST-CONFIRMATION','COST-CONFIRMED','TRANSMITTED','ACKNOWLEDGED','ABANDONED','SUCCEDED','ARCHIVED','DISPATCH-ERROR','ABORTED-BY-USER') NOT NULL DEFAULT 'WAITING-FOR-COST-CONFIRMATION' COMMENT '\r\n [WAITING-FOR-COST-CONFIRMATION] queue in pending cost confirmation status,\r\n [COST-CONFIRMED] cost have been confirmed by user, or have been automatically confirmed due to his settings,\r\n [TRANSMITTED] transmitted to dispatching service,\r\n [ACKNOWLEDGED] received by dispatching service,\r\n [ABANDONED] pending from too much time,\r\n [SUCCEDED] queue has been delivered successfully to all of the recipients,\r\n [ARCHIVED] succeded queue moved in the archive,\r\n [DISPATCH-ERROR] error while dispatching service,\r\n [ABORTED-BY-USER] refused by user after acknowledgement',
`status_message` varchar(512) NOT NULL,
`created_at` datetime NOT NULL,
`cost_confirmed_at` datetime DEFAULT NULL,
`transmitted_at` datetime DEFAULT NULL,
`acknowledged_at` datetime DEFAULT NULL,
`error_at` datetime DEFAULT NULL,
`abandoned_at` datetime DEFAULT NULL,
`succeded_at` datetime DEFAULT NULL,
`archived_at` datetime DEFAULT NULL COMMENT 'Date the user moved the queue in the archive, cannot be acheived if the queue is not succeded',
`aborted_by_user_at` datetime DEFAULT NULL,
`status_error_message` text,
`recipient_settings_extracted` bit(1) NOT NULL DEFAULT b'0' COMMENT 'indicates that this queue''s recipients data have been extracted and saved in the recipient log table.',
`postal_orders_generation_status` enum('NONE','PENDING','DONE','ERROR') DEFAULT 'NONE' COMMENT 'Status of postal order (files) generation, NONE: no postal order to process, PENDING: processing postal orders, DONE: processed, ERROR: error during processing',
`history_year` int(4) DEFAULT NULL COMMENT 'Year to be used in historicization',
`history_month` int(2) DEFAULT NULL COMMENT 'Year to be used in historicization',
`history_day` int(2) DEFAULT NULL COMMENT 'Year to be used in historicization',
`debug_last_modifier_class` varchar(512) DEFAULT 'UNKNOWN' COMMENT 'last class acceding to storage service',
`save_cost_with_legacy_strategy` enum('YES','NO') DEFAULT 'YES',
`search_uses_email` tinyint(4) NOT NULL COMMENT 'The queue uses email',
`search_uses_pec` tinyint(4) NOT NULL COMMENT 'The queue uses pec',
`search_uses_letter` tinyint(4) NOT NULL COMMENT 'The queue uses letter (ex sendposta)',
`search_uses_multibox` tinyint(4) NOT NULL COMMENT 'The queue uses multibox',
`search_uses_multicerta` tinyint(4) NOT NULL COMMENT 'The queue uses multicerta',
`search_recipients_text` longtext NOT NULL COMMENT 'Used to search recipients associated to queue',
`directory` enum('ARCHIVE') DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid_uq` (`uuid`),
KEY `multiqueue_upload_session_fk` (`upload_session_uuid`),
KEY `multiqueue_multiqueue_draft_uuid_fk` (`draft_uuid`),
KEY `multiqueue_sendcode_idx` (`sendcode`),
KEY `multiqueue_idaccount_fk` (`idaccount`),
KEY `multiqueue_idaccount_parent_fk` (`idaccount_parent`),
KEY `multiqueue_owner_uuid_account_uuid_fk` (`owner_uuid`),
KEY `multiqueue_dateins_idx` (`dateins`),
KEY `multiqueue_status_idx` (`status`),
KEY `postal_orders_generation_status_idx` (`postal_orders_generation_status`),
KEY `multiqueue_client_name_idx` (`client_name`),
KEY `multiqueue_history_year_idx` (`history_year`),
KEY `multiqueue_history_month_idx` (`history_month`),
KEY `multiqueue_history_day_idx` (`history_day`),
KEY `multiqueue_multiqueue_main_queue_uuid_fk` (`main_queue_uuid`),
KEY `multiqueue_author_uuid_account_uuid_fk` (`author_uuid`),
KEY `multiqueue_author_id_account_idaccount_fk` (`author_id`),
KEY `multiqueue_directory_idx` (`directory`),
CONSTRAINT `multiqueue_author_id_account_idaccount_fk` FOREIGN KEY (`author_id`) REFERENCES `account` (`IDACCOUNT`),
CONSTRAINT `multiqueue_author_uuid_account_uuid_fk` FOREIGN KEY (`author_uuid`) REFERENCES `account` (`uuid`),
CONSTRAINT `multiqueue_idaccount_fk` FOREIGN KEY (`idaccount`) REFERENCES `account` (`IDACCOUNT`) ON DELETE SET NULL,
CONSTRAINT `multiqueue_idaccount_parent_fk` FOREIGN KEY (`idaccount_parent`) REFERENCES `account` (`IDACCOUNT`) ON DELETE SET NULL,
CONSTRAINT `multiqueue_multiqueue_draft_uuid_fk` FOREIGN KEY (`draft_uuid`) REFERENCES `multiqueue_draft` (`uuid`),
CONSTRAINT `multiqueue_multiqueue_main_queue_uuid_fk` FOREIGN KEY (`main_queue_uuid`) REFERENCES `multiqueue` (`uuid`),
CONSTRAINT `multiqueue_owner_uuid_account_uuid_fk` FOREIGN KEY (`owner_uuid`) REFERENCES `account` (`uuid`),
CONSTRAINT `multiqueue_upload_session_fk` FOREIGN KEY (`upload_session_uuid`) REFERENCES `file_transfer_upload_session` (`uuid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1186654 DEFAULT CHARSET=utf8
For the column 'search_recipients_text' the view COLUMNS reports
4294967295 for CHARACTER_MAXIMUM_LENGTH
If I run this query, though:
select length(q.search_recipients_text), q.search_recipients_text, q.*
from multiqueue q
order by length(q.search_recipients_text) desc
the biggest length I get is 114649
Perhaps am I misunderstanding the meaning of the CHARACTER_MAXIMUM_LENGTH field ?
Thanks in advance
Nicola