| Bug #91961 | mysql_upgrade in ANSI-Mode failes with syntax error on inplace from 5.7 to 8.0 | ||
|---|---|---|---|
| Submitted: | 10 Aug 2018 9:28 | Modified: | 16 Feb 2019 17:46 |
| Reporter: | Joerg Plenert | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S2 (Serious) |
| Version: | 8.0.12 | OS: | Windows (Windows 10 X64) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | mysql_upgrade | ||
[10 Aug 2018 9:28]
Joerg Plenert
[23 Aug 2018 12:15]
MySQL Verification Team
Please provide here your my.ini file. Thanks.
[23 Aug 2018 12:18]
Joerg Plenert
[mysqld] basedir = D:/mysql80/ datadir = D:/mysql80/data3307 port=3307 sql_mode=ANSI_QUOTES,NO_ENGINE_SUBSTITUTION default-time-zone="+00:00"
[23 Aug 2018 15:06]
MySQL Verification Team
Thank you for the feedback. In our manual upgrade process where you see the below operation: Copy the data folder to a mysql 8.0.12, delete all files in root of data dir except the ibdata1.
[23 Aug 2018 15:47]
Joerg Plenert
That is exactly what we are doing: * Create an empty db with mysql57 * Delete all files in root except ibdata1 * start mysql80. * Start mysql_upgrade -> Error
[24 Aug 2018 6:23]
MySQL Verification Team
Thank you for the details but please note that you have still not confirmed to our Miguel's earlier query. I would like to suggest you to follow the in-place upgrade procedure outlined here https://dev.mysql.com/doc/refman/8.0/en/windows-upgrading.html and report us back if you are still facing any issues. I've quickly checked this at my end and confirmed that it is working as expected. Shortly joining the test log for your reference.
[24 Aug 2018 6:24]
MySQL Verification Team
5.7.21 - 8.0.12 upgrade steps and results
Attachment: 91961_upgrade.results (application/octet-stream, text), 24.81 KiB.
[24 Aug 2018 6:35]
MySQL Verification Team
My senior colleague Shane pointed the issue i.e if sql_mode is ANSI then mysql_upgrade fails. Followed previous steps but this time with sql_mode set to ANSI(5.7/8.0) and observed that mysql_upgrade fails D:\ushastry\MySQL\mysql-8.0.12-winx64>bin\mysql_upgrade.exe -uroot -p Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. mysql_upgrade: [ERROR] 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"General log"' at line 1
[24 Aug 2018 6:58]
Joerg Plenert
Looks like we have found the root cause!
[16 Feb 2019 17:46]
Paul DuBois
Posted by developer: Fixed in 8.0.16 as part of this change: Previously, after installation of a new version of MySQL, the MySQL server automatically upgraded the data dictionary tables at the next startup, after which the DBA was expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the sys schema and user schemas. The server now performs the tasks previously handled by mysql_upgrade. After installation of a new MySQL version, the server now automatically performs all necessary upgrade tasks at the next startup and is not dependent on the DBA invoking mysql_upgrade. In addition, the server updates the contents of the help tables, which mysql_upgrade did not do. A new --upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations. This change to the upgrade procedure results in some deprecations: * mysql_upgrade is deprecated because it is no longer necessary. * The --no-dd-upgrade server option is deprecated because the --upgrade option supersedes it. mysql_upgrade and the --no-dd-upgrade option will be removed in a future MySQL version.
