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:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:5.5.9 OS:Linux (Debian Etch)
Assigned to:
Tags: mysql_upgrade 5.5.9, regression
Triage: Needs Triage: D2 (Serious)

[23 Feb 2011 14:08] Michael Froehlich
Description:
Hi, 

i have a problem concerning "mysql_upgrade" and using the command-line option "skip-write-binlog".

I upgraded a mysql with version 5.1.53 to 5.5.9.

Now i want to use mysql_upgrade --skip-write-binlog.

It tells me everything was ok.

But it doest do anything like upgrading the tables in mysql-schema.

So i started the general log, and the only thing i saw was:

...
64 Query	CHECK TABLE `ndb_binlog_index`  FOR UPGRADE
64 Query	CHECK TABLE `plugin`  FOR UPGRADE
64 Query	CHECK TABLE `proc`  FOR UPGRADE
64 Query	CHECK TABLE `procs_priv`  FOR UPGRADE
...

But if you use mysql_upgrade without "--skip-write-binlog.":
the mysql_upgrade work correct and upgrades the mysql-tables.

Regards,
Michael

How to repeat:
1. create mysql 5.1.53 installation
2. start with 5.5.9 mysql
3. enable general log on 5.5.9
4. start mysql_upgrade --skip-write-binlog
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
21 rows in set (0.00 sec)

5. start mysql_upgrade without "--skip-write-binlog"
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)
[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