MySQL¶

Local Development, Updates, Good2Know Stuff

• MySQL
  • Set Global Variables
  • Update 5.x to 5.7
    • Strict Mode
    • Aggregated GROUP BY
  • JSON Queries
  • Postgres to MySQL

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

• https://dev.mysql.com/doc/refman/5.7/en/datetime.html

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

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

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