https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23073
--- Comment #13 from Thomas Dukleth <[email protected]> --- I have successfully tested for the following changes in converting the Koha Wiki database from Postgres to MySQL. Postgres MediaWiki defaults had text types without any size constraint while the same columns in MySQL had strong default size constraints. We have the example of actual use in Postgres where it is evident that there are some circumstances where perfectly reasonable and correct use exceeds the MySQL size constraints and there seems to be no problem with altering the column size in MySQL to accommodate legitimate existing data use. At the same time, it seems appropriate to truncate the data for the few accidents where someone pasted wiki page content mistakenly into the comments field. Some cases where the column size default is too small in MySQL but the data is automatically generated or essentially automatically generated indicate that the MySQL column size constraint was not well planned. In one case where the comment in a comment column is legitimate but exceeds the MySQL column size by a few characters which can be truncated without loss of understanding, it seems appropriate to truncate the column for the one row which goes over the size limit. If the MySQL size change of the same data type would be from. In the SQL statements below the comment "Original" immediately precedes an SQL statement for the original structure of the column. If the original structure statement is not commented out it is restored, otherwise the next SQL statement after comments provides the structure for the column with an increased size. For the three columns with a size increase from the default, it will be necessary in MediaWiki upgrades to check whether the size may be decreased by the upgrade if that would ever happen. -- Original -- ALTER TABLE `account_credentials` MODIFY COLUMN `acd_comment` varchar(255) NOT NULL DEFAULT ''; -- Allow for sufficiently greater than largest legitimate found data -- length of 472. An explanatory greeting message to a user about a -- login problem seems necessary and appropriate ALTER TABLE `account_credentials` MODIFY COLUMN `acd_comment` varchar(511) NOT NULL DEFAULT ''; -- Original ALTER TABLE `archive` MODIFY COLUMN `ar_comment` tinyblob NOT NULL; -- Truncated in database migration Python script for greater than largest -- length for TINYBLOB, 255 bytes including one extra byte for data size. -- -- Wrong field use mistake with illegitimate found data length 5446. -- Column truncation affecting 3 rows. -- Original -- ALTER TABLE `categorylinks` MODIFY COLUMN `cl_sortkey` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ''; -- Allow for sufficiently greater than largest legitimate found data -- length of 165. Column data is automatically generated from page -- titles and used as a key for page title sort order which would -- obviously not work properly if shorter than the longest page title. -- Template titles are the longest but there are many others over the -- default. ALTER TABLE `categorylinks` MODIFY COLUMN `cl_sortkey` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ''; -- Original ALTER TABLE `filearchive` MODIFY COLUMN `fa_description` tinyblob; -- Truncated in database migration Python script for greater than largest -- length for default size TINYBLOB, 255 bytes including one extra byte -- for data size. -- -- Wrong field use mistake with illegitimate found data length 5447. -- Column truncation affecting 3 rows. -- Original -- ALTER TABLE `logging` MODIFY COLUMN `log_comment` varchar(255) NOT NULL DEFAULT ''; -- Truncated in database migration Python script for sufficiently greater -- than largest legitimate found data length of 278. Many essentially -- automatically filled uses which slightly exceed 255 characters. -- -- An alternative to increasing the size of the column to accommodate -- legitimate use could be changing some essentially automatically filled -- text patterns in the data to some more abbreviated form. -- -- Wrong field use mistake with illegitimate found data length 5447. -- Column truncation affecting 3 rows. ALTER TABLE `logging` MODIFY COLUMN `log_comment` varchar(300) NOT NULL DEFAULT ''; -- Original ALTER TABLE `revision` MODIFY COLUMN `rev_comment` tinyblob NOT NULL; -- Truncated in database migration Python script for greater than the -- largest length for default size TINYBLOB, 255 bytes including one -- extra byte for data size. -- -- Largest legitimate found data length of 259. -- -- Column truncation affecting 1 row cutting off the end of a word but not -- affecting understandability of comment. At the end of the comment the -- word "breadcrumbs." would be truncated as "breadcr" truncating four -- characters and the full stop at the end of the sentence. -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
