[MDEV-30500] Drop statement not droped in specific circumstances Created: 2023-01-30  Updated: 2023-03-27  Resolved: 2023-03-27

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.7.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mitchell Lee Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

Windows 11 22H2
P41 hynix ssd

Issue Links:
Relates
relates to MDEV-30517 On windows 11, difference of innodb_f... Closed

 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

 Comments   
Comment by Vladislav Vaintroub [ 2023-01-31 ]

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.

Comment by Mitchell Lee [ 2023-01-31 ]

I'm FULLY aware what you r talking about.
This issue is not a kinda conflcition between different thread/connection. I fully aware of it.
The point is
the drop not working at the statements point.

Comment by Mitchell Lee [ 2023-01-31 ]

and the CREATE TEMPORARY TABLE usage condition u guys posted is
'a session related issue of temp table and not garantee to be dropped'
and etcetera, etcetera, etcetera the so much of restriction and unreliablity
for under given situation which not meet MariaDB statemtent,
want an own logic which reilable and confident.
So, hope to focus on the issue not the workaround.

Comment by Vladislav Vaintroub [ 2023-01-31 ]

Ok could you then, please create a standalone reproducible case, put it into an .sql file so it can run from the client, and we'll happily look into it.

Comment by Mitchell Lee [ 2023-02-01 ]

1. If call the CREATE TEMPORARY TABLE statemtent in one single connection in a infinite loop(of course same temp table name as the specification). Your team garantee that drop cleanly? None of logical exception expected?

2. If call the CREATE TEMPORARY TABLE statement by each different connection(but might be a same thread sometime since the user uncontrollerble behavior) each 0.5 ~1.0 second. Your team garantee that drop cleanly? None of logical exception expected?

Comment by Mitchell Lee [ 2023-02-26 ]

After upgrade to 10.7.8 and observing for a while,
it seems fixed by MDEV-30052 since the WINDOW clause in all over the db.
actuallky say, not fixed, but not occured since no call-stack breaking.
So I suggest to close for now.

Generated at Thu Feb 08 10:16:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.