emGee Software Solutions Custom Database Applications

Share this

Web Technologies

PHP 7.2.16 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 03/06/2019 - 16:00
The PHP development team announces the immediate availability of PHP 7.2.16. This is a security release which also contains several minor bug fixes.All PHP 7.2 users are encouraged to upgrade to this version.For source downloads of PHP 7.2.16 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
Categories: Web Technologies

PHP 7.1.27 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 03/06/2019 - 16:00
The PHP development team announces the immediate availability of PHP 7.1.27. This is a security release which also contains several bug fixes.All PHP 7.1 users are encouraged to upgrade to this version.For source downloads of PHP 7.1.27 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
Categories: Web Technologies

PHP 7.3.3 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 03/06/2019 - 16:00
The PHP development team announces the immediate availability of PHP 7.3.3. This is a security release which also contains several bug fixes.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.3 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
Categories: Web Technologies

Native Video on the Web

CSS-Tricks - Wed, 03/06/2019 - 13:33

TIL about the HLS video format:

HLS stands for HTTP Live Streaming. It’s an adaptive bitrate streaming protocol developed by Apple. One of those sentences to casually drop at any party. Äh. Back on track: HLS allows you to specify a playlist with multiple video sources in different resolutions. Based on available bandwidth these video sources can be switched and allow adaptive playback.

This is an interesting journey where the engineering team behind Kitchen Stories wanted to switch away from the Vimeo player (160 kB), but still use Vimeo as a video host because they provide direct video links with a Pro plan. Instead, they are using the native <video> element, a library for handling HLS, and a wrapper element to give them a little bonus UX.

This video stuff is hard to keep up with! There is another new format called AV1 that is apparently a big deal as YouTube and Netflix are both embracing it. Andrey Sitnik wrote about it here:

Even though AV1 codec is still considered experimental, you can already leverage its high-quality, low-bitrate features for a sizable chunk for your web audience (users with current versions of Chrome and Firefox). Of course, you would not want to leave users for other browsers hanging, but the attributes for <video> and <source> tags make implementing this logic easy, and in pure HTML, you don’t need to go at length to detect user agents with JavaScript.

That doesn't even mention HLS, but I suppose that's because HSL is a streaming protocol, which still needs to stream in some sort of format.

Direct Link to ArticlePermalink

The post Native Video on the Web appeared first on CSS-Tricks.

Categories: Web Technologies

CSS Algorithms

CSS-Tricks - Wed, 03/06/2019 - 11:13

I wouldn't say the term "CSS algorithm" has widespread usage yet, but I think Lara Schenck might be onto something. She defines it as:

a well-defined declaration or set of declarations that produces a specific styling output

So a CSS algorithm isn't really a component where there is some parent element and whatever it needs inside, but a CSS algorithm could involve components. A CSS algorithm isn't just some tricky key/value pair or calculated output — but it could certainly involve those things.

The way I understand it is that they are little mini systems. In a recent post, she describes a situation involving essentially two fixed header bars and needing to deal with them in different situations. In this example, the page can be in different states (e.g. a logged-in state has a position: fixed; bar), and that affects not only the header but the content area as well. Dealing with all that together is a CSS algorithm. It's probably the way we all work in CSS already, but now have a term to describe it. This particular example involves some CSS custom properties, a state-based class, two selectors, and a media query. Classic front-end developer stuff.

Lara is better at explaining what she means though. You should read her initial blog post, main blog post, collection of examples, and talk on the subject.

She'll be at PPK's CSS Day in June (hey, it's on our conferences list!), and the idea has clearly stirred up some thoughts from him.

Direct Link to ArticlePermalink

The post CSS Algorithms appeared first on CSS-Tricks.

Categories: Web Technologies

Extracting Text from Content Using HTML Slot, HTML Template and Shadow DOM

CSS-Tricks - Wed, 03/06/2019 - 08:04

Chapter names in books, quotes from a speech, keywords in an article, stats on a report — these are all types of content that could be helpful to isolate and turn into a high-level summary of what's important.

For example, have you seen the way Business Insider provides an article's key points before getting into the content?

That’s the sort of thing we're going to do, but try to extract the high points directly from the article using HTML Slot, HTML Template and Shadow DOM.

These three titular specifications are typically used as part of Web Components — fully functioning custom element modules meant to be reused in webpages.

Now, what we aim to do, i.e. text extraction, doesn’t need custom elements, but it can make use of those three technologies.

There is a more rudimentary approach to do this. For example, we could extract text and show the extracted text on a page with some basic script without utilizing slot and template. So why use them if we can go with something more familiar?

The reason is that using these technologies permits us a preset markup code (also optionally, style or script) for our extracted text in HTML. We’ll see that as we proceed with this article.

Now, as a very watered-down definition of the technologies we’ll be using, I’d say:

  • A template is a set of markup that can be reused in a page.
  • A slot is a placeholder spot for a designated element from the page.
  • A shadow DOM is a DOM tree that doesn’t really exist on the page till we add it using script.

We’ll see them in a little more depth once we get into coding. For now, what we’re going to make is an article that follows with a list of key points from the text. And, you probably guessed it, those key points are extracted from the article text and compiled into the key points section.

See the Pen
Text Extraction with HTML Slot and HTML Template
by Preethi Sam (@rpsthecoder)
on CodePen.

The key points are displayed as a list with a design in between the points. So, let’s first create a template for that list and designate a place for the list to go.

<article><!-- Article content --></article> <!-- Section where the extracted keypoints will be displayed --> <section id='keyPointsSection'> <h2>Key Points:</h2> <ul><!-- Extracted key points will go in here --></ul> </section> <!-- Template for the key points list --> <template id='keyPointsTemplate'> <li><slot name='keyPoints'></slot></li> <li style="text-align: center;">&#x2919;&mdash;&#x291a;</li> </template>

What we’ve got is a semantic <section> with a <ul> where the list of key points will go. Then we have a <template> for the list items that has two <li> elements: one with a <slot> placeholder for the key points from the article and another with a centered design.

The layout is arbitrary. What’s important is placing a <slot> where the extracted key points will go. Whatever’s inside the <template> will not be rendered on the page until we add it to the page using script.

Further, the markup inside <template> can be styled using inline styles, or CSS enclosed by <style>:

<template id='keyPointsTemplate'> <li><slot name='keyPoints'></slot></li> <li style="text-align: center;">&#x2919;&mdash;&#x291a;</li> <style> li{/* Some style */} </style> </template>

The fun part! Let’s pick the key points from the article. Notice the value of the name attribute for the <slot> inside the <template> (keyPoints) because we’ll need that.

<article> <h1>Bears</h1> <p>Bears are carnivoran mammals of the family Ursidae. <span><span slot='keyPoints'>They are classified as caniforms, or doglike carnivorans</span></span>. Although only eight species of bears <!-- more content --> and partially in the Southern Hemisphere. <span><span slot='keyPoints'>Bears are found on the continents of North America, South America, Europe, and Asia</span></span>.<!-- more content --></p> <p>While the polar bear is mostly carnivorous, <!-- more content -->. Bears use shelters, such as caves and logs, as their dens; <span><span slot='keyPoints'>Most species occupy their dens during the winter for a long period of hibernation</span></span>, up to 100 days.</p> <!-- More paragraphs --> </article>

The key points are wrapped in a <span> carrying a slot attribute value ("keyPoints") matching the name of the <slot> placeholder inside the <template>.

Notice, too, that I’ve added another outer <span> wrapping the key points.

The reason is that slot names are usually unique and are not repeated, because one <slot> matches one element using one slot name. If there’re more than one element with the same slot name, the <slot> placeholder will be replaced by all those elements consecutively, ending in the last element being the final content at the placeholder.

So, if we matched that one single <slot> inside the <template> against all of the <span> elements with the same slot attribute value (our key points) in a paragraph or the whole article, we’d end up with only the last key point present in the paragraph or the article in place of the <slot>.

That’s not what we need. We need to show all the key points. So, we’re wrapping the key points with an outer <span> to match each of those individual key points separately with the <slot>. This is much more obvious by looking at the script, so let’s do that.

const keyPointsTemplate = document.querySelector('#keyPointsTemplate').content; const keyPointsSection = document.querySelector('#keyPointsSection > ul'); /* Loop through elements with 'slot' attribute */ document.querySelectorAll('[slot]').forEach((slot)=>{ let span = slot.parentNode.cloneNode(true); span.attachShadow({ mode: 'closed' }).appendChild(keyPointsTemplate.cloneNode(true)); keyPointsSection.appendChild(span); });

First, we loop through every <span> with a slot attribute and get a copy of its parent (the outer <span>). Note that we could also loop through the outer <span> directly if we’d like, by giving them a common class value.

The outer <span> copy is then attached with a shadow tree (span.attachShadow) made up of a clone of the template’s content (keyPointsTemplate.cloneNode(true)).

This "attachment" causes the <slot> inside the template’s list item in the shadow tree to absorb the inner <span> carrying its matching slot name, i.e. our key point.

The slotted key point is then added to the key points section at the end of the page (keyPointsSection.appendChild(span)).

This happens with all the key points in the course of the loop.

That’s really about it. We’ve snagged all of the key points in the article, made copies of them, then dropped the copies into the list template so that all of the key points are grouped together providing a nice little CliffsNotes-like summary of the article.

Here's that demo once again:

See the Pen
Text Extraction with HTML Slot and HTML Template
by Preethi Sam (@rpsthecoder)
on CodePen.

What do you think of this technique? Is it something that would be useful in long-form content, like blog posts, news articles, or even Wikipedia entries? What other use cases can you think of?

The post Extracting Text from Content Using HTML Slot, HTML Template and Shadow DOM appeared first on CSS-Tricks.

Categories: Web Technologies

The Client/Server Rendering Spectrum

CSS-Tricks - Wed, 03/06/2019 - 07:52

I've definitely been guilty of thinking about rendering on the web as a two-horse race. There is Server-Side Rendering (SSR, like this WordPress site is doing) and Client-Side Rendering (CSR, like a typical React app). Both are full of advantages and disadvantages. But, of course, the conversation is more nuanced. Just because an app is SSR doesn't mean it doesn't do dynamic JavaScript-powered things. And just because an app is CSR doesn't mean it can't leverage any SSR at all.

It's a spectrum! Jason Miller and Addy Osmani paint that picture nicely in Rendering on the Web.

My favorite part of the article is the infographic table they post at the end of it. Unfortunately, it's a PNG. So I took a few minutes and <table>-ized it, in case that's useful to anyone.

See the Pen
The Client/Server Rendering Spectrum
by Chris Coyier (@chriscoyier)
on CodePen.

Direct Link to ArticlePermalink

The post The Client/Server Rendering Spectrum appeared first on CSS-Tricks.

Categories: Web Technologies

Refactoring Tunnels

CSS-Tricks - Wed, 03/06/2019 - 07:51

We’ve been writing a lot about refactoring CSS lately, from how to take a slow and methodical approach to getting some quick wins. As a result, I’ve been reading a ton about this topic and somehow stumbled upon this post by Harry Roberts about refactoring and how to mitigate the potential risks that come with it:

Refactoring can be scary. On a sufficiently large or legacy application, there can be so much fundamentally wrong with the codebase that many refactoring tasks will run very deep throughout the whole project. This puts a lot of pressure on developers, especially considering that this is their chance to "get it right this time". This can feel debilitating: "Where do I start?" "How long is this going to take?" "How will I know if I’m doing the right thing?"

Harry then comes up with this metaphor of a refactoring tunnel where it’s really easy to find yourself stuck in the middle of a refactor and without any way out of it. He argues that we should focus on small, manageable pieces instead of trying to tackle everything at once:

Resist the temptation to refactor anything that runs right the way throughout the project. Instead, identify smaller and more manageable tasks: tasks that have a much smaller surface area, and therefore a much shorter Refactoring Tunnel.

These tasks can still aim toward a larger and more total goal but can be realised in much safer and shorter timeframes. Want to move all of your classes from BEM to BEM(IT)? Sure, but maybe just implement it on the nav first.

This way feels considerably slower, for sure, but there’s so much less risk involved.

Direct Link to ArticlePermalink

The post Refactoring Tunnels appeared first on CSS-Tricks.

Categories: Web Technologies

Interview with Jeremy Lindblom - Voices of the ElePHPant

Planet PHP - Wed, 03/06/2019 - 04:00

@jeremeamia Show Notes


This episode is sponsored by

The post Interview with Jeremy Lindblom appeared first on Voices of the ElePHPant.

Categories: Web Technologies

Dealing with Unreliable Networks When Crafting an HA Solution for MySQL or MariaDB

Planet MySQL - Wed, 03/06/2019 - 01:26

Long gone are the days when a database was deployed as a single node or instance - a powerful, standalone server which was tasked to handle all the requests to the database. Vertical scaling was the way to go - replace the server with another, even more powerful one. During these times, one didn’t really have to be bothered by network performance. As long as the requests were coming in, all was good.

But nowadays, databases are built as clusters with nodes interconnected over a network. It is not always a fast, local network. With businesses reaching global scale, database infrastructure has also to span across the globe, to stay close to customers and to reduce latency. It comes with additional challenges that we have to face when designing a highly available database environment. In this blog post, we will look into the network issues that you may face and provide some suggestions on how to deal with them.

Two Main Options for MySQL or MariaDB HA

We covered this particular topic quite extensively in one of the whitepapers, but let’s look at the two main ways of building high availability for MySQL and MariaDB.

Galera Cluster

Galera Cluster is shared-nothing, virtually synchronous cluster technology for MySQL. It allows to build multi-writer setups that can span across the globe. Galera thrives in low-latency environments but it can also be configured to work with long WAN connections. Galera has a built-in quorum mechanism which ensures that data will not be compromised in case of the network partitioning of some of the nodes.

MySQL Replication

MySQL Replication can be either asynchronous or semi-synchronous. Both are designed to build large scale replication clusters. Like in any other master-slave or primary-secondary replication setup, there can be only one writer, the master. Other nodes, slaves, are used for failover purposes as they contain the copy of the data set from the maser. Slaves can also be used for reading the data and offloading some of the workload from the master.

Both solutions have their own limits and features, both suffer from different problems. Both can be affected by unstable network connections. Let’s take a look at those limitations and how we can design the environment to minimize the impact of an unstable network infrastructure.

Galera Cluster - Network Problems

First, let’s take a look at Galera Cluster. As we discussed, it works best in a low-latency environment. One of the main latency-related problems in Galera is the way how Galera handles the writes. We will not go into all the details in this blog, but further reading in our Galera Cluster for MySQL tutorial. The bottom line is that, due to the certification process for writes, where all nodes in the cluster have to agree on whether the write can be applied or not, your write performance for single row is strictly limited by the network roundtrip time between the writer node and the most far away node. As long as the latency is acceptable and as long as you do not have too many hot spots in your data, WAN setups may work just fine. The problem starts when the network latency spikes from time to time. Writes will then take 3 or 4 times longer than usual and, as a result, databases may start to be overloaded with long-running writes.

One of great features of Galera Cluster is its ability to detect the cluster state and react upon network partitioning. If a node of the cluster cannot be reached, it will be evicted from the cluster and it will not be able to perform any writes. This is crucial in maintaining the integrity of the data during the time when the cluster is split - only the majority of the cluster will accept writes. Minority will complain. To handle this, Galera introduces a vast array of checks and configurable timeouts to avoid false alerts on very transient network issues. Unfortunately, if the network is unreliable, Galera Cluster will not be able to work correctly - nodes will start to leave the cluster, join it later. It will be especially problematic when we have Galera Cluster spanning across WAN - separated pieces of the cluster may disappear randomly if the interconnecting network will not work properly.

How to Design Galera Cluster for an Unstable Network?

First things first, if you have network problems within the single datacenter, there is not much you can do unless you will be able to solve those issues somehow. Unreliable local network is a no go for Galera Cluster, you have to reconsider using some other solution (even though, to be honest, unreliable network will always be a problematic). On the other hand, if the problems are related to WAN connections only (and this is one of the most typical cases), it may be possible to replace WAN Galera links with regular asynchronous replication (if the Galera WAN tuning did not help).

There are several inherent limitations in this setup - the main issue is that the writes used to happen locally. Now, all the writes will have to head to the “master” datacenter (DC A in our case). This is not as bad as it sounds. Please keep in mind that in an all-Galera environment, writes will be slowed down by the latency between nodes located in different datacenters. Even local writes will be affected. It will be more or less the same slowdown as with asynchronous setup in which you would send the writes across WAN to the “master” datacenter.

Using asynchronous replication comes with all of the problems typical for the asynchronous replication. Replication lag may become a problem - not that Galera would be more performant, it’s just that Galera would slow down the traffic via flow control while replication does not have any mechanism to throttle the traffic on the master.

Another problem is the failover: if the “master” Galera node (the one which acts as the master to the slaves in other datacenters) would fail, some mechanism has to be created to repoint slaves to another, working master node. It might be some sort of a script, it is also possible to try something with VIP where the “slave” Galera cluster slaves off Virtual IP which is always assigned to the alive Galera node in the “master” cluster.

The main advantage of such setup is that we do remove the WAN Galera link which means that our “master” cluster will not be slowed down by the fact that some of the nodes are separated geographically. As we mentioned, we lose the ability to write in all of the data-centers but latency-wise writing across the WAN is the same as writing locally to the Galera cluster which spans across WAN. As a result the overall latency should improve. Asynchronous replication is also less vulnerable to the unstable networks. Worst case scenario, the replication link will break and it will be recreated when the networks converge.

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE How to Design MySQL Replication for an Unstable Network?

In the previous section, we covered Galera cluster and one solution was to use asynchronous replication. How does it look like in a plain asynchronous replication setup? Let’s look at how an unstable network can cause the biggest disruptions in the replication setup.

First of all, latency - one of the main pain points for Galera Cluster. In case of replication, it is almost a non-issue. Unless you use semi-synchronous replication that is - in such case, increased latency will slow down writes. In asynchronous replication, latency has no impact on the write performance. It may, though, have some impact on the replication lag. It is not anything as significant as it was for Galera but you may expect more lag spikes and overall less stable replication performance if the network between nodes suffers from high latency. This is mostly due to the fact that the master may as well serve several writes before data transfer to the slave can be initiated on high latency network.

The network instability may definitely impact replication links but it is, again, not that critical. MySQL slaves will attempt to reconnect to their masters and replication will commence.

The main issue with MySQL replication is actually something that Galera Cluster solves internally - network partitioning. We are talking about the network partitioning as the condition in which segments of the network are separated from each other. MySQL replication utilizes one single writer node - master. No matter how you design your environment, you have to send your writes to the master. If the master is not available (for whatever reasons), application cannot do its job unless it runs in some sort of read-only mode. Therefore there is a need to pick the new master as soon as possible. This is where the issues show up.

First, how to tell which host is a master and which one is not. One of the usual ways is to use the “read_only” variable to distinguish slaves from the master. If node has read_only enabled (set read_only=1), it is a slave (as slaves should not handle any direct writes). If the node has read_only disabled (set read_only=0), it is a master. To make things safer, a common approach is to set read_only=1 in MySQL configuration - in case of a restart, it is safer if the node shows up as a slave. Such “language” can be understood by proxies like ProxySQL or MaxScale.

Let’s take a look at an example.

We have application hosts which connect to the proxy layer. Proxies perform the read/write split sending SELECTs to slaves and writes to master. If master is down, failover is performed, new master is promoted, proxy layer detects that and start sending writes to another node.

If node1 restarts, it will come up with read_only=1 and it will be detected as a slave. It is not ideal as it is not replicating but it is acceptable. Ideally, the old master should not show up at all until it is rebuilt and slaved off the new master.

Way more problematic situation is if we have to deal with network partitioning. Let’s consider the same setup: application tier, proxy tier and databases.

When the network makes the master not reachable, the application is not usable as no writes make it to their destination. New master is promoted, writes are redirected to it. What will happen then if the network issues cease and the old master becomes reachable? It has not been stopped, therefore it is still using read_only=0:

You’ve now ended up in a split brain, when writes were directed to two nodes. This situation is pretty bad as to merge diverged datasets may take a while and it is quite a complex process.

What can be done to avoid this problem? There is no silver bullet but some actions can be taken to minimize the probability of a split brain to happen.

First of all, you can be smarter in detecting the state of the master. How do the slaves see it? Can they replicate from it? Maybe some of the slaves still can connect to the master, meaning that the master is up and running or, at least, making it possible to stop it should that be necessary. What about the proxy layer? Do all of the proxy nodes see the master as unavailable? If some can still connect, than you can try to utilize those nodes to ssh into the master and stop it before the failover?

Related resources  MySQL Performance Benchmarking: MySQL 5.7 vs MySQL 8.0  MySQL Performance Cheat Sheet  How to Improve Replication Performance in a MySQL or MariaDB Galera Cluster

The failover management software can also be smarter in detecting the state of the network. Maybe it utilizes RAFT or some other clustering protocol to build a quorum-aware cluster. If a failover management software can detect the split brain, it can also take some actions based on this like, for example, setting all nodes in the partitioned segment to read_only ensuring that the old master will not show up as writable when the networks converge.

You can also include tools like Consul or Etcd to store the state of the cluster. The proxy layer can be configured to use data from Consul, not the state of the read_only variable. It will be then up to the failover management software to make necessary changes in Consul so that all proxies will send the traffic to a correct, new master.

Some of those hints can even be combined together to make the failure detection even more reliable. All in all, it is possible to minimize the chances that the replication cluster will suffer from unreliable networks.

As you can see, no matter if we are talking about Galera or MySQL Replication, unstable networks may become a serious problem. On the other hand, if you design the environment correctly, you can still make it work. We hope this blog post will help you to create environments which will work stable even if the networks are not.

Tags:  MySQL MariaDB high availability ha network
Categories: Web Technologies

Upcoming Webinar Thurs 3/7: Enhancing MySQL Security

Planet MySQL - Tue, 03/05/2019 - 13:57

Join Percona Support Engineer, Vinicius Grippa, as he presents his talk Enhancing MySQL Security on Thursday, March 7th, 2019 at 7:00 AM PST (UTC-8) / 10:00 AM EST (UTC-5).

Register Now

Security is always a challenge when it comes to data. What’s more, regulations like GDPR add a whole new layer on top of it, with rules more and more restrictive to access and manipulate data. Join us in this presentation to check security best practices, as well as traditional and new features available for MySQL including features coming with the new MySQL 8.

In this talk, DBA’s and sysadmins will walk through the security features available on the OS and MySQL. For instance, these features include:

– SO security
– Audit Plugin
– MySQL 8 features (undo, redo and binlog encryption)
– New caching_sha2_password
– Roles
– Password Management
– FIPS mode

In order to learn more register for this webinar on Enhancing MySQL Security.

Categories: Web Technologies

How to Upgrade Amazon Aurora MySQL from 5.6 to 5.7

Planet MySQL - Tue, 03/05/2019 - 09:31

Over time, software evolves and it is important to stay up to date if you want to benefit from new features and performance improvements.  Database engines follow the exact same logic and providers are always careful to provide an easy upgrade path. With MySQL, the mysql_upgrade tool serves that purpose.

A database upgrade process becomes more challenging in a managed environment like AWS RDS where you don’t have shell access to the database host and don’t have access to the SUPER MySQL privilege. This post is a collaboration between Fattmerchant and Percona following an engagement focused on the upgrade of the Fattmerchant database from Amazon Aurora MySQL 5.6 to Amazon Aurora MySQL 5.7. Jacques Fu, the CTO of Fattmerchant, is the co-author of this post.  Our initial plan was to follow a path laid out previously by others but we had difficulties finding any complete and detailed procedure outlining the steps. At least, with this post, there is now one.

Issues with the regular upgrade procedure

How do we normally upgrade a busy production server with minimal downtime?  The simplest solution is to use a slave server with the newer version. Such a procedure has the side benefit of providing a “staging” database server which can be used to test the application with the new version. Basically we need to follow these steps:

  1. Enable replication on the old server
  2. Make a consistent backup
  3. Restore the backup on a second server with the newer database version – it can be a temporary server
  4. Run mysql_upgrade if needed
  5. Configure replication with the old server
  6. Test the application against the new version. If the tests includes conflicting writes, you may have to jump back to step 3
  7. If tests are OK and the new server is in sync, replication wise, with the old server, stop the application (only for a short while)
  8. Repoint the application to the new server
  9. Reset the slave
  10. Start the application

If the new server was temporary, you’ll need to repeat most of the steps the other way around, this time starting from the new server and ending on the old one.

What we thought would be a simple task turned out to be much more complicated. We were preparing to upgrade our database from Amazon Aurora MySQL 5.6 to 5.7 when we discovered that there was no option for an in-place upgrade. Unlike a standard AWS RDS MySQL (RDS MySQL upgrade 5.6 to 5.7) at the time of this article you cannot perform an in-place upgrade or even restore a backup across the major versions of Amazon Aurora MySQL.

We initially chose Amazon Aurora for the benefits of the tuning work that AWS provided out of the box, but we realized with any set of pros there comes a list of cons. In this case, the limitations meant that something that should have been straightforward took us off the documented path.

Our original high-level plan

Since we couldn’t use an RDS snapshot to provision a new Amazon Aurora MySQL 5.7 instance, we had to fallback to the use of a logical backup. The intended steps were:

  1. Backup the Amazon Aurora MySQL 5.6 write node with mysqldump
  2. Spin up an empty Amazon Aurora MySQL 5.7 cluster
  3. Restore the backup
  4. Make the Amazon Aurora MySQL 5.7 write node a slave of the Amazon Aurora MySQL 5.6 write node
  5. Once in sync, transfer the application to the Amazon Aurora MySQL 5.7 cluster

Even those simple steps proved to be challenging.

Backup of the Amazon Aurora MySQL 5.6 cluster

First, the Amazon Aurora MySQL 5.6 write node must generate binary log files. The default cluster parameter group that is generated when creating an Amazon Aurora instance does not enable these settings. Our 5.6 write node was not generating binary log files, so we copied the default cluster parameter group to a new “replication” parameter group and changed the “binlog_format” variable to MIXED.  The parameter is only effective after a reboot, so overnight we rebooted the node. That was a first short downtime.

At that point, we were able to confirm, using “show master status;” that the write node was indeed generating binlog files.  Since our procedure involves a logical backup and restore, we had to make sure the binary log files are kept for a long enough time. With a regular MySQL server the variable “expire_logs_days” controls the binary log files retention time. With RDS, you have to use the mysql.rds_set_configuration. We set the retention time to two weeks:

CALL mysql.rds_set_configuration('binlog retention hours', 336);

You can confirm the new setting is used with:

CALL mysql.rds_show_configuration;

For the following step, we needed a mysqldump backup along with its consistent replication coordinates. The option

--master-data   of mysqldump implies “Flush table with read lock;” while the replication coordinates are read from the server.  A “Flush table” requires the SUPER privilege and this privilege is not available in RDS.

Since we wanted to avoid downtime, it is out of question to pause the application for the time it would take to backup 100GB of data. The solution was to take a snapshot and use it to provision a temporary Amazon Aurora MySQL 5.6 cluster of one node. As part of the creation process, the events tab of the AWS console will show the binary log file and position consistent with the snapshot, it looks like this:

Consistent snapshot replication coordinates

From there, the temporary cluster is idle so it is easy to back it up with mysqldump. Since our dataset is large we considered the use of MyDumper but the added complexity was not worthwhile for a one time operation. The dump of a large database can take many hours. Essentially we performed:

mysqldump -h entrypoint-temporary-cluster -u awsrootuser -pxxxx \  --no-data --single-transaction -R -E -B db1 db2 db3 > schema.sql mysqldump -h entrypoint-temporary-cluster -nt --single-transaction \  -u awsrootuser -pxxxx -B db1 db2 db3 | gzip -1 > dump.sql.gz pt-show-grants -h entrypoint-temporary-cluster -u awsrootuser -pxxxx > grants.sql

The schema consist of three databases: db1, db2 and db3.  We have not included the mysql schema because it will cause issues with the new 5.7 instance. You’ll see why we dumped the schema and the data separately in the next section.

Restore to an empty Amazon Aurora MySQL 5.7 cluster

With our backup done, we are ready to spin up a brand new Amazon Aurora MySQL 5.7 cluster and restore the backup. Make sure the new Amazon Aurora MySQL 5.7 cluster is in a subnet with access to the Amazon Aurora MySQL 5.6 production cluster. In our schema, there a few very large tables with a significant number of secondary keys. To speed up the restore, we removed the secondary indexes of these tables from the schema.sql file and created a restore-indexes.sql file with the list of alter table statements needed to recreate them. Then we restored the data using these steps:

cat grants.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx cat schema-modified.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx zcat dump.sql.gz | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx cat restore-indexes.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx

Configure replication

At this point, we have a new Amazon Aurora MySQL 5.7 cluster provisioned with a dataset at a known replication coordinates from the Amazon Aurora MySQL 5.6 production cluster.  It is now very easy to setup replication. First we need to create a replication user in the Amazon Aurora MySQL 5.6 production cluster:

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' identified by 'agoodpassword';

Then, in the new Amazon Aurora MySQL 5.7 cluster, you configure replication and start it by:

CALL mysql.rds_set_external_master ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306, 'repl_user', 'agoodpassword', 'mysql-bin-changelog.000018', 65932380, 0); CALL mysql.rds_start_replication;

The endpoint mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com points to the Amazon Aurora MySQL 5.6 production cluster.

Now, if everything went well, the new Amazon Aurora MySQL 5.7 cluster will be actively syncing with its master, the current Amazon Aurora MySQL 5.6 production cluster. This process can take a significant amount of time depending on the write load and the type of instance used for the new cluster. You can monitor the progress with the show slave status\G command, the Seconds_Behind_Master will tell you how far behind in seconds the new cluster is compared to the old one.  It is not a measurement of how long it will take to resync.

You can also monitor throughput using the AWS console. In this screenshot you can see the replication speeding up over time before it peaks when it is completed.

Replication speed

Test with Amazon Aurora MySQL 5.7

At this point, we have an Amazon Aurora MySQL 5.7 cluster in sync with the production Amazon Aurora MySQL 5.6 cluster. Before transferring the production load to the new cluster, you need to test your application with MySQL 5.7. The easiest way is to snapshot the new Amazon Aurora MySQL 5.7 cluster and, using the snapshot, provision a staging Amazon Aurora MySQL 5.7 cluster. Test your application against the staging cluster and, once tested, destroy the staging cluster and any unneeded snapshots.

Switch production to the Amazon Aurora MySQL 5.7 cluster

Now that you have tested your application with the staging cluster and are satisfied how it behaves with Amazon Aurora MySQL 5.7, the very last step is to migrate the production load. Here are the last steps you need to follow:

  1. Make sure the Amazon Aurora MySQL 5.7 cluster is still in sync with the Amazon Aurora MySQL 5.6 cluster
  2. Stop the application
  3. Validate the Show master status; of the 5.6 cluster is no longer moving
  4. Validate from the Show slave status\G in the 5.7 cluster the Master_Log_File and Exec_Master_Log_Pos match the output of the “Show master status;” from the 5.6 cluster
  5. Stop the slave in the 5.7 cluster with CALL mysql.rds_stop_replication;
  6. Reset the slave in the 5.7 cluster with CALL mysql.rds_reset_external_master;
  7. Reconfigure the application to use the 5.7 cluster endpoint
  8. Start the application

The application is down from steps 2 to 8.  Although that might appear to be a long time, these steps can easily be executed within a few minutes.


So, in summary, although RDS Aurora doesn’t support an in place upgrade between Amazon Aurora MySQL 5.6 and 5.7, there is a possible migration path, minimizing downtime.  In our case, we were able to limit the downtime to only a few minutes.

Co-Author: Jacques Fu, Fattmerchant


Jacques is CTO and co-founder at the fintech startup Fattmerchant, author of Time Hacks, and co-founder of the Orlando Devs, the largest developer meetup in Orlando. He has a passion for building products, bringing them to market, and scaling them.

Categories: Web Technologies

The Bottleneck of the Web

CSS-Tricks - Tue, 03/05/2019 - 07:37

Steve Souders, "JavaScript Dominates Browser CPU":

Ten years ago the network was the main bottleneck. Today, the main bottleneck is JavaScript. The amount of JavaScript on pages is growing rapidly (nearly 5x in the last 7 years). In order to keep pages rendering and feeling fast, we need to focus on JavaScript CPU time to reduce blocking the browser main thread.

Alex Russell, describing a prototype of "Never-Slow Mode" in Chrome:

... blocks large scripts, sets budgets for certain resource types (script, font, css, images), turns off document.write(), clobbers sync XHR, enables client-hints pervasively, and buffers resources without Content-Length set.

Craig Hockenberry, posting an idea to the WebKit bug tracker:

Without limits, there is no incentive for a JavaScript developer to keep their codebase small and dependencies minimal. It's easy to add another framework, and that framework adds another framework, and the next thing you know you're loading tens of megabytes of data just to display a couple hundred kilobytes of content. ...

The situation I'm envisioning is that a site can show me any advertising they want as long as they keep the overall size under a fixed amount, say one megabyte per page. If they work hard to make their site efficient, I'm happy to provide my eyeballs.

It's easy to point a finger at frameworks and third-party scripts for large amounts of JavaScript. If you're interested in hearing more about the size of frameworks, you might enjoy me and Dave discussing it with Jason Miller.

And speaking of third-parties, Patrick Hulce created Third Party Web: "This document is a summary of which third-party scripts are most responsible for excessive JavaScript execution on the web today."

Sometimes name-and-shame is an effective tactic to spark change.

Addy Osmani writes about an ESLint rule that prohibits particular packages, of which you could use to prevent usage of known-to-be-huge packages. So if someone tries to load the entirety of lodash or moment.js, it can be stopped at the linting level.

Tim Kadlec ties the threads together very well in "Limiting JavaScript?" If your gut reaction on this is that JavaScript is being unfairly targeted as a villain, Tim acknowledges that:

One common worry I saw voiced was “if JavaScript, why not other resources too?”. It’s true; JavaScript does get picked on a lot though it’s not without reason. Byte for byte, JavaScript is the most significant detriment to performance on the web, so it does make sense to put some focus on reducing the amount we use.

However, the point is valid. JavaScript may be the biggest culprit more often than not, but it’s not the only one.

The post The Bottleneck of the Web appeared first on CSS-Tricks.

Categories: Web Technologies

414 URI Too Long - Evert Pot

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

The URI or path of a HTTP request doesn’t have any hard limits in terms of how long it’s allowed to be.

However, Browsers and search engines have limits, and on the server side it’s a good idea to limit the length of the URI to combat certain denial-of-service attacks or bugs.

Based on limits of browsers, it’s a good idea to try and not exceed 2000 bytes for the uri.

When a client does exceed it, the appropriate status code to return is 414 URI Too Long.

Example HTTP/1.1 414 URI Too Long Content-Type: text/html <p>Insufficient level of conciseness in request</p> References
Categories: Web Technologies

Un-split brain MySQL via gh-mysql-rewind

Planet MySQL - Tue, 03/05/2019 - 05:51

We are pleased to release gh-mysql-rewind, a tool that allows us to move MySQL back in time, automatically identify and rewind split brain changes, restoring a split brain server into a healthy replication chain.

I recently had the pleasure of presenting gh-mysql-rewind at FOSDEM. Video and slides are available. Consider following along with the video.


Consider a split brain scenario: a "standard" MySQL replication topology suffered network isolation, and one of the replicas was promoted as new master. Meanwhile, the old master was still receiving writes from co-located apps.

Once the network isolation is over, we have a new master and an old master, and a split-brain situation: some writes only took place on one master; others only took place on the other. What if we wanted to converge the two? What paths do we have to, say, restore the old, demoted master, as a replica of the newly promoted master?

The old master is unlikely to agree to replicate from the new master. Changes have been made. AUTO_INCREMENT values have been taken. UNIQUE constraints will fail.

A few months ago, we at GitHub had exactly this scenario. An entire data center went network isolated. Automation failed over to a 2nd DC. Masters in the isolated DC meanwhile kept receiving writes. At the end of the failover we ended up with a split brain scenario - which we expected. However, an additional, unexpected constraint forced us to fail back to the original DC.

We had to make a choice: we've already operated for a long time in the 2nd DC and took many writes, that we were unwilling to lose. We were OK to lose (after auditing) the few seconds of writes on the isolated DC. But, how do we converge the data?

Backups are the trivial way out, but they incur long recovery time. Shipping backup data over the network for dozens of servers takes time. Restore time, catching up with changes since backup took place, warming up the servers so that they can handle production traffic, all take time.

Could we have reduces the time for recovery?

There are multiple ways to do that: local backups, local delayed replicas, snapshots... We have embarked on several. In this post I wish to outline gh-mysql-rewind, which programmatically identifies the rogue (aka "bad") transactions on the network isolated master, rewinds/reverts them, applies some bookkeeping and restores the demoted master as a healthy replica under the newly promoted master, thereby prepared to be promoted if needed.

General overview

gh-mysql-rewind is a shell script. It utilizes multiple technologies, some of which do not speak to each other, to be able to do the magic. It assumes and utilizes the following:

Some breakdown follows.


MySQL GTIDs keep track of all transactions executed on a given server. GTIDs indicate which server (UUID) originated a write, and ranges of transaction sequences. In a clean state, only one writer will generate GTIDs, and on all the replicas we would see the same GTID set, originated with the writer's UUID.

In a split brain scenario, we would see divergence. It is possible to use GTID_SUBTRACT(old_master-GTIDs, new-master-GTIDs) to identify the exact set of transactions executed on the old, demoted master, right after the failover. This is the essence of the split brain.

For example, assume that just before the network partition, GTID on the master was 00020192-1111-1111-1111-111111111111:1-5000. Assume after the network partition the new master has UUID of 00020193-2222-2222-2222-222222222222. It began to take writes, and after some time its GTID set showed 00020192-1111-1111-1111-111111111111:1-5000,00020193-2222-2222-2222-222222222222:1-200.

On the demoted master, other writes took place, leading to the GTID set 00020192-1111-1111-1111-111111111111:1-5042.

We will run...

SELECT GTID_SUBTRACT( '00020192-1111-1111-1111-111111111111:1-5042', '00020192-1111-1111-1111-111111111111:1-5000,00020193-2222-2222-2222-222222222222:1-200' ); > '00020192-1111-1111-1111-111111111111:5001-5042'

...to identify the exact set of "bad transactions" on the demoted master.

Row Based Replication

With row based replication, and with FULL image format, each DML (INSERT, UPDATE, DELETE) writes to the binary log the complete row data before and after the operation. This means the binary log has enough information for us to revert the operation.


Developed by Alibaba, flashback has been incorporated in MariaDB. MariaDB's mysqlbinlog utility supports a --flashback flag, which interprets the binary log in a special way. Instead of printing out the events in the binary log in order, it prints the inverted operations in reverse order.

To illustrate, let's assume this pseudo-code sequence of events in the binary log:

insert(1, 'a') insert(2, 'b') insert(3, 'c') update(2, 'b')->(2, 'second') update(3, 'c')->(3, 'third') insert(4, 'd') delete(1, 'a')

A --flashback of this binary log would produce:

insert(1, 'a') delete(4, 'd') update(3, 'third')->(3, 'c') update(2, 'second')->(2, 'b') delete(3, 'c') delete(2, 'b') delete(1, 'a')

Alas, MariaDB and flashback do not speak MySQL GTID language. GTIDs are one of the major points where MySQL and MariaDB have diverged beyond compatibility.

The output of MariaDB's mysqlbinlog --flashback has neither any mention of GTIDs, nor does the tool take notice of GTIDs in the binary logs in the first place.


This is where we step in. GTIDs provide the information about what went wrong. flashback has the mechanism to generate the reverse sequence of statements. gh-mysql-rewind:

  • uses GTIDs to detect what went wrong
  • correlates those GTID entries with binary log files: identifies which binary logs actually contain those GTID events
  • invokes MariaDB's mysqlbinlog --flashback to generate the reverse of those binary logs
  • injects (dummy) GTID information into the output
  • computes ETA

This last part is worth elaborating. We have created a time machine. We have the mechanics to make it work. But as any Sci-Fi fan knows, one of the most important parts of time travel is knowing ahead where (when) you are going to land. Are you back in the Renaissance? Or are you suddenly to appear on board the French Revolution? Better dress accordingly.

In our scenario it is not enough to move MySQL back in time to some consistent state. We want to know at what time we landed, so that we can instruct the rewinded server to join the replication chain as a healthy replica. In MySQL terms, we need to make MySQL "forget" everything that ever happened after the split brain: not only in terms of data (which we already did), but in terms of GTID history.

gh-mysql-rewind will do the math to project, ahead of time, at what "time" (i.e. GTID set) our time machine arrived. It will issue a `RESET MASTER; SET GLOBAL gtid_purged='gtid-of-the-landing-time'" to make our re-winded MySQL consistent not only with some past dataset, but also with its own perception of the point in time where that dataset existed.


Some limitations are due to MariaDB's incompatibility with MySQL, some are due to MySQL DDL nature, some due to the fact gh-mysql-rewind is a shell script.

  • Cannot rewind DDL. DDLs are silently ignored, and will impose a problem when trying to re-apply them.
  • JSON, POINT data types are not supported.
  • The logic rewinds the MySQL server farther into the past than strictly required. This simplifies the code considerably, but imposed superfluous time to rewind+reapply, i.e. time to recover.
  • Currently, this only works one server at a time. If a group of 10 servers were network isolated together, the operation would need to run on each of these 10 servers.
  • Runs locally on each server. Requires both MySQL's mysqlbinlog as well as MariaDB's mysqlbinlog.

There's lot of moving parts to this mechanism. A mixture of technologies that don't normally speak to each other, injection of data, prediction of ETA... How reliable is all this?

We run continuous gh-mysql-rewind testing in production to consistently prove that it works as expected. Our testing uses a non-production, dedicated, functional replica. It contaminates the data on the replica. It lets gh-mysql-rewind automatically move it back in time, it joins the replica back into the healthy chain.

That's not enough. We actually create a scenario where we can predict, ahead of testing, what the time-of-arrival will be. We checksum the data on that replica at that time. After contaminating and effectively breaking replication, we expect gh-mysql-rewind to revert the changes back to our predicted point in time. We checksum the data again. We expect 100% match.

See the video or slides for more detail on our testing setup.


At this time the tool in one of several solutions we hope to never need to employ. It is stable and tested. We are looking forward to a promising MySQL development that will provide GTID-revert capabilities using standard commands, such as SELECT undo_transaction('00020192-1111-1111-1111-111111111111:5042').

We have released gh-mysql-rewind as open source, under the MIT license. The public release is a stripped down version of our own script, which has some GitHub-specific integration. We have general ideas in incorporating this functionality into higher level tools.

gh-mysql-rewind is developed by the database-infrastructure team at GitHub.

Categories: Web Technologies

Improvements to ROLLUP in MySQL

Planet MySQL - Tue, 03/05/2019 - 03:45

ROLLUP has been supported in MySQL for sometime now. But until now its use has come with two implementation restrictions: it cannot be combined with DISTINCT or ORDER BY in the same query expression. Starting in 8.0.12, these limitations have been lifted.…

Facebook Twitter Google+ LinkedIn
Categories: Web Technologies

Improvements to ROLLUP in MySQL

MySQL Server Blog - Tue, 03/05/2019 - 03:45

ROLLUP has been supported in MySQL for sometime now. But until now its use has come with two implementation restrictions: it cannot be combined with DISTINCT or ORDER BY in the same query expression. Starting in 8.0.12, these limitations have been lifted.…

Facebook Twitter Google+ LinkedIn
Categories: Web Technologies

Southern California Linux Expo (ScaLE) with MySQL, Mar 7-10, 2019

Planet MySQL - Tue, 03/05/2019 - 03:05

This week the Southern California Linux Expo 2019 (ScaLE) will be hold in Pasadena, US where you can find MySQL & Oracle Cloud representatives. Please find details about our presence below:

We are happy to invite you for stopping by at our shared booth and attend talks, we are looking forward to talking to you @ScaLE!

Categories: Web Technologies

NULL comparisons in MySQL, PostgreSQL, and SQLite

Planet MySQL - Mon, 03/04/2019 - 23:14

Any comparison involving at least one NULL operand will return NULL. However, SQL supports operators to easily write NULL-safe comparisons.

The post NULL comparisons in MySQL, PostgreSQL, and SQLite appeared first on Federico Razzoli.

Categories: Web Technologies

Croissants in Québec - thePHP.cc

Planet PHP - Mon, 03/04/2019 - 23:00
Categories: Web Technologies