| Bug #60223 | mysql_upgrade problem with option skip-write-binlog | ||
|---|---|---|---|
| Submitted: | 23 Feb 2011 14:08 | Modified: | 16 Nov 2011 20:22 |
| Reporter: | Michael Froehlich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S3 (Non-critical) |
| Version: | 5.5.9 | OS: | Linux (Debian Etch) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql_upgrade 5.5.9, regression | ||
[23 Feb 2011 14:08]
Michael Froehlich
[25 Feb 2011 23:08]
Sveta Smirnova
Looks like re-occurrence of old bug #56944
[25 Feb 2011 23:18]
Sveta Smirnova
Thank you for the report. Verified as described.
[13 Apr 2011 5:39]
Ricardo M
Hi, I'm upgrading from 5.1.50 to 5.5.11 and got similar behavior with performance_schema database not created when using --skip-write-binlog. Disabling binlog it is important when upgrading the slave server in a replication environment. Please advice how to proceed to have performance_schema created upgrading and using --skip-write-binlog.
[25 Apr 2011 19:55]
Ricardo M
Are there any plans to fix this issue? In a replication environment the --skip-write-binlog is needed when executing mysql_upgrade. Without this fix a zero downtime can not be avoided on production environment. Thanks.
[22 Sep 2011 11:55]
Leandro Morgado
The same problem has been verified upgrading 5.1.51 -> 5.5.15 (linux x86_64).
[16 Oct 2011 10:14]
Jervin R
Additionally - running with --upgrade-system-tables with or without --skip-write-binlog still does not upgrade the system tables.
[11 Nov 2011 14:06]
Vladimir Preobrazhenskiy
It seems the problem is that the "--skip-write-binlog" option is being passed to the 'mysql' tool. The latter does not know about this option and fails with error. client/mysql_upgrade.c.patch contents: 301a302,305 > > case OPT_WRITE_BINLOG: /* --write-binlog */ > add_option= FALSE; > break; seems to fix this problem (tested on Linux with ver 5.5.17)
[11 Nov 2011 14:27]
Vladimir Preobrazhenskiy
Also regarding the mysql_upgrade and replication interference. looking at the scripts/mysql_system_tables_fix.sql file, I noticed such fragments: SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE general_log MODIFY event_time TIMESTAMP NOT NULL, ( skipped a bit ) MODIFY argument MEDIUMTEXT NOT NULL; SET GLOBAL general_log = @old_log_state; and SET @old_log_state = @@global.slow_query_log; SET GLOBAL slow_query_log = 'OFF'; ALTER TABLE slow_log MODIFY start_time TIMESTAMP NOT NULL, ( skipped a bit ) MODIFY sql_text MEDIUMTEXT NOT NULL; SET GLOBAL slow_query_log = @old_log_state; If the `mysql` schema is not excluded from replication: It seems to me that "SET GLOBAL ***_query_log =..." statement just does not get binlogged; hence, is not being replicated. As a result, the slave server does not "release" its log tables (which was the very purpose of this action), and we got an error like " [ERROR] Slave SQL: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log..." So, it seems to me that mysql_upgrade in its current state is 100% guaranteed to break the replication. I'm not sure about my proposals here.
[11 Nov 2011 14:51]
Ivan Pozdeev
My proposal is that mysql_upgrade should not write its changes into binlog in any case. Upgrading a server is by definition a per-server operation and should not affect its peers. Of course, there might be side-effects due to version differences that would break replication but it's not mysql_upgrade's job to care about them.
[16 Nov 2011 20:22]
Paul DuBois
Noted in 5.5.19, 5.6.4 changelogs. mysql_upgrade did not upgrade the system tables or create the mysql_upgrade_info file when run with the --write-binlog or --skip-write-binlog option.
[15 Mar 2012 11:46]
Maurizio Garzelli
Mysql version: 5.1.56-ndb-7.1.18 (in cluster: 1 master+1slave) Linux version: Red Hat Enterprise Linux Server release 5.7 (Tikanga) Hello, I have the same issue with the mysql_upgrade --skip-write-binlogs command: I noticed that in both instances where I run the same command with and without the option, it returns the following: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log Error : You can't use locks with log tables. status : 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.plugin OK mysql.proc OK mysql.procs_priv OK mysql.servers OK mysql.slow_log Error : You can't use locks with log tables. status : 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 ssg.active_connector OK ssg.active_connector_property OK ssg.audit_admin OK ssg.audit_detail OK ssg.audit_detail_params OK ssg.audit_main OK ssg.audit_message OK ssg.audit_system OK ssg.client_cert OK ssg.cluster_info OK ssg.cluster_master OK ssg.cluster_properties OK ssg.connector OK ssg.connector_property OK ssg.counters OK ssg.email_listener OK ssg.email_listener_state OK ssg.fed_group OK ssg.fed_group_virtual OK ssg.fed_user OK ssg.fed_user_group OK ssg.folder OK ssg.hibernate_unique_key OK ssg.http_configuration OK ssg.identity_provider OK ssg.internal_group OK ssg.internal_user OK ssg.internal_user_group OK ssg.jdbc_connection OK ssg.jms_connection OK ssg.jms_endpoint OK ssg.keystore_file OK ssg.logon_info OK ssg.message_context_mapping_keys OK ssg.message_context_mapping_values OK ssg.message_id OK ssg.password_history OK ssg.password_policy OK ssg.policy OK ssg.policy_alias OK ssg.policy_version OK ssg.published_service OK ssg.published_service_alias OK ssg.rbac_assignment OK ssg.rbac_permission OK ssg.rbac_predicate OK ssg.rbac_predicate_attribute OK ssg.rbac_predicate_entityfolder OK ssg.rbac_predicate_folder OK ssg.rbac_predicate_oid OK ssg.rbac_role OK ssg.replication_status OK ssg.resolution_configuration OK ssg.resource_entry OK ssg.revocation_check_policy OK ssg.sample_messages OK ssg.secure_password OK ssg.service_documents OK ssg.service_metrics OK ssg.service_metrics_details OK ssg.service_usage OK ssg.shared_keys OK ssg.sink_config OK ssg.ssg_version OK ssg.trusted_cert OK ssg.trusted_esm OK ssg.trusted_esm_user OK ssg.uddi_business_service_status OK ssg.uddi_proxied_service OK ssg.uddi_proxied_service_info OK ssg.uddi_publish_status OK ssg.uddi_registries OK ssg.uddi_registry_subscription OK ssg.uddi_service_control OK ssg.uddi_service_control_monitor_runtime OK ssg.wsdm_subscription OK ssg.wssc_session OK Running 'mysql_fix_privilege_tables'... OK The only difference between using or not using the option is that not using that option creates the mysql_upgrade_info, while if I do use the option, it does not create any file, is that important? what does it mean it failed while using the option? Also, I noticed that if I try again, if I use no option, the command will fail and ask me to use the --force to force the upgrade if I use the --skip-write-binlog, it asks nothing and goes ahead and gives me the output as above without any file. Any thoughts? Kind regards, Maurizio Garzelli
