Update number 3.09.00.025 can remove all reserves from reserves table when there is no entries in old_reserves. This is due to @ai which is set to NULL in SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves ) and reserve_id > NULL returns no results in INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai This can be fixed easily by using COALESCE INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > COALESCE(@ai, 0)
Created attachment 17934 [details] [review] Bug 10185: Fix update 3.09.00.025 It can remove all reserves from reserves table when there is no entries in old_reserves. This is due to @ai which is set to NULL in SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves ) and reserve_id > NULL returns no results in INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai
Note that this bug concern version 3.10, but also 3.12 and master
Created attachment 17939 [details] [review] Bug 10185: Fix update 3.09.00.025 It can remove all reserves from reserves table when there is no entries in old_reserves. This is due to @ai which is set to NULL in SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves ) and reserve_id > NULL returns no results in INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Can you please write a test plan for this?
1/ Do a fresh 3.8 install 2/ Do some reserves 3/ Check your old_reserves table is empty 4/ Update your sources to 3.10 5/ Run updatedatabase.pl 6/ Your reserves table is now empty! Now repeat steps 1 to 4, then apply the patch and run updatedatabase.pl Your reserves table should still contains the reserves made at step 2
Thank you, Julian!
Hi Julian, thx for the test plan. It works as described, but I found something that is weird here - we are losing the suspended holds in the update process. The reserves seem ok, but the suspend_until is empty after the update.
Ok, I guess I found it: suspend_until is missing from the update? http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/updatedatabase.pl;h=be4dfa267c465d0e039ad2f72771892dfc41cadb;hb=92f289e9a19a61ca2bd509a0f251b13de5e4c773#l5478 Could you take a look at that and add a follow up?
2 evil data loss bugs caused by "3.09.00.025" - marking this blocker.
It seems that 'suspend' is missing too http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/updatedatabase.pl;h=be4dfa267c465d0e039ad2f72771892dfc41cadb;hb=92f289e9a19a61ca2bd509a0f251b13de5e4c773#l5109
Created attachment 17958 [details] [review] Bug 10185: Add missing columns in update 3.09.00.025
Good catch, Julian!
We will need this fix in all versions that have this database update - setting version to master.
Created attachment 17973 [details] [review] [PASSED QA] Bug 10185: Add missing columns in update 3.09.00.025 Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> SQL only changes fixing an important database update. IMPORTANT: Patches must be backported to all versions containing the database update 3.09.00.025. I used the test plan provided by Julian with some additions: 1/ Do a fresh 3.8 install 2/ Do some reserves 3/ Check your old_reserves table is empty 4/ Update your sources to 3.10 5/ Run updatedatabase.pl 6/ Your reserves table is now empty! I made sure I had item level and title level holds, also suspended holds. I repeated the test with entries in my old_reserves table (cancelled and filled holds) to make sure this is also still working correctly. Before the patch we lost all suspended holds and when the old_reserves table was empty also all holds in general. After the patch the reserve_id is added but the other data in both tables is not altered. NOTE: Adding the reserve_id first to old_reserves and then to reserves we are missing one id in between. old_reserves: 1,2,3 reserves: 5,6
Created attachment 17974 [details] [review] [PASSED QA] Bug 10185: Fix update 3.09.00.025 It can remove all reserves from reserves table when there is no entries in old_reserves. This is due to @ai which is set to NULL in SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves ) and reserve_id > NULL returns no results in INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> Test plan and comments on second patch.
Created attachment 17975 [details] [review] [PASSED QA] Bug 10185: Add missing columns in update 3.09.00.025 Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> SQL only changes fixing an important database update. IMPORTANT: Patches must be backported to all versions containing the database update 3.09.00.025. I used the test plan provided by Julian with some additions: 1/ Do a fresh 3.8 install 2/ Do some reserves 3/ Check your old_reserves table is empty 4/ Update your sources to 3.10 5/ Run updatedatabase.pl 6/ Your reserves table is now empty! I made sure I had item level and title level holds, also suspended holds. I repeated the test with entries in my old_reserves table (cancelled and filled holds) to make sure this is also still working correctly. Before the patch we lost all suspended holds and when the old_reserves table was empty also all holds in general. After the patch the reserve_id is added but the other data in both tables is not altered. NOTE: Adding the reserve_id first to old_reserves and then to reserves we are missing one id in between. old_reserves: 1,2,3 reserves: 5,6
Treating the second patch as a QA follow up.
This patch has been pushed to master and 3.12.x.
Pushed to 3.10.x will be in 3.10.6
Ok in master, 3.12.x and 3.10.x