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
by debezium 
java
6573 NOASSERTION
Change data capture for a variety of databases. Please log issues at https://issues.redhat.com/browse/DBZ.
by libusb c
3692 LGPL-2.1
A cross-platform library to access USB devices
by hathach c
2569 MIT
An open source cross-platform USB stack for embedded system
by confluentinc c
1505 Apache-2.0
Change data capture from PostgreSQL into Kafka
by whid-injector c++
1133 MIT
WiFi HID Injector - An USB Rubberducky / BadUSB On Steroids.
by ucarGroup java
926 Apache-2.0
DataLink是一个满足各种异构数据源之间的实时增量同步、离线全量同步，分布式、可扩展的数据交换平台。
by obdev c
919
A Firmware-Only USB implementation for Atmel's AVR Microcontrollers
by pelya c
827 Apache-2.0
Convert your Android device into USB keyboard/mouse, control your PC from your Android device remotely, including BIOS/bootloader.
by CedArctic c++
800 MIT
USB Rubber Ducky type scripts written for the DigiSpark.
Trending New libraries in Change Data Capture
by tejado java
202 GPL-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. 🛠🛡📱
by debezium typescript
154 Apache-2.0
A web UI for Debezium; Please log issues at https://issues.redhat.com/browse/DBZ.
by oxplot c
143 BSD-3-Clause
easy USB-C power for all your devices
by jiegec rust
82 MIT
A Rust library to run a USB/IP server
by castorm java
74 Apache-2.0
Kafka Connect connector that enables Change Data Capture from JSON/HTTP APIs into Kafka.
by suadev csharp
69
Microservices data exchange with change data capture and outbox pattern.
by Quarren42 html
47 CC-BY-SA-4.0
12 key, 3 encoder USB-C Macro Pad
by justcallmekoko c
47 MIT
A WiFi enabled USB Keylogger and Keystroke injection tool
by justcallmekoko c++
43 MIT
The super tiny USB Rubber Ducky
Top Authors in Change Data Capture
1
4 Libraries
96
2
4 Libraries
23
3
3 Libraries
6969
4
2 Libraries
186
5
2 Libraries
215
6
2 Libraries
16
7
2 Libraries
27
8
2 Libraries
8
9
2 Libraries
56
10
2 Libraries
13
1
4 Libraries
96
2
4 Libraries
23
3
3 Libraries
6969
4
2 Libraries
186
5
2 Libraries
215
6
2 Libraries
16
7
2 Libraries
27
8
2 Libraries
8
9
2 Libraries
56
10
2 Libraries
13
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:05How 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:05In 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.
QUESTION
Event Hub: org.apache.spark.sql.AnalysisException: Required attribute 'body' not found
Asked 2021-Sep-18 at 12:04I 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")
5While 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()
6It 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)
8I 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:04You 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()
13QUESTION
Replicate MySQL Data to ClickHouse
Asked 2021-Aug-17 at 14:01I 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?
ANSWER
Answered 2021-Aug-16 at 17:17[experimental] MaterializedMySQL Creates ClickHouse database with all the tables existing in MySQL, and all the data in those tables.
ClickHouse server works as MySQL replica. It reads binlog and performs DDL and DML queries.
https://clickhouse.tech/docs/en/engines/database-engines/materialized-mysql/
https://altinity.com/blog/2018/6/30/realtime-mysql-clickhouse-replication-in-practice
https://altinity.com/blog/dictionaries-explained https://altinity.com/blog/2020/5/19/clickhouse-dictionaries-reloaded
QUESTION
Does multi-table Debezium connector guarantee the ordering across change tracking tables?
Asked 2021-Aug-06 at 19:44I'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:04The 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.
QUESTION
AWS DMS CDC - Only capture changed values not entire record? (Source RDS MySQL)
Asked 2021-Jul-26 at 07:45I 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 },
12and 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}
26However 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}
43As 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:45I found the answer after digging into AWS documentation some more.
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}
48While 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}
71QUESTION
How to connect with a file socket and not with the TCP protocol?
Asked 2021-Jul-12 at 20:44I can connect with the command:
1bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak
2But 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
3It 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)
6The 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
8And 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")
9When 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
34The 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
50The 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
89The 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
91UPDATE 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
99Creating 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
100ANSWER
Answered 2021-Jul-12 at 20:44Leave 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")
QUESTION
AWS AppFlow Salesforce to Redshift Error Creating Connection
Asked 2021-Jul-09 at 15:51I'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:
so I click "Connect" in the Destination details section to setup Redshift and I fill out this page and click "Connect" again:
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:51I solved this by adding the AppFlow IP ranges for my region to my Redshift VPC's security group inbound rules.
QUESTION
Escaping Java variables to serialise SQL statement to string
Asked 2021-Jun-19 at 15:43We 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:
- Is it only strings that need escaping? We don't have or allow binary columns
- Is there a Java library that can do this already (from the Spring eco-system, Apache or otherwise)?
- 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:43Found some options.
Postgres JDBC driver is this https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/Utils.java and other impl. is even more simple https://github.com/p6spy/p6spy/blob/master/src/main/java/com/p6spy/engine/common/Value.java#L172 literally doing
1ESPECIAL_CHARACTER_PATTERN.matcher(stringValue).replaceAll("''")
2Where 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
QUESTION
R2DBC can be used for change data capture in Spring boot?
Asked 2021-Mar-23 at 11:15I 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:15R2DBC 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 VersionR2DBC 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.
QUESTION
Nested variant updating and deleting in snowflake
Asked 2021-Mar-15 at 08:06Currently 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
9Where 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}
19I 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:44There 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;
22I 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';
32I 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}
42You can also use Javascript UDF as mentioned here.
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
Get latest updates on Change Data Capture