 Good morning. Let me introduce myself. I am Shipra, Shipra Jain, working for high-scale oracle almost like six years by now. And today I am going to talk about atomic DDL. I think the name itself talks and describe a lot about the feature, but however I would be talking in detail in these slides. And moving on to the next slide, this is just a safe hours statement. This is the agenda on which these are the points on which I am going to talk about today. First one is what made it possible, then what is atomic DDL, why we do need atomic DDL, then supported statements, what are the behavior changes users are going to see. And last but not the least is DDL logs. How can you see the logs? Moving on to the next slide. So before actually I get into what atomic DDL is, what is its implementation and other things, it is very important to share the information, what actually made it possible to have it in 8.0, high-scale 8.0. So I mean some may argue, some may complain about that, we are already late in the game and why we did not have it prior to 8.0, why we have got it only in 8.0. Well, the story is actually a bit complicated. We, it was not so easy to have this feature before 8.0 due to certain limitation on the architecture label of MySQL. So that I would be explaining in the next few slides. So if you look at the, I think most of you are aware about the data dictionary. Data dictionary is nothing, it is the place where we store our metadata. So if you look at the prior to MySQL 8.0, we store the data dictionary information or maybe the metadata in different formats. So suppose the table is, I mean the metadata information related to table is stored in .frm. Then if it is a trigger, then it is stored in .trg file. For the schema, we store it in .opt file. So these all are flat file systems. So information is happening. So the information is, here is stored in a flat file system. And if you look at the non-table specific statements or objects like user events and procedure and function, these are stored in the MySQL tables. So again MySQL tables are non-transjections. And if we are using the storage engine as an NODB, then again it will have a duplicate copy of the metadata and it will be stored in a system table. So you can see that our metadata information is stored in different files, I mean flat file system, then MySQL non-transjectional system tables and then the transactional system tables. But with 8.0, we removed all of this. We just have a centralized location where we are storing the all our metadata information. So here these are the NODB transactional tables. So we have all this information, metadata information or data dictionary information stored in a centralized location. So this may actually help us to implement the atomic GDL. Let me just explain it with an example. Consider this example. For example, drop if a user is executing a drop schema. So in 5.7, basically at a high level when you say drop schema, there are these three things which is going to happen. The first one is delete tables. It is going to delete the data and metadata of the tables and then deleting the stored procedure and deleting the schema. So in the first case, when we are trying to delete the table, again the .trg.frm files from the flat files is going to get excess. So again here we are dealing with the flat file system. In the next step, when we are trying to drop the stored procedure, events or functions, here we are dealing with the myosem, rows with the myosem since the metadata information is stored in the myosem tables. So here we are dealing with the non-transitional database and the system tables. Then moving on to the drop schema, again the schema related metadata information is stored in .db or .opt file. So again we are dealing with the flat file system over here. So if you see it is a mix of file system, non-transitional and transactional storage and it cannot be done in a single transaction using a single commit. You need multiple commits to have this happen. But with 8.0, if you see everything, whether it is related to the metadata information or non-table specific metadata information or schema table, everything is stored in a system table, in .db system table. That is a centralized location. So you can just, this all operations can be done using a single transaction or a single commit. So with this implementation, we could implement the atomic DDL. This was actually turning out as a barrier for us to implement the atomic DDL. So for new data dictionary coming into the picture, we could have atomic DDL. So now I would be talking about what is atomic DDL. So atomic DDL is actually, basically we are dealing with the two aspects over here. The one is crash shaveness of the DDL. So if a user is executing a DDL and somebody execute a kill command or maybe some failure, system failure or hardware failure or some crash happens, whether it is going, it is crash shape, whether it is going to roll back all the operations or it is going to get commit. So that is the important thing. The other is when you execute any DDL operation, basically what happens behind the scene can be categorized into these three operations. The first, very first thing, what is going to happen is like, it is going to update the metadata information in a dictionary table. The second point is, it is actually going to call the storage specific handler to create the object on that particular SC or storage engine. And last, it is going to write all the information, if it is an application environment, is going to write all the information into the bin log or the binary log. So consider an example where if hardware failure or crash occurs between first and second operations. Suppose a data dictionary information is updated into the tables, but SC or the storage engine could not create an object on the database. So before 5.0, before 8.0, in MySQL 5.7, it is going to create a lot of, it is going to create a inconsistency. Since the metadata information is already there in the system tables, but the database is, I mean the object is not created. Consider the second example where if a crash or any system failure is there between these two points, last two. I mean the object has got created. The metadata information is already there. The object also got created, but it failed to write into the binary log. So what will happen? Now in that case, the master will have some other information and the slave will be drifted. So you can see the slave drift over here. And another thing is like, if in case of compound statement, what is compound statement over here? A DDL, which is doing more than one thing. Suppose it is trying to drop more than one table or it is trying to create more than one user. So in that case, let's say the table 2 does not exist or something. In that case also, it is going to write two statements to the binary log. The first one is, in this case actually, the operation will be successful for the existing table, but it will not be successful for the second table. So in the binary log, you will see two statements over there in 5.7. The first one is the successful statement for the dropping of the table, which is already existing, and the error message or the error propagation for the second table, which is not there. So those kind of, but in case of 8.0, we are not going to write anything in the binary log, since the complete operation is going to get rolled back. So I would be talking about it more in details. In example, I would be discussing if that makes things more clear. So these all three operations is going to get into a single transaction. So either the complete transaction will get committed, I mean the transaction will get committed or rolled back. So even if the server halts also, then also it is going to get rolled back in between. So this is all about the atomicity of DDL. Why atomic DDL? I think more or less I talked about these things in my previous slide itself, like it prevents the slave drift, it prevents the internal inconsistency, and this new DD actually made us to have this in this 8.0, and it also help, it enables the crash shaftness of the DDL, and there were a lot and a lot of issues reported by community due to these internal inconsistencies. But with this feature, we are going to address all of those supported statements. So almost more or less all the DDLs are listed over here with supports the atomicity. I actually have categorized these supported statements into two types of statements. One is table-level statement, the other one is non-table statements. The reason for this, having this classification or this difference is like table statements are the statement which involves the storage engine. So it actually invokes a handler, SE handler to create the database object on a particular whatever has been specified storage engine. So these are the statements which are table statements, and non-table statements are the statements which doesn't involve any storage engine. It doesn't invoke any handlers to create objects over there. So I would be taking one example from each of the category in later slides and explaining it in a better way. So changes in the DDL statement behavior. So I think most of you are aware that we have introduced the new DD as I discussed in my first two slides. So there's no, at such there's no user behavior changes due to changes in the new DD. But with this feature there would be some changes. For example, drop tables. So these all are compound statements wherein these statements are trying to work on more than one database object. So if there is more than one table, so earlier whatever is the existing table, those used to get dropped and error messages used to come for the existing, non-existing tables. But now with this feature it will error out for all the tables or database objects. Similar thing goes for the drop schema. So either all the entries as I talked in my previous slides, whether it's database, I mean tables or procedures or .pt file, everything is going to get deleted or dropped altogether if any failure occur in between. It is going to get rolled back. The same thing goes with the drop users, grant, create users. And this is just the continuation of the previous slides where I'm talking about the atomicity of the create table, truncate table. Rename table is again the same if more than one table is specified, either all of them are going to get renamed or rolled back. And same thing goes for the alter table as well. Let's take an example. This is again a drop table example that is a table statement. So in 5.7 if you say create table T1, you're trying to create only table T1, one table. And if you are trying to drop two tables T1 and T2, where in T2 does not exist. Here it is going to error out saying that table T2 does not exist. But if you say show tables, it is going to list out none of the tables. Since T1 was already exist and this statement dropped table T1. And this is going to be the result. And if you talk about the binary log, it is again going to write two things in the binary log. The first one is it is going to write about the statement drop table T1. And it should be successful. And drop table T2, wherein it should get error. So it is actually expected that slave should give the same error for drop table T2, non-existing table. So this actually increases the complexity of code. Since we have to handle it on the slave side as well. So this increases the complexity and maintainability of the replication code. So with this new feature wherein I would be talking more about this, I mean this problem got solved. So if you look at the MySQL 8.0, the same example, here indeed you will see the same error message. But if you say the show tables, you will be able to see the T1 over here. So clearly it says that either both the tables will get dropped or none of them is going to get dropped. And here we won't be writing anything in the win log. So there is nothing to be taken care of by replication. There is another example, create user. Again we are trying to create more than two users in a single statement. So the first one is with valid authentication plugin. The second one, the second user doesn't have any existing plugin. So ideally, I mean the first one with 5.7, first one will get created wherein it will error out for the second one. And again the same behavior with the binary log, it will actually write a statement to replicate to creation of the user foo wherein it should error out, expected error out for the second user bar. Where in 8.0, none of those users are going to get created and it is going to give error message saying that user does not exist or invalidant authentication. And binary log, again we are not writing anything. So there is nothing to be taken care in the binary log. The last thing in the agenda is DDL logs. So DDL logs are very important and it is a protected table wherein no DDL and DMLs are allowed. This is only for the viewing purpose and this actually is useful if any operation is anything got interrupted during operation that DDL is going on. The server is going to read this DDL log and decide upon whether the statement was successful or whether the statement was there was a failure occurred during any, I mean any during the execution of the operation. So this, what happens when a DDL is getting executed? Once it is successful, it is going to write some information into the DDL log. So DDL log will have transaction ID, I mean what all files this DDL is going to get created, what are the old files, etc., etc. Information is going to get into the DDL log. Once this DDL operation is successful, it is going to go and delete the entries for it. So DDL log will have only the entries for the currently running the DDL. So in case of any failure or something, actually server is going to go and check if there was a, suppose there was a crash and we are running into the recovery mode or something. So it is going to go and check into the DDL log and seeing that if there was any failure, if yes, then drop, then read that file, roll back the operation and if there was any physical file got created, for example during, if it is during the create table operation is going to get created.idb file and so on. So if any physical file got created, just go and delete, I mean clean up those files as well. So using this, actually it's kind of post DDL look, hook wherein it is going to see the DDL log, refer to DDL log only in case of any failure or something. So there is one option given to the user. You can set this option and see the details in this DDL log file. That's all. These are the references. Thank you. I'd like to just join as a moderator. Thank you so much, Mr. for the presentation. Do you have any questions? Yeah. I missed the first part. I cannot ask questions. Please. Is there any impact to the transactions there before there is a transaction within which I expected to be able to commit that transaction or it doesn't affect the transaction? It's just the atomicity of that particular, what I'm talking about is the atomicity of that particular statement. I'm not talking about if you use it within the transaction block or something, it is not going to affect anything. It is the way we will be seen as before. Statement level atomicity. Since there is one difference between I7 and I8, when it comes to DDL, it's like that. Right, right. Yeah, I knew, I'm very new to the MySQL, but I'm very old in for a while. I saw that the DDL log, is it the same with the redo logs on the Oracle database? Yeah, we do have redo logs, but it just, this log, this file has, this table has been created just to read out the information about the transaction. It contains the information specific to that statement only. No, it's not a while, it's a table actually in the data dictionary information. If there is a failure, then it would be. Thank you so much. Anyone, anyone need a sneak charge?