[MDEV-35237] Bulk insert fails to apply buffered operation during CREATE..SELECT statment - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.11.9, 11.4.3
Fix Version/s: 10.11.10, 11.2.6, 11.4.4
Component/s: Data Definition - Create Table, Data Manipulation - Insert
Labels:
- regression-10.11
Environment:
Linux, Windows

Description

Hello.

When creating a table using SQL query

CREATE TABLE table_name SELECT field FROM table_source

creates an empty table without records, if you perform SELECT separately, you can see that there are rows, but they are not added to the table.

The problem is floating, for example, if you remove any selection filter conditions, then the table is created with records. There are no errors in this case, so it is very difficult to know about the problem, as a result it leads to very serious problems in different areas of database application, for example, if it is a financial transaction, then it is a complete loss of information, the data as if there is, but in the table they are not.

The problem was found on Mariadb version 10.11.9.
We switched to Mariadb 11.4.3, which supposedly did not have this problem, but it turned out it does, with a different set of data and query conditions (added JOIN) the problem appeared again!

How to repeat the problem. I provide queries and tables dump.

DROP TABLE IF EXISTS problem_table;

CREATE TABLE problem_table

SELECT product_id

FROM et_price_product t1

INNER JOIN et_price_import_setup t2 ON t2.price_id = t1.price_id AND t2.uuid_parent = '' AND t2.status=1

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test_tables.sql
1.43 MB
2024-10-23 06:43

Issue Links

is caused by

MDEV-33087 ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2024-10-23 11:42 - edited

Thank you very much for the report!
I repeated as described on 10.11-11.6 with InnoDB (create as select engine=myisam works), 10.5,10.6 works as expected.
Caused by cc8eefb0dc commit

--source include/have_innodb.inc

CREATE TABLE t2 (

  price_id int(11) NOT NULL,

  status tinyint(1) NOT NULL DEFAULT 1,

  uuid_parent varchar(80) NOT NULL,

  PRIMARY KEY (price_id),

  KEY  (status),

  KEY  (uuid_parent)

) ENGINE=InnoDB ;

INSERT INTO t2 VALUES(34,1,''),(48,1,''),(55,0,''),(82,0,''),(97,0,''),(145,1,''),(151,0,''),(170,0,''),(184,0,''),(199,0,''),(222,0,''),(223,0,''),(227,0,''),(229,0,''),(232,1,''),(233,0,''),(238,0,''),(240,0,''),(244,1,''),(247,0,''),(252,0,''),(256,0,''),(267,0,''),(269,0,''),(271,1,''),(283,0,''),(292,0,''),(307,0,''),(309,1,''),(313,1,''),(316,1,''),(320,0,''),(321,1,''),(325,1,''),(330,1,''),(333,0,''),(335,0,''),(336,1,''),(337,1,''),(383,1,''),(400,1,''),(402,1,''),(404,0,''),(405,1,'efa1ef46-1514-11ea-b7dd-309c239ced1a'),(406,0,''),(408,1,''),(410,1,''),(414,1,''),(415,1,''),(418,1,'e2a676b2-b07e-11ed-b995-9600005f1103'),(420,0,''),(421,1,''),(422,1,''),(423,0,''),(424,1,''),(426,1,'');

CREATE TABLE t1 (

  supply_product_id bigint(20) NOT NULL,

  price_id int(11) NOT NULL,

  product_id bigint(20) NOT NULL,

  KEY  (price_id),

  KEY  (product_id)

) ENGINE=InnoDB;

INSERT INTO t1 VALUES (96538366,227,0),(96538367,227,0),(96538368,227,0),(96538369,227,0),(96538370,227,0),(96538371,227,0),(96538372,227,0),(96538373,227,0),(96538374,227,0),(96538375,227,0),(96538376,227,0),(96538377,227,0),(97134475,55,0),(97134476,55,0),(97134483,55,0),(97134485,55,0),(97134486,55,0),(97134487,55,0),(97134488,55,0),(97134489,55,0),(97134495,55,0),(97134499,55,0),(97134501,55,0),(97134502,55,0),(97134508,55,0),(97134511,55,0),(97134515,55,0),(97134519,55,0),(97134537,55,0),(97134538,55,0),(97134544,55,0),(97134545,55,0),(97134588,55,0),(97134601,55,0),(97134621,55,0),(97134623,55,0),(97134624,55,0),(97134625,55,0),(97134626,55,0),(97134627,55,0),(97134628,55,0),(97134629,55,0),(97138176,267,0),(98484651,145,0),(98484652,145,0),(98484653,145,0),(98484654,145,0),(98484666,145,0),(98484670,145,0),(98484674,145,0),(98484680,145,0),(98484681,145,0),(98484685,145,0),(98484689,145,0),(98484691,145,0),(98484692,145,0),(98484693,145,0),(98484700,145,0),(98484702,145,0),(98484703,145,0),(98484705,145,0),(98484708,145,0),(98484710,145,0),(98484713,145,0),(98484715,145,0),(98484716,145,0),(98484719,145,0),(98484720,145,0),(98484721,145,0),(98484724,145,0),(98484725,145,0),(98484726,145,0),(98484727,145,0),(98484728,145,0),(98484732,145,0),(98484733,145,0),(98484734,145,0),(98484735,145,0),(98484736,145,0),(98484737,145,0),(98484738,145,0),(98484739,145,0),(98484740,145,0),(98484741,145,0),(98484742,145,0),(98484743,145,0),(98484744,145,0),(98484745,145,0),(98484746,145,0),(98484747,145,0),(98484748,145,0),(98484749,145,0);

CREATE TABLE t engine=innodb

SELECT product_id FROM t1 JOIN t2 ON t2.price_id = t1.price_id AND t2.uuid_parent = '' AND t2.status=1 ;

select count(*) from t;

drop table t1,t2,t;

Alice Sherepa added a comment - 2024-10-23 11:42 - edited Thank you very much for the report! I repeated as described on 10.11-11.6 with InnoDB (create as select engine=myisam works), 10.5,10.6 works as expected. Caused by cc8eefb0dc commit --source include/have_innodb.inc CREATE TABLE t2 ( price_id int(11) NOT NULL, status tinyint(1) NOT NULL DEFAULT 1, uuid_parent varchar(80) NOT NULL, PRIMARY KEY (price_id), KEY (status), KEY (uuid_parent) ) ENGINE=InnoDB ; INSERT INTO t2 VALUES(34,1,''),(48,1,''),(55,0,''),(82,0,''),(97,0,''),(145,1,''),(151,0,''),(170,0,''),(184,0,''),(199,0,''),(222,0,''),(223,0,''),(227,0,''),(229,0,''),(232,1,''),(233,0,''),(238,0,''),(240,0,''),(244,1,''),(247,0,''),(252,0,''),(256,0,''),(267,0,''),(269,0,''),(271,1,''),(283,0,''),(292,0,''),(307,0,''),(309,1,''),(313,1,''),(316,1,''),(320,0,''),(321,1,''),(325,1,''),(330,1,''),(333,0,''),(335,0,''),(336,1,''),(337,1,''),(383,1,''),(400,1,''),(402,1,''),(404,0,''),(405,1,'efa1ef46-1514-11ea-b7dd-309c239ced1a'),(406,0,''),(408,1,''),(410,1,''),(414,1,''),(415,1,''),(418,1,'e2a676b2-b07e-11ed-b995-9600005f1103'),(420,0,''),(421,1,''),(422,1,''),(423,0,''),(424,1,''),(426,1,''); CREATE TABLE t1 ( supply_product_id bigint(20) NOT NULL, price_id int(11) NOT NULL, product_id bigint(20) NOT NULL, KEY (price_id), KEY (product_id) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (96538366,227,0),(96538367,227,0),(96538368,227,0),(96538369,227,0),(96538370,227,0),(96538371,227,0),(96538372,227,0),(96538373,227,0),(96538374,227,0),(96538375,227,0),(96538376,227,0),(96538377,227,0),(97134475,55,0),(97134476,55,0),(97134483,55,0),(97134485,55,0),(97134486,55,0),(97134487,55,0),(97134488,55,0),(97134489,55,0),(97134495,55,0),(97134499,55,0),(97134501,55,0),(97134502,55,0),(97134508,55,0),(97134511,55,0),(97134515,55,0),(97134519,55,0),(97134537,55,0),(97134538,55,0),(97134544,55,0),(97134545,55,0),(97134588,55,0),(97134601,55,0),(97134621,55,0),(97134623,55,0),(97134624,55,0),(97134625,55,0),(97134626,55,0),(97134627,55,0),(97134628,55,0),(97134629,55,0),(97138176,267,0),(98484651,145,0),(98484652,145,0),(98484653,145,0),(98484654,145,0),(98484666,145,0),(98484670,145,0),(98484674,145,0),(98484680,145,0),(98484681,145,0),(98484685,145,0),(98484689,145,0),(98484691,145,0),(98484692,145,0),(98484693,145,0),(98484700,145,0),(98484702,145,0),(98484703,145,0),(98484705,145,0),(98484708,145,0),(98484710,145,0),(98484713,145,0),(98484715,145,0),(98484716,145,0),(98484719,145,0),(98484720,145,0),(98484721,145,0),(98484724,145,0),(98484725,145,0),(98484726,145,0),(98484727,145,0),(98484728,145,0),(98484732,145,0),(98484733,145,0),(98484734,145,0),(98484735,145,0),(98484736,145,0),(98484737,145,0),(98484738,145,0),(98484739,145,0),(98484740,145,0),(98484741,145,0),(98484742,145,0),(98484743,145,0),(98484744,145,0),(98484745,145,0),(98484746,145,0),(98484747,145,0),(98484748,145,0),(98484749,145,0); CREATE TABLE t engine=innodb SELECT product_id FROM t1 JOIN t2 ON t2.price_id = t1.price_id AND t2.uuid_parent = '' AND t2.status=1 ; select count(*) from t; drop table t1,t2,t;

Marko Mäkelä added a comment - 2024-10-25 06:07

I conducted some root cause analysis. Everything goes well until the CREATE TABLE…SELECT is about to finish. We have !trx->bulk_insert even though trx->mod_tables contains some buffered entries.

ha_innobase::extra()
case HA_EXTRA_END_ALTER_COPY:
trx = check_trx_exists(ha_thd());
if (m_prebuilt->table->skip_alter_undo) {
if (dberr_t err= trx->bulk_insert_apply()) {
m_prebuilt->table->skip_alter_undo = 0;
return convert_error_code_to_mysql(
err,
m_prebuilt->table->flags,
trx->mysql_thd);
}

trx->end_bulk_insert(*m_prebuilt->table);
trx->bulk_insert = false;

Because of this inconsistency, trx->bulk_insert_apply() did not invoke bulk_insert_apply_low(). Ultimately, the trx->mod_tables will be cleared at transaction commit. The call trx->end_bulk_insert() will clear the BULK flags in mod_tables even though bulk_store exists. (Should we add a debug assertion?)

It would seem to me that the trx_t member functions bulk_insert_apply() and end_bulk_insert() had better be merged, and a similar assertion as the one in innodb_prepare_commit_versioned() needs to be present, to catch this inconsistency.

The trx->bulk_insert had been cleared a little earlier near the following:

10.11 abc46259c6e6fc0fdf8144ad4be551495d88d22a
#0 trx_t::bulk_insert_apply_for_table (this=this@entry=0x7fdb97400c00, table=<optimized out>) at /mariadb/10.11/storage/innobase/row/row0merge.cc:5361
#1 0x000056174308273f in ha_innobase::external_lock (this=0x7fdb6c093b98, thd=0x7fdb6c002478, lock_type=0x2) at /mariadb/10.11/storage/innobase/handler/ha_innodb.cc:16103
#2 0x0000561742df25f4 in handler::ha_external_lock (this=this@entry=0x7fdb6c093b98, thd=0x7fdb6c002478, lock_type=lock_type@entry=0x2) at /mariadb/10.11/sql/handler.cc:7233
#3 0x0000561742a544b6 in handler::ha_external_unlock (this=0x7fdb6c093b98, thd=<optimized out>) at /mariadb/10.11/sql/handler.h:3461
#4 QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT (this=0x7fdb6c094a00, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1362
#5 0x0000561742a54519 in QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT (this=0x7fdb6c094a00, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1372
#6 0x0000561742a5384a in QUICK_ROR_INTERSECT_SELECT::QUICK_SELECT_WITH_RECORD::~QUICK_SELECT_WITH_RECORD (this=0x7fdb6c094d80, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.h:1623
#7 List<QUICK_ROR_INTERSECT_SELECT::QUICK_SELECT_WITH_RECORD>::delete_elements (this=0x7fdb6c027da0) at /mariadb/10.11/sql/sql_list.h:525
#8 QUICK_ROR_INTERSECT_SELECT::~QUICK_ROR_INTERSECT_SELECT (this=0x7fdb6c027d60, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1717
#9 0x0000561742a53959 in QUICK_ROR_INTERSECT_SELECT::~QUICK_ROR_INTERSECT_SELECT (this=0x7fdb6c027d60, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1723
#10 0x0000561742a56082 in SQL_SELECT::cleanup (this=0x7fdb6c096800) at /mariadb/10.11/sql/opt_range.cc:1247
#11 0x0000561742a560c9 in SQL_SELECT::~SQL_SELECT (this=this@entry=0x7fdb6c096800, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1261
#12 0x0000561742bb4ac0 in st_join_table::cleanup (this=this@entry=0x7fdb6c094da8) at /mariadb/10.11/sql/sql_select.cc:15324
#13 0x0000561742bc9588 in JOIN::cleanup (this=this@entry=0x7fdb6c03ed38, full=full@entry=0x1) at /mariadb/10.11/sql/sql_select.cc:15777
#14 0x0000561742bc9d15 in JOIN::join_free (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:15678
#15 0x0000561742be9459 in do_select (join=0x7fdb6c03ed38, procedure=<optimized out>) at /mariadb/10.11/sql/sql_select.cc:22372
#16 JOIN::exec_inner (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:4962
#17 0x0000561742be9937 in JOIN::exec (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:4740
#18 0x0000561742be748a in mysql_select (thd=thd@entry=0x7fdb6c002478, tables=0x7fdb6c018f08,
fields=@0x7fdb6c018b60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fdb6c018e98, last = 0x7fdb6c018e98, elements = 0x1}, <No data fields>}, conds=0x0, og_num=0x0, order=0x0, group=0x0,
having=0x0, proc_param=0x0, select_options=0x20081040b00, result=0x7fdb6c03ebf8, unit=0x7fdb6c006730, select_lex=0x7fdb6c0188a0) at /mariadb/10.11/sql/sql_select.cc:5220
#19 0x0000561742be7ceb in handle_select (thd=thd@entry=0x7fdb6c002478, lex=lex@entry=0x7fdb6c006658, result=result@entry=0x7fdb6c03ebf8, setup_tables_done_option=setup_tables_done_option@entry=0x0)
at /mariadb/10.11/sql/sql_select.cc:600
#20 0x0000561742c2eb37 in Sql_cmd_create_table_like::execute (this=<optimized out>, thd=0x7fdb6c002478) at /mariadb/10.11/sql/sql_table.cc:12805
#21 0x0000561742b5cf3d in mysql_execute_command (thd=thd@entry=0x7fdb6c002478, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=0x0) at /mariadb/10.11/sql/sql_parse.cc:6142


		trx->bulk_insert_apply_for_table(m_prebuilt->table);
		if (!trx->bulk_insert) {
			break;
		}
		trx->bulk_insert = false;
		trx->last_sql_stat_start.least_undo_no = trx->undo_no;

The problem here is that we assume that m_prebuilt->table would be the target table. It is not the case here; it would be one of the source tables. So, we have no log to apply to the source table and then clear the flag, even though there would remain log to apply to some tables. Maybe bulk_insert_apply_for_table() should return whether any buffered changes would remain?

I tried to create a test case along the following lines, but I failed to reproduce this so far:

--source include/have_innodb.inc

CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t1 SET a=1;INSERT INTO t2 SET a=1;

CREATE TABLE t ENGINE=InnoDB SELECT * FROM t1 NATURAL JOIN t2;

SELECT COUNT(*) FROM t;

DROP TABLE t,t1,t2;

Marko Mäkelä added a comment - 2024-10-25 06:07 I conducted some root cause analysis. Everything goes well until the CREATE TABLE…SELECT is about to finish. We have !trx->bulk_insert even though trx->mod_tables contains some buffered entries. ha_innobase::extra() case HA_EXTRA_END_ALTER_COPY: trx = check_trx_exists(ha_thd()); if (m_prebuilt->table->skip_alter_undo) { if (dberr_t err= trx->bulk_insert_apply()) { m_prebuilt->table->skip_alter_undo = 0; return convert_error_code_to_mysql( err, m_prebuilt->table->flags, trx->mysql_thd); } trx->end_bulk_insert(*m_prebuilt->table); trx->bulk_insert = false ; Because of this inconsistency, trx->bulk_insert_apply() did not invoke bulk_insert_apply_low() . Ultimately, the trx->mod_tables will be cleared at transaction commit. The call trx->end_bulk_insert() will clear the BULK flags in mod_tables even though bulk_store exists. (Should we add a debug assertion?) It would seem to me that the trx_t member functions bulk_insert_apply() and end_bulk_insert() had better be merged, and a similar assertion as the one in innodb_prepare_commit_versioned() needs to be present, to catch this inconsistency. The trx->bulk_insert had been cleared a little earlier near the following: 10.11 abc46259c6e6fc0fdf8144ad4be551495d88d22a #0 trx_t::bulk_insert_apply_for_table (this=this@entry=0x7fdb97400c00, table=<optimized out>) at /mariadb/10.11/storage/innobase/row/row0merge.cc:5361 #1 0x000056174308273f in ha_innobase::external_lock (this=0x7fdb6c093b98, thd=0x7fdb6c002478, lock_type=0x2) at /mariadb/10.11/storage/innobase/handler/ha_innodb.cc:16103 #2 0x0000561742df25f4 in handler::ha_external_lock (this=this@entry=0x7fdb6c093b98, thd=0x7fdb6c002478, lock_type=lock_type@entry=0x2) at /mariadb/10.11/sql/handler.cc:7233 #3 0x0000561742a544b6 in handler::ha_external_unlock (this=0x7fdb6c093b98, thd=<optimized out>) at /mariadb/10.11/sql/handler.h:3461 #4 QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT (this=0x7fdb6c094a00, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1362 #5 0x0000561742a54519 in QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT (this=0x7fdb6c094a00, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1372 #6 0x0000561742a5384a in QUICK_ROR_INTERSECT_SELECT::QUICK_SELECT_WITH_RECORD::~QUICK_SELECT_WITH_RECORD (this=0x7fdb6c094d80, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.h:1623 #7 List<QUICK_ROR_INTERSECT_SELECT::QUICK_SELECT_WITH_RECORD>::delete_elements (this=0x7fdb6c027da0) at /mariadb/10.11/sql/sql_list.h:525 #8 QUICK_ROR_INTERSECT_SELECT::~QUICK_ROR_INTERSECT_SELECT (this=0x7fdb6c027d60, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1717 #9 0x0000561742a53959 in QUICK_ROR_INTERSECT_SELECT::~QUICK_ROR_INTERSECT_SELECT (this=0x7fdb6c027d60, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1723 #10 0x0000561742a56082 in SQL_SELECT::cleanup (this=0x7fdb6c096800) at /mariadb/10.11/sql/opt_range.cc:1247 #11 0x0000561742a560c9 in SQL_SELECT::~SQL_SELECT (this=this@entry=0x7fdb6c096800, __in_chrg=<optimized out>) at /mariadb/10.11/sql/opt_range.cc:1261 #12 0x0000561742bb4ac0 in st_join_table::cleanup (this=this@entry=0x7fdb6c094da8) at /mariadb/10.11/sql/sql_select.cc:15324 #13 0x0000561742bc9588 in JOIN::cleanup (this=this@entry=0x7fdb6c03ed38, full=full@entry=0x1) at /mariadb/10.11/sql/sql_select.cc:15777 #14 0x0000561742bc9d15 in JOIN::join_free (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:15678 #15 0x0000561742be9459 in do_select (join=0x7fdb6c03ed38, procedure=<optimized out>) at /mariadb/10.11/sql/sql_select.cc:22372 #16 JOIN::exec_inner (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:4962 #17 0x0000561742be9937 in JOIN::exec (this=this@entry=0x7fdb6c03ed38) at /mariadb/10.11/sql/sql_select.cc:4740 #18 0x0000561742be748a in mysql_select (thd=thd@entry=0x7fdb6c002478, tables=0x7fdb6c018f08, fields=@0x7fdb6c018b60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fdb6c018e98, last = 0x7fdb6c018e98, elements = 0x1}, <No data fields>}, conds=0x0, og_num=0x0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=0x20081040b00, result=0x7fdb6c03ebf8, unit=0x7fdb6c006730, select_lex=0x7fdb6c0188a0) at /mariadb/10.11/sql/sql_select.cc:5220 #19 0x0000561742be7ceb in handle_select (thd=thd@entry=0x7fdb6c002478, lex=lex@entry=0x7fdb6c006658, result=result@entry=0x7fdb6c03ebf8, setup_tables_done_option=setup_tables_done_option@entry=0x0) at /mariadb/10.11/sql/sql_select.cc:600 #20 0x0000561742c2eb37 in Sql_cmd_create_table_like::execute (this=<optimized out>, thd=0x7fdb6c002478) at /mariadb/10.11/sql/sql_table.cc:12805 #21 0x0000561742b5cf3d in mysql_execute_command (thd=thd@entry=0x7fdb6c002478, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=0x0) at /mariadb/10.11/sql/sql_parse.cc:6142 ha_innobase::external_lock() 16103 trx->bulk_insert_apply_for_table(m_prebuilt->table); 16104 if (!trx->bulk_insert) { 16105 break ; 16106 } 16107 trx->bulk_insert = false ; 16108 trx->last_sql_stat_start.least_undo_no = trx->undo_no; The problem here is that we assume that m_prebuilt->table would be the target table. It is not the case here; it would be one of the source tables. So, we have no log to apply to the source table and then clear the flag, even though there would remain log to apply to some tables. Maybe bulk_insert_apply_for_table() should return whether any buffered changes would remain? I tried to create a test case along the following lines, but I failed to reproduce this so far: --source include/have_innodb.inc CREATE TABLE t1(a INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2(a INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 SET a=1; INSERT INTO t2 SET a=1; CREATE TABLE t ENGINE=InnoDB SELECT * FROM t1 NATURAL JOIN t2; SELECT COUNT (*) FROM t; DROP TABLE t,t1,t2;

Thirunarayanan Balathandayuthapani added a comment - 2024-10-25 09:26

--source include/have_innodb.inc

CREATE TABLE t1 (

  f1 int NOT NULL,

  f2 tinyint(1) NOT NULL,

  f3 varchar(80) NOT NULL,

  PRIMARY KEY (f1),

  KEY  (f2),

  KEY  (f3)

) ENGINE=InnoDB ;

INSERT INTO t1 VALUES(1,1,''),(2,0,''), (4,1,'e');

CREATE TABLE t2 (

  f1 int NOT NULL,

  f2 int NOT NULL,

  KEY  (f1)

) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,0),(1,0);

SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1;

CREATE TABLE t engine=innodb

SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1 ;

select count(*) from t;

drop table t1,t2,t;

Minimal test case to repeat the issue.

We can workaround this issue by doing SET GLOBAL innodb_alter_copy_bulk=0;

Thirunarayanan Balathandayuthapani added a comment - 2024-10-25 09:26 --source include/have_innodb.inc CREATE TABLE t1 ( f1 int NOT NULL, f2 tinyint(1) NOT NULL, f3 varchar(80) NOT NULL, PRIMARY KEY (f1), KEY (f2), KEY (f3) ) ENGINE=InnoDB ; INSERT INTO t1 VALUES(1,1,''),(2,0,''), (4,1,'e'); CREATE TABLE t2 ( f1 int NOT NULL, f2 int NOT NULL, KEY (f1) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,0),(1,0); SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1; CREATE TABLE t engine=innodb SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1 ; select count(*) from t; drop table t1,t2,t; Minimal test case to repeat the issue. We can workaround this issue by doing SET GLOBAL innodb_alter_copy_bulk=0;

Thirunarayanan Balathandayuthapani added a comment - 2024-10-27 20:58

https://github.com/MariaDB/server/pull/3598

Thirunarayanan Balathandayuthapani added a comment - 2024-10-27 20:58 https://github.com/MariaDB/server/pull/3598

MariaDB Server

Bulk insert fails to apply buffered operation during CREATE..SELECT statment

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration