Description:
See the table DDL at the end.
I have a table that has as 'id' column as the primary key and set to autoincrement. It is a tinyint type. As I kept inserting data into it, the 'id' value reached tinyint threshold. When I try to insert another row, it fails. Its fine but it fails 'silently'. It does not give me an error message indicating it.
Later, I changed the type from tinyint to int. I was. then able to insert new rows.
Reiterating, the concern is that the operation failure should give an easy error message so that it can be understood and handled.
I was fortunate, this error came in Development environment for me. If it was in production, my application would have been badly affected.
CREATE TABLE `question_banks` (
`id` tinyint NOT NULL AUTO_INCREMENT,
`qb_name` varchar(200) NOT NULL DEFAULT 'Generic',
`count_of_questions_in_exam` tinyint NOT NULL DEFAULT '0',
`location` varchar(500) DEFAULT 'ANY',
`skill_levels_applicable` varchar(600) DEFAULT 'L0',
`status` varchar(45) NOT NULL DEFAULT 'NEW',
`exam_type` varchar(15) NOT NULL DEFAULT 'SKILL_TEST',
PRIMARY KEY (`id`),
UNIQUE KEY `qb_name_UNIQUE` (`qb_name`)
) ENGINE=InnoDB AUTO_INCREMENT=216 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
How to repeat:
1. Create a table using the DDL.
CREATE TABLE `question_banks` (
`id` tinyint NOT NULL AUTO_INCREMENT,
`qb_name` varchar(200) NOT NULL DEFAULT 'Generic',
`count_of_questions_in_exam` tinyint NOT NULL DEFAULT '0',
`location` varchar(500) DEFAULT 'ANY',
`skill_levels_applicable` varchar(600) DEFAULT 'L0',
`status` varchar(45) NOT NULL DEFAULT 'NEW',
`exam_type` varchar(15) NOT NULL DEFAULT 'SKILL_TEST',
PRIMARY KEY (`id`),
UNIQUE KEY `qb_name_UNIQUE` (`qb_name`)
) ENGINE=InnoDB AUTO_INCREMENT=216 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2. Keep inserting rows in this table using insert command in a loop that repeats for 10000 times.
INSERT INTO `assessments`.`question_banks`
(`id`,
`qb_name`,
`count_of_questions_in_exam`,
`location`,
`skill_levels_applicable`,
`status`,
`exam_type`)
VALUES
(<{id: }>,
<{qb_name: Generic}>,
<{count_of_questions_in_exam: 0}>,
<{location: ANY}>,
<{skill_levels_applicable: L0}>,
<{status: NEW}>,
<{exam_type: SKILL_TEST}>);
3. Check the result. It should have 10000 recs in table, but it will have lesser.
Suggested fix:
Add an error message that explains the issue and 'fails' the insert.
Description: See the table DDL at the end. I have a table that has as 'id' column as the primary key and set to autoincrement. It is a tinyint type. As I kept inserting data into it, the 'id' value reached tinyint threshold. When I try to insert another row, it fails. Its fine but it fails 'silently'. It does not give me an error message indicating it. Later, I changed the type from tinyint to int. I was. then able to insert new rows. Reiterating, the concern is that the operation failure should give an easy error message so that it can be understood and handled. I was fortunate, this error came in Development environment for me. If it was in production, my application would have been badly affected. CREATE TABLE `question_banks` ( `id` tinyint NOT NULL AUTO_INCREMENT, `qb_name` varchar(200) NOT NULL DEFAULT 'Generic', `count_of_questions_in_exam` tinyint NOT NULL DEFAULT '0', `location` varchar(500) DEFAULT 'ANY', `skill_levels_applicable` varchar(600) DEFAULT 'L0', `status` varchar(45) NOT NULL DEFAULT 'NEW', `exam_type` varchar(15) NOT NULL DEFAULT 'SKILL_TEST', PRIMARY KEY (`id`), UNIQUE KEY `qb_name_UNIQUE` (`qb_name`) ) ENGINE=InnoDB AUTO_INCREMENT=216 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci How to repeat: 1. Create a table using the DDL. CREATE TABLE `question_banks` ( `id` tinyint NOT NULL AUTO_INCREMENT, `qb_name` varchar(200) NOT NULL DEFAULT 'Generic', `count_of_questions_in_exam` tinyint NOT NULL DEFAULT '0', `location` varchar(500) DEFAULT 'ANY', `skill_levels_applicable` varchar(600) DEFAULT 'L0', `status` varchar(45) NOT NULL DEFAULT 'NEW', `exam_type` varchar(15) NOT NULL DEFAULT 'SKILL_TEST', PRIMARY KEY (`id`), UNIQUE KEY `qb_name_UNIQUE` (`qb_name`) ) ENGINE=InnoDB AUTO_INCREMENT=216 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2. Keep inserting rows in this table using insert command in a loop that repeats for 10000 times. INSERT INTO `assessments`.`question_banks` (`id`, `qb_name`, `count_of_questions_in_exam`, `location`, `skill_levels_applicable`, `status`, `exam_type`) VALUES (<{id: }>, <{qb_name: Generic}>, <{count_of_questions_in_exam: 0}>, <{location: ANY}>, <{skill_levels_applicable: L0}>, <{status: NEW}>, <{exam_type: SKILL_TEST}>); 3. Check the result. It should have 10000 recs in table, but it will have lesser. Suggested fix: Add an error message that explains the issue and 'fails' the insert.