[MDEV-19907] In Partiitioned table using HASH, AUTOINCREMENT value reverts to original CREATE TABLE value when INSERTing explicitly to empty partition immediately after ANALYZE TABLE. Created: 2019-06-29  Updated: 2020-08-25  Resolved: 2019-07-04

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - MyISAM
Affects Version/s: 10.3.7, 10.3.8, 10.3.9, 10.3.10, 10.3.11, 10.3.12, 10.3.13, 10.3.14, 10.3.15, 10.3.16, 10.4.6, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Juan Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: 10.3, 10.4, MyISAM, partitioning
Environment:

CentOS 7.5

Attachments: Text File 29736_10-4-crash.log    
Issue Links:
Duplicate
duplicates MDEV-18244 Server crashes in ha_innobase::update... Closed

 Description   

INSERTing explicitly to specific partition immediately after running ANALYZE TABLE in a MyISAM or Aria table partitioned by HASH with an AUTOINCREMENT column causes the AUTOINCREMENT value to revert to the original CREATE TABLE value, resulting in duplicate values.

This happens when the table is MyISAM, has at least 2 partitions, and the inserts are done first to one partition, and immediately after running ANALYZE TABLE, INSERTs are done to another partition, and all inserts are done to specific partitions of a table partitioned by HASH.

Any statement that touches table metadata, such as a SELECT from the table or a SHOW CREATE TABLE, makes it so the subsequent INSERT works correctly, but an INSERT into an empty partition, done immediately after an ANALYZE TABLE, always produces this error.

Changing engines to either Aria or InnoDB has no effect in 10.2 servers (they still work correctly). However, in 10.3 and 10.4 servers using either Aria or InnoDB for the test results in a server crash on the first insert operation.

====Test Case====

drop table if exists example_table ;
 
CREATE TABLE example_table (
 
  key1_id bigint(20) NOT NULL,
 
  key2_id bigint(20) NOT NULL,
 
  independent_id int(11) NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (key1_id,key2_id) USING BTREE,
 
  KEY independent_id (independent_id) USING BTREE
 
) ENGINE=MyISAM AUTO_INCREMENT=1312895095 DEFAULT CHARSET=latin1
 
 PARTITION BY HASH (key1_id)
 
(PARTITION p0001 ENGINE = MyISAM,
 
 PARTITION p0002 ENGINE = MyISAM);
 
 
 
insert ignore into example_table partition(p0001) (
 
  key1_id,
 
  key2_id
 
)
 
values
 
(0,1),
 
(0,2),
 
(0,3),
 
(0,4),
 
(0,5),
 
(0,6),
 
(0,7),
 
(0,8)
 
;
 
insert ignore into example_table partition(p0002) (
 
  key1_id,
 
  key2_id
 
)
 
values
 
(1,1),
 
(1,2),
 
(1,3),
 
(1,4),
 
(1,5)
 
;
 
 
 
 
 
analyze table example_table;
 
 
 
--show create table example_table\G;
 
-- select 'a' from example_table limit 1;
 
 
 
insert ignore into example_table partition(p0001) (
 
  key1_id,
 
  key2_id
 
)
 
values
 
(2,1),
 
(2,2),
 
(2,3),
 
(2,4),
 
(2,5)
 
;
 
 
 
select independent_id, count(*) from example_table
 
group by independent_id;



 Comments   
Comment by Juan [ 2019-06-29 ]

The attached log shows the crash that results when running the same test case on 10.4.6 with Aria or InnoDB engines in place of MyISAM. Same result in 10.3. In 10.2, Aria & InnoDB run the test correctly with no duplicate AUTOINCREMENT values, as does MyISAM.

Comment by Alice Sherepa [ 2019-07-04 ]

Thanks! I repeated the crash on 10.3, 10.4, it seems to be the same bug as MDEV-18244:

10.3 099007c3c92d140562577

 
==24231==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000008 (pc 0x5594d2b16256 bp 0x7f9862d4b900 sp 0x7f9862d4b850 T27)
 
    #0 0x5594d2b16255 in ha_innobase::update_thd(THD*)  /10.3/storage/innobase/handler/ha_innodb.cc:2958
 
    #1 0x5594d2af35fe in ha_innobase::info_low(unsigned int, bool)  /10.3/storage/innobase/handler/ha_innodb.cc:13923
 
    #2 0x5594d2af52f0 in ha_innobase::info(unsigned int)  /10.3/storage/innobase/handler/ha_innodb.cc:14237
 
    #3 0x5594d32baf55 in ha_partition::info(unsigned int)  /10.3/sql/ha_partition.cc:8157
 
    #4 0x5594d32c7f0c in ha_partition::update_next_auto_inc_val()  /10.3/sql/ha_partition.cc:10326
 
    #5 0x5594d329c6aa in ha_partition::write_row(unsigned char*)  /10.3/sql/ha_partition.cc:4268
 
    #6 0x5594d22d05f3 in handler::ha_write_row(unsigned char*)  /10.3/sql/handler.cc:6420
 
    #7 0x5594d1b34aea in write_record(THD*, TABLE*, st_copy_info*)  /10.3/sql/sql_insert.cc:2030
 
    #8 0x5594d1b2e1d5 in mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool)  /10.3/sql/sql_insert.cc:1071
 
    #9 0x5594d1bc4a8d in mysql_execute_command(THD*)  /10.3/sql/sql_parse.cc:4735
 
    #10 0x5594d1bda295 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool)  /10.3/sql/sql_parse.cc:8105
 
    #11 0x5594d1bb4d9d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool)  /10.3/sql/sql_parse.cc:1858
 
    #12 0x5594d1bb1f01 in do_command(THD*)  /10.3/sql/sql_parse.cc:1404
 
    #13 0x5594d1f08ec1 in do_handle_one_connection(CONNECT*)  /10.3/sql/sql_connect.cc:1402
 
    #14 0x5594d1f0889e in handle_one_connection  /10.3/sql/sql_connect.cc:1308
 
    #15 0x5594d3231cc0 in pfs_spawn_thread  /10.3/storage/perfschema/pfs.cc:1862
 
    #16 0x7f987a1eb6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #17 0x7f987968041c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Wrong auto_increment values while using MyIsam:

CREATE TABLE t1 (
 
    id1 int NOT NULL, 
    id2 int NOT NULL,
 
    id int(11) NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (id1,id2), 
    KEY id (id)) 
ENGINE=myisam AUTO_INCREMENT=1 PARTITION BY HASH (id1) (PARTITION p0001, PARTITION p0002);
 
 
 
INSERT INTO t1 partition(p0002) (id1,id2) VALUES (1,1), (1,2), (1,3);
 
 
 
ANALYZE TABLE t1;
 
#show table status like 't1';
 
 
 
INSERT INTO t1 partition(p0001) ( id1, id2) VALUES (2,1), (2,2), (2,3), (2,4), (2,5) ;
 
SELECT id, count(*) FROM t1 GROUP BY id;


MariaDB [test]> SELECT id, count(*) FROM t1 GROUP BY id;
 
+----+----------+
 
| id | count(*) |
 
+----+----------+
 
|  1 |        2 |
 
|  2 |        2 |
 
|  3 |        2 |
 
|  4 |        1 |
 
|  5 |        1 |
 
+----+----------+
 
5 rows in set (0.000 sec)

Comment by Juan [ 2019-07-04 ] 

text ~ "hash" AND text ~ "autoincrement" ORDER BY created DESC

If this issue duplicates another, it is not appearing in the search above.

Comment by Juan [ 2019-07-04 ]

Found it!

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