Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
Limitations
- SEQUENCE is not yet protected by atomic C-O-R (
MDEV-29772). - S3 storage engine is not protected by atomic C-O-R because rename (to/from backup/tmp tables) is expensive operation in S3.
Debug tracing
mtr --mysqld=--debug=d,ddl_log,query:i:o,/tmp/ddl.log
|
i: Add thread ID to output lines (may be needed for multi-threaded debug, but not for simple tests)
o: Set the debug output file.
For more --debug options refer to The DBUG Package.
How to view the chain of DDL log commands
Lets see the output of
create table t1 (a int); |
create or replace table t1 (b int); |
dispatch_command: query: create table t1 (a int)
|
ddl_log_write_entry: ddl_log: pos: 1->0 action: 12 (create table) phase: 0 handler: 'MyISAM' name: 't1' from_name: '(null)' tmp_name: './test/t1'
|
ddl_log_write_execute_entry: ddl_log: pos: 2=>1
|
disable_execute_entry: ddl_log: pos: {2}
|
dispatch_command: query: create or replace table t1 (b int)
|
ddl_log_write_entry: ddl_log: pos: 2->0 action: 12 (create table) phase: 0 handler: 'MyISAM' name: '#sql-create-14b094-4-t1' from_name: '(null)' tmp_name: './test/#sql-create-14b094-4-t1'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>2
|
ddl_log_write_entry: ddl_log: pos: 3->0 action: 7 (initialize drop table) phase: 0 handler: '(null)' name: '(null)' from_name: '(null)' tmp_name: ''
|
ddl_log_write_execute_entry: ddl_log: pos: 4=>3
|
ddl_log_write_entry: ddl_log: pos: 5->0 action: 8 (drop table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: '(null)' tmp_name: './test/#sql-backup-14b094-4-t1'
|
update_next_entry_pos: ddl_log: pos: 3->5
|
ddl_log_write_entry: ddl_log: pos: 6->2 action: 5 (rename table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: 't1' tmp_name: '(null)'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>6
|
ddl_log_write_entry: ddl_log: pos: 7->6 action: 12 (create table) phase: 0 handler: 'MyISAM' name: 't1' from_name: '(null)' tmp_name: './test/t1'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>7
|
disable_execute_entry: ddl_log: pos: {1}
|
ddl_log_execute_action: ddl_log: pos: 5->0 type: 2 action: 8 (drop table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: '' tmp_name: './test/#sql-backup-14b094-4-t1'
|
update_phase: ddl_log: pos: 5 phase: 1
|
update_phase: ddl_log: pos: 5 phase: 2
|
disable_execute_entry: ddl_log: pos: {4}
|
First we split the output by dispatch_command(). Let's look at the CREATE OR REPLACE command in detail.
dl_log_write_entry: ddl_log: pos: 2->0 action: 12 (create table) phase: 0 handler: 'MyISAM' name: '#sql-create-14b094-4-t1' from_name: '(null)' tmp_name: './test/#sql-create-14b094-4-t1'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>2
|
2->0 means write the entry at pos 2 and the next entry is 0 meaning 2 is the last entry in the chain.
1=>2 means the entry at pos 1 is the first entry in the chain (the execute entry). The above chain has one command: create table. This command is replayed as drop table, so by replaying DDL log we drop the table #sql-create-14b094-4-t1 if we find one. Infix create means this is the new table, i.e. the one we creating now. "t1" suffix means how this table will be finally named. This suffix may be trimmed if the name is too long, but that doesn't affect the final name as the suffix is for debug purposes only.
ddl_log_write_entry: ddl_log: pos: 3->0 action: 7 (initialize drop table) phase: 0 handler: '(null)' name: '(null)' from_name: '(null)' tmp_name: ''
|
ddl_log_write_execute_entry: ddl_log: pos: 4=>3
|
ddl_log_write_entry: ddl_log: pos: 5->0 action: 8 (drop table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: '(null)' tmp_name: './test/#sql-backup-14b094-4-t1'
|
update_next_entry_pos: ddl_log: pos: 3->5
|
The above example shows how the chain 4=>3->5 is written: we link entries 3 and 5 in the additional call update_next_entry_pos(). The chain 4=>3->5 drops the table #sql-backup-14b094-4-t1. Infix backup means this is the old table, i.e. the one that existed as t1 before CREATE OR REPLACE was executed.
ddl_log_write_entry: ddl_log: pos: 6->2 action: 5 (rename table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: 't1' tmp_name: '(null)'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>6
|
ddl_log_write_entry: ddl_log: pos: 7->6 action: 12 (create table) phase: 0 handler: 'MyISAM' name: 't1' from_name: '(null)' tmp_name: './test/t1'
|
ddl_log_write_execute_entry: ddl_log: pos: 1=>7
|
This adds more commands into the chain at pos 1, so it becomes 1=>7->6->2. It first drops the table t1, then renames #sql-backup-14b094-4-t1 to t1 and the entry 2 drops #sql-create-14b094-4-t1
This chain is rollback chain: in case of errors or crashes it reverts back t1 to the original state (from backup) and drops the new table (either in t1 or in #sql-create-14b094-4-t1 depending on where the crash happened).
disable_execute_entry: ddl_log: pos: {1}
|
This disables chain 1. I.e. the above rollback chain 1=>7->6->2 now will never be executed.
ddl_log_execute_action: ddl_log: pos: 5->0 type: 2 action: 8 (drop table) phase: 0 handler: 'MyISAM' name: '#sql-backup-14b094-4-t1' from_name: '' tmp_name: './test/#sql-backup-14b094-4-t1'
|
update_phase: ddl_log: pos: 5 phase: 1
|
update_phase: ddl_log: pos: 5 phase: 2
|
disable_execute_entry: ddl_log: pos: {4}
|
The above executes chain 4=>3->5. It doesn't show the execution of empty action 3 (initialize drop table) as this is an utility action for the action 5. Finally it disables the chain 4 so it will not be executed again. It just drops the original table in the backup form so we can call it the cleanup chain.
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
- relates to
-
MDEV-29772 CREATE OR REPLACE not atomic for sequences (a.k.a tables with SEQUENCE=1)
-
- Closed
-