Showing entries 1 to 10
Displaying posts with tag: mysql upgrade (reset)
MySQL 8 – timestamp cannot be null and explicit_defaults_for_timestamp

A friend’s application started failing with MySQL causing error about timestamp columns and it needs urgent fixing from the database side. A timestamp column was not accepting the null values…

The post MySQL 8 – timestamp cannot be null and explicit_defaults_for_timestamp first appeared on Change Is Inevitable.

Two Extremely Useful Tools (pt-upgrade and checkForServerUpgrade) for MySQL Upgrade Testing

My last blog, Percona Utilities That Make Major MySQL Version Upgrades Easier, detailed the tools available from the Percona toolkit that assists us with major MySQL version upgrades. The pt-upgrade tool aids in testing application queries and generates reports on how each question performs on servers running various versions of MySQL.

MySQL Shell Upgrade Checker is a utility that helps in compatibility tests between MySQL 5.7 instances and MySQL 8.0 upgrades, which is part of the mysql-shell-utilities. The util.checkForServerUpgrade() function checks whether the MySQL 5.7 instance is ready for the MySQL 8.0 upgrade and generates a report with warnings, errors, and notices for preparing the current MySQL 5.7 setup for upgrading to MySQL 8.0.

We can run this Upgrade Checker Utility in the current MySQL 5.7 …

[Read more]
Percona Utilities That Make Major MySQL Version Upgrades Easier

It is essential to upgrade MySQL to the most recent version. Do you believe it’s tough to test and upgrade to a newer version?

For a variety of reasons, including new features, performance advantages, bug corrections, and so on, databases with obsolete versions are vulnerable. Major version upgrades, on the other hand, can be problematic if they haven’t been extensively tested with your application, as the procedure may break it, prevent it from functioning properly, or result in performance concerns.

Let’s go through a few useful tools that can assist you with MySQL upgrades.

The tool helps you run application SELECT queries and generates reports on how each query pattern performs on the servers across the different versions of MySQL we tested.

[Read more]
Upgrading to MySQL 8? Meet the MySQL Shell Upgrade Checker Utility

MySQL Shell is a pretty nice piece of software. Is not just another mysql client but it is also a tool that offers scripting capabilities for JavaScript and Python. And one of the coolest things you can do with it is to check if your MySQL 5.7 server is ready for an upgrade or not. Enter: Upgrade Checker Utility.

MySQL Shell Upgrade Checker Utility

So what is it? It is a script that will check your MySQL 5.7 instance for compatibility errors and issues with upgrading. It’s important to notice the word “check”. It doesn’t fix. Just check. Fix is on you, friendly DBA (or we can happily assist with it).

But isn’t there something that already does that? Close, but no. The mysqlchk program and the –check-upgrade parameter does something similar: Invokes the CHECK TABLE …. FOR UPGRADE command. The Upgrade …

[Read more]
Replicating from MySQL 8.0 to MySQL 5.7

In this blog post, we’ll discuss how to set a replication from MySQL 8.0 to MySQL 5.7. There are some situations that having this configuration might help. For example, in the case of a MySQL upgrade, it can be useful to have a master that is using a newer version of MySQL to an older version slave as a rollback plan. Another example is in the case of upgrading a master x master replication topology.

Officially, replication is only supported between consecutive major MySQL versions, and only from a lower version master to a higher version slave. Here is an example of a supported scenario:

5.7 master –> 8.0 slave

while the opposite is not supported:

8.0 master –> 5.7 slave

In this blog post, I’ll walk through how to overcome the …

[Read more]
Nasty MySQL Replication Bugs that Affect Upgrade to 5.6

There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master. The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of thousands of RBR events. The most common way this affects how we upgrade a replication hierarchy, is when we have the master running MySQL 5.5 and the slave running MySQL 5.6 and we have transactions involving DATETIME column(s). Tables with DATETIME columns will have different underlying structure when created on MySQL 5.5 versus when created on MySQL 5.6. Ideally you would avoid creating …

[Read more]
MySQL upgrade best practices

MySQL upgrades are necessary tasks and we field a variety of questions here at Percona Support regarding MySQL upgrade best practices. This post highlights recommended ways to upgrade MySQL in different scenarios.

Why are MySQL upgrades needed? The reasons are many and include: Access to new features, performance benefits, bug fixes…. However, MySQL upgrades can be risky if not tested extensively beforehand with your application because the process might break it, prevent the application from functioning properly – or performance issues could arise following the upgrade. Moreover, I suggest keeping an eye on new releases of MySQL and Percona Server – check what has changed in the …

[Read more]
Tungsten University: Zero-Downtime MySQL Maintenance And Schema Operations

  Do you know how to do rolling maintenance on your database hosts so you can make changes without stopping applications? How about upgrading schema and applications themselves? Tungsten clusters have a host of features that can help you with everything from basic administration to complex application upgrades. This webinar shows you the different types of administration you need to perform, and

Amateurs – They give us professionals a bad name

Any person with half a brain would see from the error messages below that the MySQL server is not operating optimally, or more specifically the MySQL upgrade has not completely successfully and let users can go happily use the website. It amazing me when web hosting providers tell their paying client that an upgrade has been performed yet they did not have the intelligence to actually look at the error log for confirmation. Got a mysql> prompt, it’s all good. One of the first things I check is the error log.

When will people learn the MySQL error log is a valuable resource both for what it contains, and what it should not contain.

120426 17:36:00 [Note] /usr/libexec/mysqld: Shutdown complete

120426 17:36:00 mysqld_safe mysqld from pid file /var/run/mysqld/ ended
120426 17:36:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120426 17:36:00 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: …
[Read more]
A step by step guide to upgrading to MySQL 5.5

MySQL 5.5 has created a lot of hype and its not just hype, there are major performance enhancements not only in the MySQL server itself but in the newer InnoDB plugin shipped with MySQL 5.5. That's exactly the reason why I have myself upgraded to MySQL 5.5 (The server running this blog run MySQL 5.5). Now since I haven't come across a guide to help in upgrading to MySQL 5.5, I thought why not make one myself

Showing entries 1 to 10