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:

1
2
3
4
5
6
7
8
9
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>

int main(int argc, char **argv)
{
printf("MySQL client version: %s\n", mysql_get_client_info());
exit(0);
}

Compile and execute:

1
2
gcc version.c -o version  `mysql_config --cflags --libs`
./version

You can see the output as follows:

1
MySQL client version: 10.1.47-MariaDB

It shows that the client version can be printed out successfully.

Next, we create a new createdb.cfile, enter the following code, test the function of creating a database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>

int main(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",
NULL, 0, NULL, 0) == NULL)
{
fprintf(stderr, "%s\n", mysql_error(con));
mysql_close(con);
exit(1);
}

if (mysql_query(con, "CREATE DATABASE testdb"))
{
fprintf(stderr, "%s\n", mysql_error(con));
mysql_close(con);
exit(1);
}

mysql_close(con);
exit(0);
}

Compile and execute

1
2
gcc createdb.c -o createdb  `mysql_config --cflags --libs`
./createdb

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>

void finish_with_error(MYSQL *con)
{
fprintf(stderr, "%s\n", mysql_error(con));
mysql_close(con);
exit(1);
}

int main(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);
}

Compile and execute:

1
2
gcc testdb.c -o testdb  `mysql_config --cflags --libs`
./testdb

The output is as follows:

1
2
3
4
5
6
7
8
9
10
11
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.

Create a sample project with Maven:

1
2
mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId=my-app 
-DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

Then in the generated pom.xmldependencies module, add the following content:

1
2
3
4
5
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.7.0</version>
</dependency>

Then edit src/main/java/com/mycompany/appthe App.javafile in the directory , the content is as follows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.mycompany.app;

import java.sql.*;

public class App {

public static void main( String[] args ) throws SQLException {
try (Connection conn = DriverManager.getConnection("jdbc:mariadb://192.168.0.204/", "proxysql", "proxypassword")) {
// create a Statement
try (Statement stmt = conn.createStatement()) {
//execute query
try (ResultSet rs = stmt.executeQuery("SELECT 'Hello World!'")) {
//position result to first
rs.first();
System.out.println(rs.getString(1)); //result is "Hello World!"
}
}
}
}
}

Then execute the following command to compile

1
mvn install

Execute the following command to execute

1
mvn exec:java -Dexec.mainClass="com.mycompany.app.App"

The output is Hello Word!

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.

The revised App.javacontent is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package com.mycompany.app;

import java.sql.*;

public class App {

public static void main( 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.mycompany.app;

import java.sql.*;

public class App {

public static void main( String[] args ) throws SQLException {
String connectionString = "jdbc:mysql:replication://192.168.0.204,192.168.0.64/testdb?user=proxysql&password=proxypassword&maxPoolSize=10&pool";

try (Connection connection = DriverManager.getConnection(connectionString)) {
try (Statement stmt = connection.createStatement()) {
stmt.executeQuery("INSERT INTO cars VALUES(2,'Mercedes',57127)");
connection.setReadOnly(true);
ResultSet rs = stmt.executeQuery("SELECT * FROM cars");
rs.next();
System.out.println(rs.getString(2));
}
}
}
}

Compile and execute, the output is as follows

1
VolksWagen

execution succeed.

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

1
2
wget https://downloads.mariadb.org/interstitial/connector-c-3.1.11/mariadb-connector-c-3.1.11-src.zip
unzip mariadb-connector-c-3.1.11-src.zip

Start compiling.

Create a separate compilation directory, compile in this directory, and install

1
2
3
4
5
6
7
mkdir build-mariadb-connector-c
cd build-mariadb-connector-c
cmake ../mariadb-connector-c-3.1.11-src -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=/usr/local
make
make install
echo "/usr/local/lib/mariadb/" > /etc/ld.so.conf.d/mariadb.conf
ldconfig

Then execute the following command to install the Python connector:

1
pip3 install mariadb

Next, we directly test the connection pool function, and simply test the query and new functions.

Edit the following code and name it to testmariadb.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import mariadb

pool = mariadb.ConnectionPool(
user="proxysql",
password="proxypassword",
host="192.168.0.204",
port=3306,
pool_name="web-app",
pool_size=20
)

try:
pconn = pool.get_connection()
cur = pconn.cursor()
data = [(3, 'Skoda', 9000),(4, 'Volvo', 29000),(5, 'Bently', 350000)]
cur.executemany("INSERT INTO testdb.cars(id, name, price) VALUES (?, ?, ?)", data)
cur.execute("select * from testdb.cars")
cars = []
for (id,name,price) in cur:
cars.append(f"{id} {name} {price}")
print("\n".join(cars))
except mariadb.PoolError as e:
# Report Error
print(f"Error opening connection from pool: {e}")

Execute:

1
python3 testmariadb.py

The output is as follows

1
2
3
4
5
1 VolksWagen 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bently 350000

For other functions of the Python connector, you can refer to the following link: https://mariadb.com/docs/appdev/connector-python/

Python connector is also LGPL agreement.

Node.js

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
const mariadb = require('mariadb');
const pool = mariadb.createPool({
host:'192.168.0.204',
user:'proxysql',
password: 'proxypassword',
connectionLimit: 5
});
async function asyncFunction() {
let conn;
try {
conn = await pool.getConnection();
const res = await conn.query("INSERT INTO testdb.cars value (?, ?, ?)", [6,'Citroen',21000]);
console.log(res);
const rows = await conn.query("SELECT * from testdb.cars");
console.log(rows);

} catch (err) {
throw err;
} finally {
if (conn) return conn.end();
}
}
asyncFunction();

Then execute

1
node testmariadb.js

The output is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
OkPacket { affectedRows: 1, insertId: 6, warningStatus: 0 }
[
{ id: 1, name: 'VolksWagen', price: 52642 },
{ id: 2, name: 'Mercedes', price: 57127 },
{ id: 3, name: 'Skoda', price: 9000 },
{ id: 4, name: 'Volvo', price: 29000 },
{ id: 5, name: 'Bently', price: 350000 },
{ id: 6, name: 'Citroen', price: 21000 },
meta: [
ColumnDef {
_parse: [StringParser],
collation: [Collation],
columnLength: 11,
columnType: 3,
flags: 16899,
scale: 0,
type: 'LONG'
},
ColumnDef {
_parse: [StringParser],
collation: [Collation],
columnLength: 1020,
columnType: 253,
flags: 0,
scale: 0,
type: 'VAR_STRING'
},
ColumnDef {
_parse: [StringParser],
collation: [Collation],
columnLength: 11,
columnType: 3,
flags: 0,
scale: 0,
type: 'LONG'
}
]
]

The connector of MariaDB Node.js also uses the LGPL license.

For other information about Node.js connectors, please refer to: https://mariadb.com/kb/en/nodejs-connector/

PHP

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:

https://www.php.net/manual/en/mysql.php

Go

Connecting to MariaDB database in Go language is the same as connecting to Mysql database.

First install the Mysql driver of the Go language, (the protocol of this driver is MPL2.0, which is also a relatively loose protocol)

1
go get -u github.com/go-sql-driver/mysql

Edit the testmariadb.gofile, the content is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package main

import "fmt"
import "strings"
import "database/sql"
import _ "github.com/go-sql-driver/mysql"

func main() {
const (
userName = "proxysql"
password = "proxypassword"
ip = "192.168.0.204"
port = "3306"
dbName = "testdb"
)
var DB *sql.DB
path := strings.Join([]string{userName, ":", password, "@tcp(",ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

DB, _ = sql.Open("mysql", path)
DB.SetConnMaxLifetime(100)
DB.SetMaxIdleConns(10)
if err := DB.Ping(); err != nil{
fmt.Println("open database fail")
return
}
fmt.Println("connnect success")
tx, err := DB.Begin()
if err != nil{
fmt.Println("tx fail")
return
}
stmt, err := tx.Prepare("INSERT INTO cars VALUES (?,?,?)")
if err != nil{
fmt.Println("Prepare fail")
return
}
res, err := stmt.Exec(8, "Mercedes", 57127)
if err != nil{
fmt.Println("Exec fail")
return
}
tx.Commit()
fmt.Println(res.LastInsertId())

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)
}
}

Then execute

1
go run testmariadb.go

The output is as follows:

1
2
3
4
5
6
7
8
9
10
connnect success
8 <nil>
1 "VolksWagen" 52642
2 "Mercedes" 57127
3 "Skoda" 9000
4 "Volvo" 29000
5 "Bently" 350000
6 "Citroen" 21000
7 "Hummer" 41400
8 "Mercedes" 57127

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:

1
mysql = "20.0.1"

Then edit the src/main.rsfile:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
use mysql::*;
use mysql::prelude::*;

#[derive(Debug, PartialEq, Eq)]
struct Car {
id: i32,
name: String,
price: i32,
}

fn testmariadb() -> std::result::Result<std::string::String, mysql::Error> {
let url = "mysql://proxysql:proxypassword@192.168.0.204:3306/testdb";

let pool = Pool::new(url)?;

let mut 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())

}

fn main() {
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:

https://docs.rs/mysql/20.0.1/mysql/

In addition, Rust has a relatively new asynchronous connector. For details, please refer to the following two links:

https://docs.rs/mysql_async/0.25.0/mysql_async/

https://github.com/blackbeam/mysql_async

Rust’s connector uses the MIT/Apache2.0 license.

Summary

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.

Reference link:

http://zetcode.com/db/mysqlc/

https://mariadb.com/kb/en/mariadb-connector-c/

https://mariadb.com/kb/en/mariadb-connector-j/

https://mariadb.com/docs/appdev/connector-python/

https://mariadb.com/kb/en/nodejs-connector/

https://www.php.net/manual/en/mysql.php

https://golang.org/pkg/database/sql

https://github.com/go-sql-driver/mysql

https://doc.rust-lang.org/book/title-page.html

https://docs.rs/mysql/20.0.1/mysql/

https://docs.rs/mysql_async/0.25.0/mysql_async/

https://github.com/blackbeam/mysql_async

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×