MySQL

Clients

Backups

Use search in JSON field in UPDATE statement

1
2
3
4
5
6
UPDATE membership
SET account = CASE
WHEN(JSON_EXTRACT(payload, "$.pack.account") IS NOT NULL)
THEN JSON_EXTRACT(payload, "$.pack.account")
ELSE account
END

Importing large dumps

The first step is remove every CREATE DATABASE and USE db_name instruction. If the dump is very large the best way to achieve this is using sed since opening a large dump in a text editor is a very bad idea.

Example:

1
2
sed -i '/CREATE DATABASE.*/c\' dump.sql
sed -i '/USE whatever.*/c\' dump.sql

Sometimes when you are importing large dumps you get errors like Can't connect to the server and ERROR 2047 (HY000): Wrong or unknown protocol.
To try to mitigate this issue you can set some mysql parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql -u root -p

set global net_buffer_length=1000000; --Set network buffer length to a large byte number

set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number

SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour

-- Create new schema to import the dump
CREATE DATABASE my_schema;

-- Select the new created database
USE my_schema;

source file.sql --Import your sql dump file

SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!

Comments

⬆︎TOP