Compatibility with MySQL Group Replication and MySQL InnoDB Cluster #11885

ghost · 2017-07-20T20:27:15Z

Columns added to support MySQL group replication (every table must have a primary key)

…ve a primary key)

sgiehl · 2017-07-21T07:17:03Z

Besides changing the schema, there also needs to be an update script, that performs the changes while updating.
Also I'm not sure if INTEGER is the right choice. We are already having issues at other places where INTEGER is simply too small.

…INTEGER is simply too small."

ghost · 2017-07-21T10:17:52Z

Integer was changed to BIGINT. Where would we place the update script? Which file is best as an example?

sgiehl · 2017-07-22T19:15:46Z

See https://github.com/piwik/piwik/tree/3.x-dev/core/Updates
As I don't think this will get merged for 3.0.5, maybe you could name the Update-Script 3.1.0 already.

ghost · 2017-07-22T23:31:44Z

Update script was created core/Updates/3.1.0.php. Hopefully we could be considered for 3.1.0.

ghost · 2017-08-03T20:21:09Z

Tested the update script and it works well. Retested and everything checks out and we are ready for merge.

…d of 3.1

mattab · 2017-09-18T01:20:29Z

core/Db/Schema/Mysql.php

@@ -81,6 +81,8 @@ public function getTablesCreateSql()
                          `setting_name` VARCHAR(255) NOT NULL,
                          `setting_value` LONGTEXT NOT NULL,
                          `user_login` VARCHAR(100) NOT NULL DEFAULT '',
+						  `plugin_setting_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,


Would it be possible to make the spacing consistent with the lines above (ie. use spaces not tabs)?

Done, tabs were changed to spaces in Mysql.php.

ghost · 2017-09-18T13:16:00Z

Ok, tabs were changed to spaces.

mattab · 2017-09-18T21:05:20Z

core/Updates/3.2.0.php

+     */
+    private function getPluginSettingsMigrations($queries)
+    {
+		$queries[] = $this->migration->db->addColumn($this->pluginSettingsTable, 'plugin_setting_id', 'BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT', 'user_login');


Hi @Aardvark8 fyi you missed a few tabs here

ghost

ok, tabs were replaced with spaced in 3.2.0.php as well.

mattab · 2017-11-20T00:05:18Z

Hi @Aardvark8

When trying to run the upgrade it fails because there is already one field with auto increment in site_setting table, see:

    The database upgrade process may take a while, so please be patient.

  Executing ALTER TABLE `piwik_plugin_setting` ADD COLUMN `idplugin_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;... Done. [1 / 3]
  Executing ALTER TABLE `piwik_site_setting` ADD COLUMN `idsite_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;... 
    [X] Critical Error during the update process:

    * core/Updates/3.2.1.php:
    Error trying to execute the migration 'ALTER TABLE `piwik_site_setting` ADD COLUMN `idsite_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;'.

ghost · 2017-11-20T12:55:54Z

Hi Matt, We do not receive that error. Looking at the file, there does not appear to be any other column in that table with AUTO_INCREMENT. Is it possible, that your tester ran the code twice and received the error on the second run (maybe after adding the primary key column by the original name)?

diosmosis · 2018-06-14T04:00:04Z

@mattab merging since it works for me & build passes.

Thanks for submitting your first PR @Aardvark8 !

mattab · 2018-06-14T08:40:06Z

On demo2 we're getting the error

    Matomo database will be upgraded from version 3.5.1 to the new version 3.6.0-b1.

    The database upgrade process may take a while, so please be patient.

  Executing ALTER TABLE `plugin_setting` ADD COLUMN `idplugin_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;... Done. [1 / 3]
  Executing ALTER TABLE `site_setting` ADD COLUMN `idsite_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;... 
    [X] Critical Error during the update process:

    * core/Updates/3.6.0-b1.php:
    Error trying to execute the migration 'ALTER TABLE `site_setting` ADD COLUMN `idsite_setting` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;'.
    The error was: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

so this is not working as expected? cc @diosmosis

edit: table schema on demo2

MariaDB [74850_demo2]> describe site_setting;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| idsite        | int(10) unsigned    | NO   | MUL | NULL    | auto_increment |
| plugin_name   | varchar(60)         | NO   |     | NULL    |                |
| setting_name  | varchar(255)        | NO   |     | NULL    |                |
| setting_value | longtext            | NO   |     | NULL    |                |
| json_encoded  | tinyint(3) unsigned | NO   |     | 0       |                |
+---------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [74850_demo2]> describe plugin_setting;
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| plugin_name      | varchar(60)         | NO   | MUL | NULL    |                |
| setting_name     | varchar(255)        | NO   |     | NULL    |                |
| setting_value    | longtext            | NO   |     | NULL    |                |
| user_login       | varchar(100)        | NO   |     |         |                |
| json_encoded     | tinyint(3) unsigned | NO   |     | 0       |                |
| idplugin_setting | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
+------------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

sgiehl · 2018-06-14T09:26:20Z

The problem is that the update script for creating the site_setting table described it incorrect:
https://github.com/matomo-org/matomo/blob/3.x-dev/core/Updates/2.14.0-b2.php#L36
idsite should not have an auto_increment...

diosmosis · 2018-06-14T18:10:10Z

Looks like this problem should have been fixed by https://github.com/matomo-org/matomo/blob/3.x-dev/core/Updates/3.0.0-b1.php#L172-L177, not sure why this did not get applied on demo2.

…tion and MySQL InnoDB Cluster (matomo-org#11885) * Columns added to support mysql group replication (every table must have a primary key) * changed integer to bigint as "there are issues at other places where INTEGER is simply too small." * Update script created for 3.1.0 to Fix matomo-org#11596 * corrected php syntax and made the columns primary keys on add * updated the db upgrade to reflect the inclusion in version 3.2 instead of 3.1 * changed tabs to spaces * replaced tabs with spaces * Use consistent column names * Use consistent column names * Will be in 3.2.1 * Change Update version + bump version. * Version bump fail.

Columns added to support mysql group replication (every table must ha…

fcc684a

…ve a primary key)

ghost changed the title ~~Fix #11596: Compatibility of Piwik with MySQL 5.7.17 in group replication #11596~~ Fix #11596: Compatibility of Piwik with MySQL 5.7.17 in group replication Jul 20, 2017

changed integer to bigint as "there are issues at other places where …

ee98c69

…INTEGER is simply too small."

Update script created for 3.1.0 to Fix #11596

65b7e15

ghost changed the title ~~Fix #11596: Compatibility of Piwik with MySQL 5.7.17 in group replication~~ Fix #11596: Compatibility of Piwik with MySQL Group Replication and MySQL InnoDB Cluster Jul 30, 2017

mattab added the Needs Review PRs that need a code review label Aug 3, 2017

mattab added this to the 3.1.0 milestone Aug 3, 2017

corrected php syntax and made the columns primary keys on add

589c083

updated the db upgrade to reflect the inclusion in version 3.2 instea…

7039458

…d of 3.1

mattab requested changes Sep 18, 2017

View reviewed changes

changed tabs to spaces

aee99aa

mattab reviewed Sep 18, 2017

View reviewed changes

replaced tabs with spaces

d5c8410

ghost commented Sep 19, 2017

View reviewed changes

mattab added 3 commits November 20, 2017 13:00

Use consistent column names

540482b

Use consistent column names

29ae6b8

Will be in 3.2.1

98b51fd

mattab added the Pull Request WIP Indicates the current pull request is still work in progress and not ready yet for a review. label Nov 20, 2017

mattab modified the milestones: 3.5.0, 3.4.0 Mar 19, 2018

diosmosis added 2 commits June 13, 2018 19:39

Merge branch '3.x-dev' into mysql-group-replication-support-branch

cc17da4

Change Update version + bump version.

cf81777

Version bump fail.

6345887

diosmosis merged commit 5ba7f57 into matomo-org:3.x-dev Jun 14, 2018

diosmosis mentioned this pull request Jun 14, 2018

Fix update for case where site_setting still has idsite AUTO_INCREMENT. #13063

Merged

mattab added Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. labels Aug 28, 2018

mattab changed the title ~~Fix #11596: Compatibility of Piwik with MySQL Group Replication and MySQL InnoDB Cluster~~ Compatibility with MySQL Group Replication and MySQL InnoDB Cluster Aug 28, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Compatibility with MySQL Group Replication and MySQL InnoDB Cluster #11885

Compatibility with MySQL Group Replication and MySQL InnoDB Cluster #11885

ghost commented Jul 20, 2017

sgiehl commented Jul 21, 2017

ghost commented Jul 21, 2017 •

edited by ghost

sgiehl commented Jul 22, 2017

ghost commented Jul 22, 2017

ghost commented Aug 3, 2017

mattab Sep 18, 2017

ghost Sep 18, 2017

ghost commented Sep 18, 2017

mattab Sep 18, 2017

ghost left a comment

mattab commented Nov 20, 2017

ghost commented Nov 20, 2017

diosmosis commented Jun 14, 2018

mattab commented Jun 14, 2018 •

edited

sgiehl commented Jun 14, 2018

diosmosis commented Jun 14, 2018

Compatibility with MySQL Group Replication and MySQL InnoDB Cluster #11885

Compatibility with MySQL Group Replication and MySQL InnoDB Cluster #11885

Conversation

ghost commented Jul 20, 2017

sgiehl commented Jul 21, 2017

ghost commented Jul 21, 2017 • edited by ghost

sgiehl commented Jul 22, 2017

ghost commented Jul 22, 2017

ghost commented Aug 3, 2017

mattab Sep 18, 2017

Choose a reason for hiding this comment

ghost Sep 18, 2017

Choose a reason for hiding this comment

ghost commented Sep 18, 2017

mattab Sep 18, 2017

Choose a reason for hiding this comment

ghost left a comment

Choose a reason for hiding this comment

mattab commented Nov 20, 2017

ghost commented Nov 20, 2017

diosmosis commented Jun 14, 2018

mattab commented Jun 14, 2018 • edited

edit: table schema on demo2

sgiehl commented Jun 14, 2018

diosmosis commented Jun 14, 2018

ghost commented Jul 21, 2017 •

edited by ghost

mattab commented Jun 14, 2018 •

edited