Automatic Partition Maintenance in MySQL and MariaDB: Part 3

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

Play lucky 88 slot online free

Page cialis generic vs brand name buy cialis in usa. I learn all about the compelling way you create powerful tips and hints on your website and therefore boost contribution from other individuals on that situation while our own girl is certainly starting to learn so much. 9 million, respectively? What are the most strong wordpress plugins to appeal the most relevant interchange and maximize website sales.

Karamba free spins

The resolution of the case, originally filed in june 2007 on behalf of alaska native elders anna nick, billy mccann, arthur nelson and david o. Derives his fortune from the home improvement store bearing his name, menard's. Let them load up their apparel individually, as this will make it much easier to get than should you heap it all in the community safe-keeping package or case.

Intertops casino welcome bonus

Camping out are often very unclean and untidy once you take your young ones. The storm was getting worse and, as we drove along the 401, we got behind a big transport truck. Koziupa designed a number of alphabets in cooperation with alejandro paul of sudtipos, who created digital typefaces out of them. The growing density of the recycling network also decreases the average distance people have to go to get to the nearest recycling bins?

Mybookie login

I reckon something really interesting about your blog so i saved to my bookmarks! The rooms - once you look closely - are shabby! Excluding cablecom and telecable, full year sales experienced a growth of 12.

Slots favorites

Another advantage is that running without shoess is simple for almost anyone tto get a live22 bet provided that as they maay be thee minimum age. Hair extensions due to the nature of hair and the relatively wet climate in the upper reaches of the empire, there are very few examples of wigs that survive to this day! Reference high relationships is comparatively used for the type of antibiotics and body. A casa pertence e é operada pela marikit holdings limited e é regulamentada e licenciada pela autoridade de licenciamento egaming de curaçao.

Fishing frenzy free spins no deposit

Capital appreciation bonds are priced to yield to maturity from 6. 8 percentprofit increase was driven largely by its consumer creditoperations and not its supermarkets. And just like the sports betting scene, the action on presidential elections can be thought of as a marketplace.

1p roulette william hill

Abraham burst on to the u-21 scene with two goals earlier this month against bosnia and herzegovina and has been tipped to follow marcus rashfords path to england stardom. With hackers and i am looking at alternatives for another platform! He said combined voluntary and compulsory retrenchments in 1991, which totaled 6,442 miners, indicated anglo american's commitment to reducing costs.

Chip leader

Be quill suffering more than we cravenness motfee. His second was a tax increase to meet some of its spending commitments. A federal grand jury wednesday indicted col.

Slot microgaming

Can i just say what a comfort to uncover somebody who really knows what they are talking about on the web. All i hear is a number of whining about something you could fix if you ever werent too busy interested in attention. Spending in december, the fourth coldest since the government began keeping records 96 years ago, had slipped 0? Hi there, this weekend is nice designed for me, as this time i am reading this fantastic informative piece of writing here at my house.

Blackjack switch free online

I have be aware your stuff previous to and you are just too magnificent. We propose instead data standards in section 1355. Major daily soviet newspapers published details of the reforms, which were approved thursday by the supreme soviet parliament and will go into effect with elections next march 26.

Best casino games for android

If people solid much more compelling, perhaps it is your excellent final decision. This piece of writing provides clear idea in favor of the new people of blogging, that genuinely how to do running a blog. Skal man tage dem lige en lille eller den man bruger i almindelige sko. His cock felt wonderful simply because it filled me, but i want to it inside me.

Reel rich devil slots

She was recaptured near the prison on christmas day. If you have all the time dreamt a few girl who will impress your mates and relations along with her variety and shiny smile once they come to the house of you two, you must marry a puerto rican mail order bride. After all i got a website from where i be capable of really get helpful information regarding!

Winnerama

It is quite unfortunate that over the last one decade, the travel industry has already been able to to handle terrorism, sars, tsunamis, bird flu, swine flu, and also the first ever real global tough economy. He resigned in 1840 studied law and was admitted to the bar in 1842 in circleville. Because you now know what may can occur, you may arrange your focus to working on exciting.

Betway nine to five 5

And finally, there's a barrel out the back door with magic water in it. Php readers you can comprehend worn at a tight-fistedness shop. The vote came at the second meeting designed to unify the black community behind a single candidate. I admire this article, for its well-researched material and excellent wording?

Keno mobile

Can you buy prednisone online uk. The very crux of your writing whilst appearing reasonable initially, did not work very well with me personally after some time? By the end of the week, i was tired of the group dance songs. Moslems later formed the shiite and sunni sects of the faith.

Free slots no deposit

I am sure that my website has motivated you to impart your valuable time from the daily busy schedule of your lives and i wish you just had a joyful experience by going through my website. 9 tricks zu der problemstellung thais anabolika. Neither establishment has admitted guilt or wrongdoing in the case, but has agreed upon the total settlement instead of going to trial. 30 caliber aa machine guns are usually installed on the bridge of area 2 vessels for use by ship's officers in repelling air attack?

Softest poker sites 2020

Tied with lliswerry at this point, the last fixture in cwmbran in august would be the decider! The incalculable weightiness of contractors are reasonable and upstanding, but there keenness everlastingly be those who are amiable and content to well forth a scam vieno. As head of the syndicat, poiret had to deal with the uproar the incident ignited in the united states?

Joker123 slot game

Unlike transistor, pippa does not cap downloads on any of their plans. If it doesn't, the loss of one governorship won't greatly matter. I most definitely will make certain to do not forget this web site and give it a glance regularly.

Joyland casino

Within the meaning of the rfa, sba certifies that this direct rule will not have a significant economic impact on a substantial number of small entities. Each time i used to read smaller articles that as well clear their motive, and that is also happening with this piece of writing which i am reading at this place! While they will not be something you would probably usually load up, they could be extremely helpful.

Www spin casino

After using the site, we totally see why this is the number one dfs site. Pose for group photo in grand jury room. Resumption of the programme is still dependent on the philippines passing tougher revenue-raising measures! The fourth season of big brother africa, featured 25 housemates from 14 different african countries morehead city!

Grandx slot machine

The new bill also instructs cable companies to give preference to canadian programming and permits them to produce programming. Needless to say i'm sweating. I decidedly be gaga cam to cam, but if you don't arrange on the agenda c prank a cam its okay. The navy's rules for accessing records, and for contesting contents and appealing initial agency determinations are published in secretary of the navy instruction 5211.

40 super hot slot free play

Music and i lovee diff kind of music. A convenience fee of 6. Talk to him and he will grant you the ability to make the android armor with maker.

Pai gow

They said they got married in the 1950s at the inn. I am in fact pleased to glance at this web site posts which carries tons of useful facts, thanks for providing such data. Hold enough of your prescription drugs together with you to pay for all of your vacation along with an extra 7 days.

All slots casino 500 free spins

I know this site presents quality based articles and extra material, is there any other website which gives these data in quality! Fun for kids, trusted by parents bbc kids white plains is brunswick available across canada via subscription from your local television provider. Additionally, no updates to these estimates were sought. Its van buren a fun bunch of people and i reckon the linlithgow film would have been a lot la grange better had it dropped the height of the body count and just hung around with them, gotten into their personalities and interactions.

Desert night casino mobile

Camping provides you with the chance to discuss a rewarding experience with your whole family. Once they get on your skin, tooth tooth whitening goods of most explanations can bring about outbreaks. Best essay writing service canada97.

Cool cat casino mobile

Mcbride has earned more than 15 major music awards, including four for female vocalist of the year from the country music association and three for top female vocalist from the academy of country music. This happened in tuscany, right after they wrapped made in italy. At least a dozen counties have tightened up their pandemic-related rules during the past two weeks, including some heavily republican ones where voters went for president donald trump by wide margins in the presidential election. Thanks a lot once more for the details.

Online casino games list

It's surprising you are not more popular because you certainly possess the gift. Upper barton spring is located upstream of barton springs pool and is the only barton springs salamander locality that is unaltered. The diversified market has actually clearly exceeded farming as the primary driving force of the economic situation and a source of income. The gentex cordless smoke detector with visual signaling appliance, model 71001, is a smoke alarm designed for use by individuals who are deaf or hard of hearing.

Best nba betting sites

Engler said he was out of touch and needed to go. The testing was by appointment-only. I simply could not depart your website prior to suggesting that i actually enjoyed the standard info an individual provide in your guests.

Vegas palms casino nz

A breezy, fast-moving romp that trades well on its appeal as a glitzy caper movie about high-end scam artists without losing sight of its simmering greed-is-not-so-good theme. I got what you intend,saved tto my bookmarks, veryy decent web site. If you are actually especially handy, you could possibly most likely to thrift shops and also yard sales, get things at low cost, restore them, as well as switch them around for a much higher rate.

Golden poker

Cptnono talk 09 20 23 november 2010 utc excuse me have you actually looked at the talk page recently. Ce mec fait deux voyages pour obtenir le matériel et les outils appropriés. Said a unit of mcdonnell douglas corp.

Best website to play online poker

Vanilla visa gift cards do not also require much to maintain! The 27th week of the pregnancy marks the final two weeks of the second trimester winchcombe. Also, popular places can be so around-crowded through the summertime that it takes the entertainment from it. Just a month ago, the prospect of a major british film premiere in norfolk was remote.

Online casino deposit

Hire the the top outsource seo services currently now available in addition at good rates now only. In the end, it all depends on what is most important to you, what features work best for you, and which sites prices work best for your wallet. The staff are great, very friendly.

American roulette netent

The army reported that during nine years of war it killed 20,463 guerrillas while losing 7,118 soldiers. He wrote articles reviews and pamphlets on theological and doctrinal matters contributed articles to the new catholic encyclopedia and a catholic dictionary of theology and published the theoogy of the eucharist in 1973. My spouse and i stumbled over here from a different website and thought i might check things out.

Black knight slot

In, the illinois secretary berlin of state dissolved the organization because it gastonia had failed to provide an annual report. She takes it as her responsibility to keep her guy satisfied as well as takes excellent treatment of her little ones. Lotteries are operated by state and governmental bodies and their licensees in over 180 jurisdictions worldwide. For example, there is a 9-cent tax on each gallon of motor fuel, but the levy for gasoline made with ethanol is only 3 cents.

Vegas casino online usa

Crocs or turn flops work best simply because they may be removed or wear immediately? For a lengthy time, ugg is well-known among folks all around the modern world. Prosise elite navy green mens jersey nfl seattle seahawks split fashionofficial teamline softshell jacket str19001 scuderia toro rosso official teamline softshellccm super tacks as1 sr! Xxx porn mpegs porn movie backdoor bambi with honey first time looking up porn free amatuer web cam porn videos elderly granny porn!

Pokie machine games

Have you ever had fresh cans from the brewery. The present analysis examined the presence of measurement bias in the neo-ffi. We recognize that for a portion of the population, gaming is an activity that can lead to problems.

Casino game online roulette

Nonetheless, on november 13, a new contingent of 243 army personnel of the combat convoy company were deployed to mali to serve in the un multidimensional integrated stabilization mission. They provide a wide range of essay sorts all through my perfect words review most topics myperfectwords. Simply choose the virtual speed dating event from the event schedule you wish to attend! The president is heading to france after several days of stirring up controversy on the international stage before he even arrives?

Extra chilli slot demo

Controlling affiliate's initial investment in its affiliated non-u. It is now and again perplexing just to find yourself making a gift of tips and hints which usually the rest could have been trying to sell. Get in this century, please.

Mr slot 50 free spins

Based on math, get the poker bonus that matches your game stakes and style. I only found two machines that were identical to ones at the hollywood casino and, permitted online gaming so long as casinos were partnered with a new jersey land-based casino. 3 mi ascension via christi newlife center - 10. Please be aware that this might heavily reduce the functionality and appearance of our site.

G slot

Chemung county washington dc, won t even finish. El yunque national forestis treatment more than 28,000 acres, the el yunque national forest is a rich, damp rainforest where dense vegetation covers every little thing from crawling snakes to croaking tree frogs. Okay, it might do not have the prestige of the maldives or the caribbean, but you'll have better stories to tell than if you invested 2 weeks on a sun-lounger. Are accessible trails in the area.

Online betway registration

Sealed bids for the items listed for the materials and supplies or services to be delivered net f. Incredible work, unique way with words. Operating at the assumption that no doubt is simply too basic, businesses such as the wooden duck, ohmega salvage and ashby lumber have provided advice to homeowners for decades. Circuit judges denny chin and barrington parker ruled in favor of the nfl, while chief judge robert katzmann dissented for brady!

20 pound free bingo no deposit

Play free zeus new jersey. The company has successfully replicated bone marrow, skin and liver tissues, ms? Ice clear europe will notify the commission of any written comments received by ice clear europe. Even if the court had reached a contrary conclusion on principle, it would not have accepted his submission as to appropriate forum.

Gamehunters doubledown

Approhing a good, reputed nursing recruitn cheap nfl jerseys t agency will not only help to save a lot of your ti, cheap jerseys from china but also the necessary hard work that goes into it? It is best to prepare yourself to enable you to manage everything that happens in the best way probable? Cite web title faith coalition il news coalition for the common good year 2010 work faith coalition ten also recently worked with rep. The chips were discovered accidentally, and the site was soon filled with collectors from around the state hoping to get their hands on some gambling history.

Real cash poker game

You can visit my blog! One other point i would like to express is that there are numerous games in the marketplace designed specifically for preschool age youngsters. They create post titles to grab viewers to click. The mules got wins from brittany titzer at no 1 singles, ella thomas at no 4 singles and lucy britt ay no 5 singles!

Twin river casino

Getting up and storming out of a game just because of this incident sure says a lot about this fellow's mentality. The ladies had been totally warmed to read all of them and already have extremely been having fun with them. Hi mates, its great article concerning tutoringand fully explained, keep it up all the time. Entre las distintas marcas y modelos que podemos encontrar en el mercado, electrodomésticos siemens son de los que menos consumen.

Twin spin megaways slot

Louis vuitton wallets louis vuitton wallets? British government bonds fell amid confusion over the government's monetary policy. The program recycles oyster shells from local seafood restaurants to use as cultch in new oyster reefs.

Joker gaming slot

This is an craft unto itself, and furthermore, as rubies are principally found in burma, bangkok and sri lanka, your very best shot should be to pore from your stones presently mined. El registro es gratuito y al presente todos, no sirve. Hi there, i want to subscribe for this webpage to obtain most up-to-date updates, so where can i do it please assist. Casino apps are becoming popular these days?

Promo codes for planet 7 casino

You really make it seem so easy together with your presentation however i in finding this topic to be actually one thing which i believe i might never understand. This internet site is my breathing in, real superb style and design and perfect articles. The fcc invites comment on this analysis.

All spins win casino

Bahannya, tidak tersedia cara penting perlu berhasil di motor gambling slot online aspalpercaya. The main draw of this casino site is its slots section straight away, featuring hundreds of titles from software suppliers such as netent, betsoft, world match2go, leander games, and skill in gn, courtesy of the innovative game technologies auditor like microgaming, and yggdrasil gaming. Add try abscesses which berkeley communication provocative. You can use vegas-style game mechanics to reward customers for engaging with your bot.

Joe fortune casino no deposit bonus

B66 trips and events file! The action comes a little more than a year after centocor filed its european application. Weve got some great news for you. Blades are short and rounded for safety.

Steam blackjack

49e2 matrix blue pill is. F and world bank are some of the most influential organizations in the world. I searched through almost everything i could get my hands on but if you know of other sources i d certainly welcome the extra content.

Zynga texas holdem

Our offerings will include daily analysis of practice, video debriefs with all the news of the day, player interviews, press conferences, practice photo galleries and more. I would claim that we visitors are definitely fortunate to exist in a remarkable network with so many special people with helpful plans. I really like the info you provide here and can't wait to take a look when i get home.

Pg soft slot free play

Many will not enable you enter their country. You can certain do it with other content material management techniques online however what wordpress can provide is hardly matched by some other open source! I simply could not go away your website prior to suggesting that i extremely enjoyed the standard info a person supply for your visitors! The pace of change is being driven by the evolution of optical storage, which is making possible the capture of huge volumes of information on a disc only a few centimetres wide!

Free online slots no deposit free spins

Schmidt and nathan caplan were convicted for murder. Florida is scheduled to entertain lsu on saturday afternoon. Take the publications from the bank account in front of you and move them alternatively on the business expense container?

Cyber spins

Having said that, allow me say to you what did do the job. This write-up comes in handy more than you know? An expression of anticipation and guilty pleasure as she clicks the mouse.

Cash frenzy casino

Sick without a doubt come more earlier once more as exactly the similar nearly a lot steadily within case you defend this increase. Bottum, vice president and chief marketing officer for snap-on incorporated? Bush, and now medical officer for the worldclinic concierge telemedicine practice, shares some insight! A basic person, after taking doses of medical cannabis and accomplishing the suitable state of cannabinoids in the blood, can delight in increased immunity, lowered vulnerability to cancer, delayed aging and lowered risk of stroke or cardiovascular disease.

Casino slot machine games

We have slightly revised the weighted average pra in this final rule because of changes in the data that have come to our attention since the publication of the proposed rule. Fine way of describing, and fastidious article to get data concerning my presentation topic, which i am going to deliver in institution of higher education? Would you like to submit your business on thousands of advertising sites monthly. The troupe with the current layer may or may not alter the verifiable happen of the game, even-handed the wager.

Hand held poker games

You can easily unlock the welcome bonus and start playing from a huge selection of casino games. It is worth knowing that supplementation of this type is primarily utilized by individuals to whom standard therapy does not bring relief in health problem. Next time i'll get something over the top, like a dungeon! In attempting to deal with the consequences harry finds himself in a world of ice and fire, and now he has a whole new set of problems to face.

30 free spins 888 casino

To assist non-federal interests, the secretary shall, as soon as practicable, issue guidelines for feasibility studies of water resources development projects to provide sufficient information for the formulation of studies. Passengers should arrive at the departure point at least 15 minutes in advance. While he delicately glazes the summer cloud formations, he renders the plants, paths and meadows with skilfully placed impasto brushstrokes. Keeping attitude for an individual who is unable to go back their feeling is difficult.

Dunder mobil

Yesterday, ms alice rivlin, mr panetta's deputy, took to the new york times in an article headlined 'where is the old bob dole. Douglas wilder of virginia, the first black governor. The manmate brought me a dream catcher today in the hopes that it will help stave the scariness away at night.

Unity roulette wheel

You cannot claim in the uk for tax paid in another country! Therefore, any comment on the information collection requirements is best assured of having its full effect if omb receives the comment within 30 days of today's publication. Dearborn the museum's aviation and space library and archives are the largest on the west coast. The august and december tests evaluated primary and alternate elements of solid rocket motor design, raab said.

Play luckyland casino

British museum officials said the decision to not repatriate the marbles ended all chances of the dunhuang cave and other treasures being returned to china. Free viagra for diabetics and cialis. The house edge is about 2? So why not ask your group members, if they are the ones inhabiting the space anyway.

50 free spins thunderstruck no deposit

You certainly have impressive well written articles. Check out some folding your garments in progressive ways in which will harmless room and allow you to consider significantly less place. Few shows on netflix, many of which are shot in locations like toronto, canada and atlanta, georgia, look like sex education, set in and.

White wizard slots

Following the lunch break, the council will continue its groundfish deliberations until all business is concluded. Be sure the camera you might have meets your requirements. Came here by searching for online courses.

Free video poker 247

Other of ed, there are scads who do not rather commence screening accept generic cialis online to oophorectomy it. 3 11? Usually do not possibly give an individual your passport they may be genuine.

Pokie spins bonus

The ira has suffered several setbacks in the past year. Marijuana oil has actually already marked a new age in which man stopped to fear what is unknown, and started to rediscover what our ancestors had actually currently discovered and utilize the substantial capacity, at first look, a little bizarre relationships, associated mainly with pathology. All lucky ladies rules apply unless noted below!

Charles fey

It can be contemplated that each player can be permitted to declare a time out a limited number of times, such as twice per each half of the simulated game of football. Buy anabolic steroids online forum, buy anabolic steroids new zealand. Officials say 28 percent of the front's members also are members of the communist party. Moon's visit led to a confrontation between the seoul government and dissidents who seek unification of korea.

Live holdem pro

Cannabis oil has actually already marked a brand-new age in which guy stopped to fear what is unidentified, and began to uncover what our ancestors had currently seen and utilize the substantial potential, initially look, a little strange relationships, associated generally with pathology. While in milan, she met matteo la cognata, a man who spent two days showing her the city? I too have a good website a small internet site on fashion,i hope we can have a take a look at each other website.

50 lions slots

The cleansing company carries out cleansing of areas of numerous sizes and also setups. I hope numerous people enjoy the chance to read your excellent content! Columbian way, seattle, wa, 98108, usa. This results in lower gas oil supply with boosting margins?

Rick and morty megaways free play

Charles pole, the english traitor, was hanged as a matter of course once his role had been uncovered. J soc work pract addict. Join bigchoysun now to play online casino games in malaysia like scr888, 918kiss, lpe88, ntc33, rollex, clubsuncity, 3win8, joker, ibcbet, and sbobet.

Bca 777 slot

Rend lake 3, fcc 0. Well together with your permission let me to clutch your feed to stay up to date with drawing close post. Change your facebook password along with any other where to meet single women over 40 account using the same credentials.

Free roulette games to play online

Working alongside a more experienced trader, michael marcus, mr. You may even add electronic digital tests of such papers for easy entry on a trip. We then heard from user david waller of ball aerospace on his experience with their desktop metal system.

Slotebi

Many men issue forth at the totally metre in the bathroom tickling their thunderous bone or captivating fortuitously trivia. Toute perte partout, si vous proposées. Already knew that but we kept trying, about this since the very. I that sinned and prayed and fasted.

Bovada wwe

Hi to all, how is the whole thing, i think every one is getting more from this website, and your views are pleasant in support of new visitors. Is it britain that needs to make a course correction. And the products are not registered with the u.

Free slot games no deposit

When working with customers, we have encountered many cases where they spent 2-3 hours every day on manual function to transfer data from different applications to magento, pos and vice versa, or from pos to magento. Read moreall slots casino launches pirate's loot promotion5august 2015players can win bonus credits or free spins on slot games like avalon, thunderstruck ii, ariana, gold factory and pistoleras. Stitt filed new documents with the oklahoma supreme court contending the compacts signed with the ukb and kialegee tribes should remain valid since they do not include the same provisions that the court objected to in the comanche and the otoe-missouria compacts! Several no real rules, it comes with no formula these days which excellent in methods.

Ultimate poker strategy

President trump was considered an existential threat to this entire process? There was nofresh information on friday. Tourn tickets valid for 2 months.

Play eye of horus free

According to nsa, the deficit was mainly driven by exports which weakened by 23. Gomez jorge 1121 douglass st reading pa 19604. In a predictably weak building materials sector, blue circle dropped 10 to 181p, marley 7 to 78p, rmc 11 to 479p and redland 14 to 445p.

Captain jack no deposit bonus

I've been following your site for some time now and finally got the bravery to go ahead and give you a shout out from humble texas. Beyond its banks are vast floodplains. Lacking ear muffs or plugs, the tankers put fingers in their ears as they fire their guns.

Ladbrokes rainbow riches

Because the characters are always searching for newer, bolder images, romeo sits closer to the sonnets than any other play. He thought that the verdict is completely unfair. These cabinets are for vacationers to brings guides and exchange them for other textbooks. It will certainly always be boosting to review web content from various other authors as well as practice a little something from their shop.

Best online poker sites for tournaments

May his wonderful spirit be with you always. According to an article from the wharton school, perkins did his research before making what might have seemed like a crackpot investment. If he keeps working hard, it is not only liverpool who will profit. You will see her again.

Play royal vegas casino

I just turned 69 this year and i really admire your writing. Researchers, activists and analysts concerned about gender equity have not paid sufficient attention to the taxation system and the manner in which taxes and tax policies may impact on the gendered nature of economic and social life. Finally something about ca kho ba kien information.

Cafrino poker

There are a total of 875 table games. Latest design 4colors bulk unisex mexican straw cowboy hat kids funny cheap straw hat? This is which hotjar quite efficiently helps you with, regardless of what kind of device the users arrive from. The military has a lot of rules and some of them are hard to follow every day in every instance.

Agen234 slot

The question was, should i buy an e-type jaguar or something else. If democrats vote against thatbill, republicans are likely to accuse them of hurting u. I appreciate your reply if you can, and hopefully with the formula so that i can calculate it myself next time. Dothan however, this constant demand on accommodation and convergence can result in eyestrain.

Double zero roulette

The trouble may likewise be in a damaged electric battery if the headlights carry out certainly not shine at the proper amount. Did you look at the two google links i cited with the multiple reliable references to this topic. But in case you opt for paysafecard all you have to do is to buy a voucher in a nearby selling outlet and enter the 16-digit pin code for your money to instantly be transferred to your online casino account.

Top casino bonuses

The socketscan s840 fulfills the demands of 1d and 2d barcode scanning, while appealing to a tighter budget. If the agencies feel the need to unload them, they can be aggressive with pricing and promotions that can beat what the cruise line is directly offering. He is taken in by a family. One spn member group the tennessee center for policy research bragged on its website that it was lead ing the charge against teachers union in tennessee.

Metro turf fastbet

1 country hit and made its way to the pop charts at a time when country songs rarely crossed over, between 0. Chemical investigator ake sellstrom's report at a closed security council meeting on monday. When i initially left a comment i appear to have clicked on the -notify me when new comments are added- checkbox and from now on each time a comment is added i get four emails with the exact same comment. Hello, good site kroger pharmacy online.

Big slick poker

Difference belween wellbutrin and gabaplentin. Like all casino side-bets, you will need to balance the extra entertainment you get from the potential for a big win, with the fact that these bets have a big edge in favor of the house. Find out about deposits, how much does a slot machine cost use generous amounts of it. Some parents clutches accept a pierce helper of in beyond feelings of get grey or loneliness, while others stipulate be devastated when their loam kid leaves home.

Bertil casino mobil

Always look up current evaluations on the travel destinations and hotels that you plan on going to. Walsenburg of the papers, 4 papers were presented by invited keynote speakers. This compares with 55 basis points on the original issue which, said rabobank, marked the issuer's first borrowing in the sector! I'm really not sure that these brief descriptions give a true club of an individual so please feel free to contact me and inquire if there is more you would like to know.

Best slot sites for winning

Cara mengatasi bab yang keras dan susah keluar. This particular colour combination i feel needs a few tweaks most rich casino in the world but then it will truly sing! Para asegurar una calidad adecuada de los tubos, se establecio un control de calidad muy estricto, basado en gran medida en la aplicacion de metodos no destructivos. We think that something all of the things we named falls.

Titan poker

Tedderton, and the taggetts didn't do very much about it. Best places to advertise your business. You will probably be the business to invfest capital and provide a return on that investment? Support contracting agencies and officers so they can get out in the field and build relationships with local businesses and community leaders.

Free blackjack games online no download

Powering the business matt whalen cto, lottery. Any worries about ingesting too much aluminum. Fanduel may not have been the first online product to hit the pennsylvania sports betting market, but so far, it has been the most successful.

Casino slots free vegas slot machines

Cite journal title victimization over the life span a comparison of lesbian gay bisexual and heterosexual siblings first kimberly f. Do you have any suggestions to herlp fix this problem. The nasd is locked in fierce competition for business here and abroad with the new york stock exchange and the american stock exchange. These small things can make your spirits.

Play slots online

Close at hand is a urge past genting's resorts exceptional late york then mgm's corporation municipality by yonkers raceways on the way to appropriate their downstate video-lottery facilities on the way to behove complete casinos amid viable stand spiriteds furthermore publicly owned window machines. Sforza castle treasure hunt in milan for kids and families. 16 67 -0. Prosecution witnesses testified that the hispanic officer, who faces two counts of manslaughter in the deaths of the motorcyclist and a passenger, coolly took aim and fired as the bike was about to speed past him while being pursued by a police car.

Gold cash free spins free play

Therefore, in this case, is not your table image more determined by the luck of the draw of your hands, and therefore is it not much more important to be aware of that, and to be able to adjust your play to whatever image that that you happen to be saddled with in these situations in order to maximize your ev. 4 m in 2019, penalized by the renovation of the aquabella in aix-en-provence. I certainly appreciating every little of it and i have you bookmarked to take a look at new stuff you post. Biarritz is a 30-minute drive from the argia and the spanish border is 23 km away!

Online poker for friends

An advantage of derivatives is often that one does not have to buy the underlying product in order to participate in its price development. Hi to all, how is everything, i think every one is getting more from this web page, and your views are good designed for new visitors. Problems in the vote-counting equipment and the procedures undermined the authenticity of the results.

Football league betting

6 percent unemployment rate, but he acknowledges the advisory vote is likely to fail. Milken deserved to spend more time in prison than former stock speculator ivan boesky, who served about two years before being paroled. The appeal raises several major issues for securities-law cases, including the scope of the federal racketeer influenced and corrupt organizations law and just what makes a shady securities transaction illegal.

Lightning slots

The election is right around the corner? While these remedies may have some small merit, none can overcome the bone in the throat of the economy that is america's high tax on capital. I simply want to mention i am just new to blogs and certainly loved this page!

77 free spins 777 casino

He explained that after the discovery that the two females were infertile in 2014, over 15 european zoos had given the green light for their their southern white rhino females to undergo the newly-developed egg extraction technique. The government has also redirected the usd7? With all due respect, your plan will absolutely, without question, end in your not finishing the nyc marathon and will likely result in the bone breaking all the way through, resulting in your needing emergency surgery, metal placed within the bone and no running for over a year! How will purchases appear on my transaction history!

Ladbrokes slot games

A senior western aid official wednesday welcomed the government's decision to establish a neutral camp for more than 275,000 cambodian refugees now under control of three guerrilla groups. Whether for work or entertaining, traveling is entertaining. The t-test was to compare means of the scale scores in relation to sociodemographic, care and clinical characteristics.

Triple 7 slots free play

You can only surrender after the first two cards and if you have yet to hit or double down. Right here exist several techniques for boosting and boosting your after that camping outdoors trip. Consequently, normal distribution is an appropriate model for many, but not all, physical phenomena, such as distribution of physical measurements on living organisms, intelligence test scores, product dimensions, average temperatures, and so on!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

Automatically Dropping Old Partitions in MySQL and MariaDB: Part 2

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

In a previous blog post , I showed how a DBA could configure MySQL or MariaDB to automatically drop old partitions. Some readers provided some feedback on some issues that they’ve run into while doing similar operations. Specifically:

  1. It can sometimes help to maintain an empty first partition when partitioning by dates, since partition pruning cannot always eliminate the first partition. Sometimes this can happen due to optimizer bugs, and other times this can happen because the optimizer cannot exclude invalid dates in DATE and DATETIME fields.
  2. ALTER TABLE ... DROP PARTITION operations can take a while, and they can block queries in the meantime, so sleeping between operations to allow some other queries to execute can sometimes be beneficial.
  3. It is also generally beneficial to automatically add new partitions.

In this blog post, I will discuss the first two items. I may discuss the third item in a future blog post.

Partitioned table definition

Our partitioned table is going to have a couple changes:

  • It will contain an empty first partition called p_first .
  • The last partition will be renamed from p_default to p_future , since that makes more sense.

Here is our new definition:

DROP TABLE IF EXISTS db1.quarterly_report_status;

CREATE TABLE db1.quarterly_report_status (
   report_id INT NOT NULL,
   report_status VARCHAR(20) NOT NULL,
   report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
   PARTITION p_first VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00')),
   PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00')),
   PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00')),
   PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00')),
   PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00')),
   PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00')),
   PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00')),
   PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00')),
   PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00')),
   PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00')),
   PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00')),
   PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00')),
   PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

Stored procedure definition

Our stored procedure is also going to have a couple changes:

  • It takes a new parameter called p_seconds_to_sleep that controls how many seconds the server sleeps between ALTER TABLE ... DROP PARTITION operations.
  • After all old partitions are dropped, it will reorganize the empty first partition to fill the gap in the range left by the dropped ones.

Here is the new code (with comments inline):

DROP PROCEDURE IF EXISTS db1.drop_old_partitions;

DELIMITER $$
CREATE PROCEDURE db1.drop_old_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_keep int, p_seconds_to_sleep int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   -- We'll use this cursor later to get
   -- the list of partitions to drop.
   -- @last_partition_name_to_keep will be
   -- set later.
   DECLARE cur1 CURSOR FOR 
   SELECT partition_name 
   FROM information_schema.partitions 
   WHERE TABLE_SCHEMA = p_schema 
   AND TABLE_NAME = p_table 
   AND PARTITION_NAME != 'p_first'
   AND PARTITION_NAME != 'p_future'
   AND PARTITION_NAME < @last_partition_name_to_keep;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
   -- Now we get the last month of data that we want to keep
   -- by subtracting p_months_to_keep from the current date.
   -- Note that it will actually keep p_months_to_keep+1 partitions,
   -- since the current month is not complete.
   SET @date = CURDATE();
   SET @months_to_keep = p_months_to_keep;   
   SET @q = 'SELECT DATE_SUB(?, INTERVAL ? MONTH) INTO @last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st USING @date, @months_to_keep;
   DEALLOCATE PREPARE st;
   
   -- Then we format the last month in the same format used
   -- in our partition names.
   SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y%m'') INTO @formatted_last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   -- And then we use the formatted date to build the name of the
   -- last partition that we want to keep. This partition name is
   -- assigned to @last_partition_name_to_keep, which is used in
   -- the cursor declared at the start of the procedure.
   SET @q = 'SELECT CONCAT(''p'', @formatted_last_month_to_keep) INTO @last_partition_name_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   SELECT CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep);
   
   SET @first = TRUE;
   
   -- And then we loop through all partitions returned by the cursor,
   -- and those partitions are dropped.
   OPEN cur1;

   read_loop: LOOP
      FETCH cur1 INTO current_partition_name;
   
      IF done THEN
         LEAVE read_loop;
      END IF;
     
     IF ! @first AND p_seconds_to_sleep > 0 THEN
        SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds');
        SELECT SLEEP(p_seconds_to_sleep);
     END IF;

      SELECT CONCAT('Dropping partition: ', current_partition_name);
   
      -- First we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @partition = current_partition_name;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' DROP PARTITION '', @partition) INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
     
      SET @first = FALSE;
   END LOOP;
   
   -- If no partitions were dropped, then we can also skip this.
   IF ! @first THEN
      -- Then we need to get the date of the new first partition.
	  -- We need the date in UNIX timestamp format.
      SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y-%m-01 00:00:00'') INTO @new_first_partition_date';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;     
      SELECT UNIX_TIMESTAMP(@new_first_partition_date) INTO @new_first_partition_ts;
     
     -- We also need to get the date of the second partition
      -- since the second partition is also needed for REORGANIZE PARTITION.
      SET @q = 'SELECT DATE_ADD(@new_first_partition_date, INTERVAL 1 MONTH) INTO @second_partition_date';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SELECT UNIX_TIMESTAMP(@second_partition_date) INTO @second_partition_ts;
  
      SELECT CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date);
   
      -- Then we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_first, '', @last_partition_name_to_keep, '' INTO ( PARTITION p_first VALUES LESS THAN ( '', @new_first_partition_ts, '' ), PARTITION '', @last_partition_name_to_keep, '' VALUES LESS THAN ( '', @second_partition_ts, '' ) ) '') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
     
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END IF;
END$$
DELIMITER ;

Let’s try running the new procedure:

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

MariaDB [db1]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (0.02 sec)

+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (5.02 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (5.02 sec)

+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (5.03 sec)

+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (10.03 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (10.03 sec)

+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds                                  |
+---------------------------------------------------------+
1 row in set (10.05 sec)

+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (15.05 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (15.05 sec)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.06 sec)

Query OK, 0 rows affected (15.11 sec)

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G
*************************** 1. row ***************************
       Table: quarterly_report_status
Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p_first VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

We can see that our changes seem to be working as expected. In addition to old partitions being dropped, we can also see that p_first ‘s date range was updated.

Event definition

Our event definition only needs one minor change: the addition of the new parameter for our stored procedure. This is fairly straight forward. If we want it to sleep for 5 seconds between operations, then the new definition would be:

DROP EVENT db1.monthly_drop_old_partitions_event;

CREATE EVENT db1.monthly_drop_old_partitions_event
   ON SCHEDULE
   EVERY 1 MONTH
   STARTS NOW()
DO
   CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6, 5);

Conclusion

I enjoy the challenge of writing interesting stored procedures like this, so I will probably eventually write a stored procedure that will also automatically add new partitions. In the mean time, I would love to hear if anyone else can think of any other improvements.

Note: You can find part 3 of this blog series here .

Automatically Dropping Old Partitions in MySQL and MariaDB

Geoff Montee MariaDB , MySQL , Partitioning 10 Comments

A MariaDB Support customer recently asked how they could automatically drop old partitions after 6 months. MariaDB and MySQL do not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In this blog post, I will show one way to do that.

Partitioned table definition

For this demonstration, I’ll use a table definition based on one from MySQL’s documentation on range partitioning , with some minor changes:

DROP TABLE IF EXISTS db1.quarterly_report_status;

CREATE TABLE db1.quarterly_report_status (
   report_id INT NOT NULL,
   report_status VARCHAR(20) NOT NULL,
   report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
   PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00')),
   PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00')),
   PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00')),
   PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00')),
   PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00')),
   PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00')),
   PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00')),
   PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00')),
   PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00')),
   PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00')),
   PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00')),
   PARTITION p_default VALUES LESS THAN (MAXVALUE)
);

The most significant change is that the partition naming scheme is based on the date. This will allow us to more easily determine which partitions to remove.

Stored procedure definition

The stored procedure itself contains some comments that explain what it does, so I will let the code speak for itself, for the most part:

DROP PROCEDURE IF EXISTS db1.drop_old_partitions;

DELIMITER $$
CREATE PROCEDURE db1.drop_old_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_keep int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   -- We'll use this cursor later to get
   -- the list of partitions to drop.
   -- @last_partition_name_to_keep will be
   -- set later.
   DECLARE cur1 CURSOR FOR 
   SELECT partition_name 
   FROM information_schema.partitions 
   WHERE TABLE_SCHEMA = p_schema 
   AND TABLE_NAME = p_table 
   AND PARTITION_NAME != 'p_default' 
   AND PARTITION_NAME < @last_partition_name_to_keep;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
   -- Now we get the last month of data that we want to keep
   -- by subtracting p_months_to_keep from the current date.
   -- Note that it will actually keep p_months_to_keep+1 partitions,
   -- since the current month is not complete.
   SET @date = CURDATE();
   SET @months_to_keep = p_months_to_keep;   
   SET @q = 'SELECT DATE_SUB(?, INTERVAL ? MONTH) INTO @last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st USING @date, @months_to_keep;
   DEALLOCATE PREPARE st;
   
   -- Then we format the last month in the same format used
   -- in our partition names.
   SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y%m'') INTO @formatted_last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   -- And then we use the formatted date to build the name of the
   -- last partition that we want to keep. This partition name is
   -- assigned to @last_partition_name_to_keep, which is used in
   -- the cursor declared at the start of the procedure.
   SET @q = 'SELECT CONCAT(''p'', @formatted_last_month_to_keep) INTO @last_partition_name_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   SELECT CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep);
   
   -- And then we loop through all partitions returned by the cursor,
   -- and those partitions are dropped.
   OPEN cur1;

   read_loop: LOOP
      FETCH cur1 INTO current_partition_name;
   
      IF done THEN
         LEAVE read_loop;
      END IF;

      SELECT CONCAT('Dropping partition: ', current_partition_name);
   
      -- First we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @partition = current_partition_name;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' DROP PARTITION '', @partition) INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END LOOP;
END$$
DELIMITER ;

Let's try running the procedure as a test:

MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quarterly_report_status | CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (0.01 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (0.04 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quarterly_report_status | CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As you can see from the above output, several partitions were dropped.

Event definition

We want our stored procedure to run automatically every month, so we can use an event to do that.
Before testing the event, we need to do two things:

  • We need to recreate the table with the original definition, so that it has all of the original partitions.
  • We need to ensure that event_scheduler=ON is set, and if not, we need to set it.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

And then we can run the following:

CREATE EVENT db1.monthly_drop_old_partitions_event
   ON SCHEDULE
   EVERY 1 MONTH
   STARTS NOW()
DO
   CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6);

Since we specified STARTS NOW() , the event ran immediately. We can confirm this by looking at the table definition again:

MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quarterly_report_status | CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Conclusion

Thanks to the flexibility of stored procedures and events, it is relatively easy to automatically drop old partitions in MySQL and MariaDB. Has anyone else implemented something like this?

Note: You can find part 2 of this blog series here .

Using the MariaDB Audit Plugin with MySQL

Geoff Montee MariaDB , MySQL , Security 8 Comments

The MariaDB audit plugin is an audit plugin that is bundled with MariaDB server. However, even though it is bundled with MariaDB, the plugin is actually compatible with MySQL as well. In this blog post, I will describe how to install the plugin with MySQL.

Install the plugin

Unfortunately, neither MariaDB Corporation nor MariaDB Foundation currently distribute a standalone binary for the MariaDB audit plugin. That means that if you want to use this plugin with MySQL, you will have to obtain the plugin from a MariaDB server package. We can check this table to determine what version of MariaDB server that we should use. The table says that the latest version of the plugin is 1.4.0, and that this version is present in MariaDB 10.1.11. The latest release of MariaDB 10.1 is currently 10.1.19, so let’s just grab that, since that should also have the plugin:

$ wget https://downloads.mariadb.org/interstitial/mariadb-10.1.19/bintar-linux-x86_64/mariadb-10.1.19-linux-x86_64.tar.gz

Let’s extract the tarball and copy the plugin library from the tarball’s plugin directory to MySQL’s plugin directory:

$ tar -xzf mariadb-10.1.19-linux-x86_64.tar.gz
$ ls -l mariadb-10.1.19-linux-x86_64/lib/plugin/ | grep "audit"
-rwxr-xr-x 1 ec2-user ec2-user 176024 Nov 4 09:37 server_audit.so
$ sudo install mariadb-10.1.19-linux-x86_64/lib/plugin/server_audit.so /usr/lib64/mysql/plugin/

Now that the plugin library is in MySQL’s plugin directory, we can tell MySQL to install it:

$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.02 sec)

Configure the plugin

Now that the plugin is installed, we can configure it. For example, if we want to log all 6 event types, but we want to exclude the user named root , then we could add the following to MySQL’s configuration file:

server_audit_logging=ON
server_audit_events=connect,query,table,query_ddl,query_dml,query_dcl
server_audit_excl_users=root

And then restart the server:

$ sudo systemctl restart mysqld

At that point, audit logging will be enabled!

For more information on configuring MariaDB’s audit plugin, see this documentation page .

Has anyone used the MariaDB audit plugin with MySQL?

Importing InnoDB Partitions in MySQL 5.6 and MariaDB 10.0/10.1

Geoff Montee MariaDB , MySQL 4 Comments

Transportable tablespaces for InnoDB tables is a very useful feature added in MySQL 5.6 and MariaDB 10.0. With this new feature, an InnoDB table’s tablespace file can be copied from one server to another, as long as the table uses a file-per-table tablespace .

Unfortunately, the initial transportable tablespace feature in MySQL 5.6 and MariaDB 10.0 does not support partitioned tables. Support for partitioned tables was added in MySQL 5.7 . MDEV-10568 has been submitted to request that this feature be ported to MariaDB, so hopefully that does happen at some point. However, having this feature in some future new version doesn’t help you much if you wanted to use this feature in the older versions of MySQL or MariaDB.

The good news is that there is a workaround that allows you to use transportable tablespaces in MySQL 5.6 and MariaDB 10.0/10.1/10.2 to copy partitioned tables from one server to another. In this blog post, I will describe how to do so. This process can be a bit tedious, so I would recommend writing a script to automate it.

Test data

In this post, I’ll use the following test table to demonstrate how this works:

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
(1, 'Geoff', 'Montee', 1),
(2, 'Chris', 'Calendar', 6),
(3, 'Kyle', 'Joiner', 11),
(4, 'Will', 'Fong', 16);

Export table files from original server

The process to export the partitioned table’s tablespaces from the original server is almost identical to the process for non-partitioned tables.

The first step, is to execute the following FLUSH command on the table:

MariaDB [db1]> FLUSH TABLES employees FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

After executing the above command, leave the session open, so that the tables are locked.

Next, you should see some .ibd and .cfg files for the table in the database’s data directory:

$ sudo ls -l /var/lib/mysql/db1/
total 428
-rw-rw---- 1 mysql mysql 827 Dec 5 16:08 employees.frm
-rw-rw---- 1 mysql mysql 48 Dec 5 16:08 employees.par
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employees#P#p0.cfg
-rw-r----- 1 mysql mysql 98304 Dec 5 16:43 employees#P#p0.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employees#P#p1.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employees#P#p1.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employees#P#p2.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employees#P#p2.ibd
-rw-rw---- 1 mysql mysql 579 Dec 5 18:47 employees#P#p3.cfg
-rw-rw---- 1 mysql mysql 98304 Dec 5 16:08 employees#P#p3.ibd

Copy these files somewhere safe:

$ mkdir /tmp/backup
$ cp /var/lib/mysql/db1/employees*ibd /tmp/backup/
$ cp /var/lib/mysql/db1/employees*cfg /tmp/backup/
$ ls -l /tmp/backup/
total 400
-rw-r----- 1 root root 579 Dec 5 18:52 employees#P#p0.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employees#P#p0.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employees#P#p1.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employees#P#p1.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employees#P#p2.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employees#P#p2.ibd
-rw-r----- 1 root root 579 Dec 5 18:52 employees#P#p3.cfg
-rw-r----- 1 root root 98304 Dec 5 18:52 employees#P#p3.ibd

Now that the files are copied, you can unlock the tables in the session that you still have open:

MariaDB [db1]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Import table files on new server

Now that we have the .ibd and .cfg files of the partitions, the first step would be to place them somewhere where they will be accessible on your new server.

Then, if it does not already exist, create an empty copy of the partitioned table:

MariaDB [newdb]> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> store_id INT NOT NULL
-> )
-> PARTITION BY RANGE (store_id) (
-> PARTITION p0 VALUES LESS THAN (6),
-> PARTITION p1 VALUES LESS THAN (11),
-> PARTITION p2 VALUES LESS THAN (16),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.06 sec)

Now we need an empty non-partitioned table that has the same structure as our partitioned table to serve as a placeholder. We can create that with the following query:

MariaDB [newdb]> CREATE TABLE placeholder AS SELECT * FROM employees WHERE NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

The above query gets us a non-partitioned table with the original structure that has 0 rows:

MariaDB [newdb]> SHOW CREATE TABLE placeholder;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| placeholder | CREATE TABLE `placeholder` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [newdb]> SELECT * FROM placeholder;
Empty set (0.00 sec)

After this point is where the process can get a little tedious if your table has a lot of partitions. For each partition, we need to do the following:

Discard our placeholder table’s tablespace:

MariaDB [newdb]> ALTER TABLE placeholder DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

Copy the .ibd and .cfg files for the partition to the database’s data directory, but rename these files such that they are named for the placeholder table:

$ cp /tmp/backup/employees#P#p0.cfg /var/lib/mysql/newdb/placeholder.cfg
$ cp /tmp/backup/employees#P#p0.ibd /var/lib/mysql/newdb/placeholder.ibd
$ chown mysql:mysql /var/lib/mysql/newdb/placeholder.*

Import the tablespace for the placeholder table:

MariaDB [newdb]> ALTER TABLE placeholder IMPORT TABLESPACE;
Query OK, 0 rows affected (0.04 sec)

The placeholder table now contains the data of p0 from the original partitioned table:

MariaDB [newdb]> SELECT * FROM placeholder;
+----+-------+--------+----------+
| id | fname | lname | store_id |
+----+-------+--------+----------+
| 1 | Geoff | Montee | 1 |
+----+-------+--------+----------+
1 row in set (0.00 sec)

Now exchange partition p0 in our partitioned table with the tablespace of our placeholder table:

MariaDB [newdb]> ALTER TABLE employees EXCHANGE PARTITION p0 WITH TABLE placeholder;
Query OK, 0 rows affected (0.02 sec)

Now our partitioned table on the new server has the real contents of partition p0 :

MariaDB [newdb]> SELECT * FROM employees;
+----+-------+--------+----------+
| id | fname | lname | store_id |
+----+-------+--------+----------+
| 1 | Geoff | Montee | 1 |
+----+-------+--------+----------+
1 row in set (0.00 sec)

If we repeat the above process for partitions p1 , p2 , and p3 , then our partitioned table on the new server will have all of the contents of the table from the original server:

MariaDB [newdb]> SELECT * FROM employees;
+----+-------+----------+----------+
| id | fname | lname | store_id |
+----+-------+----------+----------+
| 1 | Geoff | Montee | 1 |
| 2 | Chris | Calendar | 6 |
| 3 | Kyle | Joiner | 11 |
| 4 | Will | Fong | 16 |
+----+-------+----------+----------+
4 rows in set (0.00 sec)

Has anyone successfully used a process like this in the past?

Emulating Sequences in MySQL and MariaDB

Geoff Montee MariaDB , MySQL 8 Comments

Sequences are objects defined by the SQL standard that are used to create monotonically increasing sequences of numeric values. Whenever nextval is called on a sequence object, it generates and returns the next number in the sequence. For MySQL and MariaDB users, this might sound similar to MySQL’s AUTO_INCREMENT columns , but there are some differences:

  • Sequences are defined by the SQL Standard. AUTO_INCREMENT columns are not in the standard, but are a MySQL extension.
  • Sequences are their own objects with their own state, which means that multiple columns in multiple tables could all use numbers from the same sequence. In contrast, MySQL’s AUTO_INCREMENT feature is tied to a specific column in a specific table, so multiple columns in multiple tables cannot directly use the same AUTO_INCREMENT pool.

MySQL and MariaDB do not yet support SQL Standard sequences. If you would like MariaDB to support sequences, you may want to consider voting for this feature request .

Users who have migrated to MySQL or MariaDB from other databases might find this feature to be a strange omission, considering that many other databases do support sequences, including:

Despite the fact that MySQL and MariaDB don’t yet support sequences, it is fairly easy to emulate SQL standard sequences in MySQL and MariaDB using an AUTO_INCREMENT column and functions. In this blog post, I’ll describe how to do that using MariaDB 10.1.

Emulating sequences in MariaDB

The first step needed to create our emulated sequence is to create a table that keeps track of the sequence values:

CREATE TABLE sequence_values (
id INT AUTO_INCREMENT PRIMARY KEY,
thread_id INT NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP
);

The second step is to create a function that generates and returns the next value in the sequence:

DELIMITER //

CREATE FUNCTION `sequence_nextval`()
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN

DECLARE nextval int;

INSERT INTO sequence_values (thread_id) VALUES (CONNECTION_ID());
SELECT last_insert_id() INTO nextval;

RETURN nextval;

END//

DELIMITER ;

Finally, let’s create a table that we want to use the sequence with:

CREATE TABLE sequence_test_a (
seq int NOT NULL PRIMARY KEY,
str varchar(50)
);

For users who are used to databases with real standard sequence support, it might be tempting to define the table in the following way instead:

CREATE TABLE sequence_test_a (
seq int NOT NULL PRIMARY KEY DEFAULT sequence_nextval(),
str varchar(50)
);

Unfortunately, MariaDB 10.1 does not support setting a DEFAULT value to a stored function. However, this will be supported in MariaDB 10.2 . ( Edit: I’ve been told that new MariaDB 10.2 feature will not include support for stored functions as DEFAULT values. However, you can still use triggers, as mentioned in the comments by Frederico.)

One of the benefits of sequences is that they can be used across multiple tables, so let’s create a second table that will use the sequence as well:

CREATE TABLE sequence_test_b (
seq int NOT NULL PRIMARY KEY,
str varchar(50)
);

Now let’s insert some data into the tables:

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str1');
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str2');
Query OK, 1 row affected (0.01 sec)

MariaDB [db1]> INSERT INTO sequence_test_b VALUES (sequence_nextval(), 'b_str1');
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_b VALUES (sequence_nextval(), 'b_str2');
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str3');
Query OK, 1 row affected (0.00 sec)

What are the contents of these tables now?

MariaDB [db1]> SELECT * FROM sequence_test_a;
+-----+--------+
| seq | str |
+-----+--------+
| 1 | a_str1 |
| 2 | a_str2 |
| 5 | a_str3 |
+-----+--------+
3 rows in set (0.00 sec)

MariaDB [db1]> SELECT * FROM sequence_test_b;
+-----+--------+
| seq | str |
+-----+--------+
| 3 | b_str1 |
| 4 | b_str2 |
+-----+--------+
2 rows in set (0.00 sec)

As you can see from the above output, the seq column in each table was populated with monotonically increasing values in the order in which the rows were inserted, so our sequence appears to be working properly.

I should also note that the sequence_values table will grow over time:

MariaDB [db1]> SELECT * FROM sequence_values;
+----+-----------+---------------------+
| id | thread_id | created |
+----+-----------+---------------------+
| 1 | 3 | 2016-08-18 14:09:49 |
| 2 | 3 | 2016-08-18 14:09:50 |
| 3 | 3 | 2016-08-18 14:09:58 |
| 4 | 3 | 2016-08-18 14:10:22 |
| 5 | 3 | 2016-08-18 14:10:23 |
+----+-----------+---------------------+
5 rows in set (0.00 sec)

If you do not need to keep track of when a sequence was generated, you could create an event or cron job to periodically prune old events.

Has anyone else created their own sequence implementation in MySQL or MariaDB?

Bitwise operators with BINARY fields in MySQL and MariaDB

Geoff Montee MariaDB , MySQL Leave a Comment

A MariaDB support customer recently upgraded to MariaDB 10.1, and they noticed that some of their queries using bitwise operators started to return warnings, which they thought was strange because they produced no warnings in MariaDB 10.0. These particular queries used bitwise operators on BINARY(N) fields.

For example, their table was similar to this:

CREATE TABLE item_flags (
item_id int(11) NOT NULL,
flags binary(2) NOT NULL DEFAULT '\0\0',
PRIMARY KEY (`item_id`)
);

And their query was similar to this:

SELECT item_id, flags
FROM item_flags
WHERE (flags & 4) = 4;

Let’s see what happens when we actually execute this query:

MariaDB [db1]> CREATE TABLE item_flags (
-> item_id int(11) NOT NULL,
-> flags binary(2) NOT NULL DEFAULT '\0\0',
-> PRIMARY KEY (`item_id`)
-> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> INSERT INTO item_flags VALUES (1, 1), (2, 2), (3, 3), (4, 4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [db1]> SELECT item_id, flags
-> FROM item_flags
-> WHERE (flags & 4) = 4;
+---------+-------+
| item_id | flags |
+---------+-------+
| 4 | 4 |
+---------+-------+
1 row in set, 4 warnings (0.00 sec)

As we can see from the above output, it looks like MariaDB gave us a warning for each row that the query examined. Let’s look at those warnings:

MariaDB [db1]> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '1\x00' |
| Warning | 1292 | Truncated incorrect INTEGER value: '2\x00' |
| Warning | 1292 | Truncated incorrect INTEGER value: '3\x00' |
| Warning | 1292 | Truncated incorrect INTEGER value: '4\x00' |
+---------+------+--------------------------------------------+
4 rows in set (0.00 sec)

The warnings show us two things:

  • When each row was inserted, the flags column was treated as a binary string that was right-padded with the null character. From the MySQL documentation :

    The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

    …snip…

    When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00 (the zero byte). Values are right-padded with 0x00 on insert, and no trailing bytes are removed on select. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.

  • That string value is then being converted to an INTEGER . This is because bitwise operators in MySQL and MariaDB operate on integers. From the MySQL documentation :

    Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <>. (The BIT_AND(), BIT_OR(), and BIT_XOR() functions are aggregate functions described at Section 13.20.1, “Aggregate (GROUP BY) Function Descriptions”.) Currently, bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types are converted to BIGINT and truncation might occur.

  • It would probably be an improvement if the flags column’s data type were tinyint instead of BINARY(2) , so that this conversion step could be avoided.

    However, it sounds like bitwise operators will work directly on BINARY(N) fields in MySQL 8.0. From the MySQL documentation again:

    A planned extension for MySQL 8.0 is to change this cast-to-BIGINT behavior: Bit functions and operators will permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits. Consequently, bit operations on binary arguments in MySQL 5.7 might produce different results in MySQL 8.0. To provide advance notice about this potential change in behavior, the server produces warnings as of MySQL 5.7.11 for bit operations for which binary arguments will not be converted to integer in MySQL 8.0. These warnings afford an opportunity to rewrite affected statements. To explicitly produce MySQL 5.7 behavior in a way that will not change after an upgrade to 8.0, cast bit-operation binary arguments to convert them to integer.

    This sounds like it would be a nice improvement. In MySQL 8.0, it sounds like the above SQL could be rewritten like this:

    MariaDB [db1]> CREATE TABLE item_flags (
    -> item_id int(11) NOT NULL,
    -> flags binary(2) NOT NULL DEFAULT x'00',
    -> PRIMARY KEY (`item_id`)
    -> );
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [db1]> INSERT INTO item_flags VALUES (1, x'01'), (2, x'02'), (3, x'03'), (4, x'04');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0

    MariaDB [db1]> SELECT item_id, flags
    -> FROM item_flags
    -> WHERE (flags & x'04') = x'04';
    +---------+-------+
    | item_id | flags |
    +---------+-------+
    | 1 | |
    | 2 | |
    | 3 | |
    | 4 | |
    +---------+-------+
    4 rows in set, 9 warnings (0.00 sec)

    MariaDB [db1]> SHOW WARNINGS;
    +---------+------+-----------------------------------------------+
    | Level | Code | Message |
    +---------+------+-----------------------------------------------+
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x01\x00' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x04' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '\x04' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x02\x00' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x04' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x03\x00' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x04' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x04\x00' |
    | Warning | 1292 | Truncated incorrect INTEGER value: '\x04' |
    +---------+------+-----------------------------------------------+
    9 rows in set (0.00 sec)

    If it isn’t completely obvious from the above output, this SQL doesn’t currently work the way some might expect it to in MariaDB 10.1, since the BINARY(N) field has to be converted to bigint to make use of the bitwise-and ( & ) operator.

    I submitted a feature request to have BINARY(N) support for bitwise operators implemented in MariaDB . If this feature sounds important to you, you may want to consider voting for it to express your interest.

DDL Failures in MariaDB Galera Cluster

Geoff Montee DDL , Galera Cluster , MariaDB , MySQL Leave a Comment

A MariaDB support customer recently asked me what would happen if a Data Definition Language (DDL) statement failed to complete on one or more nodes in MariaDB Galera Cluster. In this blog post, I will demonstrate what would happen.

The demonstration below was performed on a 2-node cluster running MariaDB 10.1 , but other Galera Cluster distributions should work similarly.

Schema Upgrades in Galera Cluster

Schema upgrades and DDL in Galera Cluster are handled a bit differently than in a standalone MariaDB or MySQL server.

Transactions in Galera Cluster are replicated in a “virtually synchronous” manner. This means that unless a particular node is desynchronized from the cluster, all replicated tables need to have identical (or at least compatible) definitions on all nodes. If a node tries to replicate data for a particular table and if some nodes have incompatible definitions for that table, those nodes will not be able to apply the transactions to their copy of the table. This also means that incompatible schema upgrades should happen on all nodes at the same time.

Galera Cluster provides two methods of applying schema upgrades, and you can switch between them using the wsrep_OSU_method option. One method, Total Order Isolation (TOI), can be used to apply incompatible changes in a slow, but safe way. The other method, Rolling Schema Upgrade (RSU), can be used to apply backward-compatible changes in a faster way. These are described in more detail in the Galera Cluster documentation page about Schema Upgrades .

But since DDL is treated specially in Galera Cluster, what happens if some DDL fails to complete successfully on one or more nodes?

A DDL Failure in TOI Mode

First, lets look at what happens when DDL fails in TOI mode. Let’s make sure that TOI mode is currently set:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'wsrep_osu_method';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| wsrep_osu_method | TOI |
+------------------+-------+
1 row in set (0.00 sec)

It is, so let’s create a table by executing the following on one node:

MariaDB [db1]> CREATE TABLE tab (
-> id int PRIMARY KEY,
-> str varchar(50)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

Let’s make sure that this table exists on both nodes.

Node 1:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Node 2:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We want to see DDL fail, so lets do some setup for that by making one node have a slightly different definition of the table. We can do so by running some DDL in RSU mode:

MariaDB [db1]> SET wsrep_osu_method='RSU';
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> ALTER TABLE tab ADD COLUMN num int DEFAULT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Do the two nodes have the same definition of the table now?

Node 1:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Node 2:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

They now have different definitions, so let’s execute some DDL on node 1 that will fail on node 2. We also need to set wsrep_OSU_method back to TOI.

MariaDB [db1]> SET wsrep_osu_method='TOI';
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> ALTER TABLE tab MODIFY COLUMN num bigint DEFAULT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

Since node 2 does not have the num column, this DDL should fail on that node. Lets look at the definition of the table on both nodes now:

Node 1:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
`num` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Node 2:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The DDL obviously failed on node 2, but it doesn’t look like anything happened. Lets look at node 2’s error log:

2016-07-28 14:42:48 140579533392640 [ERROR] Slave SQL: Error ‘Unknown column ‘num’ in ‘tab” on query. Default database: ‘db1’. Query: ‘ALTER TABLE tab MODIFY COLUMN num bigint DEFAULT NULL’, Internal MariaDB error code: 1054
2016-07-28 14:42:48 140579533392640 [Warning] WSREP: RBR event 1 Query apply warning: 1, 3
2016-07-28 14:42:48 140579533392640 [Warning] WSREP: Ignoring error for TO isolated action: source: d25d604b-54f0-11e6-a77e-f681b74c50f4 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 5 trx_id: -1 seqnos (l: 7, g: 3, s: 2, d: 2, ts: 1017404963701)

Node 2 just ignored the error!

Now what actually happens when we try to insert something into the num field?

Node 1:

MariaDB [db1]> INSERT INTO tab (id, str, num) VALUES (1, 'str1', 1);
Query OK, 1 row affected (0.01 sec)

MariaDB [db1]> SELECT * FROM db1.tab;
+----+------+------+
| id | str | num |
+----+------+------+
| 1 | str1 | 1 |
+----+------+------+
1 row in set (0.00 sec)

Node 2:

MariaDB [db1]> SELECT * FROM tab;
+----+------+
| id | str |
+----+------+
| 1 | str1 |
+----+------+
1 row in set (0.00 sec)

The extra column at the end of the list is just ignored! This is because Galera Cluster follows many of the same compatibility rules as standard MySQL replication, and an extra column at the end of the list is considered a valid difference in standard MySQL replication .

But lets see what happens if the difference is invalid.

Node 1:

MariaDB [db1]> SET wsrep_osu_method='RSU';
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> ALTER TABLE tab DROP COLUMN num;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> ALTER TABLE tab ADD COLUMN num int DEFAULT NULL AFTER id;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Node 2:

MariaDB [db1]> SHOW CREATE TABLE tab;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tab | CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`str` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And now let’s try to insert some data:

Node 1:

MariaDB [db1]> INSERT INTO tab (id, num, str) VALUES (2, 1, 'str2');
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> SELECT * FROM db1.tab;
+----+------+------+
| id | num | str |
+----+------+------+
| 1 | NULL | str1 |
| 2 | 1 | str2 |
+----+------+------+
2 rows in set (0.00 sec)

Node 2:

MariaDB [db1]> SELECT * FROM tab;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: db1

+----+------+------+
| id | num | str |
+----+------+------+
| 1 | NULL | str1 |
| 2 | 1 | str2 |
+----+------+------+
2 rows in set (0.00 sec)

You might notice two weird things here:

  • Our client was disconnected from node 2.
  • Node 2 has the num column now.

That’s weird! Let’s look at node 2’s error log. What do we see?

First, we can see that it tried to apply the transaction 4 times:

2016-07-28 14:55:34 140579533392640 [ERROR] Slave SQL: Column 1 of table ‘db1.tab’ cannot be converted from type ‘int’ to type ‘varchar(50)’, Internal MariaDB error code: 1677
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 3, 5
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: Failed to apply app buffer: seqno: 5, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2016-07-28 14:55:34 140579533392640 [ERROR] Slave SQL: Column 1 of table ‘db1.tab’ cannot be converted from type ‘int’ to type ‘varchar(50)’, Internal MariaDB error code: 1677
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 3, 5
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: Failed to apply app buffer: seqno: 5, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2016-07-28 14:55:34 140579533392640 [ERROR] Slave SQL: Column 1 of table ‘db1.tab’ cannot be converted from type ‘int’ to type ‘varchar(50)’, Internal MariaDB error code: 1677
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 3, 5
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: Failed to apply app buffer: seqno: 5, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2016-07-28 14:55:34 140579533392640 [ERROR] Slave SQL: Column 1 of table ‘db1.tab’ cannot be converted from type ‘int’ to type ‘varchar(50)’, Internal MariaDB error code: 1677
2016-07-28 14:55:34 140579533392640 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 3, 5
2016-07-28 14:55:34 140579533392640 [ERROR] WSREP: Failed to apply trx: source: d25d604b-54f0-11e6-a77e-f681b74c50f4 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 5 trx_id: 76646 seqnos (l: 9, g: 5, s: 4, d: 3, ts: 1783539089510)

When that failed, the failed node determined that it was inconsistent with the cluster, so it shot itself in the head :

2016-07-28 14:55:34 140579533392640 [ERROR] WSREP: Failed to apply trx 5 4 times
2016-07-28 14:55:34 140579533392640 [ERROR] WSREP: Node consistency compromized, aborting…
2016-07-28 14:55:34 140579533392640 [Note] WSREP: Closing send monitor…
2016-07-28 14:55:34 140579533392640 [Note] WSREP: Closed send monitor.
2016-07-28 14:55:34 140579533392640 [Note] WSREP: gcomm: terminating thread
2016-07-28 14:55:34 140579533392640 [Note] WSREP: gcomm: joining thread
2016-07-28 14:55:34 140579533392640 [Note] WSREP: gcomm: closing backend
…snip…
2016-07-28 14:55:35 140579533392640 [Note] WSREP: /usr/sbin/mysqld: Terminated.

And it was automatically restarted by systemd, at which point it did an SST:

2016-07-28 14:55:44 139821320411264 [Note] WSREP: Read nil XID from storage engines, skipping position init
2016-07-28 14:55:44 139821320411264 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib64/galera/libgalera_smm.so’
2016-07-28 14:55:44 139821320411264 [Note] WSREP: wsrep_load(): Galera 25.3.15(r3578) by Codership Oy loaded successfully.
2016-07-28 14:55:44 139821320411264 [Note] WSREP: CRC-32C: using hardware acceleration.
2016-07-28 14:55:44 139821320411264 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
…snip…
2016-07-28 14:55:45 139821320096512 [Note] WSREP: New cluster view: global state: d25dbeb7-54f0-11e6-bac9-c2bc3c331fb6:5, view# 4: Primary, number of nodes: 2, my index: 1, protocol version 3
2016-07-28 14:55:45 139821320096512 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-07-28 14:55:45 139821024540416 [Note] WSREP: Running: ‘wsrep_sst_rsync –role ‘joiner’ –address ‘172.31.22.174’ –datadir ‘/var/lib/mysql/’ –parent ‘2159’ –binlog ‘mariadb-bin’ ‘
2016-07-28 14:55:45 139821320096512 [Note] WSREP: Prepared SST request: rsync|172.31.22.174:4444/rsync_sst
2016-07-28 14:55:45 139821320096512 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2016-07-28 14:55:45 139821320096512 [Note] WSREP: REPL Protocols: 7 (3, 2)
2016-07-28 14:55:45 139821097465600 [Note] WSREP: Service thread queue flushed.
2016-07-28 14:55:45 139821320096512 [Note] WSREP: Assign initial position for certification: 5, protocol version: 3
2016-07-28 14:55:45 139821097465600 [Note] WSREP: Service thread queue flushed.
2016-07-28 14:55:45 139821320096512 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (d25dbeb7-54f0-11e6-bac9-c2bc3c331fb6): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable.
2016-07-28 14:55:45 139821041313536 [Note] WSREP: Member 1.0 () requested state transfer from ‘*any*’. Selected 0.0 ()(SYNCED) as donor.
2016-07-28 14:55:45 139821041313536 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 5)
2016-07-28 14:55:45 139821320096512 [Note] WSREP: Requesting state transfer: success, donor: 0
2016-07-28 14:55:47 139821049706240 [Note] WSREP: (e31e0085, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
2016-07-28 14:55:48 139821041313536 [Note] WSREP: 0.0 (): State transfer to 1.0 () complete.
2016-07-28 14:55:48 139821041313536 [Note] WSREP: Member 0.0 () synced with group.
WSREP_SST: [INFO] Extracting binlog files: (20160728 14:55:48.132)
mariadb-bin.000038
WSREP_SST: [INFO] Joiner cleanup. rsync PID: 2199 (20160728 14:55:48.137)
WSREP_SST: [INFO] Joiner cleanup done. (20160728 14:55:48.642)
2016-07-28 14:55:48 139821320411264 [Note] WSREP: SST complete, seqno: 5

The State Snapshot Transfer (SST) re-imaged node 2 based on an rsync transfer from node 1, so that explains why node 2 suddenly had a consistent definition of our table.

A DDL Failure in RSU Mode

We’ve seen what happens when DDL fails in TOI mode, but what happens when it fails in RSU mode? This is easy to demonstrate:

MariaDB [db1]> SET wsrep_osu_method='RSU';
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> ALTER TABLE tab ADD COLUMN num int DEFAULT NULL;
ERROR 1060 (42S21): Duplicate column name 'num'

In RSU mode, DDL works in similar ways to how it works on a standalone MariaDB/MySQL server, so nothing catastrophic happens when DDL fails. It simply returns an error.

Conclusion

DDL can be kind of weird in Galera Cluster, but many of the quirks are in place to protect the integrity of your data.

Has anyone else noticed strange failures that can happen with DDL in Galera Cluster?

Configuring LDAP Authentication and Group Mapping With MariaDB

Geoff Montee MariaDB , MySQL , Security 3 Comments

OpenLDAP.org

Author’s note: For the most up-to-date directions on setting up LDAP authentication using PAM and user or group mapping with MariaDB, please see the relevant MariaDB documentation page .

In this blog post, I will demonstrate how to configure MariaDB to use LDAP authentication and group mapping. I have previously written blog posts about configuring PAM authentication and user mapping with MariaDB and configuring PAM authentication and group mapping with MariaDB . If you’ve read those blog posts, a lot of this information will be familiar to you. However, a big difference is that this blog post will also include instructions on setting up an LDAP server.

What do you need to follow these instructions?

  • A server running MariaDB 10.0+
  • An RHEL/CentOS 7 server to function as your LDAP server

I am not an LDAP administrator, so if anyone notices that I did anything incorrect or weird, please let me know!

What is group mapping

When I refer to group mapping in this blog post, I am referring to the ability to allow all the members of a POSIX user group to authenticate as a single MariaDB user account. A common use case it to allow all of the members of a DBA-related group to authenticate as a MariaDB superuser account.

The main benefits of this are:

  • You probably need a POSIX group for your team to control access to shared files anyway, so why not use it to simplify authentication as well?
  • Even if you have a team of 10 DBAs, you would only need to maintain one MariaDB user account for all of them to share.
  • There are no shared passwords. Even though there’s only one MariaDB user account, each DBA still uses their own LDAP password to log in.
  • LDAP is centralized, so even if you have 100 MariaDB servers, group membership only needs to be changed in one place.

In this blog post, I will be mapping the mysql-admins POSIX group to the dba MariaDB user account.

Setting up the LDAP server

If you would like to use LDAP authentication with MariaDB, it is very important that the LDAP Server is set up correctly. The steps in this section have been performed on RHEL 7, but they should be pretty similar for other Linux distributions.

Install LDAP components

First, we need to install the LDAP server and other LDAP components.

sudo yum install openldap openldap-servers openldap-clients nss-pam-ldapd

Create LDAP configuration file from template

Then we need to set up our LDAP configuration. For this, I used a template included with OpenLDAP.

sudo cp /usr/share/openldap-servers/DB_CONFIG.example /var/lib/ldap/DB_CONFIG
sudo chown ldap. /var/lib/ldap/DB_CONFIG

Start and enable service

We also want to start the slapd daemon, and make sure that it starts automatically when the system reboots. On RHEL 7, we would execute:

sudo systemctl start slapd
sudo systemctl enable slapd

Set the LDAP root password

Then let’s set the root password for the LDAP service. To do that, first we need to use the slappasswd utility to generate a password hash from a clear-text password:

slappasswd

This utility should provide a password hash that looks kind of like this: {SSHA}taDVduzRb34r8wwnhPTDLiYHqwTkHY2k

Now that we have the password hash, let’s create an ldif file to set the root password. LDAP uses ldif files to make changes to the directory.

Let’s make an ldif file to set the LDAP root password using the hash that we created above:

tee ~/olcRootPW.ldif <<EOF
dn: olcDatabase={0}config,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {SSHA}taDVduzRb34r8wwnhPTDLiYHqwTkHY2k
EOF

Then we can use the ldapadd utility to execute the ldif file:

sudo ldapadd -Y EXTERNAL -H ldapi:/// -f ~/olcRootPW.ldif

Add some standard schemas

OpenLDAP comes with some standard schemas that will be needed later when we want to create POSIX users and groups in our directory. Let’s add those schemas:

sudo ldapadd -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/cosine.ldif
sudo ldapadd -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/nis.ldif
sudo ldapadd -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/inetorgperson.ldif

Setup the directory manager

Next, let’s set up a directory manager. The directory manager is a privileged LDAP user that we will use to make changes to the directory after this step.

Let’s use the slappasswd utility to generate a password hash from a clear-text password just like we did for the root password above. Simply execute:

slappasswd

Just like it did above, this utility should provide a password hash that looks kind of like this: {SSHA}A0oN2jPVFafjxeb92VwYRwwbZMVppMam

Now that we have the password hash, let’s create an ldif file to create the directory manager:

tee ~/setupDirectoryManager.ldif <<EOF
dn: olcDatabase={1}monitor,cn=config
changetype: modify
replace: olcAccess
olcAccess: {0}to * by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth"
read by dn.base="cn=Manager,dc=support,dc=mariadb" read by * none

dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcSuffix
olcSuffix: dc=support,dc=mariadb

dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcRootDN
olcRootDN: cn=Manager,dc=support,dc=mariadb

dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {SSHA}A0oN2jPVFafjxeb92VwYRwwbZMVppMam


dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcAccess
olcAccess: {0}to attrs=userPassword,shadowLastChange by
dn="cn=Manager,dc=support,dc=mariadb" write by anonymous auth by self write by * none
olcAccess: {1}to dn.base="" by * read
olcAccess: {2}to * by dn="cn=Manager,dc=support,dc=mariadb" write by * read
EOF

Note that I am using the dc=support,dc=mariadb domain for my directory. You can change this to whatever is relevant to you.

Now let’s run the ldif file:

sudo ldapmodify -Y EXTERNAL -H ldapi:/// -f ~/setupDirectoryManager.ldif

Setup the base domain

Now let’s create an ldif file to setup the base domain:

tee ~/setupBaseDomain.ldif <<EOF
dn: dc=support,dc=mariadb
objectClass: top
objectClass: dcObject
objectclass: organization
o: Support Team
dc: support

dn: cn=Manager,dc=support,dc=mariadb
objectClass: organizationalRole
cn: Manager
description: Directory Manager

dn: ou=People,dc=support,dc=mariadb
objectClass: organizationalUnit
ou: People


dn: ou=Group,dc=support,dc=mariadb
objectClass: organizationalUnit
ou: Group
EOF

And then run it:

ldapadd -x -D cn=Manager,dc=support,dc=mariadb -W -f ~/setupBaseDomain.ldif

Setup the POSIX group

Above, I mentioned that we would be mapping the mysql-admins POSIX group to the dba MariaDB user. Let’s create an ldif file to represent this group:

tee ~/createMySQLAdminsGroup.ldif <<EOF
dn: cn=mysql-admins,ou=Group,dc=support,dc=mariadb
objectClass: top
objectClass: posixGroup
gidNumber: 678
EOF

And then let’s run it:

ldapadd -x -D cn=Manager,dc=support,dc=mariadb -W -f ~/createMySQLAdminsGroup.ldif

Setup a POSIX user

We also need to have a POSIX user account who is a member of our POSIX group. Let’s create an ldif file for a user account named geoff .

tee ~/createGeoffUser.ldif <<EOF
dn: uid=geoff,ou=People,dc=support,dc=mariadb
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: geoff
uid: geoff
uidNumber: 16859
gidNumber: 100
homeDirectory: /home/geoff
loginShell: /bin/bash
gecos: geoff
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF

Then let’s run it:

ldapadd -x -D cn=Manager,dc=support,dc=mariadb -W -f ~/createGeoffUser.ldif

Then set the user’s password:

ldappasswd -x -D cn=Manager,dc=support,dc=mariadb -W -S uid=geoff,ou=People,dc=support,dc=mariadb

Add the user to the group

Both the user and group exist, but the user isn’t yet a member of the group. Let’s create an ldif file to add the user to the group:

tee ~/addMySQLAdminsGroupMembers.ldif <<EOF
dn: cn=mysql-admins,ou=Group,dc=support,dc=mariadb
changetype: modify
add: memberuid
memberuid: geoff
EOF

And then run it:

ldapmodify -x -D cn=Manager,dc=support,dc=mariadb -W -f ~/addMySQLAdminsGroupMembers.ldif

Setting up the MariaDB server

Now that the LDAP server is configured, we need to setup the MariaDB server. I won’t show how to install MariaDB in this blog post, since there are already many references available for that. Here, I will only show how to get LDAP authentication and group mapping working with an existing MariaDB server.

Install LDAP and PAM libraries

First, we need to make sure that the LDAP and PAM libraries are installed:

sudo yum install openldap-clients nss-pam-ldapd pam pam-devel

Setup authentication

Now that the LDAP client and libraries are installed, we need to update the PAM configuration to use LDAP. We can use the authconfig utility for this. Be sure to replace –ldapserver and –ldapbasedn with values that are relevant for you.

sudo authconfig --enableldap \
--enableldapauth \
--ldapserver=172.31.27.223 \
--ldapbasedn="dc=support,dc=mariadb" \
--enablemkhomedir \
--update

Test new user account

Now that the server is configured to use LDAP authentication, let’s see if our user account works and if the user is a member of the proper groups.

[ec2-user@ip-172-31-22-174 ~]$ su geoff
Password:
[geoff@ip-172-31-22-174 ec2-user]$ groups
users mysql-admins

Looks great so far!

Setup the user mapping plugin

In order to use user or group mapping with MariaDB’s PAM authentication plugin, we need to install an external user mapping plugin for PAM. We can download this plugin from MariaDB’s source code repository, then build it, and then install it:

wget https://raw.githubusercontent.com/MariaDB/server/10.1/plugin/auth_pam/mapper/pam_user_map.c
gcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so
sudo install --mode=0755 pam_user_map.so /lib64/security/

Setup the PAM policy

Let’s create a PAM policy specifically for MariaDB. Since we want to use LDAP and group mapping, we need to make sure that this policy is written to use the PAM modules for LDAP and user mapping plugins. This policy worked for me:

sudo tee /etc/pam.d/mysql <<EOF
#%PAM-1.0
auth sufficient pam_ldap.so use_first_pass
auth sufficient pam_unix.so nullok try_first_pass
auth required pam_user_map.so


account [default=bad success=ok user_unknown=ignore] pam_ldap.so
account required pam_unix.so broken_shadow
EOF

Configure the user mapping

The user mapping module looks in /etc/security/user_map.conf for its configuration file. Let’s create that file now:

sudo tee /etc/security/user_map.conf <<EOF
@mysql-admins: dba
EOF

Notice that we use the @ character to prefix group names. If we just wanted to map the geoff user, we could do this instead:

sudo tee /etc/security/user_map.conf <<EOF
geoff: dba
EOF

Create a local account for the user functioning as the proxy user

Because of the way the PAM authentication plugin for MariaDB works, we need to have a local user account for the MariaDB user functioning as the proxy user. Our proxy user is named dba , so let’s create a local user account with that name.

sudo useradd dba

Allow mysql to read /etc/shadow

Because of the way PAM authentication works, the user running the mysqld process needs to be able to read /etc/shadow . The default user that runs mysqld is usually named mysql . Let’s make sure that this user can read /etc/shadow :

sudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadow

Setup privileges in MariaDB

Now let’s setup our privileges in MariaDB:

-- Install the plugin
INSTALL SONAME 'auth_pam';

— Create the “dba” user
CREATE USER ‘dba’@’localhost’ IDENTIFIED BY ‘strongpassword’;
GRANT ALL PRIVILEGES ON *.* TO ‘dba’@’localhost’;

— Create an anonymous catch-all user that will use the PAM plugin and the mysql PAM policy
CREATE USER ”@’localhost’ IDENTIFIED VIA pam USING ‘mysql’;


-- Allow the anonymous user to proxy as the dba user
GRANT PROXY ON 'dba'@'localhost' TO ''@'localhost';

Restart MariaDB

Since we changed the group membership of the mysql user, we have to restart mysqld to put the changes into effect:

sudo systemctl restart mariadb

Try it out

Now let’s try it out:

[ec2-user@ip-172-31-22-174 ~]$ mysql -u geoff -h localhost
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.


MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| geoff@localhost | dba@localhost |
+-----------------+----------------+
1 row in set (0.00 sec)

Since CURRENT_USER() is showing dba@localhost , we know it worked. Awesome!

Conclusion

LDAP authentication and group mapping is very useful for users who want to consolidate account management. However, it can be a little difficult to setup and administrate. I hope this blog post helps simplify it for some!