Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.7.7
-
None
-
Windows 11 22H2
P41 hynix ssd
Description
Below sample statements make not expected error that duplicated records.
The sample expect 'view > newly made custom temp table > main table'
but, as the result, duplicated error occured on the main table
which means, the temp table never dropped clean.
on windows 11,
using 'innodb_flush_method=unbuffered'
-------------------------------
[Error statement]
CREATE DEFINER=`root`@`localhost` PROCEDURE `procdure_temp`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
*+ drop TABLE if EXISTS _temp_table;+*
START TRANSACTION;
create table if not exists _temp_table SELECT * from table_target LIMIT 1;
INSERT INTO
_temp_table
SELECT
*
FROM
view_for_target_table
;
COMMIT;
START TRANSACTION;
delete from table_target;
INSERT INTO
table_target
SELECT
*
FROM
_temp_table;
commit;
drop TABLE if exists _temp_table;
END
------------------------
[avoid error]
CREATE DEFINER=`root`@`localhost` PROCEDURE `procdure_temp`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
START TRANSACTION;
create table if not exists _temp_table SELECT * from table_target LIMIT 1;
- delete from _temp_table;*
INSERT INTO
_temp_table
SELECT
*
FROM
view_for_target_table
;
COMMIT;
START TRANSACTION;
delete from table_target;
INSERT INTO
table_target
SELECT
*
FROM
_temp_table;
commit;
drop TABLE if exists _temp_table;
END
Attachments
Issue Links
- relates to
-
MDEV-30517 On windows 11, difference of innodb_flush_method=O_DIRECT vs. innodb_flush_method=unbuffered
-
- Closed
-
If you need temporary table, why don't you CREATE TEMPORARY TABLE?
Otherwise, the table you created can be shared in multiple sessions, and this is not what you want, and this can possibly be the reason you get your duplicates.