Automatic Partition Maintenance in MySQL and MariaDB: Part 3

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

High card flush online

Xi jinping continued to hold the three most powerful positions as ccp general secretary, state president, and chairman of the central military commission. On a spring night in 1987, the bright, musically talented wife of a prominent methodist minister was brutally attacked at her home! This guide is intended for players that are using the original crashlanded scenario.

Online roulette live wheel

De pngitaza on wednesday 29th of april 2. According to the contemporary fad, i have actually written a blog about the upkeep as well as physician on-call. Suddenly, a tell-tale hint of scarlet in the underbrush ahead betrays a target.

Blue water casino

New jersey has reported a very good beginning for online playing throughout the state. If the chargeback claim was justified, we pass on the cost to the merchant. The men had hoped to keep their dirty secrets from the jury?

Jackpot cafe slots

Happening a yearly getaway will give you anything to be effective difficult for and it will surely supply you with a light-weight at the end of the tunnel. It seems so unfair for such a beloved giving person to leave the earth so soon. Free slots play no download with bonus you can spin the reels on slots, gus greenbaum? How inhumane can they be.

Netbet 100 free spins no deposit

Likud leaders say they hope to form a narrow government, then invite labor into a partnership. Waterville and most importantly, more than 80 restaurants in argentina serving up delicious seasonal food. Most other beaches in the county are closed, resulting in a huge influx of people from miles around to the beaches in carlsbad.

All slots casino nz

26 when london trading ended, having moved little from its opening level. All deposits into your 10bet soccer betting account ought to be instant aside from bank transfers which can take as much as 5 days in worse case eventualities. I bookmarked it to my bookmark mybookie casino reddit web site checklist and shall be checking again soon.

Sam's town

The link to this photo or video may be broken, or. Swing by the sample sale this weekend at our green lake headquarters. It was the third israeli air raid into lebanon in a week and the fifth this year. A basic person, after taking dosages of medical marijuana and achieving the suitable state of cannabinoids in the blood, can enjoy increased immunity, decreased vulnerability to cancer, postponed aging and reduced risk of stroke or cardiac arrest.

Megabucks las vegas

Holidays will almost always be an active time of year, specially on the international airport. The top menu provides shortcuts to 7 critical areas of the site and is available from every page. Yl schoenenwinkel coach zd coach sneakers nd schoenenwinkel coach lz nike air max 1 bestellen cv nike air max 1 kopen it nike air max kopen pt prada shoes wl prada tassen fy prada american cup ms louboutin sneakers lr louboutin nederland qt louboutin amsterdam nh longchamp tas ot longchamps tassen rq longchamps tassen ku hermes sjaal an hermes sjaal vf hermes riem prijs aa gucci muts jg gucci tas xx gucci tas wy air jordans bestellen fv jordans nederland ps jordans nederland xt michael kors laarzen iz michael kors tassen tz michael kors schoenen zk burberry online rh burberry sjaal vg burberry kinderkleding ct nike nederland we nike store amsterdam jp nike voetbalschoenen.

888 casino slots

It is just generally agreed that the better player will overcome the luck factor and will win in the long run. Hi there mates, good article and fastidious urging commented at this place, i am in fact enjoying by these. Through the winter, especially, it can be smart to always keep several blankets, some footwear, some fits plus a large candle, in the trunk area of the car.

Sunset station

Additionally, they can also assay manifest their composition skills and see what works of art they can create. These kinds of protective gear usually are model specific since they are manufactured to fit perfectly across the natural housing. Wizardman operation big bear 02 44 11 january 2011 utc support a quick revisit and nothing more to fix.

Is bovada

I am certain there are some more fun moments up front for many who take a look at your website. So, you should not have any sort of issue regarding anything. We numerically confirm the consistency equation by extensive molecular dynamics simulations for finite systems, corroborate 888 casino ban yourself earlier results indicating that the kinematic viscosity approaches a finite, non-vanishing value in the thermodynamic limit, and establish the finite size behavior of the diffusion coefficient.

Versailles casino mobile

For three of the sons, it was the first time they had tasted kfc! At the farm on little road, children could pet rabbits, goats, ponies, chickens and every spring, a newborn lamb always named pam. Underwent chemotherapy, radiotherapy and surgery to remove large bowel. Read full story survey says most plan to use money to improve personal finances.

Ebay fruit machines

How long do we have to download all our classes over 200 classes, have from nina idm thank you again, but success 5 classes in half hour? This will make a significant difference especially when having a street vacation over a cross country. In 2007, federal authorities decided to crack down when a utah antiquities dealer offered to work undercover for the government buying intricately painted bowls that can sell for thousands of dollars apiece, as well as and other artifacts!

Dragon link slot wins 2020

We have a robust multifaceted economy, they did not. On april 8, the government passed the covid-19 exceptional condition act that was in force for 3 months and was extended for another 3 months! Andrei sakharov, the nobel peace prize-winning soviet scientist for whom the prize was named, was invited by the parliament to present the awards himself in strasbourg. Org there are over 200 employees most of which are sworn deputies.

Red lion online casino

I'd really like to be a part of online community where i can get suggestions from other knowledgeable people that share the same interest. Livegambler xxl casinosbankwire oncyber paddypowercasinos cyberactecash ccyber crackingcyber theorycasinos. Why marco bitran is no friend to small business when you get there boston is an excellent place to visit on vacation or on company alone or with the family boston is an excellent place, wherever you decide to go.

Giants gold slot

But now, knowing he is not here anymore, i grieve for him. The workers, in addition to demanding higher wages and bonuses, want the right to form their own labor unions. I am caring, kind, and considerate, and also funny i've been told. Best mba essay writing service65.

10 free roulette no deposit

I mourn the loss but i do not deny the loss. Nightware is designed for the apple watch to help those suffering with ptsd-related nightmares. Under the terms of the deal, announced wednesday, koch optics will commence a tender offer and subsequent merger for all of. Fastidious respond in return of this query with firm arguments and describing the whole thing regarding that!

Roe poker

Fantastic blog you have here but i was wondering if you knew of any message boards that cover the same topics talked about here. Just wanna comment on few general things, the website layout is perfect, the articles is really fantastic. Though he lacks his predecessor's zeal for eliminating federal legal aid to the indigent, george bush has quietly sheltered the reagan appointees.

Yevgeniy timoshenko

Following the war some tdr 1s were converted for operation as private sportsplanes? That's how we were able to survive? Hello every one, here every person is sharing these kinds of familiarity,.

Best rtp jackpot city

Valerie and ed were married on july 20, 1957 in alliance, ohio. I-jj,-jqj buforo show saddle, f-un buck stitching 8, silver trim. However, don tangiers casino terms and conditions t let taxes avoidance enter the true way of sound trading. In volleyball, she was a three-time all-state selection.

Good betting websites

Mohamed, sleeping on campus, thought his end had come when a bomb-laden jet roared low over the university. Handicapped betting is an alternative way to bet on certain events. Last may, apache hive 1. No specific compensation amounts were demanded in court, but u.

All action slots

For you, such events have limitless grandness. Second, whilst i can see a leaps in logic you make, i am definitely not convinced of exactly how you appear to unite the ideas which inturn help to make your final result. For most recent news you have to pay a quick visit internet and on the web i found this website as a finest site for hottest updates? Since the 1990s successive governments have been laying the foundations for this expansion of mining, and claiming it will generate large revenues for the state and enable the country to develop?

Rampart casino players club

Predictably, a actor faces the hoop in the midst of mutually feet coating the basketball hoop. Also extends its marketing platform and access to internet users beyond yahoo. In addition to the attacks on u. Gamcare - help and support for people affected by problem gambling - uk.

Casino 100 free spins no deposit

Hi, its fastidious post concerning media print, we all be familiar with media is a enormous source of facts. Jclemens public talk 07 47 29 november 2010 utc comment. Show additional replies, including those that may contain offensive content. Brand content from vodafone, for instance, has recently covered topics such as making the most of tech while working from home and how smartphones could help find ways of treating covid-19.

Roulette wheel ball

Does walmart carry generic prozac. Requirements for businesses dealing in virtual currency. On-camera video lights can be an excellent lighting solution for portraits. Coterie drudge pains cuts the manifest torment oneself alliance in half, and taybar.

Golden reels no deposit free spins

De ouyasqex on monday 13th of april 2015. Utilize the atm while you are in the nearby foreign currency. Zithromax 500mg over the counter. Finally something about search engine optimization.

Silver casino

The unit is self-programming, and as it m. 5 percent deposit charge in place. A soviet delegate to the geneva talks said moscow's change of position was aimed at starting the inspections as soon as possible.

Vegascrest

Most of the ladies happened to be absolutely thrilled to learn all of them and have now in reality been taking pleasure in them. Whichever you choose, keep the hips in line with your heels and shoulder blades on the back. I did however expertise a few technical issues using this website, as i experienced to reload the site many times previous to i could get it to load properly?

Spin and go max

Aftermath for his timely service in the first battle robert boyd was rewarded with the family lands which he held in tack as well as the restoration of his family s title of lord boyd. Looking for an improved hotel room when you are examining in will most likely allow you to get one particular. Reading this info so i am happy to exhibit that i have a very excellent uncanny feeling i discovered just what i needed.

Play 21 card game

Sponsorship is often hidden from afghans because, military officials acknowledged, the information wouldn't be trusted if afghans knew its source? 7ho7481 s 30 100 66 82 -. Near us it on a really, really long, shallow beach. 1xslots casino dia de los muertos.

Most expensive poker cards

Well i am adding this rss to my email and can look out for. Blofeld is one of our major contributors and sadly he was blocked and has quit wikipedia can anyone look into this as i am unaware of this. The only kind of people i want counting my money are short guys that wear yarmulkes every day.

Trusted sports betting sites

If you are useless as so many democrats are you will be left behind and destitute. Certainly there are lots of more fun moments in the future for those who start reading your site. Often, an offender may be provided an appeal deal. This would send a strong message to the world that panama is not a place where people can hide illicit money.

Betamo no deposit

Patient can also depict sweating and agitation diaphoresis or anxiety? On this page, you will learn some terrific ideas and acquire try out to attend the health club prior to getting with a airline flight. Home office said that discontinuing the use of the orasure kits shouldn't significantly affect its financial results because only a few clients were submitting specimens for testing. There have been no marches or radio news broadcasts lately.

Bovada practice blackjack

I returned all those that had been submitted. At the same time, he tried to silence the witnesses, but everything he did failed. Inspector vishal choudhry pankaj jaggi anu kashyap priya kaushal ismail khan shehzad khan. A fall in her 20s had left her with plates and screws in her back, a source of nagging pain.

Woo casino

She realized several things, which include how it is like to possess a very effective helping heart to get others very easily know just exactly several problematic subject areas. A losing player can seldom catch up to the leader, and? I needed to create you this little bit of observation to be able to thank you so much yet again regarding the great knowledge you have contributed on this website.

Pokerstars paypal deposit

Daycare is and also consistently will definitely be necessary to many households, and also childcare centers do not happen low-priced. The castle s military character was maintained for several centuries the last siege occurring in 1746 during the jacobite risings. White south africa inflicted much suffering before bowing to the inevitable.

Fast play casino

Some people wind up awaiting their area continues to be not all set! Like the other games mentioned above it, slot machines have seen great spikes in player numbers thanks to online versions of this popular casino game in recent years. Skinner will serve as an independent consultant, it said. Right-wing leaders want deportations back again following sunday's fatal bus stabbing of an israeli near tel aviv, the 12th such attack on jews in six weeks.

Nyspins bonus

These include online bank transfer, e-wallet, prepaid card, bank card, or even cryptocurrency. They could then find their back onto your pores and skin? A basic person, after taking dosages of medical cannabis and attaining the suitable state of cannabinoids in the blood, can take pleasure in increased immunity, minimized vulnerability to cancer, delayed aging and minimized risk of stroke or cardiac arrest. Well, obtaining knowledgeable tree removal service is one of the best answer to go.

Sky poker mobile

Hopefully this site is getting bigger and the visitors will also increase every day. I said -- i can't believe those bastards -- i said we were great, i said people can come and sit and read for hours and no one bothers them, i said we stock 150,000 titles, i showed them the new york city section. The rest was blank paper cut to look like 3 million. Open and load the entire mp3 tracks that you wish to be part of collectively.

Online roulette real money paypal

The cum loving sex bombs are ready for some exciting filth. According to government statistics, roughly a third of all scottish car journeys are less than two miles long, with a quarter being less than one mile long. Basically what we have is another tie between ati and nvidia. Or so it was thought.

Official roulette wheel

He also feels that a celebration of oneself on one day is silly, when every day that one is alive should be a cause for celebration. I in addition to my guys were actually taking note of the good thoughts found on the blog and then the sudden i had a horrible feeling i never expressed respect to the blog owner for them! Feed the new motion drive belt from the rear to the front, pushing it over the brake pedal shaft and steering plate toward the electric clutch.

777 heaven fruit machine for sale

The one major criticism of tiger gaming would be the fact you can only play by download to pc. Your system functions as a the game of golf instructor? I quietly began in this,. While particular aspects of treatment need medical certifications, aiding clients with daily tasks like buying, cleansing, as well as preparing food perform certainly not!

777 games free

Com cialis going generic in 2019 in us. However, sweepstakes sites offer a great alternative. Degarmo's approach has paid off in more than one way.

Betway bet365

You see the pattern, correct? Hi ryan, you should apply for part time unemployment. Small companies are the big companies of the future. To judge from her recital at the wigmore hall on wednesday she has less sheer power on board than crespin did at her last london appearances there, but pollet naturally has youth and flexibility on her side.

Yebo casino free spins no deposit

I am unsure about if i have shown you my flashers before. The invacare xtra is a folding adult or children's manual wheelchair designed for use by individuals with mobility disabilities or spinal cord injury. Although the central focus of the festival will be western-based classical music, there will be portions of the festival sheridan devoted to other musical idaho genres and other art forms such as shepway poetry, craft-work, the visual arts, and food. Therefore, phil and danny negreanu and annie duke and all the rest of the gang could play paradise poker tourneys non stop for the rest of their lives and probably do no better than you or i, which in all honesty, is not all that great.

Play las vegas slot machines

Microsoft office 2017 serial key is easy to use. Lately, i am becoming a big fan of quinoa and adding them to every possible meal i ever make. I am only commenting to let you know what a really good encounter my princess developed studying your web site. The big ideas creative lead always wants more features, as mentioned in the op.

Game casino online android

Every jumps operation next to bodog private club throw away the prime guarantee being a limitation in the service of the gratuity. It had been an absolute hard issue in my view, nevertheless noticing the well-written strategy you managed it took me to weep over happiness. The 26-year-old drifter with a long criminal record who launched a mute, murderous assault on a california elementary school tuesday was described by his aunt as a loner and childhood alcoholic, police said. Even enterprise travel can be created pleasurable.

Best way to play roulette

The experiences of other people who have traveled towards the very same spot could be far more helpful compared to biased sales info through the location alone. The floodplain deposits from the current rivers are the coonambidgal formation however this term is used informally for the older pleistocene flood deposits as well. We by no means flip purchasers down? Relates to american foreign policy in the middle east, and especially to american relations with lebanon and iran.

Free bingo games vegas world

Com origdiff user coibot editsummary id 20547434 lang en wikidomain w namespace pagename goodnestone park username dr! There is no other disease that can really annoy your face, pimples really suck. Customers can then choose from 27 different sports to bet on, as well as casino and bingo options.

Free deuces wild

I read jim clayton's book, for example. I do believe that you need to publish more about this topic, it might not be a taboo subject but typically people do not talk about such topics. Other automatic scanning functions include rotation, edge detection, image cropping, and adjustment for brightness and contrast.

All spins pokies

Scholars say few people would have been more surprised by the pledge of allegiance's prominence in the 1988 presidential campaign than the man who wrote it. Paoi 12 - section nini classified? Well together with your permission let me to grab your feed to stay up to date with coming near near post. Wow, this paragraph is nice, my sister is analyzing such things, therefore i am going to let know her.

Softest poker sites

You realize, many persons are looking round for this information, you could help them greatly. March 10, 1981 roberts stadium, evansville, in. However, it is difficult to quantify the effects of pollutants and weather conditions due to the high unknown nonlinearities in the impact of these environmental and weather factors on the illnesses and possible interactions amongst these factors?

Ruby slots 100 plentiful spins

Call 1-800-gambler for immediate help or visit www. The us division of state carries a internet site where you could track down information and embassies in many places. The training is designed to foster such feelings as teamwork, company loyalty and self-esteem. A party against whom relief is sought may serve a written response on nfa and the other party at or before the hearing.

Coushatta casino free play

If you are looking towards something enthralling, test this in fault. Class ranking and grades10 boston college law school does not rank its students. The games available are displayed by default in a list, but players can switch to multiview, to keep track of more matches. Sometimes you can comment directly on a 21.

Bovegas no deposit

Last week, i discovered online casinos. Clay, who said tuesday that he had left el rukn in 1986, testified a day earlier that the defendant hired el rukn members to come to greenville to kill barber, a former employee and childhood friend of robinson's. I could also use some back up in san francisco with the possibly of two heroes missing with doom, red hulk, and tombstone gunning for them. Cheers considerably this amazing site is definitely proper plus everyday.

Free slot machine games with bonus rounds

I will not be reluctant to suggest the website to anybody who needs to have guidance about this matter. This is the second time i have spent this amount on this challenge and got nothing. Actions targeted at these factors could contribute to improving bcg immunisation coverage.

Winaday tournament

But i've found these six to be reliable and easy to master. They have something called an omelet station that was producing nothing like a proper omelette. And rebecca in customer service sets a standard all customer service should aspire to? They twin themselves traveling the closed the moon outstanding, lounging on beaches.

Bet365 casino live roulette

Very few web sites that take place to be in depth beneath, from our point of view are undoubtedly effectively really worth checking out. A process for las vegas casino app obtaining pre-determined, accurate rate amounts of mercury. Sam nunn and a few others, most lawmakers, democrats and republicans alike, lack the nerve to oppose the president.

Mybookie ncaa football

If we are unable to confirm that you are 18 years old then we may suspend your account longueuil until such time alhambra that we are able to confirm your age. Angelinet tosado address on file. Some commenters suggested that remittance transfer providers may not have the ability to recover funds from third parties involved in the transfer and that the financial impact of losses experienced by the provider as a result of errors by another party could be significant enough for remittance transfer providers to exit the market?

Wild jackpots casino

Either of these trees would make superb specimens on the boundary or towards the back of the lawn of a smallish garden. This article is actually a nice one it assists new net viewers, who are wishing for blogging. The composite index rose 3.

Six deck blackjack strategy

Meanwhile, the mlb announced its latest testing results and reported six positives this week out of 10,939 tests performed. Attain you know any techniques to help stopcontent from mammal ripped off. You need potential customers that buy from you. Future work is web design company oshawa markham barrie peterborough!

Online pokies johnny cash

The unusual is that i believe there is a bunch additional spam than you comprehend. However at the end defteros dies and the aspros appearance drastically changs? President bush complained to senate majority leader george mitchell on friday that 120 of his appointments are languishing in the senate as lawmakers prepare to begin a long summer recess. Uncle sam gives it cheaper than new york merchants.

Indian poker

Electronic inductive discharge and variable spark advance in electronic unit with bedworth electronic immobilizer, transponder led and fuel pump groton shutoff when bike tips over. Very interesting, since i read this article i became very interested. This is true for investment recommendation as properly.

20 super hot free slots

My considerable internet research has finally been recognized with reliable concept to talk about with my two friends. 44 13 56 07 thu nov 18 2010 user gemondo uk user talk contribs on page prince william of wales diff undo overlap username overlaps with domain gemondo uk gemondo. Atlanta has won the last two against the packers at home and seven of the last nine dating back to 2008. The usability session took approximately 70 min.

Mfortune no deposit bonus

Please share all ideas with me. How the probabilistic magic program works. Thompson our company is engaged in supplying quality aluminium foils machines to its customers at best price.

Free poker games for fun

Do not forget that they also need to make money and may apply to you by proposing great odds. Blige, the grand finale was an evening to celebrate and honor our five extraordinary 2010 minerva award winners. Another benefit of trust as a vehicle in forming alliances is the reduction of search costs for alliance partners. Sebab tidak ada keraguan bahwa tiap tiap-tiap putaran berasal dari mesin slot bakal mengimbuhkan emosionil yang menguntungkan.

Online casino directory

See who you know at mr green online casino, leverage your professional network, and get. For instance, take a piece of fabric and fasten it to a section of hair by winding a piece of fine stratford corby cord or string around them both. Developmental algebra if i can accept that premise, then i should prepare for anything, right.

888 casino review

I visited many sites except the audio feature for audio songs existing at this site is actually wonderful. Visibility may be suddenly reduced at times in heavy snow! Regardless of whether it will take a day or two to get your suitcases and obtain it for you personally, you are able to probably scrub your garments on your hotel. Four contenders for the democratic presidential nomination have accepted invitations to attend the state party's jefferson-jackson day dinner april 30, according to indiana democratic chairman john b.

Germania online casino

The tip 'n grip tray is a non-slip tray designed for use by individuals with balance, mobility, or upper extremity disabilities. I am grateful for the help as well as hope that you find out what a great job that you are undertaking instructing some other people thru your web blog. Thanks for your practical opinion on what is normally an overlooked topic.

Online casinos that use paypal

On the other hand, you buy to right cabee. Com u 11545 l 748 ul x wul x user coibot otherlinks link www. Wolfson, a professor at the university of connecticut law school, worked for the sec from 1967 to 1972.

Multifly slot

Hair revital x ingredients will quickly reduce the dht levels to maintain in right level for stimulating hair growth and retrieves your lost hair back during the right time. Food is a basic need that many take for granted, yet growing a robust local food system can strengthen communities by improving quality of life and helping to meet a variety of environmental, economic, and social goals. More than 900 of the times top 100 companies use nextbase products, as well as 20,000 smaller companies.

Captain cook free spins

Y esto choca con el principio de igualdad de todos ante la ley y con la neutralidad de quien juzga. Thanks a lot lots this fabulous website will be official and also informal. This makes it very simple to buy close to to find the best cost.

Play stinkin rich slots online

Great chat games and everyone makes you feel welcome. Upon successful completion of the doctoral defense, the student will be awarded the degree of doctor of philosophy phd. Each person awarded an iso under this plan shall notify the corporation in writing immediately after the date he or she makes a disposition of any shares acquired pursuant to the exercise of such iso. Since few in the education field have even heard of this school much less their online teacher education program, they should have pr people travel the country and establish relationships with school boards nationwide before placing unsuspecting students in the awkward position of having to win the confidence of the powers that be?

Bovada sign up

The only game which is forbidden while you are clearing a casino bonus is craps. As we got to the front battalion headquarters, a sergeant asked us to fall blackjack online vs casino in line and we did. Jaden smith unveils the fourth water box, a portable water filtration system developed by smith'snonprofit 501cthree on monday, dec.

Slots empire

And this means keeping it simple. Trending now mahjongg dimensions new. Hostel old lab is located in the heart of zagreb, between king p.

List of online sportsbooks

Surrounded by lush gardens, state-of-the-art facilities, a stunning white-sand beach and crystalline waters. Suspicious message in subject line outlook. 1,69 boyunda külotlu çorap giymesini seven bir kiz olarak istediginiz rengi tercih edip benimle iliski yasayabilirsiniz! Ithere is nothinghence there is noand i alsoplus iso chose to resideto reside into live onto call hometo live a lifeto measureto maintainto haveto exist in in rhode island but my wife wants us to movegoideaadvancetransitiontactickeep movingcirculateproceedgetfocuschoicerelocate!

Carter casino

Central to entertainment - fill your showrooms and other venues by offering players entertainment rewards extending the players stay. The proclamation focused on certain resources and uses in establishing the monument so the proposed plan amendment will also focus on those areas in implementing the proclamation. H m haljina kosulja sa cipkom kragujevac? Ya que usted no puede estar seguro de que alguien este infectado, el riesgo de estar en contacto con el virus aumenta con el numero de companeros sexuales que usted tenga.

Royal aces no deposit codes

To actually optimize your next camping experience, you need to have a large number of beneficial tricks and tips. Our internal audit department also reviews documentation, compliance and file management. Joe gots's review of solitaire by mobilityware.

Extra stars egt

Chief warrant officer james j. He has not even answered a direct email i sent him well over a month ago. 5 mi enjoy recreation amenities such as. She was born on august 18th, 1922 in jelgava, latvia, the oldest of three sisters.

Online poker between friends

For now i will subscribe to your point however trust in the future you actually connect your facts better? Apart from a game lobby with wide blackjack, roulette, and video poker options from classic versions to modern variations, players at fruity king casino also enjoy detailed information on the games to learn a trick or two. Before the stock market plunge, ford had forecast industry sales of 10 million cars and 5 million trucks.

Free daily spins casino

This is a powerful checklist for any investor, not just real estate investors that can be used as a basis for decision making when you analyze opportunities and investments! We are committed to a policy of responsible gaming at our gaming and pari-mutuel racing properties. Then, see if the same.

Free slots no deposit needed

In practice, this means that a decision of the chamber can be appealed in a court of law. It recently signed a pact with the california community college system to accept all students who complete core courses with a 3. In addition, the action incorporated a magazine cut off that allowed it to be used as a single shot!

Five cards in blackjack

Once you do so you will be able to email any other user who has email enabled by going to their user or user talk page e. A guide to sources of information on the national labor relations board. Quod choro te quo, quodsi molestiae te cum. 4 million people have applied for amnesty, short of the ins's earlier projections of about two million and its plans to accommodate as many as 3.

Hit it rich slots

In case you are planing a trip to a spot with a good amount of pets, you have got to be doubly very careful with foods storage space. It is also my belief that mesothelioma has an very long latency interval, which means that signs of the disease might not emerge until eventually 30 to 50 years after the 1st exposure to asbestos fiber. Quick question that is fully off topic. If you are searching for camping outdoors, so you are unfamiliar with the activity, then you certainly must not start out by itself.

Hot shots slots free online

A formidable share, i just given this onto a colleague who was doing just a little analysis on this. Be well mannered, begin a conversation together, then ask to consider their picture. It is a descriptive measure of a child's current functional perf. I have been watching this thread since i ordered my -59 csb in nov.

Desert night casino

It works best when used without a hearing aid or with a hearing aid that has a telephone coil for use on hearing aid-compatible phones, he added. This first thing you need to do is download and install blender petaluma. As i website owner i think the articles here is really good, thank you for your efforts. Maccosmeticswholesalers mac makeup 13pcs brushes c 1.

Uptown aces casino free spins

I have good friends, i try to get by! Kickoff for the pennsylvania state championship game was minutes away. Html boobs some fox viewers in alabama also wrote in.

Alcohol roulette

Thanks for genuinely very kind and also for picking out variety of superior themes millions of individuals are really desperate to understand about. Com n'est plus disponible aux joueurs résidants en france. Marijuana oil has currently marked a new era in which guy stopped to fear what is unidentified, and started to rediscover what our ancestors had currently noticed and use the significant capacity, at first glimpse, a little strange relationships, associated mainly with pathology.

Casino wager free bonus

Generic viagra price at walmart. Through the austin and kat affiliate program, your web traffic can purchase cbd oils, treats and powerful vitamins for pets. Affiliate marketing affiliate marketing programs one way to raise income from your affiliate marketing online affiliate marketing programs is usually to incorporate them in the email connection that you have with devoted buyers. The communist party's central committee will hold an extraordinary meeting saturday to set the agenda for the soviet union's parliament next week, a foreign ministry spokesman said today.

Diamond reels no deposit bonus 2020

Ut aliquam elit vitae mi consectetur, non scelerisque tortor imperdiet! Excellent short article, many thanks and also we want much more. This compares with net borrowing of pounds 9m in july. In the company peekskill repaired its relationship with woods hole its franchisees by immediately dropping the most contentious of the contract terms that had been proposed by chairman john cranor five years previously.

Club player casino bonus codes 2019

On november 13, 2018 his heavenly father decided it was time for him to come home? A standard person, after taking doses of medicinal cannabis and achieving the suitable state of cannabinoids in the blood, can delight in increased resistance, reduced vulnerability to cancer, delayed aging and reduced threat of stroke or heart attack. Processed aliment is typically unreasonable in chemical additives, hormones, sugar, qualifyingly, touch-and-go pot-bellied, and calories, all of which can adversely agitate exno.

Charge to mobile casino

A basic person, after taking dosages of medical marijuana and attaining the proper state of cannabinoids in the blood, can delight in increased immunity, reduced susceptibility to cancer, delayed aging and minimized danger of stroke or cardiac arrest. Rob was a wonderful guy, and will be missed by all who knew him! I will be playing 10nl with a 20 bi bankroll, and playing 10-15 hours a week? We have been leading a very busy life so far, occupied with multiple things movies, friends, socialising, work and web series!

Bovada online

I found your site by the use of google at the same time as searching for a related matter, your web site got here up. Porn film torrents pass out porn tube renaissance lesbo porn free striptease download porn minnor porn video. Certain 1 tips in this post are definitely the most beneficial we have all had. Vardenafil vs tadalafil vs viagra which gives better erection.

Best blackjack online gambling

The total glance of your web site is wonderful, letalone the content. Learn the foretell diversions gambling re-examination. When i originally commented i clicked the -inform me when brand-new comments are included- checkbox as well as now each time a comment is included i obtain four emails with the same remark? Grsz 11 03 30 25 november 2010 utc yes thinking about it you may well be right.

Ojo spins

In 20 jahren arbeitete ich mit vielen chefredakteuren. A vcd is a plastic tube applied over the penis which creates a seal with the skin at the base of the penis. Free casino for fun only free vegas world slots usa no deposit casino bonus codes. I feel truly lucky to have seen the web site and look forward to some more enjoyable times reading here?

Nightrush no deposit bonus

Entertaining even superficial mucous cavities on slot spiele gratis gamen fowl play gold spiele mit geld 6 buchstaben volshenitsa captain who remembers from shenkursk had quieted by humans except which greatly relieved b company detroit campaign. A standard person, after taking dosages of medicinal marijuana and achieving the appropriate state of cannabinoids in the blood, can take pleasure in increased immunity, reduced vulnerability to cancer, delayed aging and minimized danger of stroke or heart attack. It unremarkably occurs in the hands, feet, ankle, and wrists. Slots machine games online free bonus rounds we would love to help, but we also reward our team members with competitive salaries and a comprehensive benefits package.

Pokerstars no deposit bonus 2020

Jpg now has an otrs tag attached. They make these prepaid debit cards particularly for that allowance arrangements that families have. The goodwill visit by the youngest son of queen elizabeth ii follows her canadian visit last week. Pradahowling wolf with moon tattoo on shoulder.

Rounders poker

Ministers attending the session agreed to huddle privately on friday before opening opec's full winter conference on saturday morning! If you are going for finest contents like i do, just pay a quick visit this website all the time since it provides quality contents, thanks. Asmantas was quoted as saying gas supplies might be cut 50 percent.

Okto88 slot

See what gaming events are on the horizon? Generally preferring to stay behind the scenes, he finally has allowed himself to be talked into a presentation. A tornado in lebanon, about 25 miles north of here, left two injured. Did you use to play.

Win win sport betting

23 islamic economics and finance. Merely wanna input that you have a very decent internet site , i the design and style it actually stands out. Wigs online if you compare a 32b, and 34b, you will see that the 34b is bigger in both the band and the cup.

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!