A summary of some errors caused by the MySQL version upgrade

Encountered some errors in the project process that occurred because of a database version reason

MySQL server Upgrade Data restore table structure cannot be displayed

After the MySQL server upgrade, after the database data is restored, the DESC table structure using NAVICAT is appearing

"1558–column count of Mysql.proc is wrong. expected, found 16. Created with the MySQL 50018, now running 50146. Please use the Mysql_upgrade to fix this error. " Such an error content

My MySQL server is upgraded from 5.1 to version 5.5, and the original database table backup and restore is directly copy MySQL data directory; Search Find out

The solution is:

Mysql.proc
This is the system table from 5.1, which is used to record information about stored procedures or functions. Your database must have been upgraded or restored with a different version for backup migration.

Using commands: Mysql_upgrade can be solved

Mysql_upgrade-u root–datadir=/opt/mysql–basedir=/opt/mysql/data–password=123456

MySQL utility for upgrading databases to new MySQL versions.

5.6.10 mysqldump:couldn ' t execute ' SELECT @ @GTID_MODE ' ERROR

Mysqldump:couldn ' t execute ' SELECT @ @GTID_MODE ': Unknown system variable ' gtid_mode ' (1193)

Don't know system variable Gtid_mode,google search in MySQL official found a bug report

Mysqldump–set-gtid-purged=auto does not detect if Mysqld has Gtids

5.6.5 introduced Gtids. Mysqldump 5.6 was made
Compatible to use Gtids
2. In versions < 5.6, where gtids are not
Even defined, mysqldump 5.6 fails as it cannot
Execute ' SELECT @ @GTID_MODE '
3. Using–set-gtid-purged=auto, should detect whether
The server has gtids enabled or not. However, this
Option works a for 5.6 versions.

Workaround:if your are using mysqldump 5.6 to backup mysql-5.5 and old database
Versions, Use–set-gtid-purged=off option.

Fix:the fix is to check for the server version before executing ' SELECT @ @gtid_mode '

5.6.5 version introduced gtids; if MySQL version is less than 5.6,gtids is undefined, mysqldump5.6 cannot execute ' SELECT @ @GTID_MODE '; should use –set-gtid-purged= Auto is used to detect if Gtids is enabled, but this option can only work on version 5.6 MySQL

The remote server that I want to back up is

wfyo/+d7k+/ixh4wzaprzionuhreijre/vlt3zcfpfgj8lxb/cg49holbfpjzv6xtaicyffj0t+ suakbshz79wzofacjbr14ra4ct33zmd8qayop3f53ixrpy+/smsaaattfvv//pzhn5yy61gedp/vi3/042v6jmuosblox// Hr3ewq60pkaaaaasuvork5cyii= "/>

The previous normal mysqldump syntax is the following error

So when data is backed up with mysqldump 5.6 for a MySQL server version that is less than 5.6 or older, the statement "SELECT @ @GTID_MODE" is executed first (although the SELECT @ @gtid_mode The resulting property value is off)

You have to add the –set-gtid-purged=off attribute, the export is successful