RTTS: A Replicated Temporary Table Strategy for Resolving Temporary Table Reopening Failures in MySQL 5.7 Academic Registration Systems

الملخص

Academic registration systems frequently rely on stored procedures to process prerequisite validation, course eligibility assessment, academic restrictions, and concurrent student enrollment operations. In legacy MySQL environments, particularly MySQL 5.7, temporary tables are commonly used to simplify intermediate query processing. However, MySQL exhibits documented limitations related to reopening temporary tables when the same temporary dataset is accessed through multiple execution paths, nested subqueries, or complex join structures. These limitations often generate runtime errors such as “Can't reopen table” and “Table doesn't exist,” causing registration procedures to fail. This paper presents a real-world case study based on the GetAvailableCourses procedure implemented within an academic registration system serving   1,833 students and maintaining 165,282 historical academic records spanning multiple student cohorts from the 1990s to the present.

The study analyzes the root causes of temporary table reopening failures and proposes a Replicated Temporary Table Strategy (RTTS), in which synchronized temporary table replicas are created and assigned to independent execution paths. Experimental deployment within a production environment running MySQL 5.7.44 eliminated observed reopening failures while preserving existing business logic. The results demonstrate improved execution stability, maintainability, and reliability in academic registration workflows.