My personal and professional life
2019-12-25
2019-12-05
Picture-in-picture mode for videos in Firefox 71
Публикувано от
Georgi D. Sotirov
в
18:11
0
коментара
2019-10-18
New features in MySQL 8.0.18
Hash join optimization
- Nested-Loop Join - it's the simplest where each row from the outer loop is passed to the inner loop for processing. The obvious drawback of this one is that the inner table needs to be read many times.
- Block Nested Loop (BNL) - uses buffering of rows from the outer table to reduce the number of times the inner table is read. In this case instead of passing just one row to the inner loop multiple rows from the buffer could be passed at once. This greatly reduces the number of time the inner table needs to be read. Of course, this requires more memory (see join_buffer_size). This algorithm is used for range, index and ALL join types.
SELECT E.ename, E.sal, JS.sal_min, JS.sal_max
FROM emp E,
job_sal JS
WHERE E.job = JS.job
AND E.sal NOT BETWEEN JS.sal_min AND JS.sal_max;
+----------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------+
| -> Filter: (E.sal not between JS.sal_min and JS.sal_max) (cost=499211.71 rows=442901)
-> Inner hash join (E.job = JS.job) (cost=499211.71 rows=442901)
-> Table scan on E (cost=1962.39 rows=996514)
-> Hash
-> Table scan on JS (cost=0.75 rows=5)
|
+----------------------------------------------------------------------------------------+
1 row in set (0.0023 sec)
EXPLAIN ANALYZE
- the time for returning the first row (in ms);
- the time for returning all rows (in ms);
- the number of rows read;
- the number of loops.
+-------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------+
| -> Filter: (E.sal not between JS.sal_min and JS.sal_max)
(cost=502072.82 rows=442901) (actual time=0.372..747.742 rows=915768 loops=1)
-> Inner hash join (E.job = JS.job)
(cost=502072.82 rows=442901) (actual time=0.355..575.011 rows=1000014 loops=1)
-> Table scan on E (cost=2534.62 rows=996514) (actual time=0.185..353.877 rows=1000014 loops=1)
-> Hash
-> Table scan on JS (cost=0.75 rows=5) (actual time=0.133..0.144 rows=5 loops=1)
|
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.7754 sec)
MySQL is OpenSSL only
With this version the support for YaSSL and WolfSSL libraries are removed, so MySQL could be compiled only with OpenSSL. I have personally always built MySQL with OpenSSL (i.e. using option -DWITH_SSL=system), because this is what comes with Slackware, but of course the more important is that "the MySQL/OpenSSL combination is very well tested and production proven" as Georgi Kodinov explained in his post MySQL is OpenSSL-only now !.Spatial
Point
and Point
, or Point
and MultiPoint
as before.Update 2019-11-09: That timings are in milliseconds wasn't specified in the manual, so I filed bug 97492 as I also think that it would be good to print the unit next to timings.
Публикувано от
Georgi D. Sotirov
в
12:55
0
коментара
Етикети: MySQL
2019-09-29
New usages of TREE explain plan in MySQL
- HASH joins would be visible only in TREE format. I find this rather strange, because you would expect to see this operation no matter the format of the plan, so I hope it's extended in future.
- EXPLAIN ANALYZE would actually run the query and provide timing information about it's execution. I would also include additional iterator-based information about how the optimizer estimations compares to the actual execution.
Публикувано от
Georgi D. Sotirov
в
16:59
0
коментара
Етикети: MySQL
2019-07-31
New features in MySQL 8.0.17
Multi-valued indexes
CREATE TABLE translators (
id INT AUTO_INCREMENT,
jdata JSON,
PRIMARY KEY(id)
);
- Case 1 - array of strings
INSERT INTO translators (jdata)
VALUES ('{"name": "T1", "langs": ["English", "French", "Spanish"]}'),
('{"name": "T2", "langs": ["English", "Spanish"]}'),
('{"name": "T3", "langs": ["French", "Spanish"]}');
SELECT id, jdata->>'$.name', jdata->'$.langs'
FROM translators
WHERE 'English' MEMBER OF (jdata->'$.langs');
SELECT id, jdata->>'$.name', jdata->'$.langs'
FROM translators
WHERE JSON_OVERLAPS(jdata->'$.langs', '["English"]');
+----+------------------+----------------------------------+
| id | jdata->>'$.name' | jdata->'$.langs' |
+----+------------------+----------------------------------+
| 1 | T1 | ["English", "French", "Spanish"] |
| 2 | T2 | ["English", "Spanish"] |
+----+------------------+----------------------------------+
2 rows in set (0.00 sec)
ALTER TABLE translators
ADD INDEX idx_langs_arr ((CAST(jdata->'$.langs' AS CHAR(8) ARRAY)));
- Case 2 - array of objects
INSERT INTO translators (jdata)
VALUES ('{"name": "T1", "langs": [{"lang": "English"}, {"lang": "French"}, {"lang": "Spanish"}]}'),
('{"name": "T2", "langs": [{"lang": "English"}, {"lang": "Spanish"}]}'),
('{"name": "T3", "langs": [{"lang": "French"}, {"lang": "Spanish"}]}');
SELECT id, jdata->>'$.name', jdata->'$.langs[*].lang'
FROM translators
WHERE 'English' MEMBER OF (jdata->'$.langs[*].lang');
SELECT id, jdata->>'$.name', jdata->'$.langs[*].lang'
FROM translators
WHERE JSON_OVERLAPS(jdata->'$.langs[*].lang', '["English"]');
ALTER TABLE translators
ADD INDEX idx_langs_obj ((CAST(jdata->'$.langs[*].lang' AS CHAR(8) ARRAY)));
JSON
- Function JSON_OVERLAPS compares two JSON documents and returns true "if the two document have any key-value pairs or array elements in common". Like MEMBER OF and JSON_CONTAINS function JSON_OVERLAPS may benefit from multi-value indexes.
- Functions JSON_SCHEMA_VALID and JSON_SCHEMA_VALIDATION_REPORT are added in regards to JSON Schema support. The first one validates a JSON document against a JSON schema and returns true if valid otherwise false, so its usable as a CHECK Constraint. The second one would provide insights into validation errors in the form of JSON document.
Cast to FLOAT, DOUBLE and REAL
SELECT CAST('1.23.34' AS FLOAT) cast_res, CONVERT('1.23.34', FLOAT) conv_res;
+--------------------+--------------------+
| cast_res | conv_res |
+--------------------+--------------------+
| 1.2300000190734863 | 1.2300000190734863 |
+--------------------+--------------------+
1 row in set, 2 warnings (0.00 sec)
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1.23.34' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1.23.34' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
SELECT CAST('1.23.34' AS DOUBLE) cast_res, CONVERT('1.23.34', DOUBLE) conv_res;
SELECT CAST('1.23.34' AS REAL) cast_res, CONVERT('1.23.34', REAL) conv_res;
+----------+----------+
| cast_res | conv_res |
+----------+----------+
| 1.23 | 1.23 |
+----------+----------+
1 row in set, 2 warnings (0.00 sec)
CLONE command
- MySQL InnoDB Cluster from scratch – even more easy since 8.0.17 by Frédéric Deschamps;
- Clone: Create MySQL instance replica by Debarun Banejee; and
- Create an Asynchronous MySQL Replica in 5 minutes again by Frédéric Deschamps.
Deprecations
- Function FOUND_ROWS and query modifier SQL_CALC_FOUND_ROWS. Documentation suggests using COUNT(*) to find the number of rows.
- Numeric data type attributes:
- Integer display width. There is now warning "1681 Integer display width is deprecated and will be removed in a future release." if you try to create tables with INT(11) for example. I personally have such definitions as provided by mysqldump and MySQL Workbench, so I'll have to remove them all, before the statements are rejected with syntax error in future.
- The ZEROFILL attribute. I never used it anyway.
- The UNSIGNED attribute for FLOAT, DOUBLE, and DECIMAL data types. I haver used it as well.
- AUTO_INCREMENT support for FLOAT and DOUBLE data types. Anyone?
- The syntax FLOAT(M,D) and DOUBLE(M,D) for specifying number of digits for floating point types.
- Logical operators && (double ampersand), whish is synonym for AND, || (double pipe), which is synonym for OR and ! (exclamation mark), which is synonym for NOT. I'm not sure I ever used any of these even in daily queries, because I find using AND, OR and NOT a lot more expressive.
- BINARY keyword for specifying _bin collations. I never used this as well.
Bug fixes
SET binlog_row_image = 'minimal';
UPDATE tst SET end_date = '2019-04-20' WHERE id = 1;
Публикувано от
Georgi D. Sotirov
в
19:23
1 коментара
Етикети: Development, MySQL
2019-06-05
TREE explain format in MySQL 8.0.16
- TRADITIONAL - the old tabular explain plan suitable for console;
- JSON - the explain plan in JSON format used by MySQL Workbench to display nice graphics in Visual Explain, which I use in presentations.
Query 1: Using LEFT OUTER JOIN and derived table
SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_salFROM dept D
LEFT OUTER JOIN
(SELECT E.deptno,
MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
GROUP BY E.deptno
) AS DT
ON DT.deptno = D.deptno;
Query 2: Using LATERAL derived table
SELECT D.dname, LDT.min_sal, LDT.avg_sal, LDT.max_salFROM dept D,
LATERAL
(SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
WHERE E.deptno = D.deptno
) AS LDT;
Query 1
+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+| id | select_type | table || type || key | key_len | ref | rows || Extra |
+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+
| 1 | PRIMARY | D || ALL || NULL | NULL | NULL | 4 || NULL |
| 1 | PRIMARY | <derived2> || ref || <auto_key0> | 5 | dept_emp.D.deptno | 2 || NULL |
| 2 | DERIVED | E || index || fk_deptno | 5 | NULL | 14 || NULL |
+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+
3 rows in set, 1 warning (0.01 sec)
+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `dept_emp`.`D`.`dname` AS `dname`... left join (
/* select#2 */ select `dept_emp`.`E`.`deptno` AS `deptno`,min(`dept_emp`.`E`.`sal`) AS `min_sal`... |
+-------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Query 2
+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+| id | select_type | table || type || key | key_len | ref | rows || Extra |
+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+
| 1 | PRIMARY | D || ALL || NULL | NULL | NULL | 4 || Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> || ALL || NULL | NULL | NULL | 2 || NULL |
| 2 | DEPENDENT DERIVED | E || ref || fk_deptno | 5 | dept_emp.D.deptno | 1 || NULL |
+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+
3 rows in set, 2 warnings (0.0006 sec)
+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select `dept_emp`.`D`.`dname` AS `dname`, ... join lateral (
/* select#2 */ select min(`dept_emp`.`E`.`sal`) AS `min_sal`... |
+-------+------+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Query 1
+-----------------------------------------------------------------+| EXPLAIN |
+-----------------------------------------------------------------+
| -> Nested loop left join
-> Table scan on D
-> Index lookup on DT using <auto_key0> (deptno=d.deptno)
-> Materialize
-> Group aggregate: min(e.sal), avg(e.sal), max(e.sal)
-> Index scan on E using fk_deptno |
+-----------------------------------------------------------------+
1 row in set (0.0025 sec)
Query 2
+----------------------------------------------------------------------+| EXPLAIN |
+----------------------------------------------------------------------+
| -> Nested loop inner join
-> Invalidate materialized tables (row from D)
-> Table scan on D
-> Table scan on LDT
-> Materialize (invalidate on row from D)
-> Aggregate: min(e.sal), avg(e.sal), max(e.sal)
-> Index lookup on E using fk_deptno (deptno=d.deptno) |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.0007 sec)
Note (code 1276): Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1
Публикувано от
Georgi D. Sotirov
в
19:27
0
коментара
Етикети: MySQL
2019-05-24
Translations of Bugzilla 5.0.5 and 5.0.6
About the translation
Upgrade
This morning, after I prepared the translations, I managed to upgrade my installation, which was absolutely flawless following the instructions for direct upgrade from Git. With 5.0.5 the table bugs_fulltext was converted from MyISAM to InnoDB, and with 5.0.6 the type of the columns type_id in the tables flaginclusions, flagexclusions and flags was changed from smallint to mediumint (see Integer types), which show allow for many more flagtypes. I got delayed a little only because I had to cleanup some local changes and hide (with .gitignore) some files (logs, patches, archives with translations, and etc.). That reminded me to issue a pull request for one of my local changes - adding of viewport meta tag, which is important nowadays for support of mobile devices.Download the translations
Happy using! And of course happy Bulgarian Education and Culture and Slavonic Literature Day (a.k.a Saints Cyril and Methodius' Day)!
Публикувано от
Georgi D. Sotirov
в
13:25
0
коментара
Етикети: Bugzilla BG, Translations
2019-04-30
New features in MySQL 8.0.16 ecosystem
Start pushing for Semantic Versioning to be adopted at MySQL, and you will avoid this kind of miscommunications— Giuseppe Maxia (@datacharmer) February 1-st 2019
MySQL Server
So, what's new in the new release of the server. Here are my picks.CHECK constraints
ON UPDATE
, ON DELETE
).Here's my first example. I think it's quite common to have two dates in a table (e.g. start and end date) that should represent the beginning and end of something, so they should be in chronological order. Let's try to do this on the emp table from the example DEPT and EMP schema of Oracle, which I adapted for MySQL.
ADD COLUMN retdate DATE AFTER hiredate,
ADD CONSTRAINT ret_after_hire CHECK (retdate > hiredate);
UPDATE emp
SET retdate = STR_TO_DATE('1019-04-25', '%Y-%m-%d')
WHERE empno = 7369;
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
ADD CONSTRAINT emp_chks CHECK (hiredate >= CURDATE() AND sal > 0);
ADD COLUMN created DATE DEFAULT (hiredate);
MODIFY COLUMN created DATE NOT NULL DEFAULT (CURDATE()),
ADD CONSTRAINT emp_chks CHECK (hiredate >= created AND sal > 0);
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal)
VALUES
(9999, 'MULDER', 'INVESTIG.', 7839, '2019-04-25', 4242);
Spatial data
Server does all upgrades
--validate-config
for validation of server configuration much like system administrators are used to do so with other servers (e.g. Apache). It's really important especially for production environments where unexpected downtime could be extremely unpleasant. Read more in Server Configuration Validation.System users
MySQL accounts are now categorized and thus distinguished by system (who posses the SYSTEM_USER privilege) and regular users (who does not). This allows for better separation of roles as only system users could perform certain administrative operations on system accounts as previously any user having the appropriate privileges could for example delete any account or kill connections belonging to any user. It's also possible to revoke global privileges partially for certain schemas (i.e. like an exceptions) through the new system variable partial_revokes, which previously required granting privileges separately for each existing schemes and adding grants for each new schema.Security
MySQL Router
MySQL Shell
MySQL Workbench
Публикувано от
Georgi D. Sotirov
в
13:54
0
коментара
Етикети: Development, MySQL
2019-03-30
Finishing with Dir.bg's mail
- copying about 1 GB mails, which shouldn't be a problem considering the capacity of today's connections as long as the service becomes available again to which I strongly hope, because I have some important messages.
- redirection of N in count services to my mail address in Gmail. This would be a tough task, because I'm not even sure I'll be able to enumerate all although I keep the mails with registration confirmation, but for now I'm considering keeping the address in Dir.bg until I transfer everything. I have already started, because I didn't receive the mails from BGOUG.
- updating web pages in Internet, source code and others.
That's it. I maybe post something short, when I'm done with the migration.
Публикувано от
Georgi D. Sotirov
в
13:05
0
коментара
Етикети: Problems
2019-02-10
First migrations from CVS to Git
Repository preparation
Revision log messages. You may have a convention for writing revision log messages in CVS, but I did not have one for my personal projects, so I have written comments on single line and on multiple lines as unnumbered list like this:* Add this
* Modify that
I had to modify these comments in CVS, before the migration, because these simply do not look good in TortoiseGit and GitHub. I also aligned some similar comments on files committed in few minutes time span, so that the migration could group them in a single revision. Depending on whether you had a convention for the format of the revision log messages in CVS you may need to make other adjustments. The easiest way in such case may be to modify directly the revision control files with perl, sed, awk, etc. while of course paying caution not to ruin your history (i.e. such operations should not be performed on "live" production repository).
Authors. You should be able to identify all your authors, because in CVS repositories they are normally UNIX login names and the users may no longer exist in the system. I've seen even cases where the same username was reused by different developers having the same given and family names. It's hard to identify and fix such revisions unless you're aware of developers' internship in the company, but what if their time in the company overlaps?
Hopefully, I do not have this problem with my personal repositories, but still I had to rewrite my username as full name and email address (see more about this in chapter Step One: The Author Map of Eric Raymond's DVCS migration HOWTO). It's very important to rewrite the authors, because otherwise the commits won't be properly identified in GitHub (see Contributions that are counted). If you need to fix usernames it's preferable to do it in the CVS repository before the conversion. And the easiest way to map usernames to authors is to rewrite the converted Git repository as I write about below.
Tools
In such a process one of the most important things is to chose the right tools, because you simply cannot go without them. Last year at work we evaluated several options and finally choose cvs2git, which is part of cvs2svn project. The command is written in Python and supports various options. For me it is important to make the best possible migration, so I had to make the following modifications in the example options file:KeywordHandlingPropertySetter('expanded'),
from cvs2svn_lib.keyword_expander import _KeywordExpander
# Ensure dates are expanded as YYYY/MM/DD as in CVS 1.11
_KeywordExpander.use_old_date_format()
The first line ensures that CVS keywords (e.g. $Id$, $Date$, etc.) are expanded in text files (see topic cvs2svn changes 'date' string in source codes). The following three lines ensure that dates in keywords are expanded as in CVS 1.11 or in YYYY/DD/MM format, instead of the default format YYYY-MM-DD as in CVS 1.12 and SVN.
Migration
The way to covert a CVS repository to Git is described in cvs2git's documentation (see chapter Usage), so just read it. I wrote myself a shell script to execute the different steps defined there. Initially I used cvs2git only with command line options, but because of my requirements for keywords expansion (see above), I had to improve the script to generate options file and use it instead. Like this I retained the way I call the script by just providing path to the CVS repository.For now I haven't had problems with the migration - the history was represented properly in Git. However, with many tags and branches over different files things could get really messy as I found out with the internal project at work. After the migration I check the result and if necessary fix more revision log messages. Then, I just rewrite authors with the script given in Changing author info and finally import the repository to GitHub (e.g. I use git daemon --verbose --export-all --base-path="/path/to/repo" to serve the converted repository). After the import to GitHub I have to add LICENSE and README.md files, but this is quite trivial directly through the web interface.
Публикувано от
Georgi D. Sotirov
в
15:35
0
коментара
Етикети: Git, Version Control