Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

DML test for MariaDB.

hi,

right now what to do some DML test for MariaDB and see what will happen when large amount of data is inserting to the mariaDB but DURing that time MariaDB is failover, will data keep deploying to the new master or hanged, dropped.

MysQL innoDB cluster can't handle it and transaction will dropped by returning a message by saying connection dropped ! not what I am expecting.

I run this on MySQL and it works but not in MariaDB, any reason:

CREATE TABLE batchdatatest(id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);

DELIMITER $$

CREATE PROCEDURE prepare_data()

  BEGIN
    
  DECLARE i INT DEFAULT 100;

  WHILE i < 10000 DO
       INSERT INTO batchdatatest(val) VALUES (i);
         SET i = i + 1;
     
  END WHILE;

END$$

DELIMITER ;

Open in new window


error is :

MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN
    
  DECLARE i INT DEFAULT 100;

  WHILE i < 10000 DO
       INSERT' at line 5

Open in new window


any reason ?
Avatar of David Favor
David Favor
Flag of United States of America image

DML usually means Data Manipulation Language.

Drop an update about what DML means in context of your question.
Reading your question. Unsure exactly what you've tried that failed.

Generally, if the data payload is in mid flight (transaction still open), then as soon as the transaction is interrupted on instance where transaction is running, all transactions on all other instances will stop too.

Your question sounds like maybe you tried this + had some different behavior.

If you did, post the exact error message which occurred.
As far as I understand you:

How should the failover partner know that the last transaction was committed? And how should it have access to it?

See Failover and High availability with MariaDB Connector/J:
When a failover occur the connector cannot know if the last request has been received by the database server and executed. Applications may have failover design to handle these particular cases: If the application was in autoCommit mode (not recommended), the last query may have been executed and committed. The application will have no possibility to know that but the application will be functional. If not in autoCommit mode, the query has been launched in a transaction that will not be committed. Depending of what caused the exception, the host may have the connection open on his side during a certain amount of time. Take care of transaction isolation level that may lock too much rows.
Avatar of marrowyung
marrowyung

ASKER

David Favor,

"DML usually means Data Manipulation Language."

yes, I meant this.

I mean I run that query to test it on MariaDB and MySQL and see what the behavior will be, transaction keep going or drop. that script doesn't work with that error message.

the reason I post the script is, that script can only run on MySQL but not MariaDB, I can't see why!


I yesterday test it using other long long script and it works!

https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap

Open in new window


In mySQL innodb cluster it is a drop of connection, for MariaDB, it keep going on the new node. MariaDB has session level failover.
ste5an,

"
How should the failover partner know that the last transaction was committed?"

the cluster should handle this right ? as i said MariaDB can ! probably I handle it via their proxy! the maxscale.

when cluster detect primary node dead, cluster should detect it and do a transaction session level failover, if no session level failover, at least rollback and roll forward the transaction.

 I will try again on MariaDB cluster level, without the maxscale, if this can be done!

yesterday I just reboot the write master and the DML operation keeps going ! and finally all nodes records matched !
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thomas,

"
Usually, a seamless failover is a "joint operation" of both client and the database cluster itself. "

yes! but at least it can roll back and roll forward on new write master.

both MySQL InnoDB cluster and MariaDB can't ! but with Maxscale, MariaDB can ! the proxy can handle it for you.

In MSSQL, it also can handle it but when the master is rebooting, the query will hangs and when new master is ready, the query keep running.

in this case i think the cluster handle the roll back and forward. or the MS SQL client automatically handle it.

"This involves correct manipulation of "connection errors" and retries of transactions if possible.
"
yes, this is what MySQL can only do!

"The mariaDB client (Java connector ) handles this if configured approprietly as well as the MariaDB MaxScale"

you mean ANY of them ?
>"This involves correct manipulation of "connection errors" and retries of transactions if possible.
>"
>yes, this is what MySQL can only do!
And MariaDB

>"The mariaDB client (Java connector ) handles this if configured approprietly as well as the MariaDB MaxScale"

>you mean ANY of them ?
See my previous comment. Java Connector and MaxScale both have failover capabilities.

Regards,
    Tomas Helgi
"See my previous comment. Java Connector and MaxScale both have failover capabilities. "

but I am surprise proxySQL for MySQL can't do it, what do you think ?
Hi,

>but I am surprise proxySQL for MySQL can't do it, what do you think ?
Again, see my previous comment (the first 2 paragraphs).
Have you looked at the MySQL-Router 8.0 ?

Regards,
     Tomas Helgi
"Have you looked at the MySQL-Router 8.0 ? "

I have set it up already! and my result based on that.

MariaDB + Maxscale is doing what Oracle 12c is doing, transaction level failover.

amazing !
Hi,

>MariaDB + Maxscale is doing what Oracle 12c is doing, transaction level failover.

If the database and/or a database client or middle tier applications (like MariaDB MaxScale ) do not handle seamless transaction failover when a database fails,  then the failover burden (failover logic and code) falls on the application connecting to the database cluster. Resulting in "heavier" (as oposite of lightweight) application.

Regards,
     Tomas Helgi
"Resulting in "heavier" (as oposite of lightweight) application.
"

Any workaround on this.

as I said, I am happy to see Maxscale can do it for mariaDB.
Hi,

>Any workaround on this.

No , unless you code the failover logic in your application.
In general (as I stated before) if your database and/or middletier app don't have the failover feature then it
is the responsibility of the application to handle such logic.
The failover feature of MaxScale/MariaDB ConnnectorJ client and the MariaDB database/Galera Cluster is an approach to take
that logical burden from the connecting application.

Regards,
     Tomas Helgi
"The failover feature of MaxScale/MariaDB ConnnectorJ client and the MariaDB database/Galera Cluster is an approach to take
that logical burden from the connecting application.
"

proxySQL can't !

but maxscale can.
tks all.