[MDEV-14046] Allow ALGORITHM=INPLACE for 10.1 tables that contain virtual columns - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Storage Engine - InnoDB
Labels:

Sprint:
10.2.11

Description

With the dataset of ~~MDEV-14023~~, ALTER TABLE will fail in InnoDB with a misleading error message:

ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `AttendDet_ID`

All of the following statements will lead to the above response:

ALTER TABLE AttendDet ADD INDEX(counter), LOCK=NONE;

ALTER TABLE AttendDet DROP INDEX IX_AttendDet_Shift, LOCK=NONE;

ALTER TABLE AttendDet DROP INDEX IX_AttendDet_Shift, ALGORITHM=INPLACE;

We must fix this (insert the missing records to SYS_VIRTUAL), so that early users of virtual columns in 10.1 will be able to quickly create indexes on the virtual columns in 10.2 (~~MDEV-5800~~), and to instantly add columns in 10.3 (~~MDEV-11369~~), among other things.

Attachments

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

data.tar.gz
1.12 MB
2018-09-14 13:25

Issue Links

is blocked by

MDEV-14023 10.1 InnoDB tables with virtual columns cannot be accessed in 10.2

Closed

MDEV-17199 Assertion `pos < table->n_v_def' failed in dict_table_get_nth_v_col after upgrade from 10.1/10.0 to 10.2

Closed

MDEV-18084 Server crashes in row_upd_changes_some_index_ord_field_binary or Assertion `pos < index->n_def' failed in dict_index_get_nth_field upon UPDATE after upgrade from 10.1/10.0

Closed

MDEV-19027 create_table_def fails when virtual column is present between stored columns

Closed

MDEV-19066 AddressSanitizer: use-after-poison in innobase_build_col_map after upgrade from 10.1

Closed

relates to

MDEV-11424 Instant ALTER TABLE of failure-free record format changes

Closed

MDEV-14341 Allow LOCK=NONE in table-rebuilding ALTER when indexed virtual columns exist

Open

MDEV-17468 Avoid table rebuild on operations on generated columns

Stalled

MDEV-5800 indexes on virtual (not materialized) columns

Closed

MDEV-15476 Inplace algorithm doesn't support changing virtual column datatype

Stalled

MDEV-16332 Allow ALGORITHM=NOCOPY or INSTANT for changes of virtual column type

Confirmed

MDEV-19214 Virtual column type cannot be converted from one to another - unhelpful error message

Open

(7 relates to)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2017-10-11 07:59

Marko Mäkelä made changes - 2017-10-11 07:59

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-5800~~ [ ~~MDEV-5800~~ ]

Marko Mäkelä made changes - 2017-10-11 08:01

Link

This issue is blocked by ~~MDEV-14023~~ [ ~~MDEV-14023~~ ]

Marko Mäkelä made changes - 2017-11-07 14:57

Fix Version/s		10.3 [ 22126 ]
Sprint		10.2.11 [ 203 ]

Marko Mäkelä made changes - 2017-11-07 14:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Marko Mäkelä made changes - 2018-09-13 13:03

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Elena Stepanova made changes - 2018-09-14 13:25

Attachment

data.tar.gz [ 46456 ]

Elena Stepanova made changes - 2018-09-14 14:18

Link

This issue is blocked by ~~MDEV-17199~~ [ ~~MDEV-17199~~ ]

Elena Stepanova made changes - 2018-09-14 14:18

Comment

[ Failure upon any upgrade from 10.1/10.0 with virtual columns present:
{noformat:title=bb-10.2-marko 87b16b450d4}
mysqld: /data/src/bb-10.2-marko/storage/innobase/include/dict0dict.ic:490: dict_v_col_t* dict_table_get_nth_v_col(const dict_table_t*, ulint): Assertion `pos < table->n_v_def' failed.
180914 16:17:47 [ERROR] mysqld got signal 6 ;

#7 0x00007f9ae5e7fee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#8 0x000055d1d9b7d75b in dict_table_get_nth_v_col (table=0x7f9a740343c8, pos=0) at /data/src/bb-10.2-marko/storage/innobase/include/dict0dict.ic:490
#9 0x000055d1d9b8a730 in build_template_field (prebuilt=0x7f9a7402c638, clust_index=0x7f9a74035748, index=0x7f9a74035748, table=0x7f9a74020eb0, field=0x7f9a7400b658, i=0, v_no=0) at /data/src/bb-10.2-marko/storage/innobase/handler/ha_innodb.cc:7511
#10 0x000055d1d9b8c2f3 in ha_innobase::build_template (this=0x7f9a74021ab8, whole_row=true) at /data/src/bb-10.2-marko/storage/innobase/handler/ha_innodb.cc:8010
#11 0x000055d1d9b99865 in ha_innobase::check (this=0x7f9a74021ab8, thd=0x7f9a74000b00, check_opt=0x7f9a740055e8) at /data/src/bb-10.2-marko/storage/innobase/handler/ha_innodb.cc:14590
#12 0x000055d1d987dd39 in handler::ha_check (this=0x7f9a74021ab8, thd=0x7f9a74000b00, check_opt=0x7f9a740055e8) at /data/src/bb-10.2-marko/sql/handler.cc:3973
#13 0x000055d1d975cdad in mysql_admin_table(THD *, TABLE_LIST *, HA_CHECK_OPT *, const char *, thr_lock_type, bool, bool, uint, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *), struct {...}, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *)) (thd=0x7f9a74000b00, tables=0x7f9a74011080, check_opt=0x7f9a740055e8, operator_name=0x55d1da0c4fac "check", lock_type=TL_READ_NO_INSERT, org_open_for_modify=false, repair_table_use_frm=false, extra_open_options=32, prepare_func=0x0, operator_func=(int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x55d1d987dc14 <handler::ha_check(THD*, st_ha_check_opt*)>, view_operator_func=0x55d1d96f82d5 <view_check(THD*, TABLE_LIST*, st_ha_check_opt*)>) at /data/src/bb-10.2-marko/sql/sql_admin.cc:798
#14 0x000055d1d975ef5e in Sql_cmd_check_table::execute (this=0x7f9a74011698, thd=0x7f9a74000b00) at /data/src/bb-10.2-marko/sql/sql_admin.cc:1343
#15 0x000055d1d9607dd2 in mysql_execute_command (thd=0x7f9a74000b00) at /data/src/bb-10.2-marko/sql/sql_parse.cc:6225
#16 0x000055d1d960cbbe in mysql_parse (thd=0x7f9a74000b00, rawbuf=0x7f9a74010fb8 "check table t1", length=14, parser_state=0x7f9ae4152250, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-marko/sql/sql_parse.cc:8009
#17 0x000055d1d95fa63c in dispatch_command (command=COM_QUERY, thd=0x7f9a74000b00, packet=0x7f9a740191e1 "", packet_length=14, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-marko/sql/sql_parse.cc:1824
#18 0x000055d1d95f8f9f in do_command (thd=0x7f9a74000b00) at /data/src/bb-10.2-marko/sql/sql_parse.cc:1378
#19 0x000055d1d974a9ae in do_handle_one_connection (connect=0x55d1dddab970) at /data/src/bb-10.2-marko/sql/sql_connect.cc:1335
#20 0x000055d1d974a73b in handle_one_connection (arg=0x55d1dddab970) at /data/src/bb-10.2-marko/sql/sql_connect.cc:1241
#21 0x00007f9ae7b56494 in start_thread (arg=0x7f9ae4153700) at pthread_create.c:333
#22 0x00007f9ae5f3c93f in clone () from /lib/x86_64-linux-gnu/libc.so.6
{noformat}

An example of 10.1 datadir is attached (state of the datadir after normal shutdown on 10.1, before any attempt to run 10.2 on it): [^data.tar.gz]
- start 10.2 on the datadir, all default options should be sufficient;
- run {{CHECK TABLE test.t1}}
]

Marko Mäkelä made changes - 2018-09-26 12:24

Link

This issue blocks ~~MDEV-11424~~ [ ~~MDEV-11424~~ ]

Marko Mäkelä made changes - 2019-02-20 10:15

Fix Version/s		10.4 [ 22408 ]
Affects Version/s		10.4 [ 22408 ]

Marko Mäkelä made changes - 2019-03-18 07:24

Link

This issue is blocked by ~~MDEV-18084~~ [ ~~MDEV-18084~~ ]

Marko Mäkelä made changes - 2019-04-01 14:46

NRE Projects

RM_102ES_CANDIDATE RM_105_CANDIDATE

Geoff Montee (Inactive) made changes - 2019-04-20 21:05

Link

This issue relates to MDEV-16332 [ MDEV-16332 ]

Geoff Montee (Inactive) made changes - 2019-04-20 21:06

Link

This issue relates to MDEV-19214 [ MDEV-19214 ]

Geoff Montee (Inactive) made changes - 2019-04-20 21:06

Link

This issue relates to MDEV-15476 [ MDEV-15476 ]

Geoff Montee (Inactive) made changes - 2019-04-20 21:11

Link

This issue relates to MDEV-14341 [ MDEV-14341 ]

Geoff Montee (Inactive) made changes - 2019-04-20 21:15

Link

This issue relates to MDEV-17468 [ MDEV-17468 ]

Marko Mäkelä made changes - 2019-04-30 08:33

Link

This issue is blocked by ~~MDEV-19066~~ [ ~~MDEV-19066~~ ]

Marko Mäkelä made changes - 2019-04-30 08:33

Link

This issue is blocked by ~~MDEV-19027~~ [ ~~MDEV-19027~~ ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 83000 ]

MariaDB v4 [ 144466 ]

Marko Mäkelä made changes - 2022-01-14 19:30

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Won't Fix [ 2 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Nuno made changes - 2022-05-01 20:32

Comment

[ Hi [~marko]
With your last comment, are you saying that since MariaDB 10.2 we can ALTER TABLE with ALGORITHM=INPLACE ?

I'm on MariaDB 10.5, and I cannot use ALGORITHM=INPLACE if the table has a virtual generated column (indexed).

Thanks much. ]

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 1 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2017-10-11 07:59

Updated:: 2022-05-02 11:22

Resolved:: 2022-01-14 19:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration