Automatic Partition Maintenance in MySQL and MariaDB: Part 3

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

Highest odds betting site

Self-employment occurs when a participant is working as a business owner or independent contractor. Global capital investment management ltd. As well if you can do.

Demo slot aztec deluxe

There are millions of people who play garena free fire and know everything about this game. Probe finds deadly food on store shelves hundreds of days after. Out of the window indeed. There is no wagering requirement on your vault balance.

Raging bull daily spins

Join us, and our buddies at yggdrasil, as we indulge in a christmas shopping mission. Always keep these tips in mind when you assembled your vacation plans. Julian had been adamant that he did not want a mass invasion of his home's wc by fee-paying customers, and insisted they must use the outdoor lavatory adjoining the tool shed, a stone's throw from the kitchen door. Other symptoms of pleural mesothelioma cancer include weight loss, severe inhaling and exhaling trouble, vomiting, difficulty ingesting, and bloating of the neck and face areas.

Spin and win real

One day she was in the forest. Pondok pesantren are traditional islamic schools that since their establishment have become centres for islamic learning and the dissemination of islamic knowledge in indonesia, where islamic knowledge is taught formally and islamic values are practised daily! Reeve's statement listed eight customers who were verified as having deposited money that was not in their accounts and another six who had been identified by gibraltar, and said the list may grow.

Free spins on sign up

I am certain that many of us have similar microforms and our libraries may also be able to participate. Hi there friends, its enormous article on the topic of tutoringand fully defined, keep it up all the time. This short article includes some of the best methods for assisting a fresh vacationer turn into a smart visitor. It is now and again perplexing to just find yourself freely giving hints which usually many others might have been selling.

All games egt

A guard official in washington said no decision had been made on whether to appeal! My condolences to your family and friends she seemed like a wonderful woman. A reminder the annual regional chapter banquet is this saturday may 28th at 7pm at quiet cannon montebello country club by invitation to all officers and sacramento state participants,business dress attire requested.

Live american roulette online

Cavan went down the field and niall mc drmott found himself at the end of a complex passing movement and goaled easily. Augmentez votre trafic et votre visibilité afin d'attirer des clients potentiels. There is a lot of entire world to see, both in our own backyards and round the community? Is there any funding available for this scheme, and for the promotion of high-quality agricultural and food products in general.

Best slot game on jackpot city

Hair extensions the position may be volunteer or a paid position. I think other website proprietors should take this web site as an model, very clean and superb user genial layout. I am really impressed with your writing abilities as neatly as with the layout for your blog.

Windsor casino

Combining nationalism and xenophobia with economic policies associated with social democrats might seem contradictory, but pis has forest heath successfully annapolis merged them! Strictly speaking, online gambling is illegal in the state of indiana with the exception being online sports betting. How there celebrating kudos by paying it forward? Twinks bite sexy strawberryfran01 her.

Mobile phone slots no deposit

While it is true that they listen to a certain number of bands, it is false when these groups are politicized. I am not saying that to say you haven't seen it but wood shrinks. Now we're getting entrepreneurs and people who are in it for ego trips or whatever.

Mobile phone casino free bonus no deposit

Danna alturk address on file. Where you actually lost me was first in all the details. There are guidelines not rules for running the game that provides for imagination and meshing reality with fantasy.

Fun roulette online game download

Authorized gclub online casino agent reselling casino games and services from gclub online casino such as baccarat roulette fan-tan slot. Jenkinson made his first class debut for essex in the 1922 county championship against hampshire at the united services recreation ground portsmouth. Im no professional, but i think you just crafted an excellent point. Right on the beach with a fun outdoor bar that has live music nightly.

Play money train 2 free

The staff's broad nominal dollar index moved up as the dollar appreciated against the currencies of the advanced foreign economies, consistent with the larger increase in u. Services like free wireless, on-internet site cafe, continental breakfast time and totally free wi-fi or community cell phone calls. You can jumpstart your sailing knowledge through using private lessons with expert people. Our expert team of reviewers has looked at every app we can find and written honest, comprehensive reviews of each, providing you with a list of top-rated apps that will not steer you wrong.

Play reel king megaways free

But there is also more optimism about employment. 61 1. The method the headlight functions a conventional cars and truck front lights is composed of a housing, power or technical adjustment gadget, lightweight resource, reflector as well as glass?

No deposit no wagering casino

Soldiers, builders, pioneers and explorers are admired in the initial stages of the empire life cycle. Pineda, aguero, and espinoza 2011. Lossiemouth the installation of the small business server edition can take some time, more information. Buy jonis josef tickets from the official ticketmaster.

Metal casino

Hello there, i discovered your blog by means of google even as looking for a comparable matter, your site came up, it seems good. They are all welsh, and this is very down-home welsh. Christian mark san jose address on file!

List of all konami slot machines

When you use the ideas share with you here, you need to have no anxieties for where ever you are headed to following! That followed declines of 3. A score of 8 or more indicates a strong likelihood of harmful alcohol use. Poker players thanks to its no-nonsense deposits, quick payouts and high tournament guarantees.

Pokerstars join poker club

Rihanna discusses her famously 'thicc' figure during quirky. The irs has consistently and regularly rejected the use and reliance upon such information. Possibly you should take into consideration this from far a lot more than 1 angle. Using this method, you can not be charged with medication smuggling.

Play free vegas slot games

This gesture may help ease any negative affect recent lawsuits have had on the reception of the impending casino. All i hear is a bunch of whining regarding something that you might repair if you werent also busy searching for attention. When contemplating travelling, it is very important think of the very best mode of vacation.

Continuation bet poker

Remove rust remove rust by mixing up a simple cleaning solution of 1-2 tablespoons of borax, 1 tablespoon of lemon juice, and enough water to make a paste. If some were fast paced non stop the drivers have at least ontario, you can't go any further was stopped for no other company is when they had forced entry into insuring something newer had no claims discount protection for your car in. This week, the sba offered step-by-step loan-level data regarding all ppp. What biological mechanisms are responsible for connection among diseases, and how should such dependence be studied.

Slots village no deposit bonus

Tilray that'll involve a minimum of 125,000 kilos being extracted for cannabinoids. A basic individual, after taking doses of medicinal marijuana and attaining the appropriate state of cannabinoids in the blood, can delight in increased resistance, minimized vulnerability to cancer, delayed aging and reduced risk of stroke or heart attack. Genially, as a utilization to some people it muscle be, but venture out dates isha.

Rich girl slots

If so, that would of course make it simpler. Greatest hikes in central colorado summit and eagle counties a hiking guide by kim fenske provides a description of salmon willow trail. Greetings, i think your site may be having web browser compatibility issues.

Max bet slot wins

Student who had an arab boyfriend may have been tricked into bringing the device on board. They also have sports like football and basketball, but also nontraditional sports like motocross and darts. Kroes gave birth to phyllon gorre on january 21 and was back to work with her incredible post-baby bod on march 22. The rule of law has been discharged!

College basketball betting sites

Telegram says that hundreds more are coming to the platform and that they will be able to keep track of high scores both within a group and. At argosy casino alton, every night feels like opening night. After study a few of the blog posts on your web site currently, as well as i truly like your way of blogging. For example, in 2019, william hill plc has acquired a sweden based gambling company mr.

Betive casino

Reading striker kirby was player of the match when england played the united states in february. Unidentified gunmen on wednesday killed francisco peccorini, a political adviser to the arena party. Blennies in this species swim at a maximum depth of 7 metres.

Guts online casino

One is born in relaxation, the other in hyperactivity. Use the link to the official software page 1xbet ios, which is on the market on the official web page of the bookmaker. Therefore, it's a massive leap forward because these are individual chromosomes that have all the abilities to mimic the chromosomes non our own cells.

Pokerstars lite real money

His rivals for the democratic nomination were lawyer nick moraitakis and john stembler, a theater operator. New balance bordeaux rot damen. I see a thing actually interesting about your site so i saved to bookmarks. A brief survey of other fuel cell types is also presented.

Wizard slots

Then, a few online betting sites started to accept it. Prosecutors say that the conspirators, many of them exiles from the former south, confessed to being veterans of the war in afghanistan and to having links with backers in sudan and egypt. Looking to our company, you receive interior renovators new york city with a warranty of high quality, short due dates for repair services, dependability and functions of internal services.

Free online casino no deposit

Cadillac angels-lonesome traveler-live bootleg cadillacangels. Talk to people that you take under no circumstances talked to up front, and in fact listen. Phishing mails are nearly always addressed to dear customer or something equally anonymous.

New slot machines in vegas 2020

Brenton riddle address on file. Some state highway officials aren't enthusiastic about trucks getting bigger and heavier. In light of this, the team plan to develop computer prediction models to see how the ice shelves will likely respond to the flow of meltwater, as well as scrutinize the plume formations in further detail. A basic individual, after taking dosages of medical cannabis and attaining the suitable state of cannabinoids in the blood, can enjoy increased immunity, reduced susceptibility to cancer, postponed aging and lowered danger of stroke or heart attack.

The players club casino

Download all types of attitude status for girls in hindi for whatsapp, facebook or instagram with images? 3-mile trek to mount sharp. I am in fact pleased to read this webpage posts which consists of lots of useful facts, thanks for providing such information.

1xbet roulette

The amount of natural accommodations is steadily growing. My extensive internet research has now been paid with sensible facts to exchange with my great friends. Utilizing a help wager is sort of regular in on-line craps recreations. For this specific purpose, ensure your cell phone performs anywhere you will be heading or buy a pre-paid mobile phone in your location country.

Slot machine wins 2020

For those who are not savvy, access to porn is probably just a few footsteps away given the massive grey market of offline pornography. May i simply say what a comfort to uncover someone who really knows what they are talking about over the internet. Customers are not knowledgeable about these frauds and also most often come under bad deal which ends up making the payment procedure more challenging.

Monash pokies

In case businesses look out to insure their b cheap jerseys from china usiness a legal copy of contract is the primary demand of an insurance? Online schweiz auch preise schwarzmarkt, kostenubernahme krankenkasse und online ohne rezept paypal trotz generikum pille. 4 - rally du mont blanc - 1975 - opel.

Slot demo habanero

A lot of thanks for every one of your hard work on this site. Being neck along with careful indoors my guardianship played counter to his irrational antics, i took a peewee sess not in kickered him. Wherever you decide to go when you get there boston is an excellent location to check out on getaway or on service alone or with the family boston is an excellent place.

Unibet slots

There are numerous stuff to take into account in terms of camping out, it can appear to be there is certainly excessive to deal with if you try to go camping out! Locally-possessed companies and accommodations are significantly less apt to be a focus on for terrorists and pickpockets likewise. We wish you well in your course. Various witness of the 1st discussion cassette at discogs the second studio album oesophagus was released by b92 on june 1997 in a similar musical direction as on the previous release.

Judi blackjack live

It will help the website deliver you contacts of the brides who can doubtlessly develop into your dates. It is killing health benefits. Take advantage of equipment provided on the web accommodation searching resources. He has three second-place finishes under his belt this season in pga tour events.

Value of ace in blackjack

Next up is a guide to locating crazy 4 poker tables at your favorite brick and mortar casinos, followed by a primer on proper strategy to help you improve your overall odds. Ausgestattet passiert bet-at-home mit der es zu nichts gebracht hat werthaltigen eu-lizenz durch malta. On rgp they would point out that you misspelled germany.

Lucky red casino free spins

You should place any costly electronic devices and expensive jewelry in the resort safe! Nownforever's review of scatter slots. The company also offers social online gaming through its penn interactive ventures division and has leading customer loyalty programs with over five million active customers.

Free slots games with free spins

Crew member of freighter carl coppedge. Infused with thoughtfully sourced ingredients, the line includes two delicious extra freshening toothpastes, an extra moisturizi. If you necessitate this vehemence agcap.

Free bingo no deposit no wagering

Make use of any or every one of these ideas when organizing the next vacation. The third official casino state of the united states turned out to be south dakota, where offline gambling was legalized in 1989, and the first casinos in the state were opened in deadwood. El contenido de este sitio es para proveer informacin y slo en este contexto ha de ser utilizado por lo que nuestra empresa se.

Casino nitro

Another thing that should be taken into account is that the probability for a dealer blackjack decreases when the player gets a natural since the number of tens in the deck drops. The department of higher education offers grants and guaranteed loans to missouri students. My husband and i have been so more than happy when jordan managed to finish off his basic research through your ideas he had through your weblog.

Vegas casino games online

To unlock your bonus, all you have to do is make your first deposit and place bets equivalent to 3x the value of your first deposit amount on odds of 3. At the jazz cafe calderazzo seemed keen to make the three sides equal and as a result rationed his solo work to great effect? The extra data you present about yourself and your best partner the sooner you may meet your good haitian bride? What i don't realize is actually how you're no longer actually.

1xbet free spins

All you need to do is register everything you have to be play at the video casino and live the casino website so you can find great bonuses to keep your personal information and a full list of free spins and bonus features or choose a number of types spins that you can play! Zs mulberry norge js mulberry oslo mv mulberry veske oe carteiras prada zd oculos sol prada ju oculos sol prada ux bolsas coach mp bolsas coach qo tenis coach bl zapatillas gucci dm gorros gucci xu carteras gucci qj coach bonn en coach deutschland sy coach bonn. The cf model is equipped with a big cargo door on the forward left side of the fuselage that opens to allow the removal of passenger seats, racks and galleys. Finally, he said, they realized that a much simpler device might be made that would merely shuttle a xenon atom back and forth between the stm's tip and the metal surface!

Jackpot city android casino

De cighjjta on friday 07th of november 2! We hypothesize that effective cracks are caused by thermal shock in materials with a low ability to dissipate energy! Doug burgum said he is willing to consider granting waivers on a case-by-case basis for school districts that have canceled school because of severe winter storms and dangerously cold temperatures in recent weeks, provided certain criteria are met. Claramente este no es un texto que se pueda disfrutar looking for older seniors in houston con la lectura, es uno de esos textos que para terminar de entenderlos se tienen que la tuque centralia ver en escena, ya bien desmenuzado y entendido.

Huff and puff pokie machine

Regards, an abundance of advice. Anyone found not doing their job or actively causing problems should be held accountable! Todd and had him do the spells for me!

Free spins no deposit pl 2020

But obstacles remained, including continuing threats from the reagan administration that one or both bills might be vetoed unless certain offending provisions were excised. Io 7 days spanish armada. Be at peace, till we meet again!

Casino star slots

You are looking to clear your credit report from harmful details faults that wreck your credit score. The predella included panels with scenes of the saints of the main composition and a central double size annunciation the stygmata of st. Only go camping outdoors when you find yourself completely prepared. Ch keryer's a walk in the park.

Silver oak casino free spins no deposit

I like visiting your site since i always come across interesting articles like this one? I definitely liked every bit of it and i also have you book marked to check out new things on your blog. Jogging is the point i appreciate most. Blackjack kostenlos online spielen realistic novoline spielautomaten kostenlos?

Online poker no deposit bonus

At least for a year. If it weren't for the innocent bystanders who suffer, we'd say a pox or a bomb on both your houses. The use of playing card symbols and some two of a kind wins mean that if you are the kind of player who likes to see regular small wins rather than holding out for the big bucks, this game will suit you!

Best online casino slots real money

So, are you the right place, you are looking for the collection of sports wear for mens in the best quality, also available in a variety of attractive product for your better life style. The consistency of these findings with those of the sequenced treatment alternatives to relieve depression trial suggests that subtypes may be of minimal value in antidepressant selection. Thunderstorms also stretched from the lower great lakes to new england and down across the central appalachians.

Barona players club

Besides, there are the esports bets and the casino page. At work you were a stabilizing influence for me, i hope you will find that same stability and love from your family and friends. Should you be touring the european countries, take full advantage of their excellent rail program!

New slot sites

It actually was some sort of enjoyment bank account it! Mine and metal detectors are ineffective since they are constantly registering a metallic presence. First up, the root of all evil.

Bloodsuckers slot

October 9, 1979 ambassador, newcastle, aus? We are very happy to announce that starting this tuesday, april 14th, we will be adding supper to our meal service. Museum of contemporary art, chicago, il. The share exchange rate applicable to the merger shall be 0.

Online poker no download

Camping is a pretty straightforward outside adventure only for about someone to handle. My wife and i have been relieved raymond could conclude his researching through your precious recommendations he got when using the web page. That the webcams were dark because they say since you took out the accident they double dipped lot of online continuing education opportunities you should be taken to day street police station horrifies me with casino online you bet how rude one at all times the die besten kostenlose pc spiele arm of ibc, not the first time and money? I have such fond memories of listening to their childhood stories, of the dancing lessons dario and maria gave my husband and i at their home, and of dario's enthusiasm for everything.

New online casino 2020 no deposit

24, when he blasted china's regulatory system in a speech at a shanghai forum. Shortly before your event, we match all participants using our innoloft matching algorithm to arrange the most suitable dates for them. Please note that you may continue to receive materials while we are updating our lists.

Original 777 casino

Where you guys go today. Mlife is one of the largest rewards programs in vegas. Uap and transatlantic have long been the group's biggest shareholders, with stakes of 27. In southern new england, winds gusted to 47 mph at hartford, conn?

Heart of vegas free coin

Utterly pent articles, really enjoyed looking through? Although in the home, keep it in the closet or maybe the storage area and proper before leaving to your outdoor camping getaway, put it in the trunk. In march 1885 the south australian junior football association was formed, with medindie as one of the founding members. If you order money for e-wallets, they will come in half an hour, and if you want to get a win on a bank card, the payment will be made within a day after you order the funds.

New bingo sites with fluffy favourites

Can you provide any specific examples of where copyediting is needed. The game flaunts gorgeous shadow effects and ray tracing thanks to the power of next-gen hardware. Permainan casino yang disediakan juga langsung ditayangkan atau diputar secara live?

Ancient fortunes zeus slot

In case you are spending your funds on their solutions, it really is nicely in your correct to do this. Center st took 2nd at the lady mules fall brawl saturday. But wanna input on few general things, the website pattern is perfect, the articles is real wonderful?

Royal panda live casino

I enjoy reading through an article that will make men and. This is your employment contract health experts online in june, a week before takahama was arrested, a senior sesc official gave a presentation to japan's association of investment advisers in which he warned that some pension fund executives were considered public officials, and that entertaining them was potentially a criminal offence. Ie still is the market chief and a huge component to other people will leave out your magnificent writing due to this problem.

Spin palace usa

Another month has come and gone in the online gambling world. You experience a wrap a vast contract as well as that feature. Some genuinely nice and useful information on this internet site, likewise i conceive the pattern has got great features.

Horse betting sites

In order to provide our scholars with opportunities to continue to engage in learning experiences outside of the classroom, ousd is in the process of designing activities and assignments that will provide optional learning opportunities for students beginning march 18, 2020. Hungarian defense officials have said the red army soldiers can be withdrawn only if the north atlantic treaty organization makes corresponding reductions. Your own personal know-how and kindness in handling all the pieces was important. Where you confused us was first on the details.

Mobile casino no deposit bonus sign up

It is still open non the fight to the last europa league space as well as the class. Fiat does not appear to have benefited greatly from the lira's fall! I feel very much grateful to have come across your web page and look forward to many more exciting minutes reading here.

Spinamba no deposit

Computers force be missing a one of a kind plugin on the way to content that duty, time peregrine devices will-power exercise their intrinsic gps chips. Gagged vannever rocco siffredimujeres hermosas pichandokimbarly marvelscat dirty womenbbedit cuckoldporno mexicano tubeoilet training slaveslevis jeansasian hoteabg indo diperkosaloving couple tubetetek gedebisex amateur cuckoldfinger outside. Hence, this 22bet review to see if this can be considered as one of the best online hangouts for casino lovers in the philippines. Despite his success, lowe still fights the use of kitty litter as a generic term!

Vegas casino online no deposit bonus 2019

State department spokeswoman margaret tutwiler said a note in arabic attributed the incident to the palestinian popular army, about which the united states has no information. Now wembley has been rocked by asset write-downs of almost pounds 100m, putting the company in breach of its borrowing powers and, presumably, leaving its chairman ashen-faced. This article will give you fantastic travel tips to help you can use on a cruise or somewhere else! Asa raki3comic teensvictoria swinger gymnasiumann cutizat gardenforce karachi porntrany big assdulce ninfomanajoymii sex tvael orgasminterracila jamaican vactionlesbian show clubeva anders movieschubby goth peggingass everywhere dvd.

Supercat casino 60 free spins

Composition manual, printing industry of america, 1953. Eventually, he said, he agreed to resubmit a more moderate version of his letter, and the resignation was accepted this morning. Although none of the us soldiers or agents in the room was injured in the incident, siddiqui herself was shot.

Winning days casino

I simply wish to offer a substantial thumbs up for the fantastic info you have below on this article? Any trademarked terms that appear on this page are used for descriptive purposes only. Watched the big red machine.

Playgrand casino 50 free spins no deposit

As the admin of this site is working, no uncertainty very soon it will be famous, due to its feature contents. Find all the best offers at our coupons page. It recently had a tune up at red rock bicycles here in town and had everything checked over? Boosting business, she said, was the fact that hanukkah began last night.

Mobile slots real money no deposit

Vimax pills are the best! Make sure you do thorough research also. Just before having a camping vacation, verify around your state of health insurance policy.

Casino roulette play online free

Although valacyclovir lowered the latent viral dna load better in these studies than did famciclovir, charges of reactivation by explantation and uv exposure have been the identical. Place them inside a tall clear glass cylinder vase! You actually will not desire them flipping on with your travelling bag throughout the vacation.

True blue casino no deposit bonus 2020

Quite possibly the most common practice among scam betting sites. Korean phone case brands online shopping korean phone case. We'll need to take up references dapoxetine hcl kaufen during a press conference today the three-term mayor also criticized u.

Video poker games

Comback to the future sneakers 2016 twitter! In the early 1990s, the xj40 was redesigned into the x300, which sported softer shapes and rounded headlamps on the front end- a trend starting to make its way back in the 90s. Thank you a lot for giving everyone an extremely brilliant chance to read from this blog.

Planet 7 codes 2020

Spott onn with this write-up, i actually believe this amazing site needss a great deal more attention! Wide shot arrival of ilaria spada and kim rossi stuart? Find out here now navigate here check this link right here now imp source the original source look at these guys this site my response hop over to this site learn the facts here now anonymous visit their website advice official statement bonuses.

Lucky lucky blackjack online free

40, inverse odds for monaco are 3. A great deal of lodges have got a program for reusing linens, have lighting effects that is certainly power efficient, place trying to recycle bins out for company to make use of, lower movement plumbing, option types of power and so forth. U arkansas appears to be 21novacasino websites wie omegle interviewing, as judged from their website s front page!

Pokerstars send money to another player

Php a- to start with them at greeting office temperature to shun undercooking. One thing that players may find is missing are progressive jackpot slots, which is somewhat unusual, given the rather impressive list of suppliers whose titles are available for play here. On the database search screen, select advanced search options. Now, the partnership between the tropicana hotel and casino and the william hill sports betting brand is a big deal.

3d roulette

Finance related topics for dissertation. But the new zealand team was the first to perfect hydrofoiling, finding that the boats could be nudged to lift the hulls completely out of the water onto thin carbon-fiber blades. But mostly this was a day of pageantry, given an especially clintonian flavour.

Microsoft blackjack

It must have no industry development or tourism-related functions, or in any way be involved in promoting gambling. Whether it first hand out comparable odds with you ve by no means performed earlier than their slot machines than their land-primarily based counterparts. Does district of columbia state law define brandishing. Students also take cross- disciplinary courses, including a nine-credit series in social entrepreneurship, and topical seminars in microfinance and ecological perspectives for business.

Poker three

Open it after proper cleaning and disinfecting. While the drug appeared to be effective for some people, it doesn't cure the ailment. Nevertheless, there is a great deal that you need to realize so that you will are ready for your outings. Using comparison sites that performance what a distinct model offers across multipart brands can assist you you declare where to buy.

888 poker tournaments live

Usually fall out of the right path to hint the housekeeper and housekeeper correctly. Also, what motivated hundreds of thousands of ukrainian and belarusian northwest territories jews port augusta to settle in russia between and june. If future growth in the volume of drugs and physician fee schedule services were to grow at historical rates, revenues to infectious disease physicians would increase would increase 7 percent.

Pocket fruity casino

The tent you purchase need to meet your requirements and the dimensions of your camping bash. No person would certainly ever before understand! Marijuana oil has actually already marked a new age in which man stopped to fear what is unknown, and began to uncover what our forefathers had actually already observed and use the significant potential, in the beginning look, a little bizarre relationships, associated primarily with pathology. The city used the money to start drug education programs in local schools and buy equipment, as well as finance more undercover drug buys.

Monopoly slots free spins

The benchmark 30-year treasury bond staged a one-point rally, driving the yield down to 5. 50 per equity share for the current fiscal amounting to rs 3,500 crore. Keep in mind, holidays are about fun, so first and foremost, relax and enjoy your self!

San manuel players club card

Rnplease stay us informed like this? 22bet offers a wide variety of casino games to suit all playing types. Proses taruhan untuk flop diulangi di turn. Committee aides said the panel is likely to make some change in the catastrophic insurance tax next week!

Cake poker network

With a lopsided democratic majority of 259 to 175 in the house, one or two fewer republicans wouldn't have made all that much difference. The gps receiver will pass your current location to nroute and nroute will display it, whether there is a map to display it on or not. One explanation might be that borrowers prefer to accept a higher margin rather than run the risk of switching to a cheaper but unfamiliar source of funds. Fortunately, virtually of us volition never screw to contend with mr.

Jackpot city

Enjoy the largest non-smoking area in northern arizona. Erv can be embedded in existing or specially designed image capture devices such as cameras in hud systems for vehicles, drones, uavs, underwater rovs, robotics, factory inspection machines, construction inspection systems, satellites, airplanes and more. Sure, there are loads of young people on match who are probably on tinder as well, but match also attracts significantly older, more mature and probably more experienced in bed users.

Play slots for real money

This book constitutes the refereed proceedings of the 8th international conference on case-based reasoning, iccbr 2009, held in seattle, wa, usa, in july 2009. Just don't buy the cheap knock off ones from kmart, walmart, or target. Captain phasma exclusivelego great pyramid of giza museum of science and industrylego city and space promotion shop for promotional lego city. Stadium information, ground photos, pubs, parking, by train, ticket prices, directions, maps, and fans reviews, you will find it all in here, plus lots more.

Crazy money deluxe

11 -1 78 -4. The measure was similar to a bill passed earlier this week by the house. I just wanted to post a message to be able to thank you for some of the fantastic steps you are showing on this site? You should select the best place for the best experience.

Download pokerstars pa

Duggan took full responsibility for filing his petitions a few days shy of having been registered to vote in detroit a full year. The results of the review! In one respect, british airways was unlucky.

Spin palace casino games

A few years later, now as part of the state, he was deputy secretary of sports from 2017 to 2018. Spent a lot of time with her and family she was a loving and fun person she will be so missed rest in peace. Many carnival ships also have private conference centers and the line's public rooms are also available for meetings, awards receptions and other corporate functions.

Planet 7 casino no deposit bonus codes april 2018

Even in his own post, he's showing he might be a little unsure of what he would do if his tech equities tank. This internet site is my breathing in, very great pattern and perfect content. And a lot of it.

Jackpot party casino

Can i lay a polar wordpress idea to a wordpress hosted diary. Stormie jones, saved by the world's first heart-liver transplant on valentine's day 1984, has her own remedy for dealing with the exasperating health problems that seem to multiply with every passing year. Then they read the release? Mama had given me two coloring books and a box of crayons, nevertheless spent a lot of the trip fussing over child.

Pokerstars no real money option

Use 77777 online games totally free, fast adobe brick splash mmorpgs devoid of downloads. At least they each had eight 14-inch guns. The advice in this post might help if you prepare your trip! Food will be provided by the verde valley cyclists coalition.

American roulette play online free

The president s speech finishes well before the game s start time of 8 30pm et. The wall street journal reported in its thursday editions that talks between texas air and electronic data systems were advanced and that barring a snag, an agreement could be reached soon. An orange play button is an indication that the game is available on live stream? There is little interaction between the players and most of the play depends on luck.

22bet offers

4 per cent from 8? But we are very aware that the threat of a no deal is increasing as october 31st approaches. Furthermore, you are allowed to add themes, text, drawings and colors, to your stop motion animation.

Coolbet bonus

You can do this by obtaining info intended for and produced by residents, be it from newspapers, weblogs or men and women on youtube. For vlts, mean differences were less significant than they were for lottery? Auction internet sites like ebay may attach you along with a wide audience trying to find your items. Rooms and suites are an exercise in restrained opulence, with spacious interiors, intricately crafted antiques and deep-soaking marble bathtubs.

International poker sites

Bbflatt talk 20 42 17 november 2010 utc delete. Detroit tigers at minnesota twins. Betting on licensed horse and dog racing and parimutuel wagering is legal in idaho.

Bovada nfl draft

These types of religious leaders are responsible for so many cult organizations around the world, particularly taking sexual advantage of young girls. It broadens your perspectives, allows you to make new friends to see new spots in fact it is a great way to take a moment off and away to reduce stress and blow off of steam. Huang shang compiles the nine yin manual and conceals it to prevent it from instigating trouble.

Lucky bets casino 50 free spins

Hi, i do believe your website could possibly be having browser compatibility issues. Stocks fell on tuesday, ledby tech stocks that have made some of the biggest gains thisyear, as the lack of progress in ending the fiscal crisis inwashington prompted more selling. Asking questions are really nice thing if you are not understanding something entirely, except this post presents nice understanding even. I will value if you decide to continue on this informative article.

Farah galfond

For some of the latter, this was really a way of helping their romanian counterparts. The fda said interpharm will now audit all its 24 product approvals. The issue was priced at 138 basis points above the treasury's 30-year note. Helen joseph, one of the first white south africans ever banned by the government for anti-apartheid activities, is in serious condition at a hospital.

Free online european roulette no download

Manor del mar, courtesy of ann chaves. I first would like to apologize for that because i never like to leave my audience and readers hanging without any reason why. A standard individual, after taking dosages of medicinal marijuana and achieving the proper state of cannabinoids in the blood, can enjoy increased resistance, lowered susceptibility to cancer, delayed aging and minimized risk of stroke or cardiovascular disease. Starting your unique business is easier than you think.

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!