Explore all Change Data Capture open source software, libraries, packages, source code, cloud functions and APIs.

Popular New Releases in Change Data Capture

libusb

v1.0.26: libusb 1.0.26

tinyusb

0.13.0

usbguard

usbguard-1.1.0

pglogical

pglogical 2.4.1

luna

LUNA hardware r0.4

Popular Libraries in Change Data Capture

debezium

by debezium doticonjavadoticon

star image 6573 doticonNOASSERTION

Change data capture for a variety of databases. Please log issues at https://issues.redhat.com/browse/DBZ.

libusb

by libusb doticoncdoticon

star image 3692 doticonLGPL-2.1

A cross-platform library to access USB devices

tinyusb

by hathach doticoncdoticon

star image 2569 doticonMIT

An open source cross-platform USB stack for embedded system

bottledwater-pg

by confluentinc doticoncdoticon

star image 1505 doticonApache-2.0

Change data capture from PostgreSQL into Kafka

WHID

by whid-injector doticonc++doticon

star image 1133 doticonMIT

WiFi HID Injector - An USB Rubberducky / BadUSB On Steroids.

DataLink

by ucarGroup doticonjavadoticon

star image 926 doticonApache-2.0

DataLink是一个满足各种异构数据源之间的实时增量同步、离线全量同步,分布式、可扩展的数据交换平台。

v-usb

by obdev doticoncdoticon

star image 919 doticon

A Firmware-Only USB implementation for Atmel's AVR Microcontrollers

android-keyboard-gadget

by pelya doticoncdoticon

star image 827 doticonApache-2.0

Convert your Android device into USB keyboard/mouse, control your PC from your Android device remotely, including BIOS/bootloader.

DigiSpark-Scripts

by CedArctic doticonc++doticon

star image 800 doticonMIT

USB Rubber Ducky type scripts written for the DigiSpark.

Trending New libraries in Change Data Capture

android-usb-gadget

by tejado doticonjavadoticon

star image 202 doticonGPL-3.0

Convert your Android phone to any USB device you like! USB Gadget Tool allows you to create and activate USB device roles, like a mouse or a keyboard. 🛠🛡📱

debezium-ui

by debezium doticontypescriptdoticon

star image 154 doticonApache-2.0

A web UI for Debezium; Please log issues at https://issues.redhat.com/browse/DBZ.

fpx

by oxplot doticoncdoticon

star image 143 doticonBSD-3-Clause

easy USB-C power for all your devices

usbip

by jiegec doticonrustdoticon

star image 82 doticonMIT

A Rust library to run a USB/IP server

kafka-connect-http

by castorm doticonjavadoticon

star image 74 doticonApache-2.0

Kafka Connect connector that enables Change Data Capture from JSON/HTTP APIs into Kafka.

Microservices data exchange with change data capture and outbox pattern.

SPIN

by Quarren42 doticonhtmldoticon

star image 47 doticonCC-BY-SA-4.0

12 key, 3 encoder USB-C Macro Pad

USBKeylogger

by justcallmekoko doticoncdoticon

star image 47 doticonMIT

A WiFi enabled USB Keylogger and Keystroke injection tool

Tinyduck

by justcallmekoko doticonc++doticon

star image 43 doticonMIT

The super tiny USB Rubber Ducky

Top Authors in Change Data Capture

1

todbot

4 Libraries

star icon96

2

majbthrd

4 Libraries

star icon23

3

debezium

3 Libraries

star icon6969

4

padelt

2 Libraries

star icon186

5

mvirkkunen

2 Libraries

star icon215

6

circuitvalley

2 Libraries

star icon16

7

zhuhuijia0001

2 Libraries

star icon27

8

DamonOehlman

2 Libraries

star icon8

9

weston-embedded

2 Libraries

star icon56

10

NordicPlayground

2 Libraries

star icon13

1

4 Libraries

star icon96

2

4 Libraries

star icon23

3

3 Libraries

star icon6969

4

2 Libraries

star icon186

5

2 Libraries

star icon215

6

2 Libraries

star icon16

7

2 Libraries

star icon27

8

2 Libraries

star icon8

9

2 Libraries

star icon56

10

2 Libraries

star icon13

Trending Kits in Change Data Capture

No Trending Kits are available at this moment for Change Data Capture

Trending Discussions on Change Data Capture

sys.sp_cdc_stop_job not on server

Event Hub: org.apache.spark.sql.AnalysisException: Required attribute 'body' not found

Replicate MySQL Data to ClickHouse

Does multi-table Debezium connector guarantee the ordering across change tracking tables?

AWS DMS CDC - Only capture changed values not entire record? (Source RDS MySQL)

How to connect with a file socket and not with the TCP protocol?

AWS AppFlow Salesforce to Redshift Error Creating Connection

Escaping Java variables to serialise SQL statement to string

R2DBC can be used for change data capture in Spring boot?

Nested variant updating and deleting in snowflake

QUESTION

sys.sp_cdc_stop_job not on server

Asked 2021-Oct-22 at 11:05

How to restart capture job (for Change Data Capture), if I don't have this procedure - sys.sp_cdc_stop_job on the server? (using Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 18 2021 19:01:34 Copyright (C) 2019 Microsoft Corporation ) Need to restart it to reflect my changes in configuration by sp_cdc_change_job.

ANSWER

Answered 2021-Oct-22 at 11:05

In Azure SQL Database the capture and cleanup SQL Server Agent jobs are replaced by a change data capture scheduler that periodically invokes stored procedures to capture and cleanup of the change tables. This scheduler runs stored procedures automatically.

You can check this document to understand how the Capture job initiates the running of stored procedures.

Source https://stackoverflow.com/questions/69662787

QUESTION

Event Hub: org.apache.spark.sql.AnalysisException: Required attribute 'body' not found

Asked 2021-Sep-18 at 12:04

I am trying to write change data capture into EventHub as:

1df = spark.readStream.format("delta") \
2  .option("readChangeFeed", "true") \
3  .option("startingVersion", 0) \
4  .table("cdc_test1")
5

While writing to azure eventhub it expects the content into a body attribute:

1df = spark.readStream.format("delta") \
2  .option("readChangeFeed", "true") \
3  .option("startingVersion", 0) \
4  .table("cdc_test1")
5df.writeStream.format("eventhubs").option("checkpointLocation", checkpointLocation).outputMode("append").options(**ehConf).start()
6

It gives exception as

1df = spark.readStream.format("delta") \
2  .option("readChangeFeed", "true") \
3  .option("startingVersion", 0) \
4  .table("cdc_test1")
5df.writeStream.format("eventhubs").option("checkpointLocation", checkpointLocation).outputMode("append").options(**ehConf).start()
6org.apache.spark.sql.AnalysisException: Required attribute 'body' not found.
7    at org.apache.spark.sql.eventhubs.EventHubsWriter$.$anonfun$validateQuery$2(EventHubsWriter.scala:53)
8

I am not sure how to wrap whole stream into a body. I think, I need another stream object which has a column body with value of "df"(original stream) as string. I am not able to achieve this. Please help !

ANSWER

Answered 2021-Sep-18 at 12:04

You just need to create this column by using functions struct (to encode all columns as one object) and something like to_json (to create a single value from the object - you can use other functions, like, to_csv, or to_avro, but it will depend on the contract with consumers). The code could look as following:

1df = spark.readStream.format("delta") \
2  .option("readChangeFeed", "true") \
3  .option("startingVersion", 0) \
4  .table("cdc_test1")
5df.writeStream.format("eventhubs").option("checkpointLocation", checkpointLocation).outputMode("append").options(**ehConf).start()
6org.apache.spark.sql.AnalysisException: Required attribute 'body' not found.
7    at org.apache.spark.sql.eventhubs.EventHubsWriter$.$anonfun$validateQuery$2(EventHubsWriter.scala:53)
8df.select(F.to_json(F.struct("*")).alias("body"))\
9    .writeStream.format("eventhubs")\
10    .option("checkpointLocation", checkpointLocation)\
11    .outputMode("append")\
12    .options(**ehConf).start()
13

Source https://stackoverflow.com/questions/69231060

QUESTION

Replicate MySQL Data to ClickHouse

Asked 2021-Aug-17 at 14:01

I want to periodically insert data from an MySQL database into clickHouse, i.e., when data is added/updated in mySQL database, I want that data to be added automatically to clickHouse.

I am thinking of using the Change Data Capture (CDC). CDC is a technique that captures changes made to data in MySQL and applies it to the destination ClickHouse table. It only imports changed data, not the entire database. To use the CDC method with a MySQL database, we must utilize the Binary Change Log (binlog). Binlog allows us to capture change data as a stream, enabling near real-time replication.

Binlog not only captures data changes (INSERT, UPDATE, DELETE) but also table schema changes such as ADD/DROP COLUMN. It also ensures that rows deleted from MySQL are also deleted in ClickHouse.

After having the changes, How can I insert it in the ClickHouse?

Source https://stackoverflow.com/questions/68806305

QUESTION

Does multi-table Debezium connector guarantee the ordering across change tracking tables?

Asked 2021-Aug-06 at 19:44

I'm trying to build an event aggregation solution that follows a pattern of change data capture (CDC) in MS SQL Server + Debezium MS SQL Server Connector to publish CDC as events to a kafka topic + aggregator service that consumes CDC events, aggregates them and publishes the aggregates to a different topic.

At the moment, I'm looking for a definitive answer to a question if Debezium`s SQL Server connector, that is tracking more than a single table and uses topic routing transform to route all the messages to the same topic, guarantees that it will publish events from all the tracked tables in a "global" order (like if change to table A happened before change to table B, event about the change in table A is guaranteed to be published first).

I've read through the docs here and did some testing and it seems like it is the guarantee that Debezium provides, but the wording in the docs is a little ambiguous.

ANSWER

Answered 2021-Aug-06 at 10:04

The connector sorts the changes that it reads in ascending order, based on the values of their commit LSN and change LSN. This sorting order ensures that the changes are replayed by Debezium in the same order in which they occurred in the database.

In simpler words, it is based on the order of change if it is within the same transaction. If it is not, it will be based on the time the transaction is completed.

Source https://stackoverflow.com/questions/68663381

QUESTION

AWS DMS CDC - Only capture changed values not entire record? (Source RDS MySQL)

Asked 2021-Jul-26 at 07:45

I have a DMS CDC task set (change data capture) from a MySQL database to stream to a Kinesis stream which a Lambda is connected to.

I was hoping to ultimately receive only the value that has changed and not on entire dump of the row, this way I know what column is being changed (at the moment it's impossible to decipher this without setting up another system to track changes myself).

Example, with the following mapping rule:

1     {
2        "rule-type": "selection",
3        "rule-id": "1",
4        "rule-name": "1",
5        "object-locator": {
6            "schema-name": "my-schema",
7            "table-name": "product"
8        },
9        "rule-action": "include",
10        "filters": []
11    },
12

and if I changed the name property of a record on the product table, I would hope to recieve a record like this:

1     {
2        "rule-type": "selection",
3        "rule-id": "1",
4        "rule-name": "1",
5        "object-locator": {
6            "schema-name": "my-schema",
7            "table-name": "product"
8        },
9        "rule-action": "include",
10        "filters": []
11    },
12{
13    "data": {
14        "name": "newValue"
15    },
16    "metadata": {
17        "timestamp": "2021-07-26T06:47:15.762584Z",
18        "record-type": "data",
19        "operation": "update",
20        "partition-key-type": "schema-table",
21        "schema-name": "my-schema",
22        "table-name": "product",
23        "transaction-id": 8633730840
24    }
25}
26

However what I actually recieve is something like this:

1     {
2        "rule-type": "selection",
3        "rule-id": "1",
4        "rule-name": "1",
5        "object-locator": {
6            "schema-name": "my-schema",
7            "table-name": "product"
8        },
9        "rule-action": "include",
10        "filters": []
11    },
12{
13    "data": {
14        "name": "newValue"
15    },
16    "metadata": {
17        "timestamp": "2021-07-26T06:47:15.762584Z",
18        "record-type": "data",
19        "operation": "update",
20        "partition-key-type": "schema-table",
21        "schema-name": "my-schema",
22        "table-name": "product",
23        "transaction-id": 8633730840
24    }
25}
26{
27    "data": {
28        "name": "newValue",
29        "id": "unchangedId",
30        "quantity": "unchangedQuantity",
31        "otherProperty": "unchangedValue"
32    },
33    "metadata": {
34        "timestamp": "2021-07-26T06:47:15.762584Z",
35        "record-type": "data",
36        "operation": "update",
37        "partition-key-type": "schema-table",
38        "schema-name": "my-schema",
39        "table-name": "product",
40        "transaction-id": 8633730840
41    }
42}
43

As you can see when receiving this, it's impossible to decipher what property has changed without setting up additional systems to track this.

I've found another stackoverflow thread where someone is posting an issue because their CDC is doing what I want mine to do. Can anyone point me into the right direction to achieve this?

ANSWER

Answered 2021-Jul-26 at 07:45

I found the answer after digging into AWS documentation some more.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Kinesis.html#CHAP_Target.Kinesis.BeforeImage

Different source database engines provide different amounts of information for a before image:

  • Oracle provides updates to columns only if they change.

  • PostgreSQL provides only data for columns that are part of the primary key (changed or not).

  • MySQL generally provides data for all columns (changed or not).

I used the BeforeImageSettings on the task setting to include the original data with payloads.

1     {
2        "rule-type": "selection",
3        "rule-id": "1",
4        "rule-name": "1",
5        "object-locator": {
6            "schema-name": "my-schema",
7            "table-name": "product"
8        },
9        "rule-action": "include",
10        "filters": []
11    },
12{
13    "data": {
14        "name": "newValue"
15    },
16    "metadata": {
17        "timestamp": "2021-07-26T06:47:15.762584Z",
18        "record-type": "data",
19        "operation": "update",
20        "partition-key-type": "schema-table",
21        "schema-name": "my-schema",
22        "table-name": "product",
23        "transaction-id": 8633730840
24    }
25}
26{
27    "data": {
28        "name": "newValue",
29        "id": "unchangedId",
30        "quantity": "unchangedQuantity",
31        "otherProperty": "unchangedValue"
32    },
33    "metadata": {
34        "timestamp": "2021-07-26T06:47:15.762584Z",
35        "record-type": "data",
36        "operation": "update",
37        "partition-key-type": "schema-table",
38        "schema-name": "my-schema",
39        "table-name": "product",
40        "transaction-id": 8633730840
41    }
42}
43"BeforeImageSettings": {
44    "EnableBeforeImage": true,
45    "FieldName": "before-image",
46    "ColumnFilter": "all"
47}
48

While this still gives me the whole record, it give me enough data to work out what's changed without additional systems.

1     {
2        "rule-type": "selection",
3        "rule-id": "1",
4        "rule-name": "1",
5        "object-locator": {
6            "schema-name": "my-schema",
7            "table-name": "product"
8        },
9        "rule-action": "include",
10        "filters": []
11    },
12{
13    "data": {
14        "name": "newValue"
15    },
16    "metadata": {
17        "timestamp": "2021-07-26T06:47:15.762584Z",
18        "record-type": "data",
19        "operation": "update",
20        "partition-key-type": "schema-table",
21        "schema-name": "my-schema",
22        "table-name": "product",
23        "transaction-id": 8633730840
24    }
25}
26{
27    "data": {
28        "name": "newValue",
29        "id": "unchangedId",
30        "quantity": "unchangedQuantity",
31        "otherProperty": "unchangedValue"
32    },
33    "metadata": {
34        "timestamp": "2021-07-26T06:47:15.762584Z",
35        "record-type": "data",
36        "operation": "update",
37        "partition-key-type": "schema-table",
38        "schema-name": "my-schema",
39        "table-name": "product",
40        "transaction-id": 8633730840
41    }
42}
43"BeforeImageSettings": {
44    "EnableBeforeImage": true,
45    "FieldName": "before-image",
46    "ColumnFilter": "all"
47}
48{
49    "data": {
50        "name": "newValue",
51        "id": "unchangedId",
52        "quantity": "unchangedQuantity",
53        "otherProperty": "unchangedValue"
54    },
55    "before-image": {
56        "name": "oldValue",
57        "id": "unchangedId",
58        "quantity": "unchangedQuantity",
59        "otherProperty": "unchangedValue"
60    },
61    "metadata": {
62        "timestamp": "2021-07-26T06:47:15.762584Z",
63        "record-type": "data",
64        "operation": "update",
65        "partition-key-type": "schema-table",
66        "schema-name": "my-schema",
67        "table-name": "product",
68        "transaction-id": 8633730840
69    }
70}
71

Source https://stackoverflow.com/questions/68525692

QUESTION

How to connect with a file socket and not with the TCP protocol?

Asked 2021-Jul-12 at 20:44

I can connect with the command:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2

But I cannot connect with the command:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3

It fails as in:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6

The socket file exists:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8

And my PHP application cannot connect with the statement:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9

When the MariaDB server starts its console shows:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34

The phpinfo() shows the myslqi to be enabled and having the values:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
35Active Persistent Links 0
36Inactive Persistent Links   0
37Active Links    0
38Directive   Local Value Master Value
39mysqli.allow_local_infile   Off Off
40mysqli.allow_persistent On  On
41mysqli.default_host no value    no value
42mysqli.default_port 3306    3306
43mysqli.default_pw   no value    no value
44mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
45mysqli.default_user no value    no value
46mysqli.max_links    Unlimited   Unlimited
47mysqli.max_persistent   Unlimited   Unlimited
48mysqli.reconnect    Off Off
49mysqli.rollback_on_cached_plink Off Off
50

The etc/my.cnf file contains:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
35Active Persistent Links 0
36Inactive Persistent Links   0
37Active Links    0
38Directive   Local Value Master Value
39mysqli.allow_local_infile   Off Off
40mysqli.allow_persistent On  On
41mysqli.default_host no value    no value
42mysqli.default_port 3306    3306
43mysqli.default_pw   no value    no value
44mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
45mysqli.default_user no value    no value
46mysqli.max_links    Unlimited   Unlimited
47mysqli.max_persistent   Unlimited   Unlimited
48mysqli.reconnect    Off Off
49mysqli.rollback_on_cached_plink Off Off
50[mysqld]
51bind-address    = 0.0.0.0 # Allow client binding from any IP address instead of just 127.0.0.1
52port            = 3306
53sql_mode        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
54socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
55user            = root
56basedir         = /home/europasprak/programs/mariadb/install
57datadir         = /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
58log-error       = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.error.log
59general_log     = 1
60general-log-file     = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.log
61slow-query-log-file  = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.slow.queries.log
62innodb_file_per_table = 1
63innodb_flush_log_at_trx_commit = 1
64innodb_flush_method = O_DIRECT
65character-set-client-handshake = FALSE
66character-set-server = utf8mb4
67collation-server = utf8mb4_general_ci
68wait_timeout            = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
69interactive_timeout     = 28800 # same, but for interactive sessions
70max_allowed_packet = 256M
71net_write_timeout = 180
72
73# Creating a binary log for change data capture
74server-id = 1
75log_bin = master
76expire_logs_days = 1
77binlog_format = row
78binlog_row_image = full
79
80[client]
81socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
82loose-default-character-set = utf8mb4
83
84[mysql]
85loose-default-character-set = utf8mb4
86
87[mysqladmin]
88socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
89

The MariaDB server is started with the command:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
35Active Persistent Links 0
36Inactive Persistent Links   0
37Active Links    0
38Directive   Local Value Master Value
39mysqli.allow_local_infile   Off Off
40mysqli.allow_persistent On  On
41mysqli.default_host no value    no value
42mysqli.default_port 3306    3306
43mysqli.default_pw   no value    no value
44mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
45mysqli.default_user no value    no value
46mysqli.max_links    Unlimited   Unlimited
47mysqli.max_persistent   Unlimited   Unlimited
48mysqli.reconnect    Off Off
49mysqli.rollback_on_cached_plink Off Off
50[mysqld]
51bind-address    = 0.0.0.0 # Allow client binding from any IP address instead of just 127.0.0.1
52port            = 3306
53sql_mode        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
54socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
55user            = root
56basedir         = /home/europasprak/programs/mariadb/install
57datadir         = /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
58log-error       = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.error.log
59general_log     = 1
60general-log-file     = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.log
61slow-query-log-file  = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.slow.queries.log
62innodb_file_per_table = 1
63innodb_flush_log_at_trx_commit = 1
64innodb_flush_method = O_DIRECT
65character-set-client-handshake = FALSE
66character-set-server = utf8mb4
67collation-server = utf8mb4_general_ci
68wait_timeout            = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
69interactive_timeout     = 28800 # same, but for interactive sessions
70max_allowed_packet = 256M
71net_write_timeout = 180
72
73# Creating a binary log for change data capture
74server-id = 1
75log_bin = master
76expire_logs_days = 1
77binlog_format = row
78binlog_row_image = full
79
80[client]
81socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
82loose-default-character-set = utf8mb4
83
84[mysql]
85loose-default-character-set = utf8mb4
86
87[mysqladmin]
88socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
89cd /home/europasprak/programs/mariadb/install
90./bin/mysqld_safe --defaults-file=/home/europasprak/programs/mariadb/install/etc/my.cnf
91

UPDATE and solution:

I was missing a loaded my.cnf file:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
35Active Persistent Links 0
36Inactive Persistent Links   0
37Active Links    0
38Directive   Local Value Master Value
39mysqli.allow_local_infile   Off Off
40mysqli.allow_persistent On  On
41mysqli.default_host no value    no value
42mysqli.default_port 3306    3306
43mysqli.default_pw   no value    no value
44mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
45mysqli.default_user no value    no value
46mysqli.max_links    Unlimited   Unlimited
47mysqli.max_persistent   Unlimited   Unlimited
48mysqli.reconnect    Off Off
49mysqli.rollback_on_cached_plink Off Off
50[mysqld]
51bind-address    = 0.0.0.0 # Allow client binding from any IP address instead of just 127.0.0.1
52port            = 3306
53sql_mode        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
54socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
55user            = root
56basedir         = /home/europasprak/programs/mariadb/install
57datadir         = /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
58log-error       = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.error.log
59general_log     = 1
60general-log-file     = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.log
61slow-query-log-file  = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.slow.queries.log
62innodb_file_per_table = 1
63innodb_flush_log_at_trx_commit = 1
64innodb_flush_method = O_DIRECT
65character-set-client-handshake = FALSE
66character-set-server = utf8mb4
67collation-server = utf8mb4_general_ci
68wait_timeout            = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
69interactive_timeout     = 28800 # same, but for interactive sessions
70max_allowed_packet = 256M
71net_write_timeout = 180
72
73# Creating a binary log for change data capture
74server-id = 1
75log_bin = master
76expire_logs_days = 1
77binlog_format = row
78binlog_row_image = full
79
80[client]
81socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
82loose-default-character-set = utf8mb4
83
84[mysql]
85loose-default-character-set = utf8mb4
86
87[mysqladmin]
88socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
89cd /home/europasprak/programs/mariadb/install
90./bin/mysqld_safe --defaults-file=/home/europasprak/programs/mariadb/install/etc/my.cnf
91europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysqld --verbose --help | grep -A 1 "Default options"
92bin/mysqld: One can only use the --user switch if running as root
93Default options are read from the following files in the given order:
94/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
95europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/mysql/my.cnf
96ls: cannot access '/etc/mysql/my.cnf': No such file or directory
97europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/my.cnf
98ls: cannot access '/etc/my.cnf': No such file or directory
99

Creating a simlink on the file solved the issue:

1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2bin/mysql -P 3306 -u europasprak -p db_europasprak
3europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
4Enter password: 
5ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)
6europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
7srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
8mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")
9210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
102021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
112021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
122021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
132021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
142021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
152021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
162021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
172021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
182021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
192021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
202021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
212021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
222021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
232021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
242021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
252021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
262021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
272021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
282021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
292021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
302021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
312021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
32Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
332021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56
34Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
35Active Persistent Links 0
36Inactive Persistent Links   0
37Active Links    0
38Directive   Local Value Master Value
39mysqli.allow_local_infile   Off Off
40mysqli.allow_persistent On  On
41mysqli.default_host no value    no value
42mysqli.default_port 3306    3306
43mysqli.default_pw   no value    no value
44mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
45mysqli.default_user no value    no value
46mysqli.max_links    Unlimited   Unlimited
47mysqli.max_persistent   Unlimited   Unlimited
48mysqli.reconnect    Off Off
49mysqli.rollback_on_cached_plink Off Off
50[mysqld]
51bind-address    = 0.0.0.0 # Allow client binding from any IP address instead of just 127.0.0.1
52port            = 3306
53sql_mode        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
54socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
55user            = root
56basedir         = /home/europasprak/programs/mariadb/install
57datadir         = /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
58log-error       = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.error.log
59general_log     = 1
60general-log-file     = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.log
61slow-query-log-file  = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.slow.queries.log
62innodb_file_per_table = 1
63innodb_flush_log_at_trx_commit = 1
64innodb_flush_method = O_DIRECT
65character-set-client-handshake = FALSE
66character-set-server = utf8mb4
67collation-server = utf8mb4_general_ci
68wait_timeout            = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
69interactive_timeout     = 28800 # same, but for interactive sessions
70max_allowed_packet = 256M
71net_write_timeout = 180
72
73# Creating a binary log for change data capture
74server-id = 1
75log_bin = master
76expire_logs_days = 1
77binlog_format = row
78binlog_row_image = full
79
80[client]
81socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
82loose-default-character-set = utf8mb4
83
84[mysql]
85loose-default-character-set = utf8mb4
86
87[mysqladmin]
88socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
89cd /home/europasprak/programs/mariadb/install
90./bin/mysqld_safe --defaults-file=/home/europasprak/programs/mariadb/install/etc/my.cnf
91europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysqld --verbose --help | grep -A 1 "Default options"
92bin/mysqld: One can only use the --user switch if running as root
93Default options are read from the following files in the given order:
94/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
95europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/mysql/my.cnf
96ls: cannot access '/etc/mysql/my.cnf': No such file or directory
97europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/my.cnf
98ls: cannot access '/etc/my.cnf': No such file or directory
99 ln -s /home/europasprak/programs/mariadb/install/etc/my.cnf ~/.my.cnf
100

ANSWER

Answered 2021-Jul-12 at 20:44

Leave out the port number from your connect strings. You don't use ports when you connect via sockets; that's a TCP thing.

Try this: bin/mysql -u europasprak -p db_europasprak or mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak")

Source https://stackoverflow.com/questions/68339743

QUESTION

AWS AppFlow Salesforce to Redshift Error Creating Connection

Asked 2021-Jul-09 at 15:51

I'm wanting to create a one-way real-time copy of a Salesforce (SF) object in Redshift. The idea being that when fields are updated in SF, those fields will be updated in Redshift as well. The history of changes are irrelevant in AWS/Redshift, that's all being tracked in SF - I just need a real-time read-only copy of that particular object to query. Preferably without having to query the whole SF object, clearing the Redshift table, and piping the data in.

I thought AWS AppFlow listening for SF Change Data Capture events might be a good setup for this:

When I try to create a flow, I don't have any issues with the SF source connection:

enter image description here

so I click "Connect" in the Destination details section to setup Redshift and I fill out this page and click "Connect" again:

enter image description here

About 5 seconds goes by and I receive this error pop-up:

An error occurred while creating the connection

Error while communicating to connector: Failed to validate Connection while attempting "select distinct(table_schema) from information_schema.tables limit 1" with connector failure Can't connect to JDBC database with message: Amazon Error setting/closing connection: SocketTimeoutException. (Service: null; Status Code: 400; Error Code: Client; Request ID: null; Proxy: null)

I know my connection string, username, password, etc are all good - I'm connected to Redshift in other apps. Any idea what the issue could be? Is this even the right solution for what I'm trying to do?

ANSWER

Answered 2021-Jul-09 at 15:51

I solved this by adding the AppFlow IP ranges for my region to my Redshift VPC's security group inbound rules.

Source https://stackoverflow.com/questions/68122939

QUESTION

Escaping Java variables to serialise SQL statement to string

Asked 2021-Jun-19 at 15:43

We have an event system producing database events for change data capture.

The system sends an event which contains the INSERT or UPDATE statement with ? placeholders and an array of the ordered values matching each question mark.

I want to use this for per hour backup files so if I get a statement like:

insert into T0(a,b,c) VALUES(?,?,?)

with an array of values 1, 2 and it's his then I write the a line to the backup file for that hour as

insert into T0(a,b,c) VALUES(1,2,'it\'s his');

A few things:

  1. Is it only strings that need escaping? We don't have or allow binary columns
  2. Is there a Java library that can do this already (from the Spring eco-system, Apache or otherwise)?
  3. I've seen the Postgres JDBC code for escaping https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/Utils.java - is that sufficient?

I was also thinking of creating a SQLite database for each hour, writing to SQLite and then dumping it to the hr.sql text file. This has the advantage of capitalising on all the hardwork and thought already put into SQLite handling escaping but feels like overkill if there's a way to do the toString in Java then append a line to the file.

There's a performance consideration in using SQLite as well furthering my hesitation to that that route.

ANSWER

Answered 2021-Jun-19 at 15:43
1ESPECIAL_CHARACTER_PATTERN.matcher(stringValue).replaceAll("''")
2

Where private static final Pattern ESPECIAL_CHARACTER_PATTERN = Pattern.compile("'");

In both cases, only strings need this as I thought and binary is handled separately but we don't have/need binary.

Digging further I rediscovered ESAPI https://github.com/ESAPI/esapi-java-legacy They have a lib for escaping SQL https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html#defense-option-4-escaping-all-user-supplied-input

https://github.com/ESAPI/esapi-java-legacy/blob/develop/src/main/java/org/owasp/esapi/codecs/MySQLCodec.java

Source https://stackoverflow.com/questions/68038628

QUESTION

R2DBC can be used for change data capture in Spring boot?

Asked 2021-Mar-23 at 11:15

I have a classic Spring Boot Application connected to a MySQL database.

Can I use r2dbc driver and spring data r2dbc to develop another application that listens to the database changes like a change data capture?

I've studied the r2dbc driver documentation, but I don't understand if they produces reactive hot streams or only cold streams. If it is not possible I believe that I should use Debezium, like I found in this article.

Thanks a lot

ANSWER

Answered 2021-Mar-23 at 11:15
TL;DR

R2DBC is primarily a specification to enable reactive/non-blocking communication with your database. What an R2DBC driver is capable of pretty much depends on your database.

The Longer Version

R2DBC specifies a set of interfaces including methods where every database conversation is activated through a Publisher. R2DBC has no opinion on the underlying wire protocol. Instead, a database driver implementing R2DBC has to stick to its database communication protocol. What you get through JDBC or ODBC is pretty much the same as what you can expect from an R2DBC driver.

There are smaller differences: some JDBC drivers require polling for data (such as Postgres Pub/Sub notification) whereas, in R2DBC, a notification stream can be consumed without a polling thread as all I/O is based on listening on the receive buffers and emitting data once the driver receives data. In contrast, JDBC (and pretty much all imperative API) require someone to call a method to consume/obtain data.

I'm not sure how CDC works with MySQL; I think you need to scan (poll) the BINLOG using MySQL commands or the MySQL protocol. Right now, the R2DBC MySQL driver doesn't support BINLOG polling.

Postgres has similar functionality (Logical Decode). It is supported by R2DBC Postgres (see the documentation of Logical Decode using R2DBC Postgres). In Postgres, the server pushes the replication log to the client, which gives you a hot stream as logical decode subscribes to the replication log.

The gist is pretty much that it depends on the actual database technology.

Source https://stackoverflow.com/questions/66727103

QUESTION

Nested variant updating and deleting in snowflake

Asked 2021-Mar-15 at 08:06

Currently streaming Change Data Capture events from MongoDB into snowflake, would like to apply them to the raw data that is already there.

Let's say I have a table like this:

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9

Where variant contains a very heavily nested JSON ex:

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9{
10    "foo": {
11        "bar": "Some info",
12        "baz": "Other info",
13        "stuff": {
14            "anArray": [1, 2, 3],
15            "things": "More nested info"
16        }
17    }
18}
19

I would like to use OBJECT_DELETE and OBJECT_INSERT functions to update this nested variant data in snowflake.

Tried making a js UDF but eval() is not supported.

Other approaches like writing a UDF that does key.split(".") and then recursively walking the structure and updating the field seem to take a long time and fail with JavaScript out of memory error: UDF thread memory limit exceeded in some cases.

Looking for a bit more efficient way to solve this issue.

ANSWER

Answered 2021-Mar-09 at 09:44

There is a way using OBJECT_INSERT but it's not pretty. Unfortunately I don't see a way to specify a nested key in a single OBJECT_INSERT. So:

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9{
10    "foo": {
11        "bar": "Some info",
12        "baz": "Other info",
13        "stuff": {
14            "anArray": [1, 2, 3],
15            "things": "More nested info"
16        }
17    }
18}
19create or replace table test2 (document variant);
20insert into test2 select object_construct('foo',object_construct('bar','Some info', 'baz', 'Other info','stuff', object_construct('anArray', array_construct(1, 2, 3), 'things', 'More nested info')));
21select * from test2;
22

I get:

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9{
10    "foo": {
11        "bar": "Some info",
12        "baz": "Other info",
13        "stuff": {
14            "anArray": [1, 2, 3],
15            "things": "More nested info"
16        }
17    }
18}
19create or replace table test2 (document variant);
20insert into test2 select object_construct('foo',object_construct('bar','Some info', 'baz', 'Other info','stuff', object_construct('anArray', array_construct(1, 2, 3), 'things', 'More nested info')));
21select * from test2;
22{
23      "foo": {
24                "bar": "Some info",
25                "baz": "Other info",
26                "stuff": {
27                          "anArray": [1,2,3],
28                          "things": "More nested info"
29                }
30      }
31

}

Now, I want to update foo.bar with "Changed info" so I can do (remember to set the flag to TRUE so you get an update rather than insert):

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9{
10    "foo": {
11        "bar": "Some info",
12        "baz": "Other info",
13        "stuff": {
14            "anArray": [1, 2, 3],
15            "things": "More nested info"
16        }
17    }
18}
19create or replace table test2 (document variant);
20insert into test2 select object_construct('foo',object_construct('bar','Some info', 'baz', 'Other info','stuff', object_construct('anArray', array_construct(1, 2, 3), 'things', 'More nested info')));
21select * from test2;
22{
23      "foo": {
24                "bar": "Some info",
25                "baz": "Other info",
26                "stuff": {
27                          "anArray": [1,2,3],
28                          "things": "More nested info"
29                }
30      }
31update test2 set document = OBJECT_INSERT(document, 'foo', OBJECT_INSERT(document:foo::VARIANT, 'bar', 'Changed value', TRUE), TRUE) WHERE document:foo.bar::VARCHAR = 'Some info';
32

I get back:

1+---------------------+-----------------+-----------+
2|         key         |      value      | document  |
3+---------------------+-----------------+-----------+
4| foo.bar             | "changed value" | <variant> |
5| foo.stuff.anArray.1 | 1000            | <variant> |
6| ...                 | ...             | ...       |
7+---------------------+-----------------+-----------+
8
9{
10    "foo": {
11        "bar": "Some info",
12        "baz": "Other info",
13        "stuff": {
14            "anArray": [1, 2, 3],
15            "things": "More nested info"
16        }
17    }
18}
19create or replace table test2 (document variant);
20insert into test2 select object_construct('foo',object_construct('bar','Some info', 'baz', 'Other info','stuff', object_construct('anArray', array_construct(1, 2, 3), 'things', 'More nested info')));
21select * from test2;
22{
23      "foo": {
24                "bar": "Some info",
25                "baz": "Other info",
26                "stuff": {
27                          "anArray": [1,2,3],
28                          "things": "More nested info"
29                }
30      }
31update test2 set document = OBJECT_INSERT(document, 'foo', OBJECT_INSERT(document:foo::VARIANT, 'bar', 'Changed value', TRUE), TRUE) WHERE document:foo.bar::VARCHAR = 'Some info';
32{
33          "foo": {
34                    "bar": "Changed value",
35                    "baz": "Other info",
36                    "stuff": {
37                              "anArray": [1,2,3],
38                              "things": "More nested info"
39                    }
40          }
41}
42

You can also use Javascript UDF as mentioned here.

Source https://stackoverflow.com/questions/66496691

Community Discussions contain sources that include Stack Exchange Network

Tutorials and Learning Resources in Change Data Capture

Tutorials and Learning Resources are not available at this moment for Change Data Capture

Share this Page

share link

Get latest updates on Change Data Capture