0

I'm running centos 7 with mySQL 5.7.xxx. mysql -V shows Ver 14.14 Distrib 5.7.26-29, but mysqld -V shows Ver 5.7.11-4. systemctl status mysql and systemctl status mysqld both show the same running database. SELECT @@version in the client shows 5.7.11-4-log. The database acts like it is running the previous version (some differences in JSON functions, for instance).

Some background: when we started using JSON functions I attempted to update the database to the latest 5.7 version (on my way to updating to 8 preferably), but it didn't seem to work so I tabled it until I had more time. I'm not really sure what to do next. I don't know if this is a server problem or a database problem, or how to troubleshoot or proceed.

Sorry I can't provide more information, but I'm not really sure what I'm looking for. I can backup the database and uninstall mysql completely if there's not a better way to go about it, just not sure what to do, and I also remember reading that you should update your database in place and not skip a major version on the way (ie, 5.5->5.6->5.7->8.0) but I don't know if that's true or not.

4
  • mysql is the client; mysqld is the server. It's conceivable that they have different versions if you installed them separately, though ideally you'd want their versions to be the same. If you're asking how to upgrade either (or both), it's described in the documentation. If something doesn't work for you, you should probably ask a different question, providing more details.
    – mustaccio
    Nov 3, 2022 at 23:37
  • Yes, I just wasn't sure where to start. I didn't intend them to be different and all the updates were done at the same time. This is actually helpful. This gives me something to look up, thanks. Nov 3, 2022 at 23:51
  • Also, when I say "in the client" it shows 5.7.11, I mean either through the command line on the server OR from HeidiSQL on my local computer connected to the server. Does that make any difference? Nov 3, 2022 at 23:57
  • @mustaccio this was enough information to find the answer to my question. I see if I run yum update mysql centos tells me that I will upgrade from 5.7.26, and if I use yum update mysql-server then it shows the upgrade from 5.7.11, so it seems pretty clear what to do now. Thanks. If you want to write this up into an answer I'll accept it. Nov 4, 2022 at 0:19

1 Answer 1

2

Perhaps the only way to see the client's version is mysql -V. Any SHOW or SELECT command will be executed in mysqld. Note, for example, that this happily give the client's version and it does not even try to connect to the server:

$ mysql -h no-such-server -P 12345 -V
mysql  Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
$

It is quite OK to have different versions between client and server; a large degree of both backward and forward compatibility has always been provided by MySQL and MariaDB.

As for taking the next step (5.7 --> 8.0), take a backup of 5.7, then update in place. I am referring to updating the server (mysqld). The upgrade will probably upgrade the client (mysql) on the same machine, but if it does not, there should be no problem.

Also, if you have multiple machines with mysql, all accessing your server, again, don't worry about their versions.

Caution: The "mysql" in yum update mysql is probably referring to the combined package of client, server, and whatever else it needs. To get just the client, it might be yum update mysql-client On Ubuntu, I see this (since "apt" is used instead of "yum"):

# apt list 'mysql*'
Listing... Done
mysql-client-8.0/jammy-updates,jammy-security,jammy-security,now 8.0.31-0ubuntu0.22.04.1 amd64 [installed,automatic]
mysql-client-8.0/jammy-updates,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-client-core-8.0/jammy-updates,jammy-security,jammy-security,now 8.0.31-0ubuntu0.22.04.1 amd64 [installed,automatic]
mysql-client-core-8.0/jammy-updates,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-client/jammy-updates,jammy-updates,jammy-security,jammy-security,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 all
mysql-common/jammy,jammy,now 5.8+1.0.8 all [installed,automatic]
mysql-router/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 amd64
mysql-router/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-sandbox/jammy,jammy 3.2.05-1 all
mysql-server-8.0/jammy-updates,jammy-security,jammy-security,now 8.0.31-0ubuntu0.22.04.1 amd64 [installed]
mysql-server-8.0/jammy-updates,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-server-core-8.0/jammy-updates,jammy-security,jammy-security,now 8.0.31-0ubuntu0.22.04.1 amd64 [installed,automatic]
mysql-server-core-8.0/jammy-updates,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-server/jammy-updates,jammy-updates,jammy-security,jammy-security,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 all
mysql-source-8.0/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 amd64
mysql-source-8.0/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-testsuite-8.0/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 amd64
mysql-testsuite-8.0/jammy-updates,jammy-security 8.0.31-0ubuntu0.22.04.1 i386
mysql-testsuite/jammy-updates,jammy-updates,jammy-security,jammy-security 8.0.31-0ubuntu0.22.04.1 all
mysqltcl/jammy 3.052-3ubuntu1 amd64
mysqltuner/jammy,jammy 1.7.17-1 all
4
  • I just didn't know there was a possibility to update one without the other. I'm not sure how they would have gotten misaligned, unless I had trouble updating it for some reason and found an example online that used update mysql-server. I would have just updated it with update mysql. This is resolved. mustaccio's comment gave me enough information to solve the problem. Once I knew there could be two separate versions I just updated everything to 5.7.39. I'll be updating to 8 this weekend, after I have a chance to make the backup. Thanks. Nov 4, 2022 at 18:30
  • @ChrisStrickland - It could easily happen if mysqld and mysql are on different machines. I don't have a likely case for it happening on a single machine. But, as I say, the diff should not matter.
    – Rick James
    Nov 4, 2022 at 19:34
  • No, same machine, and since I didn't know that you could update them separately I'm sure I didn't do it on purpose. The issue is that I did it just for json fields and while I had the syntactic sugar -> I didn't have ->>, which I thought would be super handy. It's resolved now and ->> in fact is super handy. Can't wait until I have time to go to 8 and get window functions. Thanks for your help. Nov 5, 2022 at 1:13
  • @ChrisStrickland - Good luck with the windowing functions. I find them to be handy for expressing complex things, but not necessarily faster than non-windowing ways.
    – Rick James
    Nov 5, 2022 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.