0

We have tables with MyISAM. The are out of sync, the number of records is different on each cluster node

> select table_schema, table_name from tables where ENGINE = 'MyISAM' and table_schema != 'mysql';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| nrmtz       | tablename1  |
| nrmwetr   | tablename1  |
| nrmrzhredr  | tablename1  |
| nrmesfw     | tablename1  |
| sdtz        | tablename1  |
| sdwetr    | tablename1  |
| sdrzhredr   | tablename1  |
| sdesfw      | tablename1  |
| smtz        | tablename1  |
| smwetr    | tablename1  |
| smrzhredr   | tablename1  |
| smesfw      | tablename1  |
+--------------+------------+
12 rows in set (0.02 sec)

> desc tablename1;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| migration_id | bigint(20) | NO   | UNI | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

   -> ;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                       |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gomigrate | CREATE TABLE `tablename1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `migration_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `migration_id` (`migration_id`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Our version (we can't upgrade easy) and cluster status

> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

> show status like '%wsrep%';
+------------------------------+-------------------------------------------------+
| Variable_name                | Value                                           |
+------------------------------+-------------------------------------------------+
| wsrep_local_state_uuid       | 927d368c-b92d-11e5-bb07-2647ef235310            |
| wsrep_protocol_version       | 5                                               |
| wsrep_last_committed         | 5592805                                         |
| wsrep_replicated             | 4287245                                         |
| wsrep_replicated_bytes       | 2067046243                                      |
| wsrep_repl_keys              | 12903900                                        |
| wsrep_repl_keys_bytes        | 201838365                                       |
| wsrep_repl_data_bytes        | 1590824198                                      |
| wsrep_repl_other_bytes       | 0                                               |
| wsrep_received               | 1350734                                         |
| wsrep_received_bytes         | 676386342                                       |
| wsrep_local_commits          | 4285656                                         |
| wsrep_local_cert_failures    | 1                                               |
| wsrep_local_replays          | 0                                               |
| wsrep_local_send_queue       | 0                                               |
| wsrep_local_send_queue_avg   | 0.000431                                        |
| wsrep_local_recv_queue       | 0                                               |
| wsrep_local_recv_queue_avg   | 0.013302                                        |
| wsrep_local_cached_downto    | 5353930                                         |
| wsrep_flow_control_paused_ns | 0                                               |
| wsrep_flow_control_paused    | 0.000000                                        |
| wsrep_flow_control_sent      | 0                                               |
| wsrep_flow_control_recv      | 0                                               |
| wsrep_cert_deps_distance     | 12.204780                                       |
| wsrep_apply_oooe             | 0.034583                                        |
| wsrep_apply_oool             | 0.000000                                        |
| wsrep_apply_window           | 1.036009                                        |
| wsrep_commit_oooe            | 0.000000                                        |
| wsrep_commit_oool            | 0.000000                                        |
| wsrep_commit_window          | 1.001590                                        |
| wsrep_local_state            | 4                                               |
| wsrep_local_state_comment    | Synced                                          |
| wsrep_cert_index_size        | 23                                              |
| wsrep_causal_reads           | 0                                               |
| wsrep_cert_interval          | 0.072367                                        |
| wsrep_incoming_addresses     | 192.168.5.32:3306,192.168.5.31:3306,192.168.5.33:3306 |
| wsrep_cluster_conf_id        | 7                                               |
| wsrep_cluster_size           | 3                                               |
| wsrep_cluster_state_uuid     | 927d368c-b92d-11e5-bb07-2647ef235310            |
| wsrep_cluster_status         | Primary                                         |
| wsrep_connected              | ON                                              |
| wsrep_local_bf_aborts        | 0                                               |
| wsrep_local_index            | 1                                               |
| wsrep_provider_name          | Galera                                          |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>               |
| wsrep_provider_version       | 3.5(rXXXX)                                      |
| wsrep_ready                  | ON                                              |
| wsrep_thread_count           | 2                                               |
+------------------------------+-------------------------------------------------+
48 rows in set (0.00 sec)

I need the tables to be InnoDB and in sync. Those two statements crashing.

CREATE TABLE test ENGINE=InnoDB SELECT * FROM nrmtz.tablename1;

ALTER TABLE tablename1 ENGINE=InnoDB;

Are there other ways doing it without crash?

1
  • Maybe you could dump the table, change the engine in the dump and reimport it.
    – jkavalik
    Feb 9 '16 at 12:25
2

I need the tables to be InnoDB and in sync. Those two statements crashing.

Does it crash with a stack trace in the server logs? Could you share it here? I tried the above two commands on a 2-node MariaDB-5.5.47 cluster and it worked as expected.

As @jkavalik suggested, you could do something like the following :

  1. Create a new INNODB table with the same definition. This table gets replicated to other nodes.
  2. Dump data from the old table into a file SELECT ... INTO OUTFILE ...
  3. Reload data from the file into the new table LOAD DATA INFILE ... [REPLACE|IGNORE] .... The inserted records also get replicated to other nodes.
  4. Repeat 2-3 on other nodes.
  5. Rename table created in step 1.
1

MyISAM support in galera is cough beta at best.

The best thing to do is: mysqldump ... -T db table_name > dump.sql (this will make a .sql and .txt file) drop the myisam table edit the .sql file and change ENGINE=MYISAM to ENGINE=INNODB LOAD DATA INFILE 'table.txt' INTO TABLE table_name;

Do this on whatever node that you want to treat as the "source of truth".

If you want a consistent table with records from all nodes, then do the above, then use sort to sort the tables (using the PK, ideally an auto-increment) removing the duplicates, then import the .txt with records from all tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.