We are using the Percona Toolkit 3.0.6 version for running the MySQL queries specifically ALTER statements but we have started using this recently and identified one issue, while users are expecting and giving the values for ENUM is upper-case it is internally converting this to lower-case and applying this changes to DB schema, hence it is causing issue in our applications.
Hello @Bony_Mondal,
Please provide an example. Show the table schema before using pt-online-schema-change, then show the command used, then show the table scheme afterwards.
HI @matthewb
I can provide with you a dummy table schema and how we are running the pt-online-schema-change query and what happend after successful execution.
table schema:
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| enum_test | CREATE TABLE `enum_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`status` enum('APPROVE','REJECT','pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
percona query:
pt-online-schema-change D=test,t=enum_test,h=localhost,u=<username>,p=<password> --execute --alter="change column status status enum(\"APPROVE\",\"REJECT\",\"pending\",\"IN_QUEUE\")" --alter-foreign-keys-method="auto" --preserve-triggers --no-check-alter --no-drop-old-table --no-check-unique-key-change --recursion-method=none
In our current setup, we define ENUM values in uppercase. However, when running operations through Percona, these values are being converted to lowercase and subsequently altered in the database. While I cannot provide the exact table schema from our cluster, I have tested this issue locally using the latest version of Percona Toolkit (3.7.0), where it functions as expected. In the cluster environment, though, the ENUM values are being converted to lowercase before being applied to the DBs (We are using Percona 3.0.6 version in our clusters).
Hello @matthewb,
Apologies for the inconvenience. We’ve identified an issue in our legacy codebase where ALTER statements are being converted to lowercase. This transformation inadvertently changes the case of ENUM values, leading to unintended behavior when Percona executes the query with triggers.
Apologies once again for any inconvenience caused. Should we encounter any future issues specifically related to Percona, we will promptly inform you.