emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Starting with SASS

Echo JS - Wed, 02/06/2019 - 14:46
Categories: Web Technologies

Animate a Blob of Text with SVG and Text Clipping

CSS-Tricks - Wed, 02/06/2019 - 07:36

I came across this neat little animation in a designer newsletter — unfortunately, I lost track of the source, so please give a shout out if you recognize it! In it, a block of text appears to bleed into view with a swirl of colors, then goes out the same way it came in. It’s a slick effect and one I wanted to recreate in code.

The approach I took was to use SVG text as a clip path for an SVG background. We must use SVG text because CSS currently only allows us to animate the background with text clipping if the background is a GIF, which we’re not going to do.

Our first task is to create a background of different SVG shapes. Oval-y blobs work pretty well. The thing to make sure of is to match the size the artboard/canvas in whatever illustration app you’re using to the same dimension as the viewBox you want the final work to be. (In Inkscape, this option is under the Scale section of Document Properties.)

The goal is to cover most of the artboard with a variety of shapes. I've found that it looks best if some of the shapes overlap.

Next, we’ll create some text in a <clipPath>, group the objects that make up the background inside a <g> element, and apply a CSS clip-path on that group. Altogether it looks something like this:

<svg viewbox="0 0 700 225"> <clipPath id="textClip" class="filled-heading"> <text y="70">We are</text> <text y="140">Creators</text> <text y="210">+Innovators</text> </clipPath> <g id="background" clip-path="url(#textClip)"> <path d="m449.78..." /> </g> </svg>

At this point, all we get is some plain text because we haven’t gotten around to the background animation quite yet.

So what about that animation? We can use a relatively simple CSS animation like this:

/* Animate the background shapes */ #background path { animation: pulse 4s cubic-bezier(0.455, 0.030, 0.515, 0.955) infinite; /* Necessary to keep the SVG objects in place while scaling */ transform-origin: 50% 50%; transform-box: fill-box; } @keyframes pulse { /* Rotating it along with the scale makes it a little bit more fancy */ 0%, 100% { transform: scale(0) rotate(33deg); } 35%, 65% { transform: scale(1) rotate(0deg); } }

So far, so good.

​transform-box: fill-box; is not supported in Internet Explorer or Edge at this point, so if you need to support those browsers, you’ll need to use a JavaScript workaround, like this one.

See the Pen
Animated blob SVG text clipping effect - Pt. 1
by Zach Saucier (@Zeaklous)
on CodePen.

We could start painting things in by hard-coding color values using a text or vector editor, but it's more fun to color the shapes dynamically. Something like this:

// Define an array of colors const colors = ['#f5a147','#51cad8','#112b39']; // Select the SVG paths var blobs = document.querySelectorAll("path"); // Randomly apply colors to the SVG fill property blobs.forEach(blob => { blob.style.fill = colors[Math.floor(Math.random() * colors.length)]; });

In order to change the text values for each iteration, we need to first add them to the SVG clip path.

<clipPath id="text" class="filled-heading"> <text y="70">We are</text> <text y="140">Creators</text> <text y="210">+Innovators</text> <text y="70">We are</text> <text y="140">Movers</text> <text y="210">+Shakers</text> <text y="70">We are</text> <text y="140">Stylish</text> <text y="210">+Techy</text> </clipPath>

Then we can either use CSS or JavaScript to reveal the lines of text in our preferred order. Unfortunately, we can't surround each section of <text> using a <g> element because <g> elements don't work inside of a clipPath. For this post, we’re going to split things up into three CSS animations, one for each group of three paths:

/* Selects paths 1-3 */ #textClip text:nth-of-type(n + 1):nth-of-type(-n + 3) { animation: showFirst 12s infinite; } /* Selects paths 4-6 */ #textClip text:nth-of-type(n + 4):nth-of-type(-n + 6) { animation: showSecond 12s infinite; } /* Selects paths 7-9 */ #textClip text:nth-of-type(n + 7):nth-of-type(-n + 9) { animation: showThird 12s infinite; } @keyframes showFirst { 0%, 33% { opacity: 1; } 33.0001%, 100% { opacity: 0; } } @keyframes showSecond { 33.0001%, 66% { opacity: 1; } 0%, 33%, 66.0001%, 100% { opacity: 0; } } @keyframes showThird { 66.0001%, 99.999% { opacity: 1; } 0%, 66%, 100% { opacity: 0; } }

That does the trick!

See the Pen
Animated blob SVG text clipping effect - Pt. 2
by Zach Saucier (@Zeaklous)
on CodePen.

At this point, we can have a little fun. For example, we can swap backgrounds for a different effect. I used Inkscape's star tool with three to four points to generate some random shapes (using Inkscape’s random parameter) and then colored them using a palette from one of the many color scheme generators (I used Palx) to produce this version:

See the Pen
Animated blob SVG text clipping effect - Pt. 3
by Zach Saucier (@Zeaklous)
on CodePen.

The backgrounds don't even need to fill up the entire background, depending on the effect that we want to create. For example, we could duplicate the text using a element and fill in the text using that as seen in this demo.

Or we could mix it up by rotating the background blobs like this:

See the Pen
Animated blob SVG text clipping effect - Pt. 5
by Zach Saucier (@Zeaklous)
on CodePen.

To make the colors change for every new set of words, we could use either a CSS or JavaScript for the animation. I used JavaScript (and moved the CSS animation that was hiding the text lines to the JavaScript):

See the Pen
Animated blob SVG text clipping effect - Pt. 6
by Zach Saucier (@Zeaklous)
on CodePen.

To center the text horizontally, add x="50%" text-anchor="middle" to each <text> element (Demo). Centering it vertically would take more manual calculation since we’re working with a multi-line format.

One of the nice things about this approach is that, since it uses SVG, it is responsive by default!

P.S. After I made this approach and was looking for the original GIF author, I came across another recreation by Martí Fenosa doing the same effect using a different approach. Check his demo out as well because it’s clever!

The post Animate a Blob of Text with SVG and Text Clipping appeared first on CSS-Tricks.

Categories: Web Technologies

Gradians and Turns: the quiet heroes of CSS angles

CSS-Tricks - Wed, 02/06/2019 - 07:34

I love coming across little overlooked CSS gems, like the gradien (grad) and turn (turn) units that Ken Bellows uncovers in his post explaining them. I don't know, maybe y'all are already aware of them, but they're certainly new to me.

They're additional options for dealing with angles, where degrees (deg) and radians (rad) are more commonly known. I'm partial to degrees anytime I'm working with rotations. But now that I know there's an easier way to express a half rotation by using 0.5turn instead of 180deg, I can see myself reaching for turns much more often.

When you're designing an animation or thinking about how to add some rotation to your design, how are you thinking about it? You probably aren't thinking in numbers. ... You don't usually think in degrees or radians. You think in terms of full turns and portions of turns. At least, I do.

After looking at his table of comparisons, I take the point:

Degrees Radians Gradians Turns My Fav Unit 30deg 0.52rad 33.33grad 0.08turn Gradians 45deg 0.79rad 50grad 0.13turn Gradians 60deg 1.04rad 66.67grad 0.17turn Gradians 90deg 1.57rad 100grad 0.25turn Turns 180deg 3.14rad 200grad 0.5turn Turns 360deg 6.28rad 400grad 1turn Turns 720deg 12.56rad 800grad 2turn Turns 1080deg 25.12rad 1200grad 3turn Turns

Hear, hear! And since these units are supported back to IE 9, seems like something fun to try out.

(Hat tip to Rachel Andrew for sharing this in her awesome CSS Layout News email.)

Direct Link to ArticlePermalink

The post Gradians and Turns: the quiet heroes of CSS angles appeared first on CSS-Tricks.

Categories: Web Technologies

Extending WordPress Dockerfile to use MySQL 5.7 (or 8.0)

Planet MySQL - Tue, 02/05/2019 - 13:28

Oracle’s website shows End of life for MySQL 5.5 as of Jan 20th of 2019, so hurry up and upgrade!

I am working building some demos for Cloud SQL and one of the requirements I had was to run MySQL 5.7 and WordPress as my sample application. The demo consisted on migrating from a single VM environment with WordPress and MySQL running alongside. The narrative: the site got popular and the database became the bottle neck because of all the shared resources between them and the application. The proposed solution? A minimal downtime migration to Cloud SQL, moving the data layer to a dedicated server.

I am going to be doing this demo a lot of times, so I needed some way to automate it. I thought of doing through Docker. I am not Docker proficient, and to begin with I asked Anthony for help to get me to what I wanted, but there are so many nuances! Maybe someone will find a better solution to it than this one, but I decided to share what I got.

Let’s examine the two scenarios I faced. All examples assume Debian/Ubuntu.

I don’t run Docker, just have a VM and want to have MySQL 5.7

In this case it’s straightforward: you need to use the MySQL official APT repository available in https://dev.mysql.com/downloads/repo/apt/.

At this time the most recent version is mysql-apt-config_0.8.12-1_all.deb, keep an eye before continuing this because it may change the version until you use this tutorial.

In line 2 you can change from mysql-5.7 to mysql-8.0, if unspecified the command, version 8.0 will be installed.

I run Docker and want to have 5.7 or 8.0 installed on it

It’s a bit similar to the previous situation, you still need to go to the APT repository page to know which file to download and add this on your Dockerfile:

Notice, you can also change the version of MySQL here. Don’t forget to pass DB_ROOT_PASSWORD​ when doing your docker build using the --build-arg argument. More details here.

It works!

These are the workarounds to avoid using MySQL 5.5. After that I was able to finally automate my demo. Feel free here to share better examples of what I did, as I said, I don’t have proficiency in the subject.

Categories: Web Technologies

Using the Little-Known CSS element() Function to Create a Minimap Navigator

CSS-Tricks - Tue, 02/05/2019 - 11:04

W3C’s CSS Working Group often gives us brilliant CSS features to experiment with. Sometimes we come across something so cool that sticks a grin on our face, but it vanishes right away because we think, “that’s great, but what do I do with it?” The element() function was like that for me. It’s a CSS function that takes an element on the page and presents it as an image to be displayed on screen. Impressive, but quixotic.

Below is a simple example of how it works. It’s currently only supported in Firefox, which I know is a bummer. But stick with me and see how useful it can be.

<div id="ele"> <p>Hello World! how're you?<br>I'm not doing that<br>great. Got a cold &#x1F637;</p> </div> <div id="eleImg"></div> #eleImg { background: -moz-element(#ele) no-repeat center / contain; /* vendor prefixed */ }

The element() function (with browser prefix) takes the id value of the element it’ll translate into an image. The output looks identical to the appearance of the given element on screen.

When I think of element()’s output, I think of the word preview. I think that’s the type of use case that gets the most out of it: where we can preview an element before it’s shown on the page. For example, the next slide in a slideshow, the hidden tab, or the next photo in a gallery. Or... a minimap!

A minimap is a mini-sized preview of a long document or page, usually shown at on one side of the screen or another and used to navigate to a corresponding point on that document.

You might have seen it in code editors like Sublime Text.

The minimap is there on the right.

CSS element() is useful in making the “preview” part of the minimap.

Down below is the demo for the minimap, and we will walk through its code after that. However, I recommend you see the full-page demo because minimaps are really useful for long documents on large screens.

If you’re using a smartphone, remember that, according to the theory of relativity, minimaps will get super mini in mini screens; and no, that’s not really what the theory of relativity actually says, but you get my point.

See the Pen Minimap with CSS element() & HTML input range by Preethi Sam (@rpsthecoder) on CodePen.

If you’re designing the minimap for the whole page, like for a single page website, you can use the document body element for the image. Otherwise, targeting the main content element, like the article in my demo, also works.

<div id="minimap"></div> <div id="article"> <!-- content --> </div> #minimap { background: rgba(254,213,70,.1) -moz-element(#article) no-repeat center / contain; position: fixed; right: 10px; top: 10px; /* more style */ }

For the minimap’s background image, we feed the id of the article as the parameter of element() and, like with most background images, it’s styled to not repeat (no-repeat) and fit inside (contain) and at center of the box (center) where it’s displayed.

The minimap is also fixed to the screen at top right of the viewport.

Once the background is ready, we can add a slider on top of it and it will serve to operate the minimap scrolling. For the slider, I went with input: range, the original, uncomplicated, and plain HTML slider.

<div id="minimap"> <input id="minimap-range" type="range" max="100" value="0"> </div> #minimap-range { /* Rotating the default horizontal slider to vertical */ transform: translateY(-100%) rotate(90deg); transform-origin: bottom left; background-color: transparent; /* more style */ } #minimap-range::-moz-range-thumb { background-color: dodgerblue; cursor: pointer; /* more style */ } #minimap-range::-moz-range-track{ background-color: transparent; }

Not entirely uncomplicated because it did need some tweaking. I turned the slider upright, to match the minimap, and applied some style to its pseudo elements (specifically, the thumb and track) to replace their default styles. Again, we’re only concerned about Firefox at the moment since we’re dealing with limited support.

All that’s left is to couple the slider’s value to a corresponding scroll point on the page when the value is changed by the user. That takes a sprinkle of JavaScript, which looks like this:

onload = () => { const minimapRange = document.querySelector("#minimap-range"); const minimap = document.querySelector("#minimap"); const article = document.querySelector("#article"); const $ = getComputedStyle.bind(); // Get the minimap range width multiplied by the article height, then divide by the article width, all in pixels. minimapRange.style.width = minimap.style.height = parseInt($(minimapRange).width) * parseInt($(article).height) / parseInt($(article).width) + "px"; // When the range changes, scroll to the relative percentage of the article height minimapRange.onchange = evt => scrollTo(0, parseInt($(article).height) * (evt.target.value / 100)); };

The dollar sign ($) is merely an alias for getComputedStyle(), which is the method to get the CSS values of an element.

It’s worth noting that the width of the minimap is already set in the CSS, so we really only need to calculate its height. So, we‘re dealing with the height of the minimap and the width of the slider because, remember, the slider is actually rotated up.

Here’s how the equation in the script was determined, starting with the variables:

  • x1 = height of minimap (as well as the width of the slider inside it)
  • y1 = width of minimap
  • x2 = height of article
  • y2 = width of article
x1/y1 = x2/y2 x1 = y1 * x2/y2 height of minimap = width of minimap * height of article / width of article

And, when the value of the slider changes (minimapRange.onchange), that’s when the ScrollTo() method is called to scroll the page to its corresponding value on the article. &#x1f4a5;

Fallbacks! We need fallbacks!

Obviously, there are going to be plenty of times when element() is not supported if we were to use this at the moment, so we might want to hide the minimap in those cases.

We check for feature support in CSS:

@supports (background: element(#article)) or (background: -moz-element(#article)){ /* fallback style */ }

...or in JavaScript:

if(!CSS.supports('(background: element(#article)) or (background: -moz-element(#article))')){ /* fallback code */ }

If you don’t mind the background image being absent, then you can still keep the slider and apply a different style on it.

There are other slick ways to make minimaps that are floating out in the wild (and have more browser support). Here’s a great Pen by Shaw:

See the Pen
Mini-map Progress Tracker & Scroll Control
by Shaw (@shshaw)
on CodePen.

There are also tools like pagemap and xivimap that can help. The element() function is currently specced in W3C’s CSS Image Values and Replaced Content Module Level 4. Defintely worth a read to fully grasp the intention and thought behind it.

This browser support data is from Caniuse, which has more detail. A number indicates that browser supports the feature at that version and up.

DesktopChromeOperaFirefoxIEEdgeSafariNoNo4*NoNoNoMobile / TabletiOS SafariOpera MobileOpera MiniAndroidAndroid ChromeAndroid FirefoxNoNoNoNoNo64*

Psst! Did you try selecting the article text in the demo? See what happens on the minimap. &#x1f609;

The post Using the Little-Known CSS element() Function to Create a Minimap Navigator appeared first on CSS-Tricks.

Categories: Web Technologies

Bandwidth or Latency: When to Optimise for Which

CSS-Tricks - Tue, 02/05/2019 - 10:57

Harry Roberts:

A good rule of thumb to remember is that, for regular web browsing, improvements in latency would be more beneficial than improvements in bandwidth, and that improvements in bandwidth are noticed more when dealing with larger files.

Direct Link to ArticlePermalink

The post Bandwidth or Latency: When to Optimise for Which appeared first on CSS-Tricks.

Categories: Web Technologies

Upcoming Webinar Wed 2/6: Percona Software News and Roadmap Update

Planet MySQL - Tue, 02/05/2019 - 08:29

Join Percona CEO Peter Zaitsev as he presents Percona Software News and Roadmap Update on Wednesday, February 6, 2019, at 11:00 AM PST (UTC-8) / 2:00 PM EST (UTC-5).

Register Now

Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software. Topics include Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

During this webinar, Peter will talk about newly released features in Percona software. He will also show a few quick demos and share with you highlights from the Percona open source software roadmap.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register today to join Peter for his Percona Software News and Roadmap Update.

Categories: Web Technologies

The Left-Prefix Index Rule

Planet MySQL - Tue, 02/05/2019 - 08:29

There's an important heuristic in database indexing that I call the left-prefix rule. It helps you understand whether the database will be able to make the best use of a multi-column index to speed up a query. It's one of a small handful of really important things to understand about indexes!


The left-prefix rule can be confusing, but once you grok it, it's simple and easy to use. It applies to MySQL, PostgreSQL, MongoDB, and many other databases. In this post I'll explain what the left-prefix rule means and how it works.

How Multi-Column Indexes Work

Most database indexes are a sorted copy of some data from the table they index. Suppose you have a database of people and you want to find them by name. If you create an index on the first_name attribute, the database makes a sorted, searchable copy of everyone's first name, with pointers back to the original records.

Indexes can have more than a single column. Multi-column indexes are also called compound indexes or composite indexes. The underlying implementation is sorted by all of the columns, in order. Suppose you index (last_name, first_name): now the database creates a copy of both of those columns and sorts them first by the last name, then if there are any people with the same last name, it sorts them by first name.

Here's an example. Notice how there are two entries for Bach, and which one comes first.

The Left-Prefix Rule

The left-prefix rule says that a query can search an index efficiently if it provides search terms (such as values in a WHERE clause) that match the leading columns of the index, in left-to-right order.

In our example above, if we're looking for people named Bach, we can search the index efficiently and find all of the Bach's. If we're looking for {Bach, Carl Philipp Emanuel}, we can find that record efficiently too.

But if we're looking for people named Irving, we're going to have to scan the whole index, because there could be Irvings anywhere in the index. It's not sorted in a way that keeps all the first names together, unless you're looking within a single last name. So another way to state the left-prefix rule is that searches that don't constrain the leading index columns aren't very efficient. The index might not even be usable for such searches.

Inequalities, Ranges, and the Left-Prefix Rule

There's more to the left-prefix rule. The usable prefix of the index, in most databases and under most circumstances, is up to and including the first inequality or range search condition.

Suppose we have a four-column index on columns (a, b, c, d). If we search for records where a=X, and b=Y, and c>Q, and d=R, then the usable prefix is only the first three columns. That's because the database will be able to progressively narrow the search until it gets to the fourth column:

  • Narrow the search to a=X. Good.
  • Narrow the search to b=Y. Okay.
  • Narrow the search to c>Q. That's a set (or range) of potentially many different values of c, so the narrowing stops after this.
  • Look for d=R. This is a scan. Within each possible value of Q, the database must scan to find rows with d=R.
Gaps in the Prefix

There's one more case to consider: what if you provide search terms for some but not all of the columns in the index? What if you search for a=X, b=Y, and d=R?

The usable prefix of the index, that's constrained/narrowed by your criteria, is limited to the contiguous prefix for which you've provided search terms. In this case, that's just (a, b). That's your usable left-prefix for constraining the search. A "hole" or gap in the index, meaning that the index has a column without a search term to apply to it, ends the prefix.

Concluding Thoughts

Now you know the three important tenets of the left-prefix rule!

  1. A query can search an index efficiently if it constrains the leading columns of the index, in left-to-right order...
  2. Up to and including the first inequality or range search condition...
  3. Without any gaps in the column prefix.

Next time you're examining the EXPLAIN plan of a query that uses an index, and you see that it doesn't use all the columns of a multi-column (compound/composite) index, check whether the query satisfies the left-prefix rule. If it doesn't, that might explain why the index's full power isn't being used to speed up the query as much as it could!

Photo by Iñaki del Olmo on Unsplash

Categories: Web Technologies

410 Gone - Evert Pot

Planet PHP - Tue, 02/05/2019 - 07:00

410 Gone is a status code that can be used in cases where a resource is gone and never coming back. It’s a more specific version of 404 Not Found.

A good example for using 410 instead of 404 is when a resource was intentionally removed.

Using 410 can be helpful, because it signals to other people linking to you that the link is dead and should be removed. A 404 is the default for missing resources, and it can just mean that the owner of the site has moved the content and didn’t put the right redirects in place.

So to sum it up: 410 implies intent.

Example HTTP/1.1 410 Gone Content-Type: text/plain Server: curveball/0.6.0 I deleted it and it's never coming back! References
Categories: Web Technologies

Updating triggers online with MySQL and MariaDB

Planet MySQL - Tue, 02/05/2019 - 04:44
This is what can happen if triggers are updated while applications are running

Updating triggers online means that we are able to do this in production, while our applications are running, without causing a disruption.

To achieve this, we need to run CREATE TRIGGER and DROP TRIGGER inside a transaction, but this is not supported by MySQL and MariaDB. If we try to do so, the current transaction is implicitly committed. MariaDB has a variable called in_transaction that allows us to demonstrate this clearly:

MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 1 | +------------------+ 1 row in set (0.000 sec) MariaDB [test]> DROP TRIGGER bi_t; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 0 | +------------------+ 1 row in set (0.000 sec)

The problem is actually more generic: MySQL and MariaDB don’t support transactional DDL. But let’s focus on triggers. I’ll explain what problems this causes, and then I will propose a solution.

The problems

The problems are:

  • Phantom trigger – it’s about every single updated trigger;
  • Inconsistent triggers – it’s about a table’s triggers as a whole;
  • Atomicity – it’s about failures.
Phantom triggers

If I want to replace one trigger with another, as mentioned, I will need to run DROP TRIGGER followed by CREATE TRIGGER. This implies that, in the (hopefully brief) interval between these expressions, the trigger will simply not exist.

If I run these statements in a transaction, this is not a problem. In fact, for other users, both the statements will take effect together when I COMMIT the transaction. But again, this cannot happen in MySQL and MariaDB. On a heavily used server, we should definitely expect some users to write the table without activating a trigger.

However, MariaDB has a solution for this problem: CREATE OR REPLACE TRIGGER. In this way the existing trigger (if any) will be destroyed and the new one will be created, but this operation is atomic. No other user can see the table without a trigger.

Inconsistent triggers

Even with the first problem solved, in most cases we have a second problem: we have multiple triggers associated to a table, and we always want them to be consistent each other.

This is better explained with an example. Suppose that we have triggers BEFORE INSERT and UPDATE. These triggers perform some data validation, and return an error if some data in not valid. If you want to know how to do this, take a look at an article I wrote some years ago in my personal blog: Validating data using a TRIGGER.

Anyway, the problem here is that, if we change the validation rules in one trigger, we want to change them in the other trigger also. In no point in time we want the triggers to be inconsistent.

Atomicity

Our triggers operations should completely succeed or completely fail. If, for example, we lose our connection in the middle of these changes, all the changes we’ve done should be undone, to avoid inconsistencies.

The solution

If you used MySQL for centuries like me, you may remember the LOCK TABLES statement. And probably you remember it with horror. It was developed in ancient times as an alternative to transactions, when MyISAM (or probably its ancestor, ISAM) was the default storage engine and InnoDB simply didn’t exist. Basically, since you could not use a real transaction, you locked a whole table with a read lock or a write lock. Then you did some stuff, and then you ran UNLOCK TABLES. Operations executed inside theLOCK block were not atomic, but at least other users could not interfere with them. Nowadays, I only see that command in legacy code – but yes, I still see it.

Why doesLOCK TABLES help in this case? Because the lock survives statements like DROP TRIGGER and CREATE TRIGGER.

So basically what you would do is:

  • LOCK TABLE something WRITE;
  • the command is queued until all existing metadata locks (transactions or DDL in progress) are released; this would happen even with a DROP TRIGGERS;
  • you DROP old triggers;
  • you CREATE new triggers;
  • UNLOCK TABLES.
Conclusions

What are the drawbacks of this solution?

  • If the connection is lost at some point, no rollback happens. In other word, the Atomicity problem mentioned above is not solved in any way.
  • If a long transaction is running, our LOCK TABLE statement will be queued for a long time. And it will cause several other statements (including SELECTs) to be queue. But this also happens with a simple DROP TRIGGER.
  • For a brief moment, the table will be completely write-locked.

What are the pro’s?

  • No disruption.
  • Isolation between concurrent sessions.
  • Once the lock is acquired, the operations on triggers will be fast.

Other methods are possible, but they are more complicated and not necessarily less risky. For example, if you use row-based replication, you could update the triggers on a slave, promote it to master, and then update the triggers on the master. But then you have a risk of conflicts during the failover.

All in all, I believe that this method is good enough for most situations.

Toodle pip,
Federico Razzoli

Photo credit: Stefano Trucco

Share

The post Updating triggers online with MySQL and MariaDB appeared first on Federico Razzoli.

Categories: Web Technologies

What is LOAD DATA and how does it work

Planet MySQL - Tue, 02/05/2019 - 02:08

I would like to highlight a less known MySQL SQL command, analyze how it works and understand some of the decisions that need to be made when using it.

Let’s start with the basics: The Reference Manual.

It does have a lot to say about LOAD DATA.…

Categories: Web Technologies

What is LOAD DATA and how does it work

MySQL Server Blog - Tue, 02/05/2019 - 02:08

I would like to highlight a less known MySQL SQL command, analyze how it works and understand some of the decisions that need to be made when using it.

Let’s start with the basics: The Reference Manual.

It does have a lot to say about LOAD DATA.…

Categories: Web Technologies

Pages