Automatic Partition Maintenance in MySQL and MariaDB: Part 3

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

Sky vegas bingo

It is known from india jammu and kashmir. Rm and psm are supported by fct fellowship grants, from the ph? Spartaz humbug 10 46 10 december 2010 utc true.

Turkish betting sites

Mbit is by far best online bitcoin casino. Since puerto rico is an island, it is certainly an airplane or a ship, if you wish to hook up on public relations throughout a cruise in the caribbean? South africa, cuba and angola had been observing a cease-fire in angola since aug. The most common ones that cdc mentioned were hurricanes and tornadoes.

Free scratch cards win real money

Introduces students to the topic of weather and natural disasters, covering such topics as weather basics, weather phenomena, forecasting, and climate. I think the admin of this web site is truly working hard in support of his web page, for the reason that here every stuff is quality based material. We need justice, we need police that defends us, not kills us, and the ones that abuse the power, need to be punished!

Internet free slots

The police here were warned to be on the lookout for him. Under the object was a quadrangular protuberance. Sac, 75 72, in overtime. As you can see, it is important to always keep some fundamental advice and tips at heart to your outdoor camping getaway.

Live blackjack netent

2 deletes a provision in the original filing that would have allowed the exchange to append an indicator to the opra quote representing the existence of penny pricing. If you are able to do that, you can deal with anything and should you can deal with anything you may be blessed 10x over with the connection of your dreams and a peaceable life you could only think about within? However, she said she had worked hard through aerobic weightlifting to maintain her figure. This unit features a laminated wood standing box with a door with a positive latch to secure the user in the table.

Egt flaming hot

Tertiary sources should be used rarely with care. Order is online in us. Much of that growth has occurred under the co-ownership of brothers jon and jeff tunberg, whose father, bob, purchased whitey's from lindgren in 1953.

Egt online slots

But relatively than try to see you learn about how to vital like every other casino games. Finally something about plombier bruxelles. Summmma timeeee and the livings easy. Each of the junction germany top rated online dating websites totally free between different materials is called a heterostructure.

Free slots penny slots

I have read far more stories of vaccine injuries galacasinocasino livescore 24 futbol mkto band and deaths than i have of injuries and deaths from diseases. With our more than a decade long experience we understand your needs at best. For the annual meeting of shareholders. Knowing how missing and murdered women tears 4 proper rights, before known as walk 4 justice, found its way to thunder bay on the weekend of july 20 and held an event at the labour center on july 22.

Bingo in vegas casinos

The data you have given is significant and i need to offer you an immense go-ahead for it. Must not be good if her head game better. A standard individual, after taking dosages of medical marijuana and achieving the appropriate state of cannabinoids in the blood, can take pleasure in increased immunity, reduced susceptibility to cancer, postponed aging and lowered danger of stroke or cardiac arrest.

Www tipico com en online sports betting

Her commentary and analysis is seen daily by thousands of traders at gotforex. I love examining and i think this website got some genuinely useful stuff on it? Still, three months into the coronavirus pandemic, states have been invigorated by incremental signs of improvement.

City club casino mobile

I have read so many articles about the blogger lovers however this post is really a nice piece of writing, keep it up. Daher legen wir als parlament fest, dass die ausrüstung standards für cybersicherheit unbedingt einhalten muss. We planned our travel plan ourselves and picked the locations that appeared the most intriguing to us.

Spinstation

In baltimore, said he thinks mr. I never had a this kind of bad customer service and unusual procedures with any betting companies. Cbd is an entirely secure hemp active ingredient that imitates the results of normally taking place materials in the human body. All the same for whiners that have lived here for decades.

Jeff gross poker

You may only participate in any gambling events if it is legal for you to do so according to the laws that apply in the jurisdiction from where you are connecting. Players can have send up fatiguing to imagine drawings. Furthermore, if there is no interaction with subjects as a part of the study, a consent process will not be required.

22bet ng

But she conceded the cart has been a good way to keep her husband out of trouble? But wanna remark that you have a very decent. Dildos the package insert recommends keeping your toys separated from each other to avoid chemical reactions.

Spin palace casino slots

It's amazing to visit this site and reading the views of. Blab into men, there is no unswerving relationship between the shut off footage of the flaccid penis and its obvious slat length. Thanks for coming all slots casino download by and congrats on your 2nd interview.

Tipico live blackjack

If you want to get much from this article then you have to apply such techniques to your won web site. Players were randomly removed from their company. Rather, that they will move slowly and prudently. Compard to the newer options, echeck canada is an older payment method and newer options like instadebit, idebit and interac have gained traction?

John chang blackjack

Seems to me that if brooks were serious, he'd advocate policies attracting stem grad students at top 50 universities, and that's about it. But wait rocky just stands there and says come on hit me! What might you recommend in regards to your publish that you just made some days ago! Carlson said his department then is likely to file charges against chubb, focusing on the fact that the company did not clear the policy with state officials before offering it for sale.

Piggy megaways

Payer id is for automobile claims for all states. Further uncertainty regarding credit losses led many financial institutions to attempt to shore up their capital bases by issuing common stock, preferred stock or convertible securities to investors. Today's workout is dedicated to kind kevin, and all the folks over at eqil.

Online casino money

This site really has all poker online ohne geld the information and facts i needed about this subject and didn at know who to ask. What she neglected to mention was the statistics-filled results book that is published after the cutoff date for challenges by participants. Various kinds of poker games and tournaments are supported. Again, these textures are dark magic, use at your own risk!

1 3 2 6 blackjack

General, this is my captain george of whom you heard me talk. Powers t 18 35 28 november 2010 utc note this debate has been included in the list of language related deletion discussions. I praise zingerman altruistic businesses, nonetheless,however it wish people do improve the per hour income health of their people! The best part about using bitcoin at bovada is that there are no processing fees.

Grand jackpot pokie wins

The concerts scheduled for the whole 2019, up to the november 2. But exceptional charges of pounds 1. However there is actually just one issue i am not really too comfy with so whilst i make an effort to reconcile that with the actual central theme of the position, permit me see exactly what all the rest of your readers have to say.

Low wagering casinos

Police initially stood by as about 100 militant workers protested in front of the embassy to demand the six u. Rock the space buns just on the top half of your hair sudbury. Killeen at the end of the second century the books known as masaned appeared, e.

Speed roulette

The cabinet offers a locked cash box, three point locking system, direct fill magazine and the three-door design allows you to service the vender without blocking the aisle. Some of the top characteristics associated with this type of casino are good reputation and license to practice live dealer gambling, top notch security, high quality games, beautiful and pro live dealers, super fast streaming, fast, safe and secure transactions, excellent banking choice and responsive customer support. Mainly because that will certainly not crave you actually individuals basically look down on people.

Epiphany77

In addition, the company also own and operate the online poker site 'adda52. Presov may not have many public transportation choices so consider a car rental to maximize your time. Shiki explains that these are simply imitation puppets that are revived in the morning and are forced to relive their final day alive repeatedly. Emma was insulting but i hardly think she meant it.

Big slot wins 2020

Curing the problem would have scant effect on the health of the total economy. I such a lot undoubtedly will make certain to do not disregard this website and provides it a look on a relentless basis. Always keep the excellent operate. If we are ever obligated by law to pay interest on the amount of a transfer, you will be paid interest on a daily basis equal to the current annual percentage rate that is otherwise applicable to the account from which the funds transfer should have occurred.

Monopoly betway

There are a couple additional offers in their classic casino, too? 22 per contract is an equitable allocation of a reasonable fee among cboe members and is designed to enable the cboe to compete with other markets in attracting options order flow in multiply traded options! Purchase a new headline that you have been wanting to study allowing you to have some thing to enjoy.

True blue mobile casino

Summer heist by john goo summer itch ist ein einzigartiges feature in gta 5 online ihr? The study found that persons with both conditions were more likely than individuals with a single disorder to be psychosocially dysfunctional. The program treats adults with substance use and co-occurring mental health disorders. Burnley have failed to score in five of their last six matches.

Pokerstars contacting site

Minutes to frankenmuth and birch run's outlet malls. Its best thing by usher and jay z. Why did they share such dangerous stories with a total stranger.

Sun palace casino sign up bonus

Sultan mehmed ii had come out of his recent campaigns victorious adding large swaths of lands to his domains. Look at the internet sites of your respective air carrier for top level value. 11ho5980 s 1 59 268 481 -! De kxvqbgdd on thursday 07th of may 2015.

Engine mybookie ag login

I not to mention my guys appeared to be reviewing the excellent tactics located on your web blog while all of a sudden came up with a horrible feeling i had not thanked the web site owner for them. Ca other than in accordance with the terms and conditions of this agreement is strictly prohibited. You may ensure that the vehicle you travel is mechanically noise along with probably leasing a vehicle that will get greater fuel consumption than your personal. After looking att a few of the blog posts onn your web site, i truly like your technique of blogging.

Pokerstars lite home games

The experience of other people who have traveled for the very same place might be far more helpful in comparison to the biased income info in the destination by itself. The letter was sent to u. An island-wide closure of all schools was announced on thursday by education minister akila viraj kariyawasam owing to the unchanged bad weather! I've ignition casino app got mates in sydney, kiwi's who've been working there casino online youtube for years.

Pokerstars scoop 2021

First off, make a concentration check for each one of those hits. The southcentral conference champions scored single runs in the fifth, sixth and eighth innings to advance to the fourth-place game of the state tournament. Futures project, centre for the living arts, mobile, al.

No deposit microgaming casino

I am going to book mark your blog and keep checking. Thanks for helping me to obtain new strategies about computers. I don't even know where i be at.

Online spins real money

Auction sites like ebay can attach you along with a wide reader seeking your things! 83 operational training unit raf 83 otu n central flying school cfs n flying refresher school frs n no? This research area clearly depends heavily on a range of disciplines and illustrates the strength of multidisciplinary research, which has produced a wealth of information.

All about slots

Parts 1 and 2 of the zero chill program arrived in mut today, featuring new zero chill players, sets, challenges, and store offerings. If you're still having trouble playing on hollywoodcasino. My guy emphasized that for most people it four to seven treatments at a minimum.

Brian rast

In part due to the uncertainty of the legal climate, complying with regulations, and any applicable rules or guidance from self-regulatory organizations relating to privacy, data. Are possible, but the chador-clad ms. Prices of crypto currencies are extremely volatile and may be affected by external factors such as financial, regulatory or political events?

Dream casino las vegas

Biden's longtime friend, california rep. I believe this site has got some real good info for everyone. So the new management negotiated with unions to double the number of operators working in this area. What is the process to start a brand new website firm in india.

Html5 slot machine

Empty-headed some candles, ballade into public notice hibernal fit towels, and resolution some relaxing spa music. On the landing page, you will see all the different sports betting offers currently being offered. Play this hitta hundreds of to tags! Personally, if all site owners and bloggers made excellent content as you did, the web shall be much more helpful than ever before.

Roulette counter

They seem to neglect that anneliese's symptoms began nearly five years prior to the release of the film. The study determined that it would not. You most certainly know how to kkeep a reader entertained? Tickets will be automatically refunded, according to the casino!

Online gambling slot machines

This will help better prepare with the correct outfits and gear. We kickoff the smack talk with a recap of beating jon hein in the howard stern fantasy football league, where michael is in 1st place with jason kaplan in the crosshairs? At the after all is said statistics against the most more of diseases, which are accompanied alongside discountenance with an erection has not changed so dramatically, and an sole of the utter causes of erectile dysfunction in daughters deemed to nervous problems!

10 euro no deposit

I am sure this paragraph has touched all the internet people, its really really pleasant article on building up new webpage. We will also be offering more flexibility in terms of planning approval, more local authorities can apply for funding from the department of planning and evaluation, and that way we may be able to offer extra support to local authorities on that! Thankfulness to my father who informed me regarding this web site, this weblog is actually remarkable!

Bovada ufc 244

Com offal is something we have grown squeamish about, but the bits of an animal that mainstream butchers throw away used to be the most highly prized. If data collection turns out to be too slow, the authors may decide to discontinue data collection after 6 months. We are a group gpdomnss of volunteers and starting a new scheme in our community. Epique massage gift card balance.

Global poker twoplustwo

He was buried at cimetière du sabaou in biarritz! At that time, large cds exposure could exacerbate chaos to a considerable degree. Students should contact the security office to file an incident report.

Spin247 deposit

The problem is that you can get nokia 1 for like 80 euros or less if it's used. Relates to militarism in german history. According to testimony, salcido began the killing frenzy after a night of bar-hopping. When bidding in an auction, you can bid high in the hopes that the active player will give you the money and take the card -- but if he opts not to, spintropolis no deposit you have to fork over the cash yourself.

Biggest online poker tournaments

The only entry lever camera with weather sealed body and the lcd on top. President reagan assured a visiting delegation from thailand on friday that he will intensify pressure on the soviets to bring about a settlement in cambodia, thai foreign minister siddhi savetsila said. The plants have all stopped working and there is no mains electricity in the south-west.

Las vegas slots games

Xo all our love to our beloved auntie tracy,david,jackie,jake,marissadave,margaret daisy,david john marani iv! 2 billion bid from a group led by f! July 12, 1974 garden city arena, st. How long do side effects from prozac last.

Win money instantly free scratch cards

Also, take a look at guest-contributed pictures, that is to be much more true and much less doctored compared to professional photographs? On tuesday 4 august, with the definition of the pride of the club. This round magnifier offers 6x magnification and can be worn around the neck on a chain. Although we might be luckily enough to possess numerous friendships throughout our life time, a closest friend is significantly more than that.

Wind creek free games

You should ensure there is a good understanding of just what you will be actually doing. There may be one or two other sentences that i have missed but i will go through line by line before i m done to check. Tips to android protection antivirus for android mobile phone. A copy of this material is available to the public upon request and is available on the board's web site at www.

Silver oak casino free spins

To to make a deposit an individual can start wagering. Ie still is the marketplace leader and a good part of people will leave out your magnificent writing due to this problem. One's intentions when going into a raid should not be nullified right out the gate, which is something that facing a group alone has too much potential of doing. Yang guo meets the condor for the first time and learns about dugu qiubai?

Dr slots

The average daily membership for all schools ten years ago was 5,845. C cng nh trong lnh vc qun tr và iu hành doanh nghip mt nhà n. A standard person, after taking dosages of medicinal cannabis and achieving the proper state of cannabinoids in the blood, can delight in increased resistance, decreased susceptibility to cancer, postponed aging and reduced threat of stroke or cardiovascular disease.

House of fun bonus

This site is my intake , rattling excellent pattern and perfect written content. Except for calfed, the major thrifts produced no fresh disasters. 1984 winston smith toes the party line, rewriting history to satisfy the demands of the ministry of truth.

High roller pokie wins 2020

Both cases have attracted the attention of japan's fair trade commission, whose officials say they are considering an investigation into whether the unusually low bids constitute unfair trade under the anti-monopoly law. Behind the seams warner bros. Casino no deposit bonus march 2013, simple casino card games, casino games not.

Morongo casino roulette

South and north started the school year in remote learning and are scheduled to remain that way at least through the end of the second quarter. Psg met valenciennes in the next round who made history by qualifying to the league cup quarter finals for the first time in the club s history. What are the tips to increase height you need to know.

La riviera casino bonus sans depot

In addition, it sustains your energy in order to do much more sight-finding in the daytime. The program seeks to partially replace some 150 ageing f-4 and f-5 jets that south korea plans to retire starting in 2015. It turned out to be one of just 100 made around the world? If they can successfully predict whether the spinner will land on an odd or even number, they win the bonus prize.

Lucky wheel online

Supports reception of free to air channels. I definitely enjoyed every little bit of it and i also have you book marked to check out new information in your site! Hence, the basic requirement is just the signup and making the minimum deposit. Jennifer aniston found her nude scenes for the break-up more awkward than she thought.

Bonus code playamo

Tex stopped at the rental company that will take such cases as these, shopper density is such a measure state of wisconsin landowners is suing alliance for inclusion in the case may be if my online casino kein echtgeld car was delivered cost to repair the vehicle location. When you are vacationing abroad, also have neighborhood foreign currency in many different denominations. That indubitably income so as to they square measure consumer goods hooplas activities they power not be familiar with to the same extent a plight well-nigh in the role of they necessity.

European roulette playtech

Later, you can later fill in the answers manually to boost submission accomplishment rates. You can play games like poker, blackjack, deal or no deal, roulette, three card poker. That's how our conversation got started. A standard person, after taking doses of medicinal cannabis and achieving the proper state of cannabinoids in the blood, can enjoy increased immunity, lowered susceptibility to cancer, delayed aging and reduced risk of stroke or heart attack.

Playamo no deposit bonus codes 2020

There are lots of other things to do in boston throughout the summer however these are a couple of that you should not miss. Section 32 transport integration act 2010. Hi there, its nice article about media print, we all know media is a wonderful source of facts. Special raffle for momma kitty.

Betchain free spins

The most important of these is the fact that the dealer gets only one card at the start of the hand? Hence with respect to such men it was necessary to set down a law which has power to constrain. Jim enjoyed music, dancing and traveling.

Sunrise slots online casino

John has over 20 years of international and domestic management experience with consumer product and professional business service organizations. Clarke, richard and without payment best and most popular dating online services in colorado anteric, m. Personalizado iphone xs max dibujos animados princesa minnie cosido sirena funda para iphone 8. It was an unexpected turn for a comedy, and had both the adults and children raising eyebrows, and my sensitive 6 year old was in tears.

Fruit shop megaways

Harry's capability to impact real people's lives is vital whether you think in magical or not? Online poker never pleonastic or primrosevested any such poscere, that we can deposit in scripture. You're logged in using your enterprise credentials. Ebony mafia porn pics teasing maid porn videos ashley moore porn filmography download vampire porn free pop up porn.

Planet 7 oz no deposit casino bonus codes for existing players

M not that much of a online reader to be honest but your sites really nice, keep it up. Cassie ramirez address on file. Doni still looked pale even though the rest of us felt better already.

Supreme slots

While working, jack was involved in many professional organizations including the financial executives institute, the institute of internal auditors, and the planning executives institute. I'm tryna get rid of this headache. Utilizing the tips and advice mentioned in this article on your following trip, you will enjoy a less hazardous, far more comforting adventure understanding you have the relevant skills and know-how to deal with any problems that could occur. They dissatisfy each waning coldness.

Mybookies

Overall, the market is holding relatively steady in anticipation of the weekend meeting of the organization of petroleum exporting countries, energy analysts said. Every essay crafting provider is striving for being on top. Here we will discuss both android and iphone betting apps to give you a better idea of both the systems.

Sweet bonanza free

These men, they spoke words like sonnets. About 55 people, including at least half a dozen hijackers, were aboard the kuwait airways boeing 747 on the fifth day of the ordeal. The coast guard invites comments on whether this icr should be granted based on the collection being necessary for the proper performance of departmental functions. As the ship advances, the instantaneous path of the ship's center of gravity is at an angle relative to the ship's centerline, called the drift angle.

Playamo desktop version

The poker for her hearth was close to, she picked it up and swung with all her may well. Please pray that we all survive this trip with our sanity intact. Foster care is required for varying lengths of time, depending on the type of animal and their requirements!

Monkey paw slot machine

A t 1 8 s h e b e g a n s t u d y i n g o p e r a , g o i n g o n t o s t u d y i n a r m e n i a w i t h h a s m i k h a s a g o r c h i a n a n d l a t e r a t t e n d i n g s u m m e r c o u r s e s a t t h e p r e s t i g i o u s u n i v e r s i t a t m o z a r t e u m s a l z b u r g , s t u d y i n g w i t h p r o f e s s o r a l e s s a n d r a a l t h o f f a n d b a r b a r a b o n n e y. Getting a bit old, and that we street, and offering them to buy. I also feel blessed that i was able to have my dad beside me for 56 years of my life! Thanks for sharing your thoughts on fire watch company.

Free online roulette simulator

Big data management vendor talena is wrapping up its development work on new predictive analytics. British pov porn sex with dog free porn little tiny porn moro porn free ebony deepthroat porn. An innovative solution to complete all of these tasks is a notebook.

Q888 casino

When donald trump acknowledged jerusalem as capital of israel on december 6th of '17, this was a huge step to bring about the third temple prophesied in the bible. China, its biggest customer, will continue to need brazilian ore, said jose carlos martins, vale's director of strategy. The official announcement about closing the deal regarding the investments in primorye gambling zone between summit ascent holdings ltd, melco and elegant city was in hong kong stock exchange on august, 26.

27 wins slots

Dress in extended pants and extended-sleeved whenever feasible and inspect yourself for ticks sometimes. De weiiryho on wednesday 03rd of decembe. Distinct areas around the globe have various ailments and you generally need to be ready for what you really are walking into. A proposed facility at the nuclear weapons plant would prepare the waste for shipment starting next year to the department's waste isolation pilot plant near carlsbad, n!

Slot wins new

Beneath you will arrive across the link to some world wide web-web sites that we think about it is ideal to go to. Gaming sessions with your and not just that, we also got. Before, we were content to watch television on a schedule dictated by the broadcasters, but things have moved on a long way since then.

20 burning hot slot free

Different men bomb away from at the having said that sooner in the bathroom tickling their jocose bone or captivating indiscriminately trivia. Also the couple would need to stay in a different hotel and work out their own schedule! Red bluff this means that the most heavily played numbers are 1 through. Marijuana oil has currently marked a brand-new period in which male ceased to fear what is unknown, and began to uncover what our ancestors had actually already observed and utilize the substantial potential, in the beginning glimpse, a little unusual relationships, associated primarily with pathology.

Trustly online casino

Tisdale is survived by two sons, william e. The justice department is investigating whether some airlines violated antitrust laws when they increased fares earlier this year, department and industry officials said thursday. Circumnavigation of the globe, an historical account. Charging for content has been hit-or-miss, attributable to a lack of generally applicable models of information value.

Clickfun casino slots

These compacts do not permit any online gaming as well! Other cities with record highs were fresno, stockton and san jose at 95, santa rosa at 91 and alameda at 86. It also fails the standard of wp npov.

Ignition poker deposit

Norge pris og reseptfritt norge nettbutikk, i sverige hvordan, for salg engelsk eller bestille online norge. 5955 ft m cite gnis id 772002 name grinnell falls gros ventre falls glacier county montana coord 48 55 49 n 113 44 22 w display inline name gros ventre falls el. Here are some links to websites that we link to mainly because we believe they are really worth visiting. Did you ignore to facilitate here square measure round about sporting websites everywhere you plumbing fixture in fact have sporting at kabaddi.

Kazino online

If digit rear abuses steroid or drugs, our children module be taken and therefore the grandchildren. The first, a tall, lean girl wearing full gta online casino update australia camouflage, shot seth an unconcerned look? Understanding the uncomplicated basics with car insurance will assist you to choose the right sort of insurance policy that could take care of your preferences in case you happen to be involved in any accident? Manufacturing index for july and monthly carsales will also be part of the mix?

Pokerstars not logging in

An english version of this order is unavailable. Turismo en ecuador did you like this. After i initially left a comment i seem to have clicked the -notify me when new comments are added- checkbox and from now on whenever a comment is added i receive four emails with the exact same comment? This rv park offers rv-only sites with full hookups.

Casino playamo

Auction sites like ebay can easily link you with a large audience searching for your things! Mizutani said the increase resulted largely from an increase in stock prices last year. Therefore, this gpsc fee item is not applicable. The lighthouse is located over a white lime cliff which is surrounded by salt water lagoons reigate and banstead sedgemoor and marshes.

Woo casino free chips

Our writers division can take care of any kind of assignment of any sort of difficulty fairly quickly. You agreed to the date because you gave the benefit of the doubt to fate, you don't really know anything about how you'll get on in person. Set ntr at horsemen's park. Cleaning up workplaces will aid maintain your office in order for the most efficient work.

Harrah's players club

A bill filed in the legislature wednesday would add a 5 percent surcharge on the income taxes of residents of pulaski county to defray the desegregation costs. The desk officer may be telephoned at 202-395-4650! Transformation from religious sect to murderous gang appears complete!

Chargeback online casino reddit

Stories are his christmas memories throughout the years. Domestic flights are usually dependable and safe! Blues player michael williams takes a break from touring his latest album, fire red, to talk technology with line 6. About this since the very pc!

Gan sports betting

Long-pitre said her water wasn't completely back to normal and was unsure whether it was safe to drink until the town issued a second advisory around noon on monday. I heartily agree that the lack of toilet facilities will impact on footfall and they seem to be closing some of their facilities very quickly. 1 million uniformed military personnel, while the house voted for a 4 percent increase.

Spinamba casino no deposit bonus

Some really nice and useful info on this internet site, as well i conceive the pattern has wonderful features. It was on in the gym today. We proposed to amend the rule to articulate requirements for requesting a protective order when review of the documents that are the subject of the request is necessary to a ruling on the motion! Quality content namely the important apt invite the folk apt expenditure a visit the network site, that what this network site namely providing.

Buffalo king slot

T between coleman and hendrickson sts. Cannabis oil has currently marked a new period in which guy stopped to fear what is unknown, and started to uncover what our forefathers had actually already seen and use the significant capacity, in the beginning glimpse, a little unusual relationships, associated generally with pathology. With demarco funeral homes assist people to share this encounter, although the burial site for cremation. Because of its intimacy, his portrait of saddam's tribal politics surpasses that in the other books in this survey, and demonstrates how the country's next ruler will need to be either an iraqi einstein, or another saddam hussein.

Flamingo slots

When traveling, generally know the quantity of entrance doors between your accommodation and the get out of. This isn't a website, it's my lunch order. First, the atmospheric propagation environment is continually changing, and radar performance degrades with diffuse scattering off the earth between bounces off the ionosphere.

Csgo poker

Often times, people forget about exactly how much h2o is required. Oxandrolone buy methandienone drostanolone buy. We have well and educated teams who can work with honest and safely and securely.

Fair go casino $5 coupon

Visit the up coming document. Epiq corporate restructuring, llc page 239 of 304. 69 align center 5th rowspan 2 800 m raidel acea colspan 2 bgcolor wheat align center 1 50! Please ensure you update the app to access this feature.

Jackpot city casino real money

Cbd gummies 4000mg cbd lotion whole foods cbd capsules toronto. It is located in the third gorge below victoria falls. Below that, there is a dignity deficit! A year-old who requires assistance with shopping, housework, and cooking d susanville.

Neon vegas askgamblers

Amansalos y doblegalos a mi favor. Do you really want to tell us trust the cia. In addition, borrowing from multilateral institutions to fund infrastructure projects will be considered. 4-26-491410000002408900000rocky ford highline cana county road dd.

Monkey slots

The range of the frequency of those currents allows a multispectral analysis of the variation in the electromagnetic local field. Airline and coach staff are already proven to rob items from circumstances when they are inspected in. It delivers full advantages to the punters, including a comprehensive sportsbook besides a complete casino and poker room. The report raises concerns about the allocation of airport slots, particularly through grandfathering and not allowing the commercial exchange of slots.

Betway my bets

They provide top quality games with excellent graphics and sound effect. Also checked my ihg account and they did give me points for applying. The entire glance of your site is wonderful, as smartly as the local movers content. It was released on january 18 2011.

Free casino slot games online

It fastens in the rear with hook and loop closure and is made to fit most individuals. The voluptuous, agonizing music came in a wave over him. Stability of some chemical systems under control with n.

Free poker games slots

Ny saina manokana dia jerena amin'ny poker, satria ny fanjakana dia mavitrika sport poker federation. Steps to enter the suite building next to the main hotel building are a drag? Casting crowns, leeland, john waller - oct. That is john bolton's side.

$100 no deposit bonus

As for the public at large, callers to a recent radio talk show were evenly divided between supporters and critics of mrs. State lawmakers have twice bailed out the department with emergency appropriations. You should do some research before wagering on a team in the 'match winner' bet type.

Python blackjack using classes

Lee's trades, according to people familiar with the case. I have joined your feed and stay up for in quest of more. State factories are to be the backbone of the economy!

List of slot machines at hollywood casino charles town

Hosted by hacksaw tom and desert doug. Back in march, you set up your laptop on the dining room table and the kids took to the couch with their devices. Sections focus on the shoulder and elbow, the hip, the knee, the foot and ankle, the spine, and computer navigation. Toomanydimes 240414 1734 female chaturbate toomanydimes.

P2p poker

This next cruise fail is a giant pet peeve of mine, which is to take one person's opinion of something as gospel. The october withdrawal is rescinded. When camping out there is no cooking area or heating and air conditioning.

Megaways slots free play

See last year's doors on the bghs avid website. Howrah 2011 election summary 1977 2006 in the 2006 state assembly elections doli roy of forward bloc won the panchla assembly seat defeating her nearest rival abul kassem molla of trinamool congress. Royalio is a great site from cozy games with a huge welcome package and a fantastic games collection? It is a very important element of marijuana due to the fact that it has lots of clinical advantages?

Clay poker chip set

Try protecting a little bit of funds and set up a goal. Because the admin of this website is working, no hesitation very shortly it will be renowned, due to its feature contents. Ocnative talk 16 37 18 november 2010 utc user compare report auto generated every six hours.

Centurion slot game

Also on wednesday, the panama canal was returning to normal gradually, and a spokeswoman said waterway officials hoped to go to 24-hour operation to clear a 125-ship backup as more personnel returned to work? Miaraka amin'ny banky tanteraka, dia hahita ianao fa ny filalaovana poker amin'ny unibet dia mahaliana toy ny tranokala izay fantatra indrindra amin'ny everest poker, bwin poker, winamax poker ou pokerstars. Com in september the country will launch production at galkynsh,the world's second-largest natural gas deposit! These drugs typically operate by dilating blood vessels, stimulating cell division and in the process encouraging hair growth.

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!