Export data frame to DBeaver compatible CSV

DBeaver does not support the escape character and the quote character to be the same.

To export a pandas data frame to a DBeaver compatible CSV you can use the following command:

df.to_csv('data.csv', quoting=csv.QUOTE_NONNUMERIC, quotechar='"', escapechar='\\', encoding="utf-8", index=False, doublequote=False)

Import CSV data to MySQL fast

Table of contents

1. Find the default MySQL input directory
2. Copy the CSV file to the proper directory
3. Import the data from the CSV

Find the default MySQL input directory

In order to import data from a CSV file the file should be placed in a specific system defined directory. To find the path of that directory, use the following SQL command:

SHOW VARIABLES LIKE "secure_file_priv";

An output example of the response would be:

/var/lib/mysql-files

Copy the CSV file to the proper directory

You should copy the CSV file to that directory (it may require sudo):

sudo data.csv /var/lib/mysql-files

Import the data from the CSV

After you have placed the file there, you can import data use the following snippet:

LOAD DATA INFILE '/var/lib/mysql-files/data.csv' 
INTO TABLE my_table 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Use Ignore row if the file contains a header. Configure the other options accordingly.

Modern collation chaos for MariaDB and MySQL

The general thumb of rule is that you should never use *_general_ci collation anymore. MySQL is using utf8mb4_0900_ai_ci as default.

But what does “utf8mb4_0900_ai_ci” mean?

• utf8mb4 means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.
• 0900 refers to the Unicode Collation Algorithm version. (The Unicode Collation Algorithm is the method used to compare two Unicode strings that conforms to the requirements of the Unicode Standard).
• ai refers accent insensitivity. That is, there is no difference between e, è, é, ê and ë when sorting.
• ci refers to case insensitivity. This is, there is no difference between p and P when sorting.

But … MariaDB does not support utf8mb4_0900_ai_ci. So what to use?

The answer is: It depends

Here is a quick summary table:

Compatibility	UCA	Preferred collation	Problems
MySQL	9.0	utf8mb4_0900_ai_ci	None
MariaDB	9.0	uca1400_as_ci	None
Both	5.2	utf8mb4_unicode_520_ci	Mother-daddy

Here is a summary table versions you should not use:

Collation	Does not support
utf8_* utf8mb3	– no support for Emoji in supplementary multi-lingual plane (SMP) – Additional Chinese characters (CJK Unified Ideographs Extension B) in Supplementary ideographic plane (SIP)
utf8mb4_general_ci	Sushi-Beer
utf8mb4_unicode_ci	Sushi-Beer

Resources:

• Official MySQL page
• Official MySQL page (Sushi Beer problem)
• Explaining the Mother-Father problem (bug report) (why use utf8mb4 and not utf8mp3)

MySQL workbench cannot connect via SSH

If you cannot connect to a MySQL server via SSH in MySQL Workbench, you should take into account that MySQL workbench cannot use OPEN SSH keys but only RSA keys. So a common cause is that the key you have is in OpenSSH format.

You can convert OpenSSH keys to RSA. The following command will do the conversion for you but keep in mind that it will overwrite the existing key so you should take a backup if you still needed.

To convert an OpenSSH key to RSA use the command:

ssh-keygen -p -m PEM -f id_rsa.pri

PostGRES – with docker

PGADMIN

Pull the image:

docker pull dpage/pgadmin4

Run the image with:

docker run -p 5050:80 -e "PGADMIN_DEFAULT_EMAIL=myemail@gmail.com" -e "PGADMIN_DEFAULT_PASSWORD=a1234
5678" -d dpage/pgadmin4

Open browser at post 5050:

http://localhost:5050

Client (psql)

Use the flags:

• it: interactive with terminal
• rm: remove container afterwards
• v: to allow access to a directory of the host (optional)

docker run -v external_dir:docker_dir -it --rm server_name psql -h host_name -U username -d database_name

You may have to set-up SSL or disable “require SSL connection” from server.

Liquibase

Recommended structure:

See:

https://www.turtle-techies.com/how-to-write-xml-changesets-in-liquibase/

MySQL – Cannot Add Foreign Key Constraint

Check that the table engines are InnoDB.

CREATE A USER IN MYSQL

Create a user in MySQL with full rights:

ElasticSearch notes

Resources:

• ElasticSearch

For installation check this url. (DEB page link)

Default cluster configuration:

cluster name

elasticsearch

Start/stop service

SysV:

sudo -i service elasticsearch start
sudo -i service elasticsearch stop

Systemd (read more here):

sudo systemctl start elasticsearch.service
sudo systemctl stop elasticsearch.service
sudo systemctl restart elasticsearch.service
sudo systemctl status elasticsearch.service

sudo systemctl enable elasticsearch.service
sudo systemctl disable elasticsearch.service

sudo systemctl start kibana.service
sudo systemctl stop kibana.service

Version and log information

view log	sudo journalctl -f
view log for service	sudo journalctl –unit elasticsearch

Cluster information

Health	curl -X GET “localhost:9200/_cat/health?v”
Indices	curl “localhost:9200/_cat/indices?v”

Index management

index stats	curl -X GET “localhost:9200/mydb/_stats?pretty”
get all documents	curl -X GET “localhost:9200/_search” -H ‘Content-Type: application/json’ -d'{ “query”: { “match_all”: {} } }’
get all documents	http://localhost:9200/mydb/_search
count all documents	curl -X GET “localhost:9200/mydb/_count”
count all documents	http://localhost:9200/mydb/_count
–	–
–	–
delete index entries	curl -XPOST ‘localhost:9200/mydb/_doc/_delete_by_query?conflicts=proceed’ -H’Content-Type: application/json’ -d’ { “query”: { “match_all”: {} }}’
delete index	curl -X DELETE “localhost:9200/mydb”

sd

Mysql Workbench error #1064

This is an incompatibility (assuming the database model is correct) between MySQL workbench and MySQL Server. For example, this could occur occasionally by installing XAMPP 3.2.2 and latest MySQL Workbench 8.