Invest in Humanity 2016

This being Thanksgiving Day, I thought I’d share something I’ve been working on for the past few weeks.

Just after the election, I wrote about the initial constructive steps I decided to take to combat the racism, sexism, misogyny, homophobia, xenophobia, and hate which dominated the 2016 election cycle. Although I initially wrote that post (and on Facebook rather than this blog) and took actions immediately due to my disappointment at the present state of the election, I knew that I wanted to do something bigger. Over the course of a couple of days of thinking it through, I formulated a plan and decided to call it Invest in Humanity 2016.

To that end, I set aside $60,000 (inclusive of my initial donations) to “invest” in causes far and wide which work to advance humanity. However, I also realize that the problems faced by different groups and the challenges known by people of different life experiences are not universal… so I knew I wanted to do something a bit unique to reach people I would not have reached by myself. I recruited several fairly diverse groups of friends (which I called “teams”), with their diverse experiences and gave each team an individual donation budget to manage. Each team has been fully responsible for finding causes they want to support, vetting them, and deciding how to support them and how much support they’d like to give. I’ve taken care of the bookkeeping and of course provided the funds. I’ve provided only very broad guidance rather than any rules or requirements for causes to support.

The teams have been busy! As of right now, we have supported 39 different organizations with a total of $21,850 in donations—but we’re just getting started.

The organizations we’ve supported so far include:

  • ACLU Nationwide
  • Alliance for Multicultural Community Services
  • Amaanah Refugee Services
  • American Humanist Association
  • Border Network for Human Rights
  • Center for Reproductive Rights
  • FairVote
  • Family Promise of Knoxville
  • Food Bank of Northern Nevada
  • Gender Diversity
  • Girls on the Run
  • Houston Food Bank
  • International Refugee Assistance Project
  • Kids In Need of Defense (KIND)
  • Knox Area Rescue Ministries
  • Literacy Advance of Houston
  • Mexican American Legal Defense and Educational Fund
  • NAACP
  • National Immigrant Justice Center
  • National Organization for Women
  • Natural Resources Defense Council
  • Our Center
  • Planned Parenthood
  • RAINN
  • Reno Works c/o Volunteers of America
  • Reproductive Health Access Project
  • Sierra Club, Lone Star Chapter
  • Southern Poverty Law Center
  • The FAYCO Foundation
  • The Love Kitchen
  • The Nature Conservancy
  • The Reno Initiative for Shelter and Equality
  • The Trevor Project
  • The Young Center for Immigrant Children’s Rights
  • Trans LifeLine
  • Transgender Allies Group
  • Transgender Law Center
  • UNR Resettlement and Placement
  • YMCA of Greater Houston

I’ve set up a small (and currently very simple) web site related to this effort at InvestInHumanity2016.com. If you’re interested in investing in humanity as well, please reach out to me. I’d love to hear from you. If you represent a charity or other cause (registered or not) and could use our support, please reach out as well!

A response to those supporting Trump based on his abortion position

[I have copied this content from my original post on Facebook, and have backdated it appropriately to maintain context.]

As for me, from the beginning I aligned much more with, and supported Bernie Sanders. I was very disappointed with the actions of the DNC in this election, and I was fairly cold on Hillary Clinton from the start (largely because she is, by anyone’s standards, a lifelong politician). Along the way, I could not have ever stomached voting for Donald Trump though, as he has made it very clear from the beginning that his positions on most everything: (a) tended to be made up on the spot based on his audience; and (b) generally amount to hating or denigrating different groups. I saw nothing of actual substance (especially that could also actually be believed) in his entire campaign, and I still haven’t since the election has closed.

I ended up aligning with and supporting Hillary Clinton in the end, and I did vote for her. I do feel that the DNC strongly pushed her, more than was probably wise, and certainly against their supposedly independent position, because they really wanted to be responsible for electing the first woman president. She has a very “unclean” past largely because of being *so* deeply involved in politics for *so* long, and due to of course being first lady to an ex-president. There are a lot of people with a lot of free time to hate her and Bill Clinton, and they’ve gotten her involved in so much scandal (and she’s gotten herself involved in enough) that regardless of how much has any merit it’s impossible to tell, objectively, how much is real. So I preferred mostly to stay away.

As for me, I would have preferred that our first woman president had a much cleaner/simpler past, so we could purely focus on her qualifications and actual positions, and deal with the inherent biases in our cultural and political system against women as leaders, as a single issue (essentially a woman version of Bernie Sanders would be ideal). Instead, of course, most of that was pushed to the side by all of the scandal, as real or imagined as it was.

Donald Trump, however, is a totally different case for myself and for many others who voted “anti-Trump”. The vast majority of those upset about his election are not upset due to his proposed policies per se (as weak and ill-defined as they may be), and are not upset because of accusations against him (based on fact or not), but based on things he himself has said (most often on camera), positions he has taken, and statements he has made about others, all on record and irrefutable. His oft-stated desires to forcibly deport all illegal immigrants, and to require registration for all muslims are frankly terrifying for many of us. His behavior towards women (ignoring all accusations and just focusing on things on record and irrefutable) is completely and absolutely unacceptable for any presidential candidate let alone president-elect of our country. And it’s those beliefs that drive their and my disappointment, anger, and ultimately actions (right or wrong).

I personally made the switch from being merely upset about Bernie Sanders’s seeming exclusion from the political process, to fully supporting (both mentally and financially) Hillary Clinton as a candidate (to defeat Trump) based on Trump’s comments, on video, towards a young (10 year old) girl: “I’ll be dating her in 10 years, can you believe it?” That was, for me, the tipping point, and the moment where I felt I had to switch from being on the sidelines, aghast that the man was still a serious candidate, to doing everything I could to try to ensure he didn’t become our president. If you think you can justify his remarks in that tape, I’d love to hear it. This was, of course, on top of the already lengthy series of previous remarks by him, indisputably, which range from questionable to what should have been immediately disqualifying.

I fully understand and sympathize with those who are having a bad time in modern America, are unemployed or underemployed, underpaid, or feeling like they don’t have opportunities to better their life. I can see why people align with the concept of the slogan “Make America Great Again”; they really want America to be great again, for them, today. The problem is that practically nothing proposed by Trump will have an actual impact to make their life great, again. The jobs those people used to have are never “coming back” as politicians often promise them, and the irony of the whole thing is that Trump, as a businessman, has had a lot more to do with their demise than he could ever do to reverse it as president. We’re not going to get back these jobs because there isn’t a market for their product at the price it would take to bring that job back, in America or elsewhere. Failure to follow this chain of thinking to its natural conclusion is dangerous and disingenuous.

It’s nice to say we’ll close the borders and wall ourselves off, and make it impossible to export jobs and import goods, we’ll forcibly remove all illegal immigrants, and anyone with a differing belief system or religion than “we” (white, Christian, America) understand. Then what? We’re suddenly great again? What’s the next step of this plan? Have we thought that far ahead? What’s next?

Republican supporters of either candidate in the previous elections did not riot when their candidates lost to Barack Obama, not because they are less “sore losers”, but because Obama’s candidacy and position was not based on hate, division, and demagoguery–it was based on hope, love, and bringing people together. And, had he lost to any typical Republican candidate, there would also not be rioting–losing a campaign based on love is not the same as winning a campaign based on hate. This time around, people are not upset that a Republican candidate was elected over a Democrat, that a man was elected over a woman, that their candidate lost, or *even* that Donald Trump (the man) was elected. They are upset that Donald Trump was elected in spite of (or possibly because of) his indisputable positions which amount to racism, sexism, hate, and divisiveness. They are upset that so many of our fellow countrymen were able and willing to support those positions, or at the least willing to ignore them to, in theory, get something else they wanted.

Illegal immigrants and muslims are currently the political bogeymen of today’s America, and because of that, politicians and constituents alike are able to treat them as abstract issues rather than as actual people. These are all complex issues which can’t be summarized into bumper stickers or campaign signs, so it’s tough to convey sensical positions on them. I think it is helpful though, for better context on the present day, to replace the current bogeyman in any present day argument with a bogeyman of (hopefully resolved) days past: how would Americans feel, today, on average, if we proposed to deport all Japanese, or require registration of all black people? I hope that those sound like crazy propositions to a modern American regardless of political affiliation.

(Yes, I realize that by definition, an “illegal immigrant” is also a criminal, in that their mere presence in the country is breaking the law. The societal, economic, and business implications of their presence is a lot more complicated than that, though. In order to keep this short, I will not go into any more detail here, but I’d be happy to discuss or debate further. I also realize that there will always be a hopefully small fraction of people who would say both of those proposals above sound “great”; but that’s not what I am arguing here, either.)

Back to the original question at hand in this thread, I do understand that one can be quite reasonable and totally against abortion of any kind. I know many people, who I respect, that take this position. I don’t agree with their stance, but I can respect that it is their belief.
However, if that is one’s stance, and one is going to vote based on that stance alone (ignoring ALL other opinions, policies, and positions of the candidate), I should hope that the candidate’s understanding of the issue would be much stronger than Trump’s is. I watched the debate live, and nearly choked during his answers on abortion because not only were his answers completely off base (borderline crazy), but he repeatedly (so it’s definitely not anyone misunderstanding him) and clearly displayed great ignorance of the entire issue.

Trump’s responses about “in the ninth month you can take baby and rip the baby out of the womb of the mother just prior to the birth of the baby” and “as late as one or two or three or four days prior to birth” (both direct quotes) are just buffoonery and blatant appeal to blind emotional reaction. (And of course, his statements previous to this debate including “punishing” women for getting abortions notwithstanding.)

On the other hand, Clinton’s responses were well reasoned and completely fair and honest. You may agree or disagree with her, but she holds a position which is at least backed by an understanding of the issue. Her position is the same as mine, basically (which was convenient for me, since Bernie was no longer an option): that abortion should be available to those that need it, and that there are various reasons for which it is in fact actually needed.

Personally, I support a multi-part approach with regards to abortion:

  1. Comprehensive (not “abstinence only”) sex education and open dialog.
  2. Easy and cheap or free access to contraception, without roadblocks such as parental consent.
  3. Easy and cheap access to emergency contraception such as “plan B”, again without roadblocks.
  4. Easy and cheap access to at-option early term abortion, again without roadblocks.
  5. Free access to all medical care, including of course pre- and post-natal medical care.
  6. Comprehensive social support programs for both mother and child (and for myself, all people generally).
  7. Access to abortion at any phase of pregnancy due to medical necessity.

With all of these in place, it’s possible to limit the amount of conception occurring in the first place, limit the number of abortions occurring due to accidental pregnancy (due either to ignorance of the process or availability of contraception), and ensure that any pregnancy regardless of cause is well cared for, and that the baby is born healthy and baby and mother can remain healthy. Unfortunately literally *all* of the above are antithetical to the platform of the Republican party. Their policies amount to, at best, hopeful ignorance, and punishment for all when that doesn’t work. The combination of denying all seven of the above are and have proven to be disastrous societally.

Claims like “in the ninth month you can take baby and rip the baby out of the womb of the mother just prior to the birth of the baby”, while they sound horrific, sound so because they literally *never* happen, theoretically legal or not. (Outside of mentally unsound people self-aborting their pregnancies, but I think we could agree that sort of thing will happen regardless of legality.) Not only would the mother have had, for some reason, to carry the baby that long and then change their mind, they will then have to find a doctor willing to perform such an abortion of a viable fetus based purely on the mother’s whim. Not likely, and it’s not necessary to make laws banning things that literally never happen, while bundling with those laws ones which place encumbrances on other practices that *are* done out of medical necessity.

I could actually understand if we had in place a society willing to widely embrace actual sex education and contraception programs, and we then *still* had some kind of abortion epidemic, and then religious groups wanted to take a more hard-line stance on it. We’re not there, though. If the groups so vocally opposed to abortion were also vocally supportive of (as opposed to obstinately against) sex education, contraception to avoid conception in the first place, and especially if they were also in support of medical care access and social support programs to support the baby during pregnancy and long after birth… then I would be much more sympathetic to their (and presumably your) cause. The current reality amounts to willful ignorance, hoping for the best, and punishing those that meet the inevitable outcome. I can’t support that and I am not sympathetic to those that advocate for it.

Combining the two issues here, and especially in light of the above, I cannot fathom support for Trump in spite of himself in order to “get” a harder stance on abortion and a hopeful overturning of Roe v. Wade. People are not rioting because they don’t understand, support, or sympathize with your cause, but because:

  • You’ve said, through actions and alignment, that the collateral damage of pushing through your cause is worth their lives–millions of them, who are alive today.
  • Widespread support of a candidate who so openly supports this level of racism, sexism, bigotry, etc. legitimizes and has and will continue to embolden those who also hold those beliefs, many to a much more extreme degree.

Hopefully this explains at least my position, and hopefully speaks for others in similar positions to myself. I’d love to debate anything here in more detail.

A constructive start for post-election 2016

[I have copied this content from my original post on Facebook, and have backdated it appropriately to maintain context.]

I don’t normally say much publicly about donating anything, and until lately about politics or social causes, either. I only really occasionally mention my support of loans through Kiva (mainly because it’s a long-running passion of mine). Today is a bit different, though; today, in honor of our democratic process, I thought a number of organizations could use a bit of extra help for the coming months and years.

So, as a constructive start, I’ve donated $1,000 each to the following organizations:

  • Planned Parenthood
  • Center for Reproductive Rights
  • Reproductive Health Access Project
  • RAINN
  • ACLU Nationwide
  • NAACP
  • Southern Poverty Law Center
  • The Young Center for Immigrant Children’s Rights
  • Transgender Law Center
  • Gender Diversity

If you, too, are looking upon the election outcome and wondering how the hell we got to this point of intolerance, overt racism, sexism, misogyny, homophobia, xenophobia, and hate, then I encourage you to find a few organizations that you can support, too, in whatever way you can.

I’m certainly not going to ask anyone to give all your money to the DNC (ugh), or run out into the streets with torches. I certainly don’t believe that a president Trump and Republican majority across the government will instantly be the end of the world, or even that half of the rhetoric upon which Trump’s entire disgusting campaign was based is even serious.

However, I do believe this is a clear signal from a large number of my countrymen that they support divisiveness over inclusion, fear over understanding, spectacle over reason, and hatred over love. And I can’t say I am surprised, but it leaves me dumbfounded and heartbroken. Not for Hillary Clinton’s loss–I could have easily supported any other serious candidate on either side without much regret–but for the loss of the dignity of our country. And, not even the loss, but the recognition of that loss, or the realization that that dignity was a farce all along.

This is not what *I* stand for, it’s not what I want my children to grow up with, and certainly not what I want my grandchildren to face. We can do a hell of a lot better. Why don’t we even bother trying? Why can’t we get past the color of a person’s skin, or the gender of a person as an authority figure, or the religion practiced by another person? How do we have so little compassion for others? Why do we continually act in a self-serving way and abuse others? I struggle to accept that the human race is just incorrigibly terrible, but I don’t see anything materially changing, either. I want more than anything for America to be a positive example to the world, but we just keep proving that we don’t have the courage for it.

While I fully respect the democratic process that allows for a country to be cast in whatever way its constituents decide, I am sure I don’t have the stomach to sit idly by while it is cast in *this* way. The fact that it has even gotten this far gone makes me feel physically ill, and doubly so because I know or am related to so many of the people who have made it so.

So, I won’t stomach it anymore. If you’ve ever claimed “Obama is a muslim” or “but he’s black”; if you’ve called someone a “nigger” or “sand nigger” or “towel head” or “wetback”; if you’ve championed from your sofa to restrict the rights of women or called Hillary a “bitch”, or made any reference to her inability to serve due to being a woman; if you’ve called someone a “fag” or “dyke” or “tranny”; seriously: fuck you.

Yes, I know that includes quite a number of people closely related to me. Sorry, but I am not sorry. This has to stop.

Look inside yourself, ask yourself: Does this make you a better person? Why do you do it? Do you truly believe that you’re just better than these people? Can you defend your ideas and ideals? Can you defend your choices? Can you defend your sense of decency?

Thanks, Oracle, for fixing the stupid and dangerous SET GLOBAL sql_log_bin!

As of MySQL 5.5.41, released on November 28 (last week), Oracle has fixed MySQL Bug 67433, which I filed on October 31, 2012 and wrote about 4 months ago in Stupid and dangerous: SET GLOBAL sql_log_bin. You can see the fix in Launchpad revision 4718.

The MySQL 5.5.41 release notes mention:

Replication: The global scope for the sql_log_bin system variable has been deprecated, and this variable can now be set with session scope only. The statement SET GLOBAL SQL_LOG_BIN now produces an error. It remains possible for now to read the global value of sql_log_bin, but you should act to remove from your applications any dependencies on reading this value, as the ability to do so will be removed in a future MySQL release. (Bug #67433, Bug #15868071)

Thanks for addressing this and making the world’s data (and DBAs’ jobs) a little safer, Oracle! Special thanks to Morgan Tocker (MySQL Community Manager at Oracle) who cared about it enough to keep pushing it through to a reasonable fix/resolution.

Nine years later, Citibank’s fraud department is still self-defeating

Back in September 2005, I wrote Actually, No. about Citibank’s self-defeating fraud department, and the follow-up two months later Reply from Citibank which was just a form letter noting that they had received my complaint.

In that situation, their fraud department would call from an unverifiable number, potentially leaving a voicemail for you, asking you to call them back at an arbitrary unverifiable phone number, where they immediately ask for your account details to verify a transaction. As far as I know they still continue that asinine behavior.

Today, I got a new one: Citibank called to verify a transaction (presumably a large-ish payment I made to a new payee using online bill pay), and the caller asked me to verify receipt of an SMS message (“for security reasons”) by reading him the numeric code it contains, in order to verify they are in fact speaking to me. I told him that I received the message, but I was not comfortable giving him the code, as I could not verify who he is. I explained that he could just as easily be trying to e.g. gain control of my account and needing the verification code to complete a password reset or similar (social engineering me). I told him I would call the number printed on the back of my card, and asked him how to get back to him when doing so. He said he would leave a note on my account.

Of course, when I called back and authenticated to the agent, the agent tried to internally transfer me to the number left in the note (the fraud department), but instead got a message saying the department was closed (open only until 6pm Eastern time) despite someone from that department calling me literally 1 minute prior. It took the customer service agent tracking down the specific employee who left the note, and messaging him, to get me transferred to his personal extension, since going through the department extension wouldn’t work. In the end, it was in fact Citibank’s fraud department calling me, we verified the transaction they wanted to verify, and everything is cool on that end.

However, the bigger issue is that Citibank’s “fraud department” procedures are asinine and extremely self-defeating. I guess it’s time for another complaint to the executive office, and probably another form-letter reply.

Visualizing the impact of ordered vs. random index insertion in InnoDB

[This post refers to innodb_ruby version 0.9.11 as of October 2, 2014.]

Many DBAs know that building indexes in “random” order (or really any order that greatly differs from ordered by key) can be much less efficient. However, it’s often hard to really understand why that is. With the “-illustrate” visualization modes available in innodb_ruby, it’s possible to quite easily visualize the structure of indexes. The space-lsn-age-illustrate mode to innodb_space allows visualization of all pages in a space file by “LSN age”, generating something like a heatmap of the space file based on how recently each page was modified.

(Note that a small Ruby script generate_data_simple.rb was used to generate the test tables used below. You’ll also want to have a reasonable monospace font supporting Unicode block characters correctly, for which I’d heartily recommend Adobe’s Source Code Pro.)

Building an index by insertion in key order

Inserting rows into an index in key order is much more efficient primarily for two reasons:

  1. Pages can be filled completely, and the database (with sufficient optimizations present) can detect the “bulk loading” behavior and efficiently split pages by creating new, empty pages, rather than splitting pages in half.
  2. Only the “edge” of the index is being written to; once a page has filled it will not be revisited. This can make the buffer pool, which caches database pages, much more effective.

Here’s an example of a table with only a PRIMARY KEY clustered index, built by inserting rows in key order:

$ innodb_space -s ibdata1 -T test/t space-lsn-age-illustrate

As you can see in the above image, the index pages are written to in nearly perfect order as they are allocated from the beginning of the file towards the end.

The first extent (the first line of the image) is allocated as a fragment extent, and contains single pages allocated for different purposes. You can clearly see the tablespace bookkeeping pages, FSP_HDR at page 0 and INODE at page 2, immediately followed by the root index page at page 3, all very recently modified. Following that are 32 individually allocated “fragment” pages which were allocated first in the index before it switched to allocating full extents. Then the index pages progress perfectly until the end of the used space.

Also note the LSN age histogram (printed in white above the colored legend at the bottom) shows all buckets equally filled.

Building an index by insertion in random order

Looking at an index built by insertion in key order was reassuring. What if it is built in random order instead? The result is perhaps a bit surprising:

$ innodb_space -s ibdata1 -T test/t_shuffle space-lsn-age-illustrate

Because the rows were inserted in completely random order, every page has an equal chance for insertion. This means in practice that every page is very recently modified, and this is clearly visible by the entire heatmap being purple. What this also means is that the entire table must be continually present in the buffer pool, and if it doesn’t fit, performance will suffer greatly. This is the main reason that performance of insertion in random order is terrible.

Additionally, you can see a few misbehaviors of InnoDB: Note the extents starting at page 1088, 1152, and 1216. Why do you think they look like that? Let me know your ideas in the comments.

Building a primary and secondary index in primary key order

What if you have multiple indexes? Looking at a very simple and typical case, inserting data in key order by the primary key, while a secondary index exists:

$ innodb_space -s ibdata1 -T test/t_index_before space-lsn-age-illustrate

Notice that this looks like the previous cases perfectly interleaved together, because it is exactly that. Since the primary key and secondary index contain completely different data, insertion is ordered by the primary key, but completely mis-ordered by the secondary index, resulting in the secondary index being built inefficiently.

Adding a secondary index to an existing table

The obvious answer then to the previous problem is to add the index after the data has been loaded, and this does in fact result in the expected outcome:

$ innodb_space -s ibdata1 -T test/t_index_after space-lsn-age-illustrate

When the index is built on the existing table (via ALTER TABLE ... ADD INDEX), it is built by scanning and sorting the data before insertion into the index, resulting in an optimal (and very fast) index build.

Visualizing page fill rate

While the above illustrations show how recently each page was modified during the index build, it’s also possible to use space-extents-illustrate to visualize how full each page is. The key-ordered index looks like this:

$ innodb_space -s ibdata1 -T test/t space-extents-illustrate

Compared to the random-ordered index:

$ innodb_space -s ibdata1 -T test/t_shuffle space-extents-illustrate

The random-ordered insertion caused pages to be split more frequently, and in some cases to be severely under-filled, causing a very poor page fill rate on average.

Those of you who are particularly observant will have noticed that the index illustrations in the first pictures above showed that the ordered-insertion index is significantly smaller than the random one. You can see here that the random-insertion index is 41% larger at 1043 pages compared to just 737 pages for the ordered-insertion index. Additionally, 206 more pages are left unused, making the actual disk space usage 57% larger.

Stupid and dangerous: SET GLOBAL sql_log_bin

It’s been almost 4.5 years since, during some code refactoring, it was decided (or accidentally changed?) that sql_log_bin should become a GLOBAL variable as well as a SESSION one. Almost 2 years ago, during MySQL 5.5 upgrades at Twitter, I filed MySQL Bug 67433 describing in detail how stupid and dangerous this change was, and asking for a reversal.

Nothing has been changed or reversed, so SET GLOBAL sql_log_bin continues to be allowed. I continue to directly see, and hear about damage caused by incorrect use of SET GLOBAL sql_log_bin. So, let me describe just how stupid and dangerous it really is.

What really happens when you run SET GLOBAL sql_log_bin?

If you have a master-slave replication configuration, your master will have binary logging enabled (log_bin) and be recording each transaction to its local binary logs, which the slaves read (more or less in real time) in order to replicate. This log must be complete for replication to be useful and trustworthy. If it is incomplete, serious data loss may occur.

It’s been possible for a long time to use SET [SESSION] sql_log_bin=0 within a connection (with a SUPER user) to temporarily disable binary logging of a session. This can be quite useful to be able to run e.g. ALTER TABLE commands without having them immediately replicated to the slaves.

Most people would guess that running the very similar SET GLOBAL sql_log_bin=0 would disable binary logging for all sessions (you know, GLOBAL?) immediately. However, that’s not quite the case.

As background, there are essentially three different scopes of variables in MySQL server:

  • Global-only: The variable only exists, and only makes sense, in a global scope. An example is innodb_buffer_pool_size — there is only one buffer pool, so it makes no sense in any session context. These variables may also be read-only, so that they can’t be changed at runtime.
  • Global and session: The global variable defines the session default, and is copied to the session variable on connect. A user may change the variable within their session if they want, and there may be restrictions on doing so. If the global variable is changed, it will only affect new sessions; existing sessions will keep the variable they copied at session start. A typical example of this is max_allowed_packet.
  • Session-only: There is no global variable associated with the session variable. A user may change the variable only within their session.

The sql_log_bin variable was previously Session-only and with the commit mentioned above, it was made Global and session.

There are a few implications of this:

  • When a session is initiated, it copies its sql_log_bin value from the global scope to its session scope copy.
  • If the DBA issues SET GLOBAL sql_log_bin=0, it only affects new sessions, any already-connected sessions will continue logging. This makes it pretty useless for managing a failover.
  • Changes to the global variable using SET GLOBAL do not even affect the currently connected session; issuing SET GLOBAL sql_log_bin=0 within a session does not prevent the following commands in that session from being binary logged. This is actually often the first sign that something didn’t work as the DBA expected—they suddenly find all slaves to be executing something they thought they had prevented from being replicated.
  • If a connection is made while sql_log_bin=0 and the DBA then issues SET GLOBAL sql_log_bin=1 the sessions which started while it was 0 will continue to not log anything to the binary log for as long as they are allowed to stay connected. This also makes it pretty useless for managing a failover.
  • If the DBA accidentally issues SET GLOBAL sql_log_bin=0 briefly, even for milliseconds, the entire master-slave replication topology may be irreparably damaged and if this is done without immediately noticing it, permanent and severe data loss may occur.

Is anyone putting it to good use?

The only comment that MySQL Bug 67433 has received from Oracle was from Sveta Smirnova, saying:

Regarding to “revert” I don’t agree: with global access it is possible to make a server master online, without bringing it down.

If you Google search for “set global sql_log_bin” you can find plenty of examples of people talking about it in various languages. I couldn’t find a single example of a script or person using or describing its use correctly, and I looked through pages and pages of examples.

So here’s a Hall of Shame instead:

Also notably, the MySQL manual text for sql_log_bin also still does not adequately describe or warn about its behavior.

Your move, Oracle

Do the right thing, and revert this change. We even provided a patch.

Update: Morgan Tocker, MySQL Community Manager at Oracle is requesting feedback on the future of SET GLOBAL sql_log_bin. Go and make your opinion known!