[MDEV-7355] CREATE OR REPLACE with MyISAM + OQGRAPH Created: 2014-12-20  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - OQGRAPH
Affects Version/s: 10.0
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Andrew McDonnell
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7354 CREATE OR REPLACE only drops InnoDB t... Open

 Description   

I don't know if this should be a new bug or a new comment in MDEV-7354.

1) create & populate MyISAM table
2) create an OQGRAPH table based on the first table
3) select from OQGRAPH
4) CREATE OR REPLACE MyISAM
5) select from the new MyISAM

Result: error. (This doesn't happen if you don't have an OQGRAPH table)

CREATE TABLE ttt (
 
	a INT NOT NULL,
 
	b INT NOT NULL
 
) ENGINE = MyISAM;
 
 
 
INSERT INTO ttt (a, b) VALUES (1, 10), (2, 10), (3, 10);
 
 
 
CREATE TABLE oq (
 
  latch VARCHAR(32) NULL,
 
  origid BIGINT UNSIGNED NULL,
 
  destid BIGINT UNSIGNED NULL,
 
  weight DOUBLE NULL,
 
  seq BIGINT UNSIGNED NULL,
 
  linkid BIGINT UNSIGNED NULL,
 
  KEY (latch, origid, destid) USING HASH,
 
  KEY (latch, destid, origid) USING HASH
 
) 
	ENGINE = OQGRAPH 
	DATA_TABLE = 'ttt'
 
	ORIGID = 'a'
 
	DESTID = 'b';
 
 
 
SELECT *
 
	FROM oq
 
	WHERE latch = 'dijkstras'
 
		AND origid = 1
 
		AND destid = 10;
 
 
 
SELECT * FROM ttt;
 
 
 
CREATE OR REPLACE TABLE ttt (
 
	a INT,
 
	b INT
 
) ENGINE = MyISAM;
 
 
 
SELECT * FROM ttt;



 Comments   
Comment by Federico Razzoli [ 2014-12-20 ]

Try the above test with Aria, and you'll get another anomaly (see the last query result).

Comment by Elena Stepanova [ 2014-12-20 ]

Interestingly, I am getting different errors when I run it in a client vs MTR.

Client, MyISAM:

MariaDB [test]> SELECT * FROM ttt;
 
ERROR 1030 (HY000): Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM

Client, Aria:

MariaDB [test]> SELECT * FROM ttt;
 
+------+------+
 
| a    | b    |
 
+------+------+
 
| NULL | NULL |
 
| NULL | NULL |
 
| NULL | NULL |
 
+------+------+
 
3 rows in set (0.00 sec)

MTR, MyISAM (and same for Aria):

 At line 33: query 'CREATE OR REPLACE TABLE ttt (
 
a INT,
 
b INT
 
) ENGINE = MyISAM' failed: 156: MyISAM table 'ttt' is in use (most likely by a MERGE table). Try FLUSH TABLES.

And if I actually do FLUSH TABLES before CREATE OR REPLACE, everything works all right – with Aria, MyISAM, and InnoDB.

Comment by Elena Stepanova [ 2014-12-20 ]

See also MDEV-7354.

Comment by Federico Razzoli [ 2014-12-21 ]

I was able to crash the server with this schema:

OQGRAPH -> MERGE -> MyISAM

...and a CREATE OR REPLACE (without FLUSH) on MyISAM.

CREATE TABLE t_my (
 
	a BIGINT NOT NULL,
 
	b BIGINT NOT NULL
 
) ENGINE = MyISAM;
 
 
 
INSERT INTO t_my (a, b) VALUES (1, 2), (2, 3), (3, 10);
 
 
 
CREATE TABLE t_mer (
 
	a BIGINT NOT NULL,
 
	b BIGINT NOT NULL
 
) 
	ENGINE = MERGE
 
	UNION (t_my)
 
	;
 
 
 
SELECT * FROM t_mer;
 
 
 
CREATE TABLE oq (
 
  latch VARCHAR(32) NULL,
 
  origid BIGINT UNSIGNED NULL,
 
  destid BIGINT UNSIGNED NULL,
 
  weight DOUBLE NULL,
 
  seq BIGINT UNSIGNED NULL,
 
  linkid BIGINT UNSIGNED NULL,
 
  KEY (latch, origid, destid) USING HASH,
 
  KEY (latch, destid, origid) USING HASH
 
) 
	ENGINE = OQGRAPH 
	DATA_TABLE = 't_mer'
 
	ORIGID = 'a'
 
	DESTID = 'b';
 
 
 
SELECT *
 
FROM oq
 
WHERE latch='breadth_first'
 
AND origid = 20
 
AND destid = 30
 
;
 
 
 
CREATE OR REPLACE TABLE t_my (
 
	a BIGINT NOT NULL,
 
	b BIGINT NOT NULL
 
) ENGINE = MyISAM;

Note that, if you omit the OQGRAPH table, everything works as expected.

Comment by Elena Stepanova [ 2014-12-21 ]

Stack trace from 10.0 revno 4543 (adding to make it searchable):

#3  <signal handler called>
 
#4  0x0000000000e039ce in ha_myisammrg::add_children_list (this=0x7f74ced51e88) at 10.0/storage/myisammrg/ha_myisammrg.cc:465
 
#5  0x0000000000e05995 in ha_myisammrg::extra (this=0x7f74ced51e88, operation=HA_EXTRA_ADD_CHILDREN_LIST) at 10.0/storage/myisammrg/ha_myisammrg.cc:1340
 
#6  0x00007f74d69b05e3 in ha_oqgraph::extra (this=0x7f74cede9088, operation=HA_EXTRA_ADD_CHILDREN_LIST) at 10.0/storage/oqgraph/ha_oqgraph.cc:1120
 
#7  0x000000000061dfca in open_and_process_table (thd=0x7f74d172f070, lex=0x7f74d1732cb8, tables=0x7f74cec142e0, counter=0x7f74d8e1f684, flags=0, prelocking_strategy=0x7f74d8e1f6c0, has_prelocking_list=false, ot_ctx=0x7f74d8e1f530, new_frm_mem=0x7f74d8e1f570) at 10.0/sql/sql_base.cc:4041
 
#8  0x000000000061ed13 in open_tables (thd=0x7f74d172f070, start=0x7f74d8e1f640, counter=0x7f74d8e1f684, flags=0, prelocking_strategy=0x7f74d8e1f6c0) at 10.0/sql/sql_base.cc:4470
 
#9  0x000000000061fd34 in open_and_lock_tables (thd=0x7f74d172f070, tables=0x7f74cec142e0, derived=true, flags=0, prelocking_strategy=0x7f74d8e1f6c0) at 10.0/sql/sql_base.cc:5113
 
#10 0x0000000000614259 in open_and_lock_tables (thd=0x7f74d172f070, tables=0x7f74cec142e0, derived=true, flags=0) at 10.0/sql/sql_base.h:486
 
#11 0x0000000000682faf in execute_sqlcom_select (thd=0x7f74d172f070, all_tables=0x7f74cec142e0) at 10.0/sql/sql_parse.cc:5206
 
#12 0x000000000067b542 in mysql_execute_command (thd=0x7f74d172f070) at 10.0/sql/sql_parse.cc:2545
 
#13 0x00000000006859bd in mysql_parse (thd=0x7f74d172f070, rawbuf=0x7f74cec14088 "SELECT *\nFROM oq\nWHERE latch='breadth_first'\nAND origid = 20\nAND destid = 30", length=76, parser_state=0x7f74d8e20610) at 10.0/sql/sql_parse.cc:6407
 
#14 0x00000000006787b2 in dispatch_command (command=COM_QUERY, thd=0x7f74d172f070, packet=0x7f74d27f6071 "SELECT *\nFROM oq\nWHERE latch='breadth_first'\nAND origid = 20\nAND destid = 30\n", packet_length=77) at 10.0/sql/sql_parse.cc:1299
 
#15 0x0000000000677b57 in do_command (thd=0x7f74d172f070) at 10.0/sql/sql_parse.cc:996
 
#16 0x0000000000794877 in do_handle_one_connection (thd_arg=0x7f74d172f070) at 10.0/sql/sql_connect.cc:1375
 
#17 0x00000000007945ca in handle_one_connection (arg=0x7f74d172f070) at 10.0/sql/sql_connect.cc:1289
 
#18 0x0000000000ccda5e in pfs_spawn_thread (arg=0x7f74d0b67bf0) at 10.0/storage/perfschema/pfs.cc:1860
 
#19 0x00007f74d8a0db50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#20 0x00007f74d6cc420d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Generated at Thu Feb 08 07:18:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.