MySQL Forums
Forum List  »  InnoDB

MySql 5.7 wrong (apparently) value of character_maximum_length
Posted by: Nicola Farina
Date: August 12, 2024 04:26AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MySql 5.7 wrong (apparently) value of character_maximum_length
245
August 12, 2024 04:26AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.