Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5786

mysql_upgrade on galera replicates "alter table" on system tables

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.5.35-galera
        • Fix Version/s: 5.5.39-galera, 10.0.13-galera
        • Component/s: None
        • Labels:

          Description

          same as MDEV-5116 except for galera. Same of having alter table changes propagate.

          A solution could be http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade RSU #2 - finter control

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5116 MariaDB upgrade breaks replication

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  The error on the "slave" db1 from the update on db3 is:

                  Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [ERROR] Slave SQL: Error 'Duplicate column name 'File_priv'' on query. Default database: 'mysql'. Query: 'ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL', Error_code: 1060
Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [Warning] WSREP: RBR event 1 Query apply warning: 1, 25
Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [Warning] WSREP: Ignoring error for TO isolated action: source: d0cc6c2f-aa5e-11e3-9913-b2cfc1030243 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 12 trx_id: -1 seqnos (l: 42, g: 25, s: 24, d: 24, ts: 2287771695569)
Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [ERROR] Slave SQL: Error 'Duplicate column name 'Grant_priv'' on query. Default database: 'mysql'. Query: 'ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL', Error_code: 1060
Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [Warning] WSREP: RBR event 1 Query apply warning: 1, 26
Mar 13 03:24:02 db1 mysqld: 140313  3:24:02 [Warning] WSREP: Ignoring error for TO isolated action: source: d0cc6c2f-aa5e-11e3-9913-b2cfc1030243 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 12 trx_id: -1 seqnos (l: 43, g: 26, s: 25, d: 25, ts: 2287780573237)
                  Show
                  danblack Daniel Black added a comment - The error on the "slave" db1 from the update on db3 is: Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [ERROR] Slave SQL: Error 'Duplicate column name 'File_priv'' on query. Default database: 'mysql'. Query: 'ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL', Error_code: 1060 Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [Warning] WSREP: RBR event 1 Query apply warning: 1, 25 Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [Warning] WSREP: Ignoring error for TO isolated action: source: d0cc6c2f-aa5e-11e3-9913-b2cfc1030243 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 12 trx_id: -1 seqnos (l: 42, g: 25, s: 24, d: 24, ts: 2287771695569) Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [ERROR] Slave SQL: Error 'Duplicate column name 'Grant_priv'' on query. Default database: 'mysql'. Query: 'ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL', Error_code: 1060 Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [Warning] WSREP: RBR event 1 Query apply warning: 1, 26 Mar 13 03:24:02 db1 mysqld: 140313 3:24:02 [Warning] WSREP: Ignoring error for TO isolated action: source: d0cc6c2f-aa5e-11e3-9913-b2cfc1030243 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 12 trx_id: -1 seqnos (l: 43, g: 26, s: 25, d: 25, ts: 2287780573237)
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  Actually a session desync in the mysql_upgrade program would be sufficient and therefore non-impacting.

                  SET wsrep_desync=ON
                  ...
                  SET wsrep_desync=OFF

                  Show
                  danblack Daniel Black added a comment - Actually a session desync in the mysql_upgrade program would be sufficient and therefore non-impacting. SET wsrep_desync=ON ... SET wsrep_desync=OFF
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  I haven't totally checked this however a /etc/mysql/debian-start having --skip-write-binlog for mysql_upgrade/mysql_check may be sufficient

                  MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --skip-write-binlog"
MYCHECK="/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf --skip-write-binlog"
                  Show
                  danblack Daniel Black added a comment - I haven't totally checked this however a /etc/mysql/debian-start having --skip-write-binlog for mysql_upgrade/mysql_check may be sufficient MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --skip-write-binlog" MYCHECK="/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf --skip-write-binlog"
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  sorry to add another complication.

                  After updating a db its normally catching up in IST or SST on startup. As such as mysql_update from the debian script is run immediately its gets a wsrep not ready or similar error.

                  Show
                  danblack Daniel Black added a comment - sorry to add another complication. After updating a db its normally catching up in IST or SST on startup. As such as mysql_update from the debian script is run immediately its gets a wsrep not ready or similar error.
                  Hide
                  Permalink
                  nirbhay_c Nirbhay Choubey added a comment -

                  http://lists.askmonty.org/pipermail/commits/2014-July/006315.html

                  Show
                  nirbhay_c Nirbhay Choubey added a comment - http://lists.askmonty.org/pipermail/commits/2014-July/006315.html
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  thank you. Looks sane. If I come up with the problem of my previous comment I'll put in a new bug report.

                  Show
                  danblack Daniel Black added a comment - thank you. Looks sane. If I come up with the problem of my previous comment I'll put in a new bug report.
                  Hide
                  Permalink
                  jplindst Jan Lindström added a comment -

                  Ok to push, please check also 10.x Galera.

                  Show
                  jplindst Jan Lindström added a comment - Ok to push, please check also 10.x Galera.
                  Hide
                  Permalink
                  nirbhay_c Nirbhay Choubey added a comment -

                  Pushed to maria-5.5-galera, maria-10.0-galera.

                  Show
                  nirbhay_c Nirbhay Choubey added a comment - Pushed to maria-5.5-galera, maria-10.0-galera.

                    People

                    • Assignee:
                      nirbhay_c Nirbhay Choubey
                      Reporter:
                      danblack Daniel Black
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: