Recently I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of applications.

So first of all, if you are using an old connector or a connector (like the one for Go) not yet supporting caching_sha2_passwordas authentication plugin, you are still able to use the oldone. If you have created a new user for your application not supporting the new authentication method, you just have to run the following command (please use the right user account):

ALTER USER 'username'@'hostname' IDENTIFIED WITH 'mysql_native_password' BY 'password';

Let’s got back to the blog post now.

Situation

The exercise of this blog consists in the migration of the MySQL server 5.5.59 used by Druapl 6.2  to MySQL 8.0 without migrating to the latest Drupal version.

This is what we have now:

So far so good

MySQL Upgrade

In the MySQL Manual, we propose 2 different strategies:

• Logical Upgrade
• In-Place Upgrade

The logical method consists of making a logical dump and I restore it, I won’t cover it here. The in-place method is as far as I know the most common one. However, there is something very important that people tend to forget: “Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.”

So know that this is clarified again, let’s continue with our plan.

As we are using 5.5.59, the latest 5.5 version, we don’t need to upgrade the binaries to the latest 5.5, if we would use a older version of 5.5, I would have recommended to upgrade first to the latest version of the same major version too. Our first step is then to put our site in maintenance and then upgrade to the latest 5.6.

MySQL 5.6

# yum update --enablerepo=mysql56-community --disablerepo=mysql57-community mysql-community-server

...

Updated:

  mysql-community-server.x86_64 0:5.6.39-2.el7                                                                                                         

Dependency Updated:

  mysql-community-client.x86_64 0:5.6.39-2.el7      

  mysql-community-common.x86_64 0:5.6.39-2.el7      

  mysql-community-libs.x86_64 0:5.6.39-2.el7     

Complete!

Perfect, let’s run the mandatory mysql_upgrade command:

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

Running 'mysqlcheck with default connection arguments

Running 'mysqlcheck with default connection arguments

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

...

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

Running 'mysql_fix_privilege_tables'...

Running 'mysqlcheck with default connection arguments

Running 'mysqlcheck with default connection arguments

drupal.access                                      OK

drupal.actions                                     OK

drupal.actions_aid                                 OK

...

drupal.watchdog                                    OK

OK

We are good, let’s put back the site online and check the home page again:

MySQL 5.7

OK, let’s move on and upgrade to the latest 5.7:

# yum upgrade  mysql-community-server

Dependency Installed:

  mysql-community-libs-compat.x86_64 0:5.7.21-1.el7                                                                                                    

Updated:

  mysql-community-server.x86_64 0:5.7.21-1.el7                                                                                                         

Dependency Updated:

  mysql-community-client.x86_64 0:5.7.21-1.el7      

  mysql-community-common.x86_64 0:5.7.21-1.el7      

  mysql-community-libs.x86_64 0:5.7.21-1.el7     

Complete!

# mysql_upgrade 

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost                                  OK

...

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

Upgrading the sys schema.

Checking databases.

drupal.access                                      OK

drupal.actions                                     OK

...

sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.

Once again, nothing wrong here, let’s check the website:

MySQL 8.0

It’s time now to upgrade to MySQL 8.0 !

Let’s perform like we did for the previous version:

# yum update --enablerepo=mysql80-community --disablerepo=mysql57-community mysql-community-server

Updated:

  mysql-community-server.x86_64 0:8.0.4-0.1.rc.el7                                                                                                     

Dependency Updated:

  mysql-community-client.x86_64 0:8.0.4-0.1.rc.el7  

  mysql-community-common.x86_64 0:8.0.4-0.1.rc.el7  

  mysql-community-libs.x86_64 0:8.0.4-0.1.rc.el7 

Complete!

[root@mysql1 drupal-6.2]# mysql_upgrade 

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Upgrading system table data.

Checking system database.

mysql.columns_priv                                 OK

mysql.component                                    OK

mysql.db                                           OK

mysql.default_roles                                OK

...

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

Found outdated sys schema version 1.5.1.

Upgrading the sys schema.

Checking databases.

drupal.access                                      OK

drupal.actions                                     OK

...

drupal.watchdog                                    OK

sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.

Again, no problem here ! Let’s see the website:

OH! We have a problem it seems… Did my user’s authentication method changed and my old PHP connector doesn’t support it ?

Let’s verify:

mysql> select Host, User, plugin from mysql.user where User like 'drup%';

+------+---------+-----------------------+

| Host | User    | plugin                |

+------+---------+-----------------------+

| %    | drupal  | mysql_native_password |

+------+---------+-----------------------+

1 rows in set (0.00 sec)

So that’s not the problem. As I said before, users authentication method is not changed. So this new default doesn’t break old applications…. but my site is still not working…

What’s wrong then ?

In fact, this old Drupal, uses a table name that is now part of the reserved keywords. It’s always advised to verify what are the new keywords reserved for MySQL itself. New features can also mean new keywords sometimes.

I searched in the code and I replaced all the calls to system table by `system` and now the result:

Conclusion

If you are using an old application, no the new authentication plugin doesn’t break your application, until you don’t create a new user for it and not specify an authentication method compatible with your connector. But of course other things, like reserved keywords in this case, can be problematic. This is why an major release upgrade always need to be tested in advance. Not only for schema and syntax compatibility but also for performance as the query execution plan might not be the one you expect event if in most cases the MySQL Optimizer becomes smarter and smarter with the releases and has the support of new features like the histograms.

And don’t forget that the new MySQL Shell includes a new utility checking your current environment to identify possible issues like the one covered in this article.

via Planet MySQL
Migrating to MySQL 8.0 without breaking old application

MySQL Package Management Options In this blog we will explore some interesting ways to install MySQL Community and Enterprise Edition binaries using your associated Linux package manager.  In this case we’ll look mostly at the Yum package manager on Oracle Linux.  The benefit of these package managers is that you can install software packages easily,… Read More »
via Planet MySQL
MySQL Yum repo setups for commercial and community use cases

via TechCrunch
Watch Thanos explain his simple plan in new ‘Avengers: Infinity War’ trailer

Windowing Functions are new to MySQL with Version 8.  I have been very lucky in the past few days to find two excellent resources that I want to share with you.  Both are worth the time and effort to study.

At the Southern California Linux Expo last week, Bruce Momjian of EnterpriseDB and PostgreSQL fame gave an amazing tutorial on Common Table Expressions and Windowing Functions (slides for both at https://momjian.us/main/presentations/sql.html).  Bruce is an amazing presenter and I highly recommend going to his sessions at conferences. So, what can a MySQL-er learn from a Postrgrestian?

Plenty.

In this case the two databases are using SQL and the features that turn that language from descriptive to imperative. But showing how these features is very hard.  Bruce said it took him six years to finish the Windowing Function presentation.  And the results are impressive.

You will have to make some changes to the presented code as MySQL does not have sequences — create a table named x, with a single int column named x, and fill it with the numbers one through 10 for the first windowing function examples.  The big item to study is the various partitioning terminology which can get confusing but is spelled out explicitly in this tutorial.

The next tutorial is from Alex Yeskov and can be found at https://blog.statsbot.co/sql-window-functions-tutorial-b5075b87d129 and is a great addendum to the other tutorial.  He stresses learning by doing and do he does!!

His examples include calculating revenue growth, running totals, dealing with duplicated data, finding the top N rows, and examining repeat customer purchases. 

Doing these two tutorials will not turn you into a windowing function expert.  But you will have a better understanding of how they work and that will make you a better SQL programmer
via Planet MySQL
Windowing Function Tutorials

The HP Z27n is the best 27-inch monitor for most people because its out-of-the-box picture quality is exemplary, it has a variety of input connections, a USB 3.0 hub, and a thin bezel, and its stand is sturdy and highly adjustable. With amazing accuracy, good pixel response time and a better contrast ratio than anything else we tested, the Z27n edged out the competition for our top pick.

Monitor quality

The Z27n is the most color-accurate display we’ve measured, even better than our previous pick. Its color reproduction and grayscale tracking are virtually perfect thanks to excellent factory calibration; it gets dim enough and bright enough to work in nearly any lighting; and its contrast ratio of over 1,000:1 means dark scenes that contain bright elements won’t look washed out.

To track the accuracy of the monitor’s ability to display grayscale and colors, we look at the DeltaE 2000 numbers provided by the CalMAN 2016 software. These numbers show us how close the displayed color is to what it is supposed to be. A value of under 1.0 is near perfect. A value under 2.0 is sufficient for print production work—it’s difficult to detect a difference even with a perfect reference for comparison. At a value of 3.0, there is a visible difference between the monitor and a reference.

Tracking of the three primary (red, green, and blue) and secondary (cyan, magenta, and yellow) colors give a DeltaE 2000 value of just 0.52. Most colors are below the 1.0 threshold with only blue and green showing slight undersaturation above 96% brightness. In our Colorchecker test, which measures over 100 colors for accuracy, we measured an average DeltaE 2000 of 0.66. This kind of accuracy is unheard-of for a consumer monitor without aftermarket calibration, and it even beats our impressive previous pick, the Dell UltraSharp U2715H.

Our grayscale test measures 256 points from full black (0%) to full white (100%). Across the spectrum, the Z27n has a DeltaE 2000 of just 0.89. The majority of the curve stays under 1.0, just touching 2.0 at around 10 percent brightness, meaning it’s precise enough for print production work even near its dimmest setting. The grayscale tracking also gives us our total gamma number at 2.46. Gamma is the relationship between the brightness of a pixel and a numerical value of voltage assigned to that pixel. It affects brightness, color saturation, and hue. A high gamma causes the picture to look dark and muddy, while low gamma makes the image look washed out. The Z27n is just above our target number of 2.4 (based on the 2.35 to 2.55 gamma range of CRT displays from yesteryear).

A good monitor should have a wide luminance range, so it can work well in dim rooms as well as bright ones. The Z27n’s full-white luminance ranges from 399 cd/m² (also known as nits) at its highest setting to 66 cd/m² at its lowest. The latter isn’t quite as low as we’d like—Chris Heinonen says great monitors should get down to 60 cd/m²—but it’s very close and shouldn’t cause eye fatigue in low lighting. (Our previous pick, the Dell U2715H, got down to 37 cd/m², and our runner-up beats even that at 34.5.) The Z27n’s luminance range means it can work in all but the very brightest and darkest rooms.

Luminance range also affects contrast ratio—the difference in relative brightness between a white (fully lit) screen and a black (unlit) screen at a given backlight level. In a display with a high contrast ratio, the bright parts of an image will pop and seem brilliant next to the darks, while a low contrast ratio will make the image seem flat and dull. The Z27n’s contrast ratio of 1,018:1 is excellent, and it means that white pixels are over a thousand times brighter than black ones at the same backlight level.

Connections and cables

The HP Z27n has an HDMI 1.4 input (which also supports MHL 2.0 for connecting mobile devices), a Mini DisplayPort 1.2 input, a DisplayPort 1.2 input, a dual-link DVI-D input, and a DisplayPort 1.2 output (for daisy-chaining a second monitor on a Windows system). HP includes both a standard DisplayPort cable and a Mini-DisplayPort-to-DisplayPort cable.

The four-port USB 3.0 hub next to the display connectors includes one 1.5 A port for charging devices. The USB ports, including the charging port, work even if the screen is off, as long as your computer is on. This is not true of all monitor USB hubs (like the one on our runner up), so it’s a nice touch.

The Z27n has no built-in speakers, but this isn’t necessarily a disadvantage because display speakers are generally appalling. It does have a 3.5 mm audio-output jack with enough power for HP’s speaker bar accessory, but we recommend good computer speakers if you have room on your desk, and good headphones if not.

Ergonomics and adjustability

If you sit at your computer for hours a day, you need a monitor that can be properly aligned to your sitting position: about arm’s length from your torso, tilted back slightly, with the top of the screen two to three inches above eye level. The Z27n’s stand is excellent, with a 45-degree swivel left and right, a tilt from -5 to 22 degrees from vertical, and 5 inches of play on its height adjustment, giving the top of the screen a maximum height of 20.9 inches from the surface of the desk. The monitor also pivots 90 degrees clockwise or counter-clockwise to switch to portrait mode. The adjustment joints are tight enough that you never have to worry about the monitor moving from its set position. Other monitors we tested had stands that either didn’t have the same number of adjustments, or that would succumb to gravity over time.

The stand also has a quick-release button, so it is easy to remove if you want to VESA-mount the monitor to a wall or a monitor arm.

Design

The side and top bezels of the Z27n are just 9 millimeters thick. These narrow edges make the screen seem larger, and allow for a near-uninterrupted view if you use multiple monitors. The bottom bezel is thicker (18 mm) to accommodate touch-sensitive menu and power buttons. Some people prefer physical buttons, but we like how quickly these touch buttons work. And if they are a sticking point for you, all the settings found in the touch-button menus can be adjusted in the (Windows-only) HP Display assistant software. The app also gives more information about what you’re adjusting than the on-screen display does, and it includes some guidelines for modifying things like brightness and color saturation.

Warranty and support

HP provides a three-year standard limited warranty that covers defects in materials or workmanship. Under this warranty, HP will replace, repair, or refund the cost of the monitor, at the company’s discretion. The company also guarantees zero bright dots on the Z27n. If a sub-pixel (the red, green, or blue portion of a pixel) is stuck in the open position, letting backlight through consistently, it’s covered under the warranty. For dark sub-pixels—stuck blocking the backlight—the number jumps up to four before it’s covered, since black pixels are a lot less noticeable. HP offers two- and three-year next-business-day exchange service, but as a $25-to-$45 add-on package. (These plans are available through HP’s store even if you purchased from another company.) Our runner-up pick from Dell also has a zero-bright-dot policy, but Dell includes a three-year Advanced Exchange Service and will ship a replacement to you the next business day.

via Wirecutter: Reviews for the Real World
The Best 27-Inch Monitor

Two Great No-Spin Knife-Throwing Tutorials

World Champion Adam Celadin and Samoth Chipmah (Tom-Tom) give tips on stabbing wood.

Posted 17 mins ago in Knives, Videos by Russ Chastain with No Comments

There’s something about throwing accurately that fascinates…. especially when the object being thrown is potentially deadly. In this video, Adam Celadin gives instruction on various methods of throwing knives without spinning them… and man, is he good. No wonder he’s a world champion.

He goes over a number of different grips, throwing methods, and stances — and perhaps more importantly, he advises students to just try different ones until they find something that works. And one of the best parts is when he demonstrates the results of releasing a knife too soon or too late. This knowledge should help beginners, because knowing what you’re doing wrong is just about the only way you can improve.

Seems to me, it takes pretty good control for a pro to be able to demonstrate, at will, such mistakes as this.

No-spin is really beautiful and complex technique.

Now I want to go outside and throw sharp stuff!

He includes a link to an hour-long video that goes into much more detail, which I’ll embed down below.

In this next one, the intro is short and to the point (no pun intended,) and he ends it with a casual toss at a target, which of course stabs deeply into the wooden slab. Again, it’s a long one; here’s an “index” from the description:

1:15 Grip
2:35 Slide technic
5:36 Release
8:04 Adjustment
9:51 How to throw
15:36 Adjustment
17:40 Mechanic tips
23:38 One technic for any distance
24:18 Power
26:59 Indexfinger
30:01 Fedin grip
34:47 Finnish grip
40:28 Grips compared
42:25 Mechanics
45:47 Mechanics
51:33 Example throws with Fedin and finnish grip
52:53 Skanf technic
54:00 Knives
58:06 Ending

Enjoy.

via All Outdoor
Two Great No-Spin Knife-Throwing Tutorials

In July of 2017, the nonprofit certificate authority Let’s Encrypt promised to deliver something that would put secure websites and Web applications within reach of any Internet user: free “wildcard” certificates to enable secure HTTP connections for entire domains. Today, Let’s Encrypt took that promised service live, in addition to a new version of the Automated Certificate Management Environment (ACME) protocol, an interface that can be used by a variety of client software packages to automate verification of certificate requests.

ACME version 2 “has gone through the IETF standards process,” said Josh Aas, executive director of the Internet Security Research Group (ISRG), the group behind Let’s Encrypt, in a blog post on the release. ACME v2 is currently a draft Internet Engineering Task Force standard, so it may not yet be in its final form. But the current version is the result of significant feedback from the industry. And its use is required to obtain wildcard certificates.

In addition to the ACME v2 requirement, requests for wildcard certificates require the modification of a Domain Name Service “TXT” record to verify control over the domain—a similar method to that used by Google and other service providers to prove domain ownership. But much of this can be automated by hosting providers that provide DNS services. A single Let’s Encrypt account can request up to 300 wildcard certificates over a period of three hours, allowing a hosting provider to handle requests for customers who may not have shell access to their sites.

Many hosting providers already support the registration of Let’s Encrypt certificates to varying degrees. WordPress.com, Tumblr, and a few other blog platforms already directly support Let’s Encrypt certificate integration for users with custom domains. But Let’s Encrypt’s free certificate offering hasn’t been snapped up by some larger hosting providers—such as GoDaddy—who also sell SSL certificates to their customers.

via Ars Technica
Let’s Encrypt takes free “wildcard” certificates live

GIF

Just when you thought that the fidget spinner fad had gone the way of the dodo bird, hoverboards, and 3D TVs, a Japanese company, MinebeaMitsumi Inc., known for manufacturing electronic components, and Mitsubishi, used 50 engineers to design and build a near-flawless fidget spinner over a six-month period that eventually set a new Guinness World Record.

Spun by hand and balanced on a single finger, it was one of MinebeaMitsumi Inc.’s employees, Takayuki Ishikawa, who set the new fidget spinner world record with an astonishing spin time of 24 minutes and 46.34 seconds. That might not seem impressive when other world records include people juggling for 12 hours straight, but try holding your finger out, perfectly still, for almost half an hour, and you’ll soon understand why it might be a while before this record falls.

[YouTube via The Kid Should See This]

via Gizmodo
50 Engineers Spent Six Months Designing a Fidget Spinner That Set a New World Record

via Ars Technica
Mark Hamill on Skywalker disagreements, fear of starring in a new Star Wars film

The latest release of dbdeployer is possibly the last one with a leading 0. If no serious bugs are found in the next two weeks, the next release will bear a glorious 1.0.

Latest news

The decision to get out of the stream of pre-releases that were published until now comes because I have implemented all the features that I wanted to add: mainly, all the ones that I wished to add to MySQL-Sandbox but it would have been too hard:

• concurrent deployment and deletion
• group replication;
• multi-source replication;
• Full customisation
• Centralised administration.
• Container-based tests;
• Mock tests that can run without any real database server.

The latest addition is the ability of running multi-source topologies. Now we can run four topologies:

• master-slave is the default topology. It will install one master and two slaves. More slaves can be added with the option --nodes.
• group will deploy three peer nodes in group replication. If you want to use a single primary deployment, add the option --single-primary. Available for MySQL 5.7 and later.
• fan-in is the opposite of master-slave. Here we have one slave and several masters. This topology requires MySQL 5.7 or higher.
  all-masters is a special case of fan-in, where all nodes are masters and are also slaves of all nodes.

It is possible to tune the flow of data in multi-source topologies. The default for fan-in is three nodes, where 1 and 2 are masters, and 2 are slaves. You can change the predefined settings by providing the list of components:

$ dbdeployer deploy replication \
    --topology=fan-in \
    --nodes=5 \
    --master-list="1 2 3" \
    --slave-list="4 5" \
    8.0.4 \
    --concurrent

In the above example, we get 5 nodes instead of 3. The first three are master (--master-list="1 2 3") and the last two are slaves (--slave-list="4 5") which will receive data from all the masters. There is a test automatically generated to test replication flow. In our case it shows the following:

$ ~/sandboxes/fan_in_msb_8_0_4/test_replication
# master 1
# master 2
# master 3
# slave 4
ok - '3' == '3' - Slaves received tables from all masters
# slave 5
ok - '3' == '3' - Slaves received tables from all masters
# pass: 2
# fail: 0

The first three lines show that each master has done something. In our case, each master has created a different table. Slaves in nodes 5 and 6 then count how many tables they found, and if they got the tables from all masters, the test succeeds.
Note that for all-masters topology there is no need to specify master-list or slave-list. In fact, those lists will be auto-generated, and they will both include all deployed nodes.

What now?

Once I make sure that the current features are reasonably safe (I will only write more tests for the next 10~15 days) I will publish the first (non-pre) release of dbdeployer. From that moment, I’d like to follow the recommendations of the Semantic Versioning:

• The initial version will be 1.0.0 (major, minor, revision);
• The spects for 1.0 will be the API that needs to be maintained.
• Bug fixes will increment the revision counter.
• New features that don’t break compatibility with the API will increment the minor counter;
• New features or changes that break compatibility will trigger a major counter increment.

Using this method will give users a better idea of what to expect. If we get a revision number increase, it is only bug fixes. An increase in the minor counter means that there are new features, but all previous features work as before. An increase in the major counter means that something will break, either because of changed interface or because of changed behavior.
In practice, the tests released with 1.0.0 should run with any 1.x subsequent version. When those tests need changes to run correctly, we will need to bump up the major version.

Let’s see if this method is sustainable. So far, I haven’t had need to do behavioural changes, which are usually provoked by new versions of MySQL that introduce incompatible behavior (definitely MySQL does not follow the Semantic Versioning principles.) When the next version becomes available, I will see if this RC of dbdeployer can stand its ground.

via The Data Charmer
dbdeployer release candidate