Georgi Sotirov's Blog

My personal and professional life

2018-08-22

LIbreOffice 6.1 and named parameters substitution

I've recently upgraded to LibreOffice 6.1 eager to try to new features and improvements. However, I soon noticed that all my sub forms in Base are broken. Those were sub forms executing SQL statements like:

SELECT id, name FROM persons

So I could get additional information from related tables by mapping master to slave fields. Apparently in earlier versions Base was rewriting the query, but this is no more the case. You'll get errors like:
  • The data content could not be loaded.
  • SQL Status: 07009 Invalid descriptor index.
  • You tried to set a parameter at position '1' but there is/are only '0' parameter(s) allowed. One reason may be that the property "ParameterNameSubstitution" is not set to TRUE in the data source.
I quickly opened the release notes and found what I've omitted previously:

Base
  • The ODBC, JDBC and Firebird SDBC drivers/bridges previously applied named parameter substitution (for ODBC and JDBC: when enabled) to all SQL commands, including those that were tagged in the GUI as "execute SQL command directly", thereby not preserving the SQL command exactly as typed. Named parameter substitution now is applied only to SQL queries that are generated from a LibreOffice SQL parse tree, leaving "execute SQL command directly" commands completely untouched.
So the solution is to rewrite the sub form queries like this (which is the unnamed syntax used by MySQL):

SELECT name FROM persons WHERE id = ?

Or in other words write the named parameter explicitly in the query.

2018-07-11

Why loading data with MySQL Workbench is so slow?

I recently started a small personal project for statistics on 24 Hours of Le Mans endurance race of which I'm a fan. I started by gathering data and for loading them started using data import functionality in MySQL Workbench. The functionality is accessible from the context menu of a table - you click with the right button on a table and then on the item "Table Data Import Wizard". It's a pretty neat feature, because it allows you:
  • first, to select a local file;
  • then, to chose destination - either an existing table (and truncate it if necessary) or a new one;
  • then, to configure settings (e.g. CVS options like line and column separators and enclosure character), file encoding and column mappings;
  • finally, to start the import and watch for errors (a message is printed for each line).
However, with the growing number of lines in the CSV file, I noticed that the data import becomes slower and slower (I didn't pay much attention initially), so for about 4500 lines it now takes about 5 minutes (!) in a local network on a server that is not that much loaded with work. I cannot imagine if I have to load hundreds of thousands or even millions of lines like this.
I tried the same with LOAD DATA INFILE and the same data was loaded in about 1 second, even though I have some triggers on the table checking newly inserted data. The difference is quite obvious.
So why is loading data in MySQL Workbench so slow compared to LOAD DATA INFILE statement?
By checking the general log the difference becomes immediately apparent - MySQL Workbench generates an INSERT statement for each line of the input file an executes these statements consecutively one by one. That's quite expensive and time consuming especially on the network (see Optimizing INSERT Statements).
But why it is so? Two possible explanations:
  1. LOAD DATA INFILE statement requires the file to be available to the server (i.e. on a local file system accessible to the server). However, with LOCAL keyword the client could transfer the file to the server, so that the server could read it. The overhead in this case would be the transfer of the file over the network. This however requires that both server and client have local data loading capabilities enabled (for server see local_infile system variable and for client it's a compile time ENABLED_LOCAL_INFILE CMake option). So although I have this capability enabled on the server apparently it's disabled in the MySQL client used by Workbench, because trying to execute LOAD DATA INFILE with the LOCAL keyword gives Error Code: 1148. The used command is not allowed with this MySQL version (see more in Security Issues with LOAD DATA LOCAL).
  2. Using INSERT statement with multiple VALUES lists to insert many rows at a time would not provide Workbench with the possibility to give information for each processed line from the input file.
If I'm right for the above, then I would expect the data import log in MySQL Workbench to be more advanced. First of all, no line information is printed, so the messages "Data import" (successful?) and various possible errors are useless. I had to copy the log and index the errors in a text editor in order to identify the problem in the data file. Then, I'd like to have information only for errors (e.g. some kind of filtering). There is a progress bar, but without any numbers - I think a message like "Processing line X of NB_LNS_IN_FILE" would be quite useful, but this would require counting the lines in advance. There is also no option to save the import log and copying it requires scrolling (i.e. no Ctrol+A or Select everything) exists as an option.
In any case, I find data import functionality in MySQL Workbench to be quite useful, but be ware if you need to load large amounts of data (i.e. several thousand lines or more). In case you need to load such amounts of data it's highly preferable to transfer the data file locally and use LOAD DATA INFILE, which according to the manual "reads rows from a text file into a table at a very high speed".

2018-06-12

Is it time for Slackware 15?

Last Slackware release (14.2) appeared almost 2 years ago and upgraded many packages, but things are moving faster nowadays, so I just wonder isn't it time for a new release? I'm following -current, so I already see some notable changes and upgrades accumulating there, including:
  • Base system:
    • Linux Kernel 4.14 LTS;
    • GNU C Library 2.27;
  • Servers:
    • Postfix 3.3 (replacing Sendmail as MTA) and Dovecot 2.3 (replacing imapd as IMAP daemon);
    • Apache 2.4.33 (with HTTP/2 support);
    • ProFTPD 1.3.6 (with mod_sftp and mod_facl support);
    • Samba 4.6;
    • MariaDB 10.3.7 (very nice given what I recently wrote about new functionalities);
    • Bind 9.11, dhcp 4.3.4, ntp 4.2.8p11 (now running as ntp:ntp) and more;
  • Development:
    • GNU's Compiler Collection 7.3 and LLVM 6.0;
    • Perl 5.26, PHP 7.2, Python 3.6, Ruby 2.5 and Rust 1.26;
    • Git 2.17 and Mercurial 4.5;
  • GUI:
    • X.Org 1.20;
    • KDE 4.14.36 (I was hoping for Plasma 5.x though);
    • SeaMonkey 2.49, Firefox 59.0 and Thunderbird 52.8;
    • GTK+ applications such as Pidgin 2.13, Gimp 2.10, etc.
There are 66 new packages in total among them FFmpeg 3.4, new libraries like opus, lame, libbluray, speex, id3lib, libwebp and various Python modules (e.g. docutils, idna and six), which I already have on SlackPack. I noted also SDL2 library, which I do not have and which is nice, because some packages already need it.
There are many new packages I was waiting like Postfix, Dovecot, Python 3, MariaDB 10.3, GCC 7.3, which should make a great new release whichever and whenever it's going to be. The only disappointment for me is that KDE is still 4.14 and Qt 4.8, but some applications already need KDE 5 and Qt 5 (e.g. like KDESvn, cppcheck's GUI, etc.).
Package pkgtools is already 15.0 in -current, so is it time for Slackware 15?

2018-05-30

First attempts with in-place upgrade from MySQL 5.7 to 8.0

I have previously written, that in-place upgrade is not possible from MySQL 8.0.4 to 8.0.11, but it's officially supported between GA releases (but not through MySQL Installer - see #90619 and more likely #79315). So today I've made my first attempts to migrate my MySQL 5.7 server to MySQL 8.0 by copying data and configuration in a Slackware virtual machine. Let's start...
  • First attempt:
2018-05-30T16:42:28.485076Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18078
2018-05-30T16:42:28.727435Z 1 [ERROR] [MY-012526] [InnoDB] InnoDB: Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.22. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2018-05-30T16:42:28.727509Z 1 [ERROR] [MY-012930] [InnoDB] InnoDB: Plugin initialization aborted with error Generic error.
2018-05-30T16:42:29.228125Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2018-05-30T16:42:29.228520Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-05-30T16:42:29.228554Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-05-30T16:42:29.235976Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.


Well, I really took my chances by just copying the data directory of the running MySQL 5.7 server :-)

Solution: Make a clean copy of the data directory when the server is stopped.
  • Second attempt:
2018-05-30T16:53:06.475573Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18236
2018-05-30T16:53:08.332393Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
InnoDB MEMCACHED: Memcached uses atomic increment
 InnoDB_Memcached: Unable to open table 'innodb_memcache/containers'
 InnoDB_Memcached: Please create config table'containers' in database 'innodb_memcache' by running 'innodb_memcached_config.sql. error Table not found'
Failed to initialize instance. Error code: 13
2018-05-30T16:53:13.256478Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T16:53:13.257272Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Address already in use
2018-05-30T16:53:13.257283Z 0 [ERROR] [MY-010257] [Server] Do you already have another mysqld server running on port: 3306 ?

2018-05-30T16:53:13.257298Z 0 [ERROR] [MY-010119] [Server] Aborting
 InnoDB_Memcached: Memcached plugin is still initializing. Can't shut down it.
2018-05-30T16:53:29.161133Z 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.
2018-05-30T16:53:30.304586Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.


Sure, I have another MySQL 8.0 server listening on port 3306, so let's fix the configuration.

Solution: Change port in configuration file.
  • Third attempt:
2018-05-30T17:04:57.144549Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18571
2018-05-30T17:04:57.523116Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2018-05-30T17:04:57.630947Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T17:04:57.633668Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-05-30T17:04:57.633699Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2018-05-30T17:04:57.633776Z 0 [ERROR] [MY-011071] [Server] unknown option '--secure-auth'
2018-05-30T17:04:57.633787Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-05-30T17:04:57.633840Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-05-30T17:04:58.636827Z 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.


Yeah, data dictionary is not yet upgraded, but for this I need a running server and there is also error about unknown configuration option.

Solution: Remove secure-auth option from configuration file as it was removed in 8.0.3 (see also its release notes).
  • Fourth attempt:
2018-05-30T17:06:21.153201Z 0 [ERROR] [MY-011071] [Server] unknown variable 'max_tmp_tables=64'

Solution: Same case as above. Remove max_tmp_tables option from configuration file.
  • Fifth attempt:
2018-05-30T17:10:36.750614Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'

Yep, I forgot that query cache is removed too...

Solution: Remove query_cache_type and query_cache_size options from configuration file as Query Cache was removed in MySQL 8.
  • Sixth attempt:
2018-05-30T17:11:18.941821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18803
2018-05-30T17:11:19.333818Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T17:11:19.335972Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-05-30T17:11:19.336010Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2018-05-30T17:11:19.348988Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2018-05-30T17:11:19.349014Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2018-05-30T17:11:19.349020Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2018-05-30T17:11:19.349026Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2018-05-30T17:11:19.349031Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2018-05-30T17:11:19.349035Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].

2018-05-30T17:11:19.352944Z 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 49, found 45. The table is probably corrupted
2018-05-30T17:11:19.352980Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2018-05-30T17:11:19.352987Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2018-05-30T17:11:19.352992Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2018-05-30T17:11:19.352997Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.

2018-05-30T17:11:19.353420Z 0 [ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.
2018-05-30T17:11:19.365069Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2018-05-30T17:11:19.366847Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.

2018-05-30T17:11:19.366874Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2018-05-30T17:11:19.368238Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2018-05-30T17:11:19.368273Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.

2018-05-30T17:11:19.368280Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2018-05-30T17:11:19.373821Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.11'  socket: '/var/lib/mysql/mysqld57.sock'  port: 3316  Source distribution.


Success! The server is already running, but apparently needs upgrade as system tables are either missing or still in MyISAM. However, should not this be done automatically? At least I'm getting this impression when reading Step 8 for In-Place Upgrade in the manual. Anyway, step 9 is to run mysql_upgrade, so after running the command the errors in the log are gone.

Of course, I'm was playing here, but at least I now have a configuration file ready for 8.0 and have validated the upgrading procedure for my use case. However, I have to check also my applications compatibility, so the real upgrade would happen later.

P.S. A nice way to check for configuration problems is described in How to check MySQL Config files by Peter Zaitsev.

2018-05-28

MariaDB 10.3 adds even more development features than MySQL 8.0

Last Friday (May 25-th) MariaDB 10.3.7 was released as stable (General Availability). I was finally been able to check the release notes and (not surprisingly) I found some features that we're still missing in MySQL (including the released a month ago 8.0.11 GA for which I wrote). I'd like to highlight the following notable changes related to development (of which I'm interested the most):
Wow. They've been working hard, that's for sure. I'll have yet to test all these new features myself, but I'm quite excited to see them available in "one of the most popular database servers". Unfortunately, neither one of these is available in MySQL. Here below is my list of what's still missing in MySQL (and MariaDB):

Features MariaDB MySQL Standard
SQL Modify table data based on subquery on the same table 10.3.2 (2017-11-09) n/a
EXCEPT or MINUS and INTERSECT set operators 10.3.0 (2017-04-16) n/a SQL:2003
Temporal queries (AS OF) 10.3.4 (2018-01-18) n/a SQL:2011
DDL CHECK constraints 10.2.1 (2016-07-04) n/a SQL:1999
Partitioning of tables with foreign keys n/a n/a
Temporal tables (WITH SYSTEM VERSIONING) 10.3.4 (2018-01-18) n/a SQL:2011
CREATE OR REPLACE for tables, triggers and stored routines 10.0.8 (2014-02-10), 10.1.4 (2015-04-13) and 10.1.3 (2015-03-02) n/a
Enable/disable for triggers n/a n/a
Sequences 10.3.0 (2017-04-16) n/a SQL:2003
A working schema rename (i.e. RENAME {DATABASE | SCHEMA}) statement n/a n/a
Views Saving of the exact view statement in the server n/a n/a n/a
Fully updatable views with INSTEAD OF triggers n/a n/a SQL:2008
Materialized views n/a n/a
Stored routines Default arguments n/a n/a
FOR loop 10.3.3 (2017-12-23) n/a
Anonymous code blocks (or compound statements outside routines) 10.1.1 (2014-10-17) n/a
Records, column and row anchored types 10.3.0 (2017-04-16) n/a
Stored modules or packages 10.3.5 (2018-02-26) n/a
Statement and schema level triggers n/a n/a
Debugging facilities n/a n/a n/a
External routines in JavaScript, Perl, PHP, etc. n/a n/a

It's quite apparent from the above table that MySQL has more gray spots than MariaDB. I've added also the related SQL standard where applicable, so in this regards MariaDB has broader standards support. It seems to me than in its latest stable version MariaDB has implemented more "Oracle like" features, than MySQL which is developed by Oracle.
So is it worth it changing the database? I believe I'll continue using both MySQL and MariaDB (which is default database in most Linux distributions) and the reasons for this are that these are becoming more and more different with the time. There are development features in MySQL (e.g. JSON data type, the Document store), which do not exist in MariaDB and vice versa. I'm still quite used using MySQL Workbench for daily work with MySQL databases, but the support for MariaDB in it is fading and unless MySQL adds the same SQL features, I doubt the new development functionalities would be usable from the Workbench, which impacts my work.

2018-05-15

MySQL Shell 8 and automatically generated docment IDs

Why I'm getting error ERROR: 5115: Document is missing a required field in MySQL Shell 8.0.11 connected to MySQL Server 5.7.22? Consider the following session in which I'm  first connecting to the server, creating a new schema, then a collection inside the schema and finally trying to create a document without explicitly providing identifier:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@server
Creating a session to 'root@
server'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1027578 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use to set one.

MySQL [server+ ssl] JS> session.createSchema('test')


MySQL [
server+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [
server+ ssl/test] JS> per_coll = db.createCollection('persons')


MySQL [
server+ ssl/test] JS> per_coll.add({name:'Georgi'})
ERROR: 5115: Document is missing a required field

MySQL [
server+ ssl/test] JS> \py
Switching to Python mode...

MySQL [server+ ssl/test] Py> pcol = db.get_collection('persons')

MySQL [server+ ssl/test] Py> pcol.add({'name':'Georgi'})
ERROR: 5115: Document is missing a required field


In this case MySQL Shell should generate the identifier as specified in the manual (see MySQL 5.7 Reference manual, chapter 19.4.4.2 Add Documents), where it's written:

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. If the document passed to the add() method does not contain the _id field, MySQL Shell automatically inserts a field into the document and sets the value to a generated universal unique identifier (UUID).
When connected to MySQL 8.0 Server there is no such problem:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@mysql8
Creating a session to 'root@mysql8'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 6646 (X protocol)
Server version: 8.0.11 Source distribution
No default schema selected; type \use to set one.

MySQL [mysql8+ ssl] JS> session.createSchema('test')


MySQL [mysql8+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [mysql8+ ssl/test] JS> per_coll = db.createCollection('persons')


MySQL [mysql8+ ssl/test] JS> per_coll.add({name:'Georgi'})
Query OK, 1 item affected (0.2450 sec)


MySQL [192.168.79.46+ ssl/test2] JS> per_coll.find()
[
    {
        "_id": "00005adc69630000000000000002",
        "name": "Georgi"
    }
]
1 document in set (0.2035 sec)


I also tried connecting MySQL Server 5.7 with MySQL Shell 1.0 and again no problem:

$ mysqlsh
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in Python mode. Use \sql to switch to SQL mode and execute queries.
mysql-py> \connect root@localhost
Creating a Session to 'root@localhost'
Enter password:
Your MySQL connection id is 1028442 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use to set one.
mysql-py> session.create_schema('test')

mysql-py> \use test
Schema `test` accessible through db.
mysql-py> pcol = db.create_collection('persons')
mysql-py> pcol.add({'name':'Georgi'})
Query OK, 1 item affected (0.16 sec)
mysql-py> pcol.find()
[
    {
        "_id": "aa0960462c58e811b27610feed07afc3",
        "name": "Georgi"
    }
]
1 document in set (0.00 sec)


The post MySQL Document Store Document IDs. by Dave Stokes is the top (and in fact only) result in Google for the error (when searching for exact match with double quotes). It points to chapter 5.1.1 Understanding Document IDs of X DevAPI User Guide, in which it's written:
X DevAPI relies on server based document ID generation, added in MySQL version 8.0.11, which results in sequentially increasing document IDs across all clients.
...
Whenever an _id field value is not present in an inserted document, the server generates an _id value.
Which quite explains the issue. Apparently, MySQL Shell 8.0.11 expects that the server would generate the identifier (for  _id column) without considering whether the Server is 5.7 or 8.0, which for me is a bug to report later.

Update 2018-05-16: Oracle verified my bug report (see bug 90876). Hope it would be fixed.

2018-05-01

Migration to Git and GitHub

Several years ago I decided to migrate all my personal projects to a distributed version control system (DVCS), but simply didn't found the time. Nowadays Git is de facto standard for version control, so I decided to migrate to Git and put all my personal projects to GitHub. So this year I would be gradually migrating my projects, starting with the least active ones. Most of them are still on CVS, which makes the migration more complex given CVS and Git are quite different in many aspects, but also because for me it's important to keep the history of my modifications (i.e. I do not want just to copy the source and initialize and new Git repository inside). Wish me luck and cross your fingers for me ;-)

2018-04-29

MySQL 8.0 released

Last Thursday (April 19-th) Oracle released MySQL 8.0.11 as General Availability (GA). It's a big new release, including many new features, but my favorites in regards to development are Common Table Expressions (CTO), Window functions, regular expression improvements, NOWAIT and SKIP LOCKED for locking reads, GROUPING function, descending indexes and JSON enhancements. However, there's lot more to MySQL 8 notably transactional data dictionary, atomic DDLs, roles, resource groups, invisible indexes, histograms and many more. There are also performance improvements including Unicode, JSON and regular expressions.
I've been playing with MySQL 8 since Release Candidate 1 was released last September. I have installed MySQL 8.0.3 (RC1) in a Slackware virtual machine and setup replication from my MySQL 5.7 server, so I could experiment with some real data. I'm generally excited with the new development features, but I could take even more if Oracle delivers :-)
There are few things to be aware of with MySQL 8.0:

  • new default authentication plugin caching_sha2_password: This was the first "problem" I encountered last September. If you still need to connect the old way then just add the following to your configuration file and restart the server:

    default_authentication_plugin = mysql_native_password

    See also Frédéric Deschamps's blog post Migrating to MySQL 8.0 without breaking old application.
  • MySQL X plugin is now enabled by default: If you're migrating from MySQL 5.7 or from a previous MySQL 8.0 release (DMRs or RCs), then remove the following directive from your configuration file in order to avoid warnings in the logs:

    plugin-load-add = mysqlx.so
  • it's not possible to upgrade from a previous non-GA release: I was surprised initially and even raised a bug (see 90619) after twitting it first, but then I recalled my previous upgrades and realized that this is normal for MySQL (see MySQL Upgrade Strategies) whether I like it or not. Upgrade from 5.8 to 8.0 is officially supported, but I haven't tried it yet. I would share my experience later.
MySQL 8 is a great new release, which I greatly encourage you to try out, because it offers many useful new features that are part of modern SQL. I however expect that the adoption of this new version would take some time and a clear sign for this is that some of the new JSON functionality was back ported to the previous GA release (5.7.22).

2018-03-04

How do I build my server's kernels

With the recent Meltdown and Spectre critical vulnerabilities, I had to recompile my server's kernel quote often, so I established the following routine:

  1. Download incremental patches:

    for pn in `seq 115 118`; do
      wget "https://cdn.kernel.org/pub/linux/kernel/v4.x/incr/patch-4.4.${pn}-$((pn+1)).xz"
    done
  2. Unpack incremental patches:

    for pn in `seq 115 118`; do
      xz -d patch-4.4.${pn}-$((pn+1)).xz
    done
  3. Apply incremental patches:

    for pn in `seq 115 118`; do
      patch -p1 < patch-4.4.${pn}-$((pn+1))
    done
  4. Update configuration:

    make oldconfig
  5. Build:

    time nice -+19 make

All this (except 4) could be automated in a bash script, but I'm feeling lazy, so I wrote it down here for my personal reference.

2018-03-01

I lost my phone :-(

Yesterday, I lost my phone Samsung A5.2016. However, "lost" is perhaps not the right word here, as what I actually done was to forget it in my youngest doughtier kindergarten ODZ 188. I went there to attend an open lesson on hygiene and upon entering the building I was calling my wife. Then, I left the phone on a cabinet, so to be able to put leggins as the weather was wet and that was it. I'm not normally leaving my phone just like this, but perhaps I was in a hurry and distracted. I noticed the lost about an hour later, when I was taking my daughter home, but calling my number was already giving me the "the phone is switched off or outside network coverage" operator message.
I returned to search my phone, but it was gone and nobody knew anyting. Through, the Facebook's group of my daughter's group in the kindergarten, I learned that one of the mothers had noticed my phone when going out from the open lesson, which means it disappeared somewhere between 16:30 EET and 16:45 EET as when I was leaving I haven't noticed any phone on the shelf and I took my daughters art from there. I'm not sure what should I think, but I just cannot comprehend how my phone was taken from a kindergarten, where I'm taking my daughter each working day. I'm already desperate that I would recover my phone, but I'll publish it's IMEI and serial number here for those who eventually may find out that their phone was stolen (although this is not sure).

Model: SM-A510F
IMEI: 352937/08/475020/4 (352937084750204)
Serial number: R58H90EPAFM

Anyway, I still have hope that I wold recover my phone, so if you stumble on this message and had bough a second hand phone recently, check your phone and let me know if it matches the information above. I would pay you the price which you've paid if the data is intact.

P.S. It's by the way the second third phone I'm losing in 9 12 years. The first last one was in a restaurant in Constanta, Romania in August 2009. It was a Nokia E65.

Update 2018-03-02 10:00 EET: This morning I filed a complaint with the police.
Update 2018-03-07 08:00 EET: As my wife properly remarked me, it's in fact the third time I'm loosing my phone. The first one was in August 2005 at a gas station returning from the wedding of one of my cousins. It was a Siemens C65.