Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
Description
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines:
Manage .frm
Storage engine data dictionary
Binary log
This will solve the following things:
- .frm context and storage engine dictionary is always in sync
- no #sql-xxxx files if server crashes during alter table
- binary log will always contain the DDL statement if the DDL successfully and committed to the storage engine. If the DDL
was not successful, all traces of the DDL (like temporary files) will be deleted/rolled back.
For example, if an alter table was successfully done and committed and we get a crash just before the write to the binary
log, crash recovery will write the ALTER TABLE command to the binary log. In the case of a crash in the middle of a multi-table drop, crash recovery will write to the binary log those tables that was actually dropped.
The cost of doing the above should not be more than 1-4 sync per DDL.
High level architecture
When doing a DDL, store somewhere (either trough write_ddl_log_entry() or
some new method):
- Operation
- Number of tables
- Table name
- Table id for original table
- Table id for resulting table (in case of rename)
- sql command (for binary log)
If there is no table id (for example for CSV) we would use the
timestamp of the files.
With the above information we would be able to continue from the place
where the operation failed.
Low level architecture is done for each sub project
Some requirements for a storage engine to be 'Atomic compliment':
- drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the
operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the
ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between
the inplace alter table commit and the rename of the .frm file.
Supported engines in 10.6 are (among others)
- InnoDB
- MyRocks
- Aria (transactional and non transactional tables)
- MyISAM
- Any engine that has only one table file (as then drop and rename will be atomic)
Attachments
Issue Links
- blocks
-
MDEV-4259 transactional DDL
-
- Open
-
-
-
- Open
-
-
-
- Open
-
-
-
- Open
-
-
-
- In Review
-
-
MDEV-21652
FK migration from old version
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- causes
-
MDEV-25683 Atomic DDL: With innodb_force_recovery=3 InnoDB: Trying to load index but the index tree has been freed
-
- Closed
-
- includes
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-24755
Rebase FK tasks on top of Atomic DDL
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-21053
Crash safety of foreign key DDL
-
- Stalled
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue is part of MDEV-11655 [ MDEV-11655 ] |
| Description |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a dll, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CVS) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture (to be done before implementation starts) Describe in detail the source changes needed for: CREATE TABLE CREATE TABLE ... SELECT DROP TABLE t1,t2,t3,t4; RENAME TABLE t1 as t2, t2 as t1; ALTER TABLE |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture (to be done before implementation starts) Describe in detail the source changes needed for: CREATE TABLE CREATE TABLE ... SELECT DROP TABLE t1,t2,t3,t4; RENAME TABLE t1 as t2, t2 as t1; ALTER TABLE |
| Link | This issue blocks MDEV-16417 [ MDEV-16417 ] |
| Assignee | Michael Widenius [ monty ] |
| Epic Link | PT-76 [ 68557 ] |
| Epic Link | PT-76 [ 68557 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Link |
This issue relates to |
| Fix Version/s | 10.5 [ 23123 ] |
| Link |
This issue relates to |
| Link | This issue is blocked by MDEV-20677 [ MDEV-20677 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Link | This issue relates to MDEV-16417 [ MDEV-16417 ] |
| Link | This issue blocks MDEV-16417 [ MDEV-16417 ] |
| Link | This issue blocks MDEV-11633 [ MDEV-11633 ] |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Link | This issue relates to MDEV-22056 [ MDEV-22056 ] |
| Assignee | Michael Widenius [ monty ] | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] |
| Assignee | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] | Michael Widenius [ monty ] |
| Link |
This issue relates to |
| Link |
This issue blocks |
| Link | This issue blocks MDEV-11655 [ MDEV-11655 ] |
| Link | This issue is part of MDEV-11655 [ MDEV-11655 ] |
| Rank | Ranked higher |
| Rank | Ranked higher |
| Link |
This issue is blocked by |
| Link |
This issue is blocked by |
| Link |
This issue blocks |
| Link | This issue relates to MDEV-21053 [ MDEV-21053 ] |
| Link |
This issue relates to |
| Link | This issue blocks MDEV-15140 [ MDEV-15140 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Link |
This issue includes |
| Link |
This issue includes |
| Link |
This issue includes |
| Link |
This issue includes |
| Link |
This issue includes |
I do not think that DDL operations in InnoDB can be truly crash-safe before MDEV-18518 has been implemented. See MDEV-24569 for my analysis of corruption that was caused by killing the server during DROP TABLE.
| Link |
This issue relates to |
| Link |
This issue is blocked by |
As noted in MDEV-24589, I think that we can implement a lighter-weight fix to have crash-safe DROP TABLE in InnoDB.
| Link |
This issue includes |
| Link |
This issue includes |
| Link |
This issue relates to |
| Link |
This issue includes |
| Link | This issue blocks MDEV-16417 [ MDEV-16417 ] |
| Link | This issue relates to MDEV-16417 [ MDEV-16417 ] |
| Link | This issue blocks MDEV-21652 [ MDEV-21652 ] |
| Description |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture (to be done before implementation starts) Describe in detail the source changes needed for: CREATE TABLE CREATE TABLE ... SELECT DROP TABLE t1,t2,t3,t4; RENAME TABLE t1 as t2, t2 as t1; ALTER TABLE |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture is done for each sub project Some requirements for a storage engine to be 'Atomic compliment': - drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between the inplace alter table commit and the rename of the .frm file. Supported engines in 10.6 are (among others) - InnoDB - Aria (transactional and non transactional tables) - MyISAM - Any engine that has only one table file (as then drop and rename will be atomic) |
| Description |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture is done for each sub project Some requirements for a storage engine to be 'Atomic compliment': - drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between the inplace alter table commit and the rename of the .frm file. Supported engines in 10.6 are (among others) - InnoDB - Aria (transactional and non transactional tables) - MyISAM - Any engine that has only one table file (as then drop and rename will be atomic) |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture is done for each sub project Some requirements for a storage engine to be 'Atomic compliment': - drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between the inplace alter table commit and the rename of the .frm file. Supported engines in 10.6 are (among others) - InnoDB - MyRocks - Aria (transactional and non transactional tables) - MyISAM - Any engine that has only one table file (as then drop and rename will be atomic) |
| Link | This issue includes MDEV-25292 [ MDEV-25292 ] |
| Description |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if it succeeded The cost of doing the above should not be more than 1-2 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture is done for each sub project Some requirements for a storage engine to be 'Atomic compliment': - drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between the inplace alter table commit and the rename of the .frm file. Supported engines in 10.6 are (among others) - InnoDB - MyRocks - Aria (transactional and non transactional tables) - MyISAM - Any engine that has only one table file (as then drop and rename will be atomic) |
The purpose of this task is to ensure that the following internal operations
are atomic (either all or nothing) for all storage engines: Manage .frm Storage engine data dictionary Binary log This will solve the following things: - .frm context and storage engine dictionary is always in sync - no #sql-xxxx files if server crashes during alter table - binary log will always contain the DDL statement if the DDL successfully and committed to the storage engine. If the DDL was not successful, all traces of the DDL (like temporary files) will be deleted/rolled back. For example, if an alter table was successfully done and committed and we get a crash just before the write to the binary log, crash recovery will write the ALTER TABLE command to the binary log. In the case of a crash in the middle of a multi-table drop, crash recovery will write to the binary log those tables that was actually dropped. The cost of doing the above should not be more than 1-4 sync per DDL. High level architecture When doing a DDL, store somewhere (either trough write_ddl_log_entry() or some new method): - Operation - Number of tables - Table name - Table id for original table - Table id for resulting table (in case of rename) - sql command (for binary log) If there is no table id (for example for CSV) we would use the timestamp of the files. With the above information we would be able to continue from the place where the operation failed. Low level architecture is done for each sub project Some requirements for a storage engine to be 'Atomic compliment': - drop table and rename_table needs to be either atomic or can be retried if there was a crash in middle of the operation. - Engines supporting inplace alter table, must also support the handlerton->check_version() call to allow the ddl recovery code to check if the inline alter table succeed. This is only needed if there was a crash between the inplace alter table commit and the rename of the .frm file. Supported engines in 10.6 are (among others) - InnoDB - MyRocks - Aria (transactional and non transactional tables) - MyISAM - Any engine that has only one table file (as then drop and rename will be atomic) |
| Link |
This issue blocks |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
It seems that we are currently missing the recovery step for the embedded server library.
As noted in MDEV-25506, some error message output needs to be suppressed during recovery. The scenario is that the server was killed during CREATE TABLE tt13 in such a way that InnoDB rolled back the transaction:
|
bb-10.6-monty 387d673edb5899adb31695f02032b060ed7574f7 |
2021-05-04 19:40:28 0 [ERROR] InnoDB: Table `test`.`tt13` does not exist in the InnoDB internal data dictionary though MariaDB is trying to drop it. Have you copied the .frm file of the table to the MariaDB database directory from another database? Please refer to https://mariadb.com/kb/en/innodb-troubleshooting/ for how to resolve the issue.
|
There is no point to issue such messages when the DDL log recovery is in progress:
|
bb-10.6-monty 387d673edb5899adb31695f02032b060ed7574f7 |
(rr) bt
|
#0 sql_print_error (format=0x561255a94148 "InnoDB: %s")
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/sql/log.cc:9177
|
#1 0x00005612553fb730 in ib::error::~error (this=0x7fff4e168310, __in_chrg=<optimized out>)
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/storage/innobase/ut/ut0ut.cc:508
|
#2 0x00005612551b9e9a in ha_innobase::delete_table (this=0x5612573d54c0,
|
name=0x561256ecdad2 "./test/tt13", sqlcom=SQLCOM_END)
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/storage/innobase/handler/ha_innodb.cc:13142
|
#3 0x00005612551a2616 in ha_innobase::delete_table (this=0x5612573d54c0,
|
name=0x561256ecdad2 "./test/tt13")
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/storage/innobase/handler/ha_innodb.cc:13214
|
#4 0x0000561254de755f in hton_drop_table (hton=0x561256eeed08, path=0x561256ecdad2 "./test/tt13")
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/sql/handler.cc:577
|
#5 0x0000561254bb4248 in ddl_log_execute_action (thd=0x561257109408, mem_root=0x7fff4e168db0,
|
ddl_log_entry=0x7fff4e168df0) at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/sql/ddl_log.cc:1711
|
#6 0x0000561254bb5d67 in ddl_log_execute_entry_no_lock (thd=0x561257109408, first_entry=2)
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/sql/ddl_log.cc:2358
|
#7 0x0000561254bb679c in ddl_log_execute_recovery ()
|
at /home/mdbe/atomic_ddl/bb-10.6-monty-for-rr/sql/ddl_log.cc:2714
|
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link | This issue relates to MDEV-25705 [ MDEV-25705 ] |
| Link | This issue includes MDEV-25292 [ MDEV-25292 ] |
| Fix Version/s | 10.6.1 [ 24437 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link | This issue relates to MDEV-25920 [ MDEV-25920 ] |
| Labels | ServiceNow |
| Link | This issue is blocked by MDEV-20677 [ MDEV-20677 ] |
| Labels | ServiceNow | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Labels | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
| Remote Link | This issue links to "Page (Confluence)" [ 32000 ] |
| Remote Link | This issue links to "Page (Confluence)" [ 32019 ] |
| Remote Link | This issue links to "Page (Confluence)" [ 32112 ] |
| Remote Link | This issue links to "Page (Confluence)" [ 32224 ] |
| Remote Link | This issue links to "Page (Confluence)" [ 32256 ] |
| Remote Link | This issue links to "Page (Confluence)" [ 32305 ] |
| Link |
This issue relates to |
| Remote Link | This issue links to "Page (Confluence)" [ 32000 ] |
| Workflow | MariaDB v3 [ 90302 ] | MariaDB v4 [ 133731 ] |
| Link | This issue relates to MDEV-29566 [ MDEV-29566 ] |
| Link |
This issue blocks |
| Link |
This issue blocks |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36695 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36695 ] |
| Zendesk Related Tickets | 201658 191541 | |
| Zendesk active tickets | 201658 |
I hope that this will address the failure scenario of
MDEV-23741.