MySQL

Local Development, Updates, Good2Know Stuff

Set Global Variables


SELECT @@global.net_read_timeout ;

SET GLOBAL net_read_timeout = 45 ;

SELECT @@global.net_read_timeout ;

Update 5.x to 5.7

Strict Mode

When ALTER table is failing because DATETIME field has wrong default values (0000-00-00 00:00:00) then check for MySQL strict mode as explained here: https://stackoverflow.com/a/36374690

This solved it for now: Default DateTime or TimeStamp issue in mySql after upgrading to >= 5.6 - Tekina

sql-mode = "NO_ENGINE_SUBSTITUTION"

Aggregated GROUP BY

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

JSON Queries

json_search returns path that's why check is not null in WHERE clause


SET @tag = 'houses';

SELECT

`data`->'$.page.linkedData[0].keywords' AS x,

json_search(`data`->'$.page.linkedData[0].keywords', 'all', @tag) as y,

places.*

from places

where

json_search(`data`->'$.page.linkedData[0].keywords', 'one', @tag) is not null

#AND

#source != 'AtlasObscura'

order by id desc

limit 10;

Result
["explore asheville", "architectural oddities", "architecture", "gilded age", "mansions", "houses", "secret", "secret passages", "hidden", "explore asheville (internal)", "hidden (internal)", "secret passages (internal)", "secret (internal)", "houses (internal)", "mansions (internal)", "gilded age (internal)", "architecture (internal)", "architectural oddities (internal)", "section-Atlas"] "$[5]"
["history", "love", "mansions", "houses", "sex", "religion", "utopias", "commune", "history (internal)", "love (internal)", "mansions (internal)", "houses (internal)", "sex (internal)", "religion (internal)", "utopias (internal)", "commune (internal)", "section-Atlas"] "$[3]"

LIKE '%term% for Json


# where json_search(UPPER(tags), 'one', UPPER('lorem%')) is not null



SET @tag = 'hous%';

SELECT

`data`->'$.page.linkedData[0].keywords' AS x,

json_search(`data`->'$.page.linkedData[0].keywords', 'all', @tag) as y

#,

#places.*

from places

where

json_search(`data`->'$.page.linkedData[0].keywords', 'all', @tag) is not null

AND

source = 'AtlasObscura'

order by id desc

limit 5;

Result

x y
["explore asheville", "architectural oddities", "architecture", "gilded age", "mansions", "houses", "secret", "secret passages", "hidden", "explore asheville (internal)", "hidden (internal)", "secret passages (internal)", "secret (internal)", "houses (internal)", "mansions (internal)", "gilded age (internal)", "architecture (internal)", "architectural oddities (internal)", "section-Atlas"] ["$[5]", "$[13]"]
["history", "love", "mansions", "houses", "sex", "religion", "utopias", "commune", "history (internal)", "love (internal)", "mansions (internal)", "houses (internal)", "sex (internal)", "religion (internal)", "utopias (internal)", "commune (internal)", "section-Atlas"] ["$[3]", "$[11]"]
["odd accommodations", "houses", "labor", "architecture", "odd accommodations (internal)", "houses (internal)", "labor (internal)", "architecture (internal)", "section-Atlas"] ["$[1]", "$[5]"]
["ruins", "mines", "abandoned", "houses", "ruins (internal)", "mines (internal)", "abandoned (internal)", "houses (internal)", "section-Atlas"] ["$[3]", "$[7]"]
["houses", "caves", "archaeology", "cliff villages", "cliffs", "native americans", "prehistoric", "houses (internal)", "caves (internal)", "archaeology (internal)", "cliff villages (internal)", "cliffs (internal)", "native americans (internal)", "prehistoric (internal)", "section-Atlas"] ["$[0]", "$[7]"]

Postgres to MySQL

Use DBeaver Community | Free Universal Database Tool

  1. Setup connection to Postgres and MySQL databases.
  2. Export source table and select Database as new container
  3. Select "Columns' mappings" to select the right data types. it's important to set the full and correct MySQL data type (varchar(10) instead of just varchar)

Export

• #database