How do top programming languages connect to MariaDB database on arm64 platform
Author: zhaorenhai
This article attempts to explore how several top programming languages connect to MariaDB database on the arm64 platform, and test whether the addition, deletion, modification, and query functions are normal. Include the following languages: C, Java, Node.js, Python, Go, Rust, PHP
The test platform uses Huawei Cloud’s Kunpeng virtual machine, and the OS uses Ubuntu 18.04. In addition, a MariaDB database has been deployed on another virtual machine in the same intranet in advance, and the database user name and password for remote connection have been created. Here assume that our new user name is proxysql and the password is proxypassword. Use the default port number 3306 for the port number.
C
First install the connector library
1
sudo apt install libmariadbclient-dev
Then create a new C language file version.cand enter the following code:
Next, we create a new testdb.cfile, enter the following code, and test the functions of adding new tables, inserting records, querying tables, associating queries, updating, deleting records, and deleting tables.
intmain(int argc, char **argv) { MYSQL *con = mysql_init(NULL);
if (con == NULL) { fprintf(stderr, "%s\n", mysql_error(con)); exit(1); }
if (mysql_real_connect(con, "192.168.0.204", "proxysql", "proxypassword", "testdb", 0, NULL, 0) == NULL) { finish_with_error(con); } if (mysql_query(con, "DROP TABLE IF EXISTS cars;")) { finish_with_error(con); } if (mysql_query(con, "DROP TABLE IF EXISTS people;")) { finish_with_error(con); } if (mysql_query(con, "CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), price INT)")) { finish_with_error(con); } if (mysql_query(con, "CREATE TABLE people(id INT, name VARCHAR(255), car_id INT)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(1,'Audi',52642)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(2,'Mercedes',57127)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(3,'Skoda',9000)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(4,'Volvo',29000)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(5,'Bentley',350000)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(6,'Citroen',21000)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(7,'Hummer',41400)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO cars VALUES(8,'Volkswagen',21600)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO people VALUES(1,'Jim',7)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO people VALUES(1,'Jim',8)")) { finish_with_error(con); } if (mysql_query(con, "INSERT INTO people VALUES(2,'Tom',6)")) { finish_with_error(con); } if (mysql_query(con, "SELECT * FROM cars")) { finish_with_error(con); } MYSQL_RES *result = mysql_store_result(con); if (result == NULL) { finish_with_error(con); } int num_fields = mysql_num_fields(result); MYSQL_ROW row; while ((row = mysql_fetch_row(result))) { for(int i = 0; i < num_fields; i++) { printf("%s ", row[i] ? row[i] : "NULL"); }
printf("\n"); } mysql_free_result(result); if (mysql_query(con, "update cars set price = 42400 where name = 'Hummer'")) { finish_with_error(con); } if (mysql_query(con, "SELECT people.name,cars.name,cars.price FROM cars,people where cars.id = people.car_id")) { finish_with_error(con); } result = mysql_store_result(con); if (result == NULL) { finish_with_error(con); } num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { for(int i = 0; i < num_fields; i++) { printf("%s ", row[i] ? row[i] : "NULL"); }
printf("\n"); } mysql_free_result(result); if (mysql_query(con, "delete from people where id = 1")) { finish_with_error(con); } if (mysql_query(con, "drop table people;")) { finish_with_error(con); } if (mysql_query(con, "drop table cars;")) { finish_with_error(con); } mysql_close(con); exit(0); }
1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 Jim Hummer 42400 Jim Volkswagen 21600 Tom Citroen 21000
According to the above test, it can be found that C language connects to MariaDB database, adds, deletes, changes and querys, creates tables, deletes tables, and other functions are all normal on the arm64 platform.
The above test just demonstrates the installation of the MariaDB Connector from the OS software source. If you want to use the latest version of the Connector, you can also refer to the official documentation to compile the latest version: https://mariadb.com/kb/en/mariadb- connector-c/
In addition, MariaDB’s C connector project is issued by LGPL’s license.
Java
First, make sure that the latest versions of OpenJDK and Maven have been installed on the test virtual machine.
The above is just a test of relatively simple functions. Below we test the connection pool function and perform functions such as adding, deleting, modifying, and query.
publicstaticvoidmain( String[] args )throws SQLException { //option "pool" must be set to indicate that pool has to be used String connectionString = "jdbc:mariadb://192.168.0.204/testdb?user=proxysql&password=proxypassword&maxPoolSize=10&pool";
try (Connection connection = DriverManager.getConnection(connectionString)) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("DROP TABLE IF EXISTS cars;"); stmt.executeQuery("CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), price INT)"); stmt.executeQuery("INSERT INTO cars VALUES(1,'Audi',52642)"); stmt.executeQuery("INSERT INTO cars VALUES(2,'Mercedes',57127)"); rs = stmt.executeQuery("SELECT * FROM cars"); rs.next(); System.out.println(rs.getString(2)); } }
try (Connection connection = DriverManager.getConnection(connectionString)) { try (Statement stmt = connection.createStatement()) { stmt.executeQuery("update cars set name = 'VolksWagen' where id = 1"); stmt.executeQuery("delete from cars where id = 2"); ResultSet rs = stmt.executeQuery("SELECT * FROM cars"); rs.next(); System.out.println(rs.getString(2)); } } } }
Compile and execute, output successfully
1 2
Audi VolksWagen
After the above test, the connection pool function and the addition, deletion, modification, and checking functions are also normal.
Java’s connector functions are relatively rich, and it also implements load balancing or read-write separation for clusters or primary-replica databases. Next we test the read-write separation function. Before doing so, please make sure that you have set up the MariaDB primary-replica database environment.
Change App.java to the following code. Note that the replication keyword is added to the jdbc connection string, which represents primary-replica replication. If it is in other load balancing environments, keywords such as loadbalance are also supported. For details, refer to the official MariaDB documentation. Also note that in the connection string, the primary database address is first and the replica database address is last. When the connection attribute is changed to ReadOnly, the statement will go to the replica database to query.
The above demonstrated project uses Maven as an example. If you are not using Maven, you can also refer to the instructions of other tools in the official MariaDB documentation: https://mariadb.com/kb/en/mariadb-connector-j/
MariaDB Java connector is also released under the LGPL agreement.
Python
First make sure that Python3 and pip3 have been installed on the test virtual machine.
To connect MariaDB in Python, you need to install the C language connector first, you can execute the following command to install
1
apt install libmariadb-dev
However, the current version of the mariadb package that comes with the current Ubuntu 18.04 version is older and cannot meet the python version requirements. We compile one from the source code.
Execute the following command to download the connector source code and unzip it
We must firstly install nodejs and npm. The mariadb connector requires at least nodejs version 10.13 or above, so we download an arm64 binary package from the official website and install it manually
1 2 3 4 5 6
wget https://nodejs.org/dist/v14.15.0/node-v14.15.0-linux-arm64.tar.xz xz -d node-v14.15.0-linux-arm64.tar.xz tar -xf node-v14 .15.0-linux-arm64.tar cd node-v14.15.0-linux-arm64/bin sudo ln -s `pwd`/node/usr/local/bin/ sudo ln -s `pwd`/npm/usr/local/bin/
Then install the mariadb connector
1
sudo npm install mariadb
Then edit a testmariadb.jsfile with the following content:
The code for PHP to connect to MariaDB and to connect to Mysql is the same.
First install the php and mysql drivers on the test machine:
1 2
sudo apt install php sudo apt install php-mysql
Then edit a testmariadb.phpfile
1 2 3 4 5 6 7
<?php $mysqli = new mysqli("192.168.0.204", "proxysql", "proxypassword", "testdb"); $mysqli->query("insert into cars values(7,'Hummer',41400)"); $result = $mysqli->query("SELECT * FROM cars where id = 7"); $row = $result->fetch_assoc(); echo htmlentities($row['name']); ?>
Execute:
1
php testmariadb.php
Output
1
Hummer
For PHP, we only tested two simple scenarios, insert and query, and proved that PHP is feasible to connect to MariaDB on the arm64 platform. For other information about PHP connecting mysql and mariadb databases, you can refer to the following link:
rows, err := DB.Query("SELECT * from cars") if err != nil{ fmt.Println("query fail") } for rows.Next(){ var id int var name string var price int err := rows.Scan(&id, &name, &price) if err != nil { fmt.Println("rows fail") } fmt.Printf("%v %q %v \n", id, name, price) } }
Above we tested the connection pool, query, transaction, insert and other functions of the MariaDB connector in the Go language, and they were all successful. For other functions, you can refer to the official website of the go language or other related websites.
Rust
Rust connects to MariaDB in the same way as connects to Mysql.
First install the latest version of Rust:
1
curl --proto '=https' --tlsv1.2 https://sh.rustup.rs -sSf | sh
After installation, you need to $HOME/.cargo/binadd it to the PATH environment variable
For the current environment, we execute the following commands to take effect temporarily:
1
source $HOME/.cargo/env
Then execute the following command to create a new project:
1
cargo new mariadb_test
then
1
cd mariadb_test
Edit the Cargo.tomlconfiguration file and [dependencies]add the following below to set the driver version:
fntestmariadb() -> std::result::Result<std::string::String, mysql::Error> { let url = "mysql://proxysql:proxypassword@192.168.0.204:3306/testdb";
let pool = Pool::new(url)?; letmut conn = pool.get_conn()?; conn.exec_drop(r"delete from cars",())?; let cars = vec![ Car { id: 1, name: "Audi".into(), price: 52642 }, Car { id: 2, name: "Volkswagen".into(), price: 21600 }, Car { id: 3, name: "Skoda".into(), price: 9000 }, ]; conn.exec_batch( r"INSERT INTO cars (id, name, price) VALUES (:id, :name, :price)", cars.iter().map(|p| params! { "id" => p.id, "name" => &p.name, "price" => p.price, }) )?; let selected_cars = conn .query_map( "SELECT id, name, price from cars", |(id, name, price)| { Car { id, name, price } }, )?; assert_eq!(cars, selected_cars); println!("Yay!"); Ok("Yay!".into())
}
fnmain() { testmariadb(); }
We tested the connection pool function, and also tested the delete, insert, query and other functions. After a batch query, the query results are put into a list and compared with the data list before inserting. If they are equal, then printYay!
In the mariadb_testdirectory, execute the following commands to compile and run
1
cargo run
Output Yay!, run successfully.
For other functions of the Rust Mysql connector, please refer to:
We tested C, Java, Node.js, Python, PHP, Go, Rust programming languages. All of them can successfully connect to MariaDB database on the arm64 platform, and the usage is the same as the x86 platform.