| Bug #59974 | 5.5.8 > 5.5.9 upgrade fails | ||
|---|---|---|---|
| Submitted: | 6 Feb 2011 12:53 | Modified: | 6 Feb 2011 19:21 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S2 (Serious) |
| Version: | 5.5.9 | OS: | Windows ((probably any)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[6 Feb 2011 13:00]
Peter Laursen
BTW: From the console output I can tell that it *does* connect to my 5.5 instance on port 3307 (and not my 5.1 instance running on port 3306)
[6 Feb 2011 13:07]
Peter Laursen
. and I do not use any authentication plugin or have any PROXY -users. This server has a few 'traditional' users only.
[6 Feb 2011 13:11]
Peter Laursen
and no info here: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html that some special upgrade procedure is necessary when upgrading to 5.5.9 (like was the case with upgrade to 5-5-7)
[6 Feb 2011 13:24]
Peter Laursen
.. and finally after starting server with --no-defaults or --skip-grant-tables options exactly same errors occur with the 'fix-privileges' script.
[6 Feb 2011 14:26]
Peter Laursen
OK .. --no-defaults seems not supported. But --skip-grant-tables is.
[6 Feb 2011 15:44]
Peter Laursen
Where is the 'mysql_fix_privilege_tables.sql' file? In 5.0 and 5.1 I find it in /share folder, but not in 5.5. I am not able to find it at all. Has it been compiled into mysql_upgrade as a resource? I thought I would try to run the script from a client, but since I cannot find the file I cannot! My *idea*/*theory*/ was that when specifying a port <> 3306 table checks would run against the instance of the specified port (what it definitely does), but maybe 'mysql_fix_privilege_tables.sql' would always run against an instance on port 3306? Anyway I have no option to check this as I cannot find the file for 5.5.
[6 Feb 2011 18:38]
Vladislav Vaintroub
Peter, Generally, you have to start mysqlcheck in the target version, in your case not from 5.1 install directory but from 5.5 install directory. There is normally no reason to upgrade from 5.5 to 5.5 (like 5.5.8 to 5.5.9). "normally" means that there must be no changes in system tables between 2 versions that differ only in patch number. I believe this policy will now be broken in 5.5, as Joro has started to modify DDL of privilege tables to correct the infamous bug#59974. Which means an upgrade will not automatically get the changes, and I guess user might have to run mysqlcheck himself. And to answer your question on where is the script - it is compiled into mysqlcheck (not as Windows resource, but in conceptually similar yet OS-agnostic way, namely as long string constant).
[6 Feb 2011 18:48]
Peter Laursen
Obviously my stupid mistake that I started the 5.1.54 version of the program and not 5.5. version: C:\Users\Peter>cd \program files\mysql\mysql server 5.5\bin C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql_upgrade --port=3307 -uroot -p Enter password: ******** Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.e e Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\my qlcheck.exe Running 'mysqlcheck' with connection arguments: "--port=3307" "--port=3307" Running 'mysqlcheck' with connection arguments: "--port=3307" "--port=3307" .. -- tables checks -- Running 'mysql_fix_privilege_tables'... OK C:\Program Files\MySQL\MySQL Server 5.5\bin> So only this remains: 1) I think I have already reported before that 'mysqlcheck' output line is repeated twice. But now this " ... arguments: "--port=3307" "--port=3307" " seems new (but cosmetical only) 2) --port/-P option is not listed at http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html. Maybe this passage "supports the following options, which can be specified on the command line or in the [mysql_upgrade] and [client] option file groups. Other options are passed to mysqlcheck. For example, it might be necessary to specify the --password[=password] option. mysql_upgrade also supports the options for processing option files described at Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”." .. is supposed to describe this (but I don not understand it).
[6 Feb 2011 18:50]
Peter Laursen
I know that there is *generally* no need to run mysql_upgrade when upgrading from a.b.x to a.b.y. But *sometimes* there is. Last time was between 5.5.6 and 5.5.7. So I always do!
[6 Feb 2011 18:56]
Vladislav Vaintroub
Have to correct my previous comment. I was refering to Bug#59038 as one that changes the DDL of system tables and thus requires mysql_upgrade.
[6 Feb 2011 18:56]
Peter Laursen
OK .. 5.5 docs also state "fix_priv_tables represents a script generated internally by mysql_upgrade that contains SQL statements to upgrade the tables in the mysql database." .. what explains that I could not find the script. It is 'generated internally' or 'compiled-in as a resource'. Let us close!
[6 Feb 2011 19:03]
Vladislav Vaintroub
5.5.6 and 5.5.7 were beta, so it was not critical. 5.5.8 on the other hand is GA and that mysql_upgrade needs to run here is (from my point of view) bad.
[6 Feb 2011 19:21]
Peter Laursen
@Vladislav .. I do not think is is *required* with 5.5.8 > 5.5.9. But it *does not harm*. Actually the server seemed to work fine before I ran the correct 'fix_privs' script (what is no loger a script! :) ). I have only made it a habit always to do. http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html .. says: "mysql_upgrade should be executed each time you upgrade MySQL". It does not distinguish 'major' and 'minor' versions. Though *most often* 'minor' upgrades do not require mysql_upgrade, there is no guarantee that it *sometimes* will not. I find a few issues a) with the documentation (first of all --port option not documented - (and --host etc.?) and b) the console output (1: mysqlcheck command repeats twice 2: port is listed twice when specified). The issue are cosmetical and b1) I have already reported in another report. I decided to close here as I have (probably) already too many reports open that are cosmetical-and-close-to-nothing. (Danish term is 'flueknepperi' - but my good and polite habits forbid me to translate it into English where it could offend somebody! You always have to be careful with the sentiments of US people as they are not living in the real world:-) )
Description: I experienced failure of mysql_upgrade. Yable checks are OK, but 'mysql_fix_privilege_tables' script fails How to repeat: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql_upgrade --port=3307 -uroot -p Enter password: ******** Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.ex e Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.1\bin\mys qlcheck.exe Running 'mysqlcheck' with connection arguments: "--port=3306" "--port=3307" Running 'mysqlcheck' with connection arguments: "--port=3306" "--port=3307" bugtest.language OK bugtest.textmaster OK bugtest.texttranslation OK fktest.child OK fktest.parent OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.nonsense OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK performance_schema.cond_instances OK performance_schema.events_waits_current OK performance_schema.events_waits_history OK performance_schema.events_waits_history_long OK performance_schema.events_waits_summary_by_instance OK performance_schema.events_waits_summary_by_thread_by_event_name OK performance_schema.events_waits_summary_global_by_event_name OK performance_schema.file_instances OK performance_schema.file_summary_by_event_name OK performance_schema.file_summary_by_instance OK performance_schema.mutex_instances OK performance_schema.performance_timers OK performance_schema.rwlock_instances OK performance_schema.setup_consumers OK performance_schema.setup_instruments OK performance_schema.setup_timers OK performance_schema.threads OK test.a OK test.auto_table OK test.b OK test.blah OK test.dttest OK test.g1 OK test.indcmt OK test.indexcmt OK test.parent OK test.sessions OK test.t1 OK test.tstest OK test.tttt OK test.tttttt OK test.utf8test OK test-database7.test OK testing.table2 OK testing.table1 OK Running 'mysql_fix_privilege_tables'... ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the m anual that corresponds to your MySQL server version for the right syntax to use near '(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop',' Grant'' at line 1 ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the m anual that corresponds to your MySQL server version for the right syntax to use near '(14), Column_priv set('Select','Insert','Update','References') COLLATE utf 8_gene' at line 1 ERROR 1064 (42000) at line 65: You have an error in your SQL syntax; check the m anual that corresponds to your MySQL server version for the right syntax to use near '(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (G rantor' at line 1 ERROR 1064 (42000) at line 452: 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 '(14) AFTER Proc_priv' at line 2 FATAL ERROR: Upgrade failed Suggested fix: Not sure. I realize that 5.5.9 may not be officially released. But .msi installer is here: ftp://mirrors.dotsrc.org/mysql/Downloads/ Also this response Running 'mysqlcheck' with connection arguments: "--port=3306" "--port=3307" -- mystifies me and using -P3307 makes no difference as compared to --port=3307 This page: http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html .. does not list a --port or -P option. But how am I supposed to upgrade an instance running on port 3307? (setting a S2 with the risk that I am doing a stupid mistake!)