Automatic Partition Maintenance in MySQL and MariaDB: Part 3

Geoff Montee MariaDB , MySQL , Partitioning 3 Comments

13 poker online

However, the firm hasn't any plans to beef up security at these locations. If you are intending to abandon, load up your refrigerated cooled drinks and prevent ice, not cubed! Internet sites such as these are great simply because they consist of each of the information in a single rather than you needing to go searching for all this. In january, open doors, an organisation that supports oppressed christians, released a report fitchburg bicester that estimated million christians are persecuted around the with no fees best and free online dating services for men in phoenix world.

Wpt online poker site

I will also be watching for really bad ones and will take appropriate action when necessary. Is this limit, pot limit, or no limit. 4 with guarantees from both sides that planes would not be fired on. Watch as the zombie soccer players take over the field and hopefully earn some free spins and big wins along the way.

Khan play poker

The writing is definitely really persuasive and this is most likely why i am taking an effort in order to comment. We've got a joint account innovative medicines canada company schultz is not the only business leader speaking out againstthe shutdown. I find out something a lot more tough on different blogs daily. Richard barone, chairman of cleveland's maxus investment group, says that he handles his largest clients as separate accounts, rather than putting them into the private investment company that his firm manages.

Online casino free spins sign up

Hattiesburg we challenged 12 case makers to a live drop test at ces and dropped their cases from 20 feet. You may have arrive off to the right place. In my opinion, if all website owners and bloggers made good content as you did, the web will likely be a lot more helpful than ever before.

Mychance casino

The fact that they are usually found in clear blue water is important. This hotel is excellent but it falls short of five stars by just a bit! Free paper illustrations library for personal and commercial use.

Free casino games to play on my phone

I enjoy you because of all of the hard work on this website. This consolidation also includes several government business enterprises, based on the principle that they either sell most of their goods and services to government institutions or departments at regulated prices, and are therefore not businesses in the true sense of the word, or they are directly involved in infrastructure financing and development! The mini retro gaming console was quite captivating and was delightful a there were more evocative holding scale remakes.

5 reel slot machines

Yang keempat adalah kursus diploma penulisan kreatif? You already know therefore considerably with regards to this subject, made me in my view consider it from a lot of various angles. Yes many would have failed, but the stronger banks would fruitinator online casino echtgeld still exist, and would have been able to pick up the pieces. Adam and eve plus size sex toys.

Judi casino slot online

Terima kasih atas perhatiannya serta dinanti orderannya, ya? Com if you are interested because there are limited spots and they do go fast. I just could not go away your site prior to suggesting that i actually enjoyed the usual info an individual supply in your visitors.

Sport betting 365

Studying this info so i am happy to. More than two dozen babies born to nepalese surrogates are being airlifted out of nepal to safety following saturday's earthquake. Once again, our friends agreed, but emphasized that they liked the 20th time this winter and perhaps there would be a new ratio of 19 of 21 over the summer. I am so impressed with the amount of study you put into it.

Leo vegas casino win spin

You need to be a section of a contest for starters of the highest-quality sites on the net. Sweet web site, super layout, very clean and employ genial. Information like this is essential to have to hand in this line of work. This is done so that people employed on the ship who are not in direct contact with you on the ship can also receive something!

Absolute poker download

The tough have to get tougher in these times. Hi there to all, how is everything, i think every one is getting more from this website, and your views are good in support of new users. How long to leave cbd under tongue. Group effort cuts the reliable machinery heyday in half, and cennui?

6 to 5 blackjack

This paragraph is truly a nice one it helps new net viewers, who are wishing for blogging? In other words, two cards with the same numbers but in different positions would both be unique. Jpg action history history fullurl special log page file 3ajessiethreiplanddetail. He did best among conservative democrats, the cbs-times poll said, but was third even there to dukakis and jackson.

Mybookie sign in

November 1, 1996 rosebud, pittsburgh, pa. There, they were very very gradually taught that nothing is quite as black and white as they had been taught by their parents or parish priests. Sanctions against south africa with congressional leaders. This is where an organisation should think about investing in human interactions and personalising the service.

Fantasy roulette

The effect of an educational program with a learning style under practice conditions on the learning outcomes of some offensive basketball skills. Le cepol a également rationalisé son fonctionnement avec une approche fondée sur les résultats. Initiating rapid population reduction an abyss.

Royal ace casino no deposit bonus codes 2016

Thanks to contemporary innovation, the resulting service is clean, totally free of heavy metals and unneeded waxes, naturally present in the plant, and the drawn liquid has a typical, oily consistency! So, this will be more helpful for me to plan the schedule for visit ishpeming. Police forbade the crowd from marching out of the stadium, and the youths sang freedom songs before dispersing quietly when they were told mpetha had not yet been freed.

Spartacus slot free play

Its to hyderabad non-aligned slick in the intruding of accession as spurt as in the trismus! All are designed to provide a space for individuals suffering from mental health, trauma and substance use issues to explore aspects of their disorders in a non-verbal way. Drrrjjcrgvpa air max 2013 evfwqkwdyrck ugg rea uhypqgyjcppn ugg soldes otqelcjfduhs ugg suomi lpdvigratckn barbour qtpdrwiuzqzu doudoune moncler qhfzbmgiypcv nike air max 1 cfjzzxzmzxks air max 90 tyhlzqmjovnj canada goose outlet khirnxilbtmv moncler jackets. Admiring the time and effort you put into your blog and detailed information you present.

Most popular online pokies

He was recognized by the late dr. It can amount to equal a no cost resort night time? Haha love your videos bro. Progressive machines, megalotto does a better job most of the time.

Run it once poker twitch

Shanaz finance and services inc. The decline was blamed on a credit crunch affecting commercial and residential builders. From the processes leading to massive ion formation in the atmosphere to the behavior of saltating organic sands on the surface, laboratory experiments are playing a pivotal role in understanding titan and expanding our understanding of planetary processes into new, exciting phase space.

Quarter slots in vegas

We have called and emailed multiple times. We strongly encourage residents to report any flooding issues since this brings to light new problem areas and allows city staff to confirm problems identified through studies. In years like this it seems that june is the crucial month for determining the severity summer draughts.

Betting picks today

The song about the choice is always compelling. I got this web page from my buddy who told me regarding this website and at the moment this time i am visiting this site and reading very informative articles here. When the zombies take over, you are going to need tools survive. Are you aware how to handle a crisis or perhaps unforeseen function.

Omni slots online casino

Cannabis oil has currently marked a new era in which man stopped to fear what is unknown, and started to rediscover what our forefathers had actually already noticed and use the substantial potential, at first glance, a little bizarre relationships, associated mainly with pathology! Unusually, and software out of russia? High following translates to record labels taking advantage of a smaller marketing budget and increasing their following share.

Fair go mobile casino

To my 2 favorite helenas - so sad for your family! He's expected to be a key witness against elkins. It concerns converting standard capacity magazines into high capacity magazines.

Poker tournaments

Fun things to do in brevard county florida with listings of local fun attractions, this directory covers locations such as url2258 casino casino click melbourne, cocoa, viera, titusville, where to meet european singles in new york and. A day earlier, castle's huge clock, made out of bronze and stainless steel, will be unveiled at the corner of bloor and yonge streets, one of downtown toronto's busiest intersections. 6 months after our divorce i met my current husband, who i fell in love with immediately, and for the first time i wanted a child.

Betway voucher pep

The smart cybersecurity solution is the installation of mcafee antivirus software. In order to halt the french attack one austrian grenadier brigade had wheeled to face the northern side of the square. Some genuinely fantastic info , sword lily i discovered this.

Hippodrome online

And they routinely try on upon to, wrongly, that this describe is substantiation that they study incorporated the requisite changes and that they should be awarded a ph. As a volunteer, you will be assisting in hands-on experiments with the kids. The sea food you may get through the night might be a lot more distinct from the people you will catch in the daytime!

Bovada football betting

Recent recession-related increases in unemployment in north yorkshire have been greatest in the better off areas. Governor ned lamont today announced that he is creating the connecticut cares small business grant program to assist small businesses and nonprofits that have been impacted by the economic downturn caused by the global covid-19 pandemic. We all notice all regarding the powerful means you provide important things through your web blog and even cause response from website visitors on that content so our child is without a doubt understanding a lot.

Chicago poker

Lynch, chase executive vice president, said chase is seeking similar opportunities to increase its portfolio. Although porter's reliance on the audience's prior knowledge was extreme for the period, it nonetheless characterized and crystalized the distinctive elements of this period's representational system. How do you like ohio.

Cafe casino no deposit bonus 2019

I simply want to tell you that i am just new to weblog and honestly loved this web-site. Bluerobed, white under, come to me? So far no formal attempt has been made for his extradition to britain and the irish authorities have made clear the charges against him in dublin will have to take precedence.

Kings roulette

Get started today with the best rated social media management that will be available in addition provides a good roi today. They engaged in fights and brawls against sufis and wahhabis. Secondly, you must have commercial expertise so the figures bennington always add up in the end. If you are intending very long ranges, you need to most likely hold iodine tablets to clean water you discover just before consuming?

Coin master slots

Line 21 is not valid with form 1040-nr? May i just say what a relief to discover somebody that genuinely knows what they are discussing on the web. Another escape system, tested successfully with dummies in november at california's china lake naval weapons center, would require astronauts, one at a time, to hook onto small tractor rockets stored in the shuttle cabin.

Igt website

This can help people to decide the best game for them. In the san francisco suburb of redwood shores. You have options when it comes to your 21st birthday presents.

Free online roulette game for fun

It was more than another decade before a regional transit bill passed the legislature. Didn't realize the event had it! So won't you help me sing another song of freedom,!

Players rewards card no deposit bonus codes

Growth is hardwired into what they do. In armenia, industry and transport remained nearly halted because of a rail blockade by azerbaijan that has caused a severe fuel shortage, tass reported. These figures are slightly lower than those for the human abstract s previous album midheaven which sold around 4 100 copies and ranked at number 163 on the billboard 200.

Get free spins without deposit

A exhilarated everyday may be in order? It does not appear that blackjack-21 casino provides any sort of comp points program for all players. Our students will not face any problem! When your become involved in succeeds, you on too.

Token slot machines for sale

Total membership to the society is around 530 the vast majority from japan. The flight was on a scheduled run from addis ababa to bahar dar to asmara, the capital of northern eritrea province. Marijuana oil has already marked a brand-new era in which man stopped to fear what is unidentified, and began to rediscover what our forefathers had already noticed and use the substantial potential, in the beginning glimpse, a little strange relationships, associated primarily with pathology.

Cosmic spins casino

If your camping in canada or another region, this is a lot more vital. But then again, in france it is very difficult to own a gun since we do not believe that it is a constitutional right to be able to play havoc with other people's lives. Since then, the villagers had to deal with all types of trials and exactions. Currency later rebounded somewhat, traders said the dollar is headed lower.

Poker sites no deposit bonus

With the game, we can invite our friends to play pubg, and talking about our school life ,chatting about some scandals,and have fun during the game? A staggering 170,000 officers and their families now have no permanent place to live, according to the soviet defense ministry? 777 casino is a part of the reputable 888 groups, and that says volumes about their credibility.

Bovada basketball

Justin sold me my very first car - a sporty red chevy cavalier. The participants reviewed the current status of magnetic field measurements and their implications with regard to recent theories and numerical models that address the fundamental scales and processes in the highly magnetized, turbulent plasma? Al jumah k, hassali ma, al qhatani d, el tahir k. Admiring the commitment you place into your website and in depth data you provide.

Slots site

The harness has a top pull and stays attached to the chair when not in use. These stunning spots provide the perfect opportunity to take a break from the boundless energy of this spirited and exhilarating destination. You could try and feel free to overcome depression the natural way! The neurosurgeon performed a craniotomy on the 15-year-old girl after a wall of her family's home fell on her as she collected water outside.

Betstar betting site

The benefits of ultra fine oats one of the best ways to consume oats on a daily basis is by using our ultra fine scottish oats. The reach of the project may not be for everyone, nor may it be widespread, but there still stand to be people who are able to gain from it by having their viewing enrich their experiences. Without really owning a physical game.

Casino la riviera

Rainer decline to name the development. Merely declaring you are vastly innovative. Join facebook to connect with simons rose and others you may know.

World series of poker games free download

Those explorers made of that the name yucatan, and i suppose the peninsula will be called so forever. Warneford of warneford place in wiltshire and all saints vicarage in halifax. However, it is estimated that only 20 per cent of the beneficiaries will spend most of the proceeds on property. Vizentini organised a massive music festival in december 1878 at the paris hippodrome with at least 15 000 spectators where he shared the podium with contemporary composers conducting their own works.

European blackjack

In addition, us cellular networks run generally below. Free with trial 4 beds 2. Irene saunders and charles saunders, both seeking custody of their daughter christine, stand outside courtroom at 150 otis street!

Card games poker

Hikers within these places know what you should expect and will not have a challenge for those who have a cranky kid or even your youngsters would like to play, scream and perform. He added that western hostages in lebanon may be released if the u. You can use this within your doorway at nighttime to incorporate security past the chain and secure.

7 euro gratis casino

Political sources said opposition parties were negotiating with lt. Business administration, management and operations. Com toyota camry le '09. Since the cu we ve also found some socks editors who have the ability to hop around on virginmedia s isp and other isp s that use virginmedia to make it look like they re in different geographical locations e.

Party poker instant play

If the center does not wobble, the custard is overcooked and will have an eggy taste to it. Ghetto chocolate whore creamed ass cock ridding, free sex video. Shortly before its first meeting in april 1987, cartoonist garry trudeau spent three weeks lampooning the task force.

Lightning roulette betway

There are cash prizes and awesome electronics at stake, so when you win, you win big? Is it just me or do a few of these comments look like they are coming from brain dead individuals. Bei hse gibt es im sortiment mikronesse u.

Fair go no deposit bonus

Remaining quiet and obtained aboard your airplane can assist you avoid a serious breeding ground for vacation tension. Satu tentang yang bakal dibahas di dalam artikel yang satu ini adalah berkenaan kasino. And from judi conway patton biography first lady of the commonwealth of kentucky september 3 2001 commonwealth of kentucky e archives?

New online slot sites

Diafra sakho came off after 58 minutes of saturday's 1-1 draw with stoke. Program graduates spend 40 hours teaching tacoma citizens how they can change their behavior to help the environment. After the death of edward iv! 9308 ft m cite gnis id 784944 name holland peak horsehead peak coord 47 10 16 n 114 39 00 w display inline name horsehead peak el!

Online roulette dealer

You supplied the solutions that i have been seeking. Various embodiments are not limited to a particular computer system platform, processor, operating system, or network. Very couple of sites that come about to become detailed below, from our point of view are undoubtedly properly worth checking out. In order to provide details on years seperate articles must be created cause otherwise the main page becomes too long and if shortened misses a lot of detail out!

Online casino best deal

I hooked up the hdmi cable from the dvd player to our av receiver. Gambling behavior and problem gambling, 533-566. Full rtv is reserved for long time established editors! I make certain that you would love to read it and also let me find out about your thoughts.

Party poker promotions

The body of the woman was found at an address in plewes close, kensworth, at around 12! During that time, industry was unable to arrive at a common approach or to propose specific amocs that are alluded to in comments that were submitted to the nprm. Thats the best amount of bonus winnings at one of our recommended casinos. Can't help but worry that i'll end up stranded on a mystical island somewhere between philly and chicago!

5 draw poker

Cvts use either a belt or chain on a system of two pulleys. Below are steps you can take in order to whitelist observer. Just about every guy dreams about getting a six pack!

Free spin casinos

Just after exploring through the internet and seeing thoughts which were not helpful, i was thinking my entire life was over? Farmers scramblers forever, no onion. Each week, vulture highlights the best new music!

Governor of poker y8 games

Be believeable when you might date human being. Like sampras, expect more from him at wimbledon. With close to 40 sports on its betting portfolio and a massive name in the betting industry, william hill is another bookie that players can fully trust and rely on.

All star knockout slot

It supplies ziplines, rope training courses and also rock climbing difficulties that are ensured to make you pee your trousers a little when you understand just what you have actually obtained right into? Gangster girl porn free male gay porn clips japanese girlfriend porn watch sex positive porn selena gomez porn websites. Our driver tad was amazing.

Sports betting promotions

Issues could get somewhat tense at large airports. The scores will come as a disappointment to president mokgweetsi masisi who has made the fight against corruption one of the cornerstones of his administration. Pack items that are in containers larger than 3.

Draftkings touchdown blackjack

Maintain in brain that most of what will be uncovered in the outline should really be readable. Before going camping the first time, ensure you have performed research on camp tents, meals and wild animals success. In the event you get rid of some or every one of the caffeinated drinks in what you eat, you may very well see less cystic acne. Leaders support hv entrepreneurs through specific initiatives, programs, or offerings such as funding, incubators, co-working spaces, accelerators, maker spaces, teaching entrepreneurship, business plan competitions, events, and platforms.

Poker online tanpa deposit awal

It can sometimes be a bit of a minefield out there, finding a casino that guarantees fair gameplay, secure payments and an overall safe environment for players. 7-a 1410000004108700000fort morgan canal county road r 0. It persists in much of its original range and populations are healthy.

Eurogrand live roulette

We assessed the construct validity of subgroups by testing differences among the classes in gender, age, baseline days abstinent, ad severity, depressive symptoms, anxiety, perceived stress, age of onset of ad, harm avoidance, novelty seeking, genotype, and smoking status. Just keep in mind, like all streaming companies, content material changes periodically. Other major durables for recreation and culture.

Live roulette online betting

At the same time, the propensity for east german households to save is similar to that of west germans. Make sure you pre-plan your route before your trip. At first the lad fingered tight pussy and ass of his sexy babe, then she sucked his cock and finally he thrust his huge sausage deep into her stretched anus.

888 casino down

We view points in different ways, but you composed in such a manner in which i may have transformed my own opinions. First pleas judges of scioto county, 1803-52. Laurel lodged talk 22 40 20 december 2010 utc the above is preserved as an archive of the discussion.

Governor of poker online free

Soul manufacturing corporation - bonniers konsthall. What's up friends, good post and fastidious urging commented at this place,! Selling its retail fragrance business has long been seen as one of several things avon could do in its effort to enhance profitability.

Megabucks winners las vegas 2019

Ciprofloxacin over the counter mexico. Html xxx a temasek-led vehicle bought a controlling stake in bankdanamon in 2003 for an undisclosed price and subsequentlyincreased its stake to 67. The restaurants will feature navajo favorites like fry bread and mutton stew.

Pokie machines victoria

The game continues with the standard rules of play. For instance, any time new generations of processors are introduced to the market, there is usually an equivalent increase in the dimensions demands of both the pc memory along with hard drive room. Thanks for this exceptionally clear insight, it just doesn't really address the subject of addiction. I am happy with the team and the team.

Mucho vegas casino

Bez predpisu cz koupit og koupit za dobre ceny, levna lekarna online eller v online lekarne, koupit v lekarne online za dostupne ceny. Just one calendar year aqua is all the rage and the up coming magenta purple is the accent colour of choice. This would mean abandoning mr!

Mr green mobile

But no one rushed the podium as happened in cleveland, or had supporters and opponents face a standoff as they did in chicago. Now we do have our own washer so that's not a problem, but my cluelessness is. Because of concerns about cost, nuclear waste disposal, safety and regulatory delay, there hasn't been an order for a new nuclear plant in the u?

Joker slot free credit

After i take a nap. The theory is that there is more growth left in data services in some european markets than in the united states. 14 and reached an agreement with saw maung to repatriate the students. If the authority starts sending out different signals about what they are supposed to think, their beliefs change practically overnight.

Jack million online

S tsuchida, n yumoto, s matsuura. 7 elections in which namibians are to elect an assembly to draft a constitution for the independence of their country, a former u. Madyira only served part of his 2013 sentence and was released early for good behavior and put on probation. Get in touch fill in an enquiry form below or call us?

Zynga poker 1b chips free

The ngo red umbrella reported 227 assaults and murders of lgbti individuals through october 1. Currently has a friend who is near, always present with us,. The second player may even choose to automatically copy or mimic the decisions made by the first player, so that final outcomes achieved by the second player are the same as those achieved by the first player.

Reel rush slot

One reason was the tv money, burning a hole in owners' pockets? This interactive cd-rom demonstrates strategies and techniques for assessing and teaching children with multiple disabilities through the use of video clips, questions pertaining to the video clips, discussion, and relevant literature. An even better thought is usually to take your acoustic guitar for a sing coupled. Guests planning to arrive outside of normal check-in hours must contact the property in advance for check-in instructions and an access code.

Mega joker slot machine free

Additionally a list of which remedies are conditional on others for instance a ban that should only be implemented if a mentorship should fail and so on. Usj-cfdt also asked for a general debate about the treatment of information. A fire control radar system online casino gambling real money was developed, assembled, and modified.

William hill casino bonus

Ranked first was virginia's democratic gov! Come join us in our voyage upon the flowing sheet, soon to embark. Secure parking from uk park and ride?

Free spins on piggy riches

Rencontre libertine dijon baise entre mec francais image de chatte sexe fille en string baise sexe en webcam baise tante francaise auto ecole sexe vieille pute mature site de de rencontre site pour un plan cul baise amie de ma femme petite salope russe baise a la clinique femme de 60 ans qui baise audrey bitoni baise site de rencontre homme asiatique vieux baise salope scene de sexe site de rencontre 18 25 gratuit branlette par cougar partouze bondage petite chinoise salope rencontre sexe en correze rencontre coquine arras shannya tweeks fait la pute a domicile porno mere et son fils histoire hard de sexe video de rasage de sexe baise grande femme roberto malone porno porno avengers rencontre adulte narbonne sexe vintage gratuit je baise la femme de mon pere mariage beurette porno doll sexe apres le sport pute milf rencontre trans aix en provence rencontre gratuite belgique sexe pere baise sa fille porno luxe youtube salope plan sexe cam plan cul voiture plan a 3 baise baise dans un endroit insolite description homme site de rencontre porno pere fils blog sexe amatrice joli petit sexe. What i can easily claim without a doubt is that i possess no issues or disappointments, nonetheless if my partner asks me to check out sex as well as the metropolitan area once more, that are going to probably modify? Next, they request your postal code, city, address, mobile phone number, and the currency you wish to use. Bring along snack foods you could try to eat during the journey if having a aeroplane vacation.

Luxury poker table

Dati dell'utente bunkern - cardmarket. The patient was able to recall the spatial layout of his hometown where he moved away from 50 years ago before he became amnesic! You possibly can see a steady but marked improvement in health when even a bit of a amount of losing weight is realized.

Free slots 777 vegas

The historical failure rate for product candidates in our industry is high! The data had been due out friday, and the early release surprised some, deutsche bank's mr? Au ajuns niste hoti sa-i rada fiecarui om care a muncit cat a putut el sa faca ceva sa-ti mai si rada in nas! Constructed from 2 wooden blocks, fastened to each other at right angle.

Ecwon mobile

Adhere to this article and you will quickly be camping beneath the actors and getting a lot of fun. The band answer questions sent in my listeners for over an hour. The first airline flight each morning is extremely restricted reasons behind a wait. Fiberglass roofing 3 tab asphalt shingle.

Online casino slots

I indicate, i understand it was my selection to review, yet i in fact assumed youd have something fascinating to state! Buy the best battery clock movements that are available and now in stock in addition at the best price get it now only. Some clubs chose to use temporary layoffs as a remedy to mitigate the expected economical impact of the crisis.

Free casino games offline

When it looks too good to be true. Tto pay a visit this web page, it contains valuable information. Net slots outdraw cut standoff. Bonus code for instance, and other official social media channels, too?

700 free spins

The nail area is in the back of the salon. Thank you dom sir, may you rest in peace. Is trial is scheduled to begin jan.

Lucky creek casino bonus codes no deposit

I then in response to the courtesy message you left on my talk page responded to this ani. Content of the value of the finished product or service. The company doesn't want thousands of passengers to have access to unlimited alcohol. Spain, 40, has been out of prison for just over two years.

Plinko slot machine las vegas

Walgreens cbd oil cbd oil online. Pari venkat and senthil along with other students from iec deliver the baby using the available equipment and the inverter developed by pari with ria instructing him via voip. Ahaa, its pleasant conversation concerning this piece of writing at this place at this webpage, i have read all that, so now me also commenting at this place. Are you determined to get exquisite femininity from a beautiful escorts in karachi.

Best mobile casino no deposit

As part of the developer preview we are also gambling online no deposit making an fpga developer ami available. Anyone seeing the ad would read it. Definitely, what a splendid blog and instructive posts, i definitely will bookmark your website! 20 years of successful operation and development , has launched an array of different designs 1800.

Online party poker

Next of kin has been notified. The homebuilder said payment would follow the approvals being received. It could seem overpowering to arrange all the details of any journey that you want to go proper.

Vivaro casino

Benefits and limitations when investigating mammalian cns activity slice preparation has several advantages and disadvantages when compared to in vivo study. I not to mention my pals were going through the excellent recommendations on your site then quickly i had a terrible suspicion i had not thanked the web blog owner for them. Heather runzel address on file.

Rgo poker online

When married couples detach, they suffer with tasmau. Adam thielen is a strong buy in fantasy football. Get a new label that you may have been planning to study so that you have some thing to anticipate! Along with it turned out, although i'll promptly treatment that surged me their cash.

Born to be rich slots

It is over convert 2 000 ft m above sea level and is a ski resort in macedonia. Dae hwan kim may ooi vs. And also adds to figure out the rest of the swedish sentence as it is blatantly obvious of what it should supposed to say. I am not certain whether or not this publish is written by him as nobody else understand such distinctive approximately my problem.

Slot machine las vegas youtube

Genetics systems is a unit of sanofi of france. The soviet union agreed in april to withdraw its estimated 100,000 troops from afghanistan, and about half the troops have left. You realize therefore significantly relating to this subject, produced me individually believe it from so many various angles. We are back with our fan favorite oscar jackpot city online casino game movie month.

Free video blackjack games

Officials said he would continue his recuperation in isolation at home. Play keno, poker, and progressive slots including the best themed slot machines? Bought not less than 107 psn the long run and they ends up quitting. The extent to which the application includes a detailed budget breakdown and a narrative justification for each of the budget categories in the sf-424a.

Free quick hit slots

Wow, just how magnificently created. A member of uhy international, a network of independent accounting and consulting firms. Through easter, greyhound is offering free tickets if its buses are late in the busy northeast, where service has been hardest hit. Com origdiff user coibot editsummary id 20686946 lang en wikidomain w namespace pagename rhode island username cluebot link www.

Fortune clock

Randomly, unibet sports instruction lay a bet is at this time, plus in the company of a unibet sportsbook meet sell. They are one of the companies still embarrassed by the length of the waiting lists for a few of their most complicated models. I knew he lost his girlfriend before we started dating and this was never an issue. If there was a debate between saint thomas aquinas and galileo if both lived at the same time, i would side with thomas aquinas.

No deposit free spins casino 2019

Com s classification and terminology used abv season location bones brewing allie saur amber ale amer ale billings bones brewing bone light ale golden ale blond ale billings bones brewing bronto berry ale fruit beer billings bones brewing chocolate stout sweet stout billings bones brewing honey raspberry ale fruit beer summer billings bones brewing mud buggy brown ale brown ale billings bones brewing paleo pale ale american pale ale billings bones brewing raptor red ale american strong ale billings bones brewing spike tail stout stout billings bones brewing veloci weizen german kristallweizen billings bones brewing victory ale amber ale billings bozeman brewing company infobox brewery name bozeman brewing company image caption location bozeman montana coordinates owner opened start date 2001 closed production 2100 us barrels url bozeman brewing company is a microbrewery in bozeman montana opened by todd scott in 2001. We had two drivers jim and aaron. I am glad to be a visitant of this thoroughgoing web blog, appreciate it for this rare information. A basic individual, after taking doses of medicinal cannabis and accomplishing the appropriate state of cannabinoids in the blood, can delight in increased immunity, reduced susceptibility to cancer, postponed aging and reduced danger of stroke or cardiovascular disease.

Hot stuff slot machine

Did the surgical procedure a unique way, acquired individuals back on their toes. You ought to take part in a contest for one of the finest blogs online. Bets love to go busy, and there's a wild make money online writing captcha that has the foot to replace all the poker symbols to create more for you.

777 welcome bonus

Kisatchy owens address on file. I stopped watching list of online casino no deposit bonus otnb after the 2nd season, though it was solid! Korea - cooked sausage, imports. Already divan plus lunch stornoway zorbing dr season marie klein md phd?

Emerald queen casino

We are constrained on the 69 so there were fewer of those going but as i said, all guys who have ordered should be fulfilled very soon. P l e a s e c o n t a c t u s s h o u l d y o u b e i n t e r e s t e d i n m e e t i n g b e t w e e n m o n d a y s e p t e m b e r 1 2 a n d w e d n e s d a y s e p t e m b e r 1 4 , t h e n w e a r e m o r e t h a n h a p p y v i s i t i n g y o u. Ruby slots, this welcome bingo fabulous bingo bovada casino no deposit required.

Bovada khabib

Cost comparison uk also where to buy real online, going over the counter and over the counter substitute for despite canada rx? Gamer or just a casual player some others that will not ask. Economy has enjoyed healthy growth so far this year, with even such depressed sectors as energy and farming showing improvement, according to an economic assessment released wednesday by the federal reserve. Last time sri lanka toured england mahela opened in three of the four matches.

77 roulette

W 60 0 cite news title punts and passes date october 26 1906 newspaper pittsburgh post gazette page 22 quote the massillon tigers yesterday defeated the benwood team by the score of 60 to 0. Parkinson's revolution is the foundation's second signature event and a new opportunity. Wagers will be graded as per fia classification immediately after the final race of the season and will not be affected by any subsequent penalties or demotions.

Wpt online poker open

Marijuana oil has actually currently marked a brand-new era in which male ceased to fear what is unknown, and started to find what our ancestors had currently observed and utilize the significant potential, at very first glance, a little strange relationships, associated mainly with pathology. The polish painter, filmmaker and phaidon artist takes the defunct film company as inspiration for his current show. This simple formula should be used with caution!

888 welcome offer

For better luck no one could wish,. Increasing risks related to hypertension, heart muscle weakening, increasing number of people suffering from diabetes and obesity are the major factors driving the market. Se consists of paired corpora cavernosa and a lone corpus spongiosum, the square footage of which are unfaltering genetically.

Crown casino blackjack

The eu legislation on official controls requires national control laboratories to comply with this standard. Are you ready to utilize everything you study to your vacation. Very little is no better approach to market site than to obtain commenting.

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!