[MDEV-29758] CREATE OR REPLACE .. SELECT of Federated table doesn't store selected values Created: 2022-10-10  Updated: 2023-05-18  Resolved: 2023-01-26

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Storage Engine - Federated
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-25292 Atomic CREATE OR REPLACE TABLE Stalled
Relates

 Description    

--source include/have_innodb.inc
 
 
 
install soname 'ha_federatedx';
 
 
 
create table t (a int) engine=InnoDB;
 
eval create or replace table tf connection 'mysql://root@127.0.0.1:$MASTER_MYPORT/test/t' engine=Federated as select 1 as a;
 
select * from tf;
 
 
 
# Cleanup
 
drop table tf, t;
 
uninstall soname 'ha_federatedx';

bb-10.11-midenok 6b5f76fcaaf

 
select * from tf;
 
a
 
drop table tf, t;

The expected result is naturally a row 1.

Also reproducible on 10.11. The problem appeared there with 

commit 93c8252f02faa8ad8dc5f005e52f1990c29d4a0d (HEAD)
 
Author: Aleksey Midenkov
 
Date:   Wed Aug 31 11:55:04 2022 +0300
 
 
 
    MDEV-25292 Atomic CREATE OR REPLACE TABLE

According to the general log, the connection to remote exits too early and without a COMMIT. Before the change, it was doing this:

                     6 Connect  root@localhost on test using TCP/IP
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    SET AUTOCOMMIT=0
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    SAVEPOINT save1
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    INSERT INTO `t` (`a`)  VALUES  (1)
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    COMMIT
 
                     4 Query    select * from tf
 
                     6 Query    set time_zone='+00:00'
 
...

Now it's doing this:

                     6 Connect  root@localhost on test using TCP/IP
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    SET AUTOCOMMIT=0
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    SAVEPOINT save1
 
                     6 Query    set time_zone='+00:00'
 
                     6 Query    INSERT INTO `t` (`a`)  VALUES  (1)
 
                     6 Quit



 Comments   
Comment by Aleksey Midenkov [ 2022-10-10 ]

Please review bb-10.11-midenok

Comment by Roel Van de Paar [ 2022-10-25 ] 

--source include/have_innodb.inc
 
 
 
INSTALL SONAME 'ha_federatedx.so';
 
CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
 
eval CREATE TABLE tf CONNECTION='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1' ENGINE=FEDERATED AS SELECT 1 AS a;
 
 
 
# Cleanup
 
DROP TABLE t1,tf;
 
UNINSTALL SONAME 'ha_federatedx';

Leads to:

10.11.0 00e48d15524f66c31f9d050d25581c95facb7045 (Debug)

 
mysqld: /test/bb-10.11-midenok_dbg/sql/protocol.cc:617: void Protocol::end_statement(): Assertion `0' failed.

10.11.0 00e48d15524f66c31f9d050d25581c95facb7045 (Debug)

 
Core was generated by `/test/midenok-bb-MD251022-mariadb-10.11.0-linux-x86_64-dbg/bin/mysqld --no-defa'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x152f6e780700 (LWP 2637207))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x0000152f8b11f859 in __GI_abort () at abort.c:79
 
#2  0x0000152f8b11f729 in __assert_fail_base (fmt=0x152f8b2b5588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x5585e4131438 "0", file=0x5585e3f63960 "/test/bb-10.11-midenok_dbg/sql/protocol.cc", line=617, function=<optimized out>) at assert.c:92
 
#3  0x0000152f8b130fd6 in __GI___assert_fail (assertion=assertion@entry=0x5585e4131438 "0", file=file@entry=0x5585e3f63960 "/test/bb-10.11-midenok_dbg/sql/protocol.cc", line=line@entry=617, function=function@entry=0x5585e3f64178 "void Protocol::end_statement()") at assert.c:101
 
#4  0x00005585e32daf6e in Protocol::end_statement (this=0x152f2c001358) at /test/bb-10.11-midenok_dbg/sql/protocol.cc:617
 
#5  0x00005585e33c8d44 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x152f2c000d48, packet=<optimized out>, packet@entry=0x152f2c00af09 "CREATE TABLE tf ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:10983/test/t1' ENGINE=FEDERATED AS SELECT 1 AS a", packet_length=packet_length@entry=116, blocking=blocking@entry=true) at /test/bb-10.11-midenok_dbg/sql/sql_parse.cc:2401
 
#6  0x00005585e33c96b0 in do_command (thd=0x152f2c000d48, blocking=blocking@entry=true) at /test/bb-10.11-midenok_dbg/sql/sql_parse.cc:1407
 
#7  0x00005585e35268e1 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x5585e608c048, put_in_cache=put_in_cache@entry=true) at /test/bb-10.11-midenok_dbg/sql/sql_connect.cc:1416
 
#8  0x00005585e3526db0 in handle_one_connection (arg=0x5585e608c048) at /test/bb-10.11-midenok_dbg/sql/sql_connect.cc:1318
 
#9  0x0000152f8b630609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#10 0x0000152f8b21c133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

This is caused by MDEV-25292. Before it did not crash.
This is still present in the latest version of bb-10.11-midenok as of today, as per above stack and revision.
As the testcase is almost a 100% match of the testcase in this bug (without the "OR REPLACE"), and the review is not complete yet, adding it here.
Happy to create a new bug instead if that is preferred or if this proves to be unrelated. Let me know.

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