Create and Insert Work but not Drop or Delete for MariaDB Galera Clustering

Comments

13 comments

  • Avatar
    testjetco

    I have around 1G data in the table concerned.

  • Avatar
    testjetco

    More details...

    3-node cluster.  Last night, I inserted 1G data in table_1 on node 1.  Data sync to other two nodes instantaneously.

    Then I deleted all the data in table_1 by the following command on node 1:

    delete from table_1;

    All data (1G data with 7266722 rows ) was deleted from table_1 on node 1.  Record count is zero.  But there are still all the records (7266722 rows) in table_1 on node 2 and 3.  Then I tried to execute a new insert command in node 3 to insert a new record into table_1.

    The command was hang but when I queried table_1 on node 1, record count was 1, i.e. the insert command on node 3 was executed successfully!  I queried table_1 on node 2 and 3 again.  Still all the deleted records from node 1 still existed in node 2 and 3.

    This morning I came back to office to check the result.  There is still 1 record in table_1 on node 1.  But all the records (7266722 rows) still exist in node 2 and 3.  Then I tried to shut down Mariadb on both node 2 and 3.  I used the following command:

    systemctl stop mysql

    The stop command hangs up.  Then I use 'kill -9 [process id]' to kill mysql process for both node 2 and 3.

    I started MariaDB for node 2 and 3 with the following command:

    systemctl start mysql

    MariaDB could be started up successfully.  Both node 2 and 3 joined the cluster again.  Then the table_1 content was sync from node 1 to node 2 and 3, i.e. only 1 record exists in all table_l on all nodes.

     

  • Avatar
    testjetco

    Is it a bug?  Anyone may help?

  • Avatar
    Vishal Kasle

    Hello,

          Evaluating your scenario. Can you please let us know what is the exact steps that you are carrying out for dropping the table. Its seems highly unlikely that if a table is dropped in one of the nodes in Galera cluster the change does not reflect on other nodes in the cluster, just not the way Galera works. Have you taken a look at the wsrep_OSU_method variable while you are doing these changes. 
    Please take a look at http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-osu-method and https://severalnines.com/blog/online-schema-upgrade-mysql-galera-cluster-using-rsu-method and let us know if this is the problem. 

     

    Regards,

    Vishal 

  • Avatar
    testjetco

    I dropped the table, table_1, on node 1.  Create the same table again.  This time less records, 159MB, with 1036245 records.  After deleting all the records with the following statement on node 1:

    delete from table_1;

    there are still 1036245 records in table_1 on node 2 and 3.

    The only way I can sync is stopping MariaDB on node 2 and 3 (using 'kill' because can't stop using 'systemctl stop mysql').  Start node 2 and 3 and re-join the cluster.

  • Avatar
    testjetco

    Simply speaking may I interpret TOI and RSU as follows:

    When wsrep_OSU_method=TOI, one table update on one node will replicate to all the other nodes.

    When wsrep_OSU_method=RSU, one table update on one node will NOT replicate to all the other nodes, until wsrep_OSU_method is updated to TOI

     

  • Avatar
    testjetco

    I have checked all nodes' wsrep_OSU_method.  They are all TOI, the default values. 

    I have performed more testing on record deletion synchonization issue.

    When deleting 11MB data from table on one node and inserting a new record on another node, it required 13 second to sync all nodes.

    When deleting 22MB data from table on one node and inserting a new record on another node, it required 3 minutes to sync all nodes.

    Yesterday when I deleted 1GB data on one node, after more than 8 hours the nodes still could not be sync!!!

    After triggering the delete sql command, I queried all the nodes and found that they were all in 'synced' status:

         wsrep_local_state_comment = Synced

  • Avatar
    Vishal Kasle

    Can you please send the complete error logs from all the three nodes as well as the my.cnf. The wsrep_OSU_method does not affect imply for changes to data for tables but rather schema upgrades like ALTER, OPTIMIZE etc. It would be much clearer where the problem lies if we take a look at the error logs from all the three nodes while issue was occuring. 

     

     

  • Avatar
    testjetco

    Vishal, may I send the files to you by email?  Here is no way to attach files

  • Avatar
    testjetco

    Logs too large to be pasted here.

    I found those statements in err log:

    2018-12-04 11:37:19 2 [Note] The slave is applying a ROW event on behalf of a DELETE statement on table table_1 and is currently taking a considerable amount of time (61 seconds). This is due to the fact that it is scanning the table while looking up records to be processed. Consider adding a primary key (or unique key) to the table to improve performance.

  • Avatar
    Vishal Kasle

    Can you please create a ticket for this issue? we can discuss it there, also you will be able to attach the logs for further investigation. You can visit https://support.severalnines.com/hc/en-us and create a service request. The issue seems to be with the primary key as the delete will do a full table scan every time it is invoked and cause the cluster to stall. But we can be sure only from the error logs and also the configuration of the nodes in the clsuter. Hence can you please create a ticket and we can discuss in detail via the same. 

    Regards,

    Vishal 

  • Avatar
    Vishal Kasle

    Hello,

         After looking at the Galera documentation, deletes on tables without Primary Key is not permitted. It is one of the limitations of Galera. Please take a look at https://mariadb.com/kb/en/library/mariadb-galera-cluster-known-limitations/

    • All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.

    So once you add Primary key to the table the issue should be resolved.

     

     

    Regards,

    Vishal

  • Avatar
    testjetco

    I have tried table with primary key defined.  It works with deletion operation on cluster syc.

Please sign in to leave a comment.

Powered by Zendesk