[Maria-developers] Live Schema Changes
![](https://secure.gravatar.com/avatar/8c75c93aed5d929ca8cb054943d2ce11.jpg?s=120&d=mm&r=g)
I'm sure you have all read http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/annotate/... already, but if you haven't, it might be neat to look at evaluating and possibly integrating it into MariaDB.
![](https://secure.gravatar.com/avatar/d1fa154903ca7ed342c36888aab05236.jpg?s=120&d=mm&r=g)
It would be even cooler if there were storage engines that could do
this internally. That would be easier to manage. The current process
requires that you validate that your version of MariaDB or MySQL does
the right thing for the tool.
Note that the tool does a few steps differently for 5.0 versus 5.1
because behavior has changed.
On Tue, Sep 21, 2010 at 7:41 PM, Adam M. Dutko
I'm sure you have all read http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/annotate/... already, but if you haven't, it might be neat to look at evaluating and possibly integrating it into MariaDB. _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Mark Callaghan mdcallag@gmail.com
![](https://secure.gravatar.com/avatar/bda86bcbef84c75ba6fdb3d7e744693b.jpg?s=120&d=mm&r=g)
I have spent some of my spare time looking into this. It turns out
that MySQL Cluster already has this ability. They have the following
handler functions listed below. I spent a weekend trying to port MySQL
Cluster's alter table function (mysql_alter_table) over to 5.1.46.
The port that I did was straightforward, and for the most part, it
works. There are a couple of small bugs/issues that I need to
investigate and track down, but the functionality is available. If
this can be made to work in a safe way, where storage engines can
choose whether to use MySQL Cluster's new alter table, and others use
the existing, it seems like a very safe thing to integrate into 5.1.
One problem I have run into is that I do not know how to get a storage
engine to opt in. This was the motivation behind the following email
to the MySQL internals alias. http://lists.mysql.com/internals/38105
For those curious, the issues that I have:
- alter table on partitioned tables is returning ER_OUTOFMEMORY (This
may only be when adding/dropping indexes)
- All storage engines use the new alter table, I want storage engines
to opt in, to reduce the risk of bugs
Any thoughts?
-Zardosht
virtual int check_if_supported_alter(TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_FLAGS *alter_flags,
uint table_changes)
virtual int alter_table_phase1(THD *thd,
TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_INFO *alter_info,
HA_ALTER_FLAGS *alter_flags)
virtual int alter_table_phase2(THD *thd,
TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_INFO *alter_info,
HA_ALTER_FLAGS *alter_flags)
virtual int alter_table_phase3(THD *thd, TABLE *table)
On Wed, Sep 22, 2010 at 2:28 PM, MARK CALLAGHAN
It would be even cooler if there were storage engines that could do this internally. That would be easier to manage. The current process requires that you validate that your version of MariaDB or MySQL does the right thing for the tool.
Note that the tool does a few steps differently for 5.0 versus 5.1 because behavior has changed.
On Tue, Sep 21, 2010 at 7:41 PM, Adam M. Dutko
wrote: I'm sure you have all read http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/annotate/... already, but if you haven't, it might be neat to look at evaluating and possibly integrating it into MariaDB. _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
Zardosht Kasheff
I have spent some of my spare time looking into this. It turns out that MySQL Cluster already has this ability. They have the following handler functions listed below. I spent a weekend trying to port MySQL Cluster's alter table function (mysql_alter_table) over to 5.1.46.
- All storage engines use the new alter table, I want storage engines to opt in, to reduce the risk of bugs
Any thoughts?
Yes. I think you should use the new interface for everything. I remember when we did this work in cluster (I did the low-end table change part), and the alter table interface was designed as a general solution, not something to be used only for some special engines. I understand of course why you want to "reduce risk". This has been the approach taken with much community development in the last couple of years (Percona patches, Google patches, Facebook patches, etc): maintain isolated patches, trying to minimise the impact of each patch on the code base to keep things manageable as individual patches and reduce risk and effort needed. But recently I see so much community development happening that I believe it is necessary to move to the next phase. With your work, the Facebook group, my group at Monty Program, Perconas work, and other stuff, the development effort outside of MySQL@Oracle is on the same order of magnitude as that inside. Maintaining individual patches does not scale to that level of effort, in my opinion. Until MySQL@Oracle opens up their development to outside groups, we need another solution. I spend the first year on MariaDB just working to make everything we need available for full-scale development on the MySQL codebase for just this reason. But I recognise that there is still something missing for MariaDB to be useable for Facebook, Percona, you, etc. I think maybe it is the release model, if so we can fix it. Or use something else, it does not have to be MariaDB, but I strongly believe we need to coordinate our efforts. Within the next 6 months or so we have the merge with all of our still into MySQL 5.5 coming up, that will take considerable effort and is not something we should each repeat individually. So we need to find a model that works for all you people out in the trenches needing to put things in production use ASAP. But you guys also need at some point to start spending the extra effort to test, fix, and integrate new things properly, otherwise we end up with a huge spaghetti of patches that cannot be maintained. Just my thoughts, - Kristian.
![](https://secure.gravatar.com/avatar/d1fa154903ca7ed342c36888aab05236.jpg?s=120&d=mm&r=g)
I agree with your intent but some of the motivation is wrong. The
public Google patch has always been a gigantic diff except for a few
large features that were extracted. The Facebook patch isn't really a
patch. It is a launchpad branch. Percona maintained patches. That must
have been a lot of work on their part. Maybe they won't spend as much
time on it going forward now that there is Percona server.
On Wed, Sep 22, 2010 at 11:27 PM, Kristian Nielsen
Zardosht Kasheff
writes: I have spent some of my spare time looking into this. It turns out that MySQL Cluster already has this ability. They have the following handler functions listed below. I spent a weekend trying to port MySQL Cluster's alter table function (mysql_alter_table) over to 5.1.46.
- All storage engines use the new alter table, I want storage engines to opt in, to reduce the risk of bugs
Any thoughts?
Yes. I think you should use the new interface for everything. I remember when we did this work in cluster (I did the low-end table change part), and the alter table interface was designed as a general solution, not something to be used only for some special engines.
I understand of course why you want to "reduce risk". This has been the approach taken with much community development in the last couple of years (Percona patches, Google patches, Facebook patches, etc): maintain isolated patches, trying to minimise the impact of each patch on the code base to keep things manageable as individual patches and reduce risk and effort needed.
But recently I see so much community development happening that I believe it is necessary to move to the next phase. With your work, the Facebook group, my group at Monty Program, Perconas work, and other stuff, the development effort outside of MySQL@Oracle is on the same order of magnitude as that inside. Maintaining individual patches does not scale to that level of effort, in my opinion.
Until MySQL@Oracle opens up their development to outside groups, we need another solution. I spend the first year on MariaDB just working to make everything we need available for full-scale development on the MySQL codebase for just this reason.
But I recognise that there is still something missing for MariaDB to be useable for Facebook, Percona, you, etc. I think maybe it is the release model, if so we can fix it. Or use something else, it does not have to be MariaDB, but I strongly believe we need to coordinate our efforts. Within the next 6 months or so we have the merge with all of our still into MySQL 5.5 coming up, that will take considerable effort and is not something we should each repeat individually.
So we need to find a model that works for all you people out in the trenches needing to put things in production use ASAP. But you guys also need at some point to start spending the extra effort to test, fix, and integrate new things properly, otherwise we end up with a huge spaghetti of patches that cannot be maintained.
Define "properly"? I suspect you mean that you want us to spend the time to get our changes into MariaDB. While that would be a good thing to do, I don't think that makes it proper. Getting changes from my team into MariaDB is more about helping others use those changes than it is about making my work easier. I maintain a branch on launchpad, not a set of patches. It isn't that hard to maintain them although merging to 5.5 might be some work. Pushing patches from Facebook to anybody else is extra work. I don't have time to do it. I try to make it easy to pull them. -- Mark Callaghan mdcallag@gmail.com
![](https://secure.gravatar.com/avatar/bda86bcbef84c75ba6fdb3d7e744693b.jpg?s=120&d=mm&r=g)
My main interest is getting a feature that many think is very useful
properly implemented and integrated into 5.1. This is a feature that
was enthusiastically brought up during the 2010 storage engine summit.
Also, I think Mark's online schema change work has sparked interest in
this. This feature exists and is used in another GA product: MySQL
cluster, so I think the code should be good.
My reason for being interested in 5.1: that is the GA version of MySQL
and MariaDB. Having this available in 5.1 allows for storage engines
to take advantage of this feature more quickly, and in turn, get it in
the hands of customers more quickly.
That being said, I am wondering what the best approach for doing this
is. I would like to produce something that can be integrated into
MariaDB and MySQL (although I realize that actually getting it into
MySQL is highly highly unlikely). Is there anyway this can be done?
In the worst case, I will only be able to integrate it in our private
branches, and that just diverges our code even more.
As I said earlier, my work is not ready. There are a couple of bugs I
need to track down. Once I get it working, I will happily share what I
have done (which is really just moving code from one MySQL branch to
another, nothing special) and encourage feedback. But if I can get it
working, is there a possible way to get this into MariaDB 5.1 (even a
"safe" version)? If not, some near future version?
Thanks
-Zardosht
On Thu, Sep 23, 2010 at 10:29 AM, MARK CALLAGHAN
I agree with your intent but some of the motivation is wrong. The public Google patch has always been a gigantic diff except for a few large features that were extracted. The Facebook patch isn't really a patch. It is a launchpad branch. Percona maintained patches. That must have been a lot of work on their part. Maybe they won't spend as much time on it going forward now that there is Percona server.
On Wed, Sep 22, 2010 at 11:27 PM, Kristian Nielsen
wrote: Zardosht Kasheff
writes: I have spent some of my spare time looking into this. It turns out that MySQL Cluster already has this ability. They have the following handler functions listed below. I spent a weekend trying to port MySQL Cluster's alter table function (mysql_alter_table) over to 5.1.46.
- All storage engines use the new alter table, I want storage engines to opt in, to reduce the risk of bugs
Any thoughts?
Yes. I think you should use the new interface for everything. I remember when we did this work in cluster (I did the low-end table change part), and the alter table interface was designed as a general solution, not something to be used only for some special engines.
I understand of course why you want to "reduce risk". This has been the approach taken with much community development in the last couple of years (Percona patches, Google patches, Facebook patches, etc): maintain isolated patches, trying to minimise the impact of each patch on the code base to keep things manageable as individual patches and reduce risk and effort needed.
But recently I see so much community development happening that I believe it is necessary to move to the next phase. With your work, the Facebook group, my group at Monty Program, Perconas work, and other stuff, the development effort outside of MySQL@Oracle is on the same order of magnitude as that inside. Maintaining individual patches does not scale to that level of effort, in my opinion.
Until MySQL@Oracle opens up their development to outside groups, we need another solution. I spend the first year on MariaDB just working to make everything we need available for full-scale development on the MySQL codebase for just this reason.
But I recognise that there is still something missing for MariaDB to be useable for Facebook, Percona, you, etc. I think maybe it is the release model, if so we can fix it. Or use something else, it does not have to be MariaDB, but I strongly believe we need to coordinate our efforts. Within the next 6 months or so we have the merge with all of our still into MySQL 5.5 coming up, that will take considerable effort and is not something we should each repeat individually.
So we need to find a model that works for all you people out in the trenches needing to put things in production use ASAP. But you guys also need at some point to start spending the extra effort to test, fix, and integrate new things properly, otherwise we end up with a huge spaghetti of patches that cannot be maintained.
Define "properly"? I suspect you mean that you want us to spend the time to get our changes into MariaDB. While that would be a good thing to do, I don't think that makes it proper. Getting changes from my team into MariaDB is more about helping others use those changes than it is about making my work easier.
I maintain a branch on launchpad, not a set of patches. It isn't that hard to maintain them although merging to 5.5 might be some work. Pushing patches from Facebook to anybody else is extra work. I don't have time to do it. I try to make it easy to pull them.
-- Mark Callaghan mdcallag@gmail.com
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
Zardosht Kasheff
My main interest is getting a feature that many think is very useful properly implemented and integrated into 5.1. This is a feature that was enthusiastically brought up during the 2010 storage engine summit. Also, I think Mark's online schema change work has sparked interest in this. This feature exists and is used in another GA product: MySQL cluster, so I think the code should be good.
Yes, I was very happy about this feature when it was implemented, and it is really sad to learn it has apparently been dropped on the floor along with 6.0. I do not know much about the state/quality of the actual code in the MySQL layer (I worked mostly on the storage engine level in NDB on this). But I participated in the design of the upper-layer interface, and remember finding it solid back then. I also know this is an important feature for cluster, so I would expect at least the part exercised by NDB to be good.
As I said earlier, my work is not ready. There are a couple of bugs I need to track down. Once I get it working, I will happily share what I have done (which is really just moving code from one MySQL branch to another, nothing special) and encourage feedback. But if I can get it working, is there a possible way to get this into MariaDB 5.1 (even a "safe" version)? If not, some near future version?
This is definitely a possibility. The process for this is that the patch need to go through code review here on maria-developers@ and pass testing on our Buildbot farm. Then it can be integrated into the next MariaDB version that is not frozen (beta/stable) at that time. The way MariaDB releases work currently is that MariaDB 5.1, 5.2, 5.3, ... are all based on the latest MySQL 5.1 code base, with cumulative added features and fixes in each. The intension is to have a new release every 9-12 months. (Personally I am interested in moving towards a release model more like the Linux kernel, but the above is how things are now). If you have any problems or questions related to the source code, I would encourage you to ask here or on internals@, there are several with deep knowledge of the MySQL code who would be very willing to help.
My reason for being interested in 5.1: that is the GA version of MySQL and MariaDB. Having this available in 5.1 allows for storage engines to take advantage of this feature more quickly, and in turn, get it in the hands of customers more quickly.
I think currently it is Percona Server and the Facebook patch that are the quickest to pick up new features. But I cannot speak for them about whether they would pick up something like this. Their main interest is InnoDB, of course.
That being said, I am wondering what the best approach for doing this is. I would like to produce something that can be integrated into MariaDB and MySQL (although I realize that actually getting it into MySQL is highly highly unlikely). Is there anyway this can be done?
In the worst case, I will only be able to integrate it in our private branches, and that just diverges our code even more.
Yes :-(. My best advice is to discuss the new interface on the mailing list and get people to buy into that. If we avoid different branches introducing conflicting new APIs, we are in a much better shape to integrate them later. If/when at some point we get more big features like this working well in multiple different branches, the need and motivation to unify them will be higher. Since you are already basing your work on the existing MySQL 6.0/-telco interface, I think you are in good shape. - Kristian.
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
MARK CALLAGHAN
Define "properly"? I suspect you mean that you want us to spend the time to get our changes into MariaDB.
No, that is not what I meant. I meant write code, tests, etc. like what you and I want to see in the MySQL source. You have often voiced opinions on quality of code, tests, etc., and I believe we think very much alike in this respect. The concrete example was extending the alter table API to better support online schema changes. When we did this at MySQL for online add column in NDB Cluster, we designed it as a general extension to the storage engine API, not as some special case. I think this is what everyone would expect from MySQL sources. But doing such features in a general way, thinking about all use cases and corner cases, can be _much_ more work than making something work well for a given problem at hand. I fully understand the temptation, or even need, to solve the smaller problem instead. My motivation is that I want to see the MySQL source base (under whatever name), see revolutionary improvements long-term. I want to see full parallel replication working with any engine that implements the necessary storage engine API. I want to see a good backup solution that works with different engines. I want to see replication that recovers reliably from crashes and has robust and easy master promotion capabilities. Etc. And I believe to get this we will need not just to add isolated features (whether as individual patches or collected in bzr branches); we will also eventually have to evolve the storage engine API, add new plugin interfaces, etc. That is a lot of work, which is hard to justify for an individual MySQL-using organisation. So I want to encourage everyone to participate in this who might be able to. - Kristian.
![](https://secure.gravatar.com/avatar/d1fa154903ca7ed342c36888aab05236.jpg?s=120&d=mm&r=g)
I agree with you about that being the proper way to advance the MySQL
family and that the MariaDB project has been open and inclusive
towards external developers like myself. Hopefully, more can come from
this and this is a topic to discuss in Istanbul.
On Fri, Sep 24, 2010 at 12:51 AM, Kristian Nielsen
MARK CALLAGHAN
writes: Define "properly"? I suspect you mean that you want us to spend the time to get our changes into MariaDB.
No, that is not what I meant. I meant write code, tests, etc. like what you and I want to see in the MySQL source. You have often voiced opinions on quality of code, tests, etc., and I believe we think very much alike in this respect.
The concrete example was extending the alter table API to better support online schema changes. When we did this at MySQL for online add column in NDB Cluster, we designed it as a general extension to the storage engine API, not as some special case. I think this is what everyone would expect from MySQL sources.
But doing such features in a general way, thinking about all use cases and corner cases, can be _much_ more work than making something work well for a given problem at hand. I fully understand the temptation, or even need, to solve the smaller problem instead.
My motivation is that I want to see the MySQL source base (under whatever name), see revolutionary improvements long-term. I want to see full parallel replication working with any engine that implements the necessary storage engine API. I want to see a good backup solution that works with different engines. I want to see replication that recovers reliably from crashes and has robust and easy master promotion capabilities. Etc.
And I believe to get this we will need not just to add isolated features (whether as individual patches or collected in bzr branches); we will also eventually have to evolve the storage engine API, add new plugin interfaces, etc. That is a lot of work, which is hard to justify for an individual MySQL-using organisation. So I want to encourage everyone to participate in this who might be able to.
- Kristian.
-- Mark Callaghan mdcallag@gmail.com
![](https://secure.gravatar.com/avatar/769f0fd707751e8755eb9b5dd837ab63.jpg?s=120&d=mm&r=g)
This is something I'm always interested in. It seems most of us
understand where we need to go, but we don't know yet how to get
there... How do we convince Mark/Mark's boss that this is the best way
to develop MySQL codebase also from Facebook's point of view? (long
term we can make much bigger advances?) Where should this unified
development happen? (MariaDB? Is Maria open enough, responsive enough?
fast enough?)
Kristian, do you want to schedule a "hacking in groups" / unconference
time slot for this?
henrik
On Fri, Sep 24, 2010 at 6:11 PM, MARK CALLAGHAN
I agree with you about that being the proper way to advance the MySQL family and that the MariaDB project has been open and inclusive towards external developers like myself. Hopefully, more can come from this and this is a topic to discuss in Istanbul.
On Fri, Sep 24, 2010 at 12:51 AM, Kristian Nielsen
wrote: MARK CALLAGHAN
writes: Define "properly"? I suspect you mean that you want us to spend the time to get our changes into MariaDB.
No, that is not what I meant. I meant write code, tests, etc. like what you and I want to see in the MySQL source. You have often voiced opinions on quality of code, tests, etc., and I believe we think very much alike in this respect.
The concrete example was extending the alter table API to better support online schema changes. When we did this at MySQL for online add column in NDB Cluster, we designed it as a general extension to the storage engine API, not as some special case. I think this is what everyone would expect from MySQL sources.
But doing such features in a general way, thinking about all use cases and corner cases, can be _much_ more work than making something work well for a given problem at hand. I fully understand the temptation, or even need, to solve the smaller problem instead.
My motivation is that I want to see the MySQL source base (under whatever name), see revolutionary improvements long-term. I want to see full parallel replication working with any engine that implements the necessary storage engine API. I want to see a good backup solution that works with different engines. I want to see replication that recovers reliably from crashes and has robust and easy master promotion capabilities. Etc.
And I believe to get this we will need not just to add isolated features (whether as individual patches or collected in bzr branches); we will also eventually have to evolve the storage engine API, add new plugin interfaces, etc. That is a lot of work, which is hard to justify for an individual MySQL-using organisation. So I want to encourage everyone to participate in this who might be able to.
- Kristian.
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
participants (5)
-
Adam M. Dutko
-
Henrik Ingo
-
Kristian Nielsen
-
MARK CALLAGHAN
-
Zardosht Kasheff