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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
DML usually means Data Manipulation Language.

Drop an update about what DML means in context of your question.
0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
0
ste5anSenior DeveloperCommented:
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.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 !
0
Tomas Helgi JohannssonCommented:
Hi,

Usually, a seamless failover is a "joint operation" of both client and the database cluster itself.
It is the database client responsibility to reconnect "stale" connections in its connection pool to a functional node of a database cluster.
This means that if the database client doesn't have this technology to do the switch then that responsibility falls on the application that uses the client. This involves correct manipulation of "connection errors" and retries of transactions if possible.
The database cluster is responsible for replication of all committed UOW to all members of the cluster.
The mariaDB client (Java connector ) handles this if configured approprietly as well as the MariaDB MaxScale

Regards,
    Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Tomas Helgi JohannssonCommented:
>"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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Tomas Helgi JohannssonCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 !
0
Tomas Helgi JohannssonCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Tomas Helgi JohannssonCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MariaDB

From novice to tech pro — start learning today.