emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Customizing dbdeployer

Planet MySQL - Sun, 03/04/2018 - 23:00

As of version 0.2.1, dbdeployer allows users to customize composite sandboxes more than ever. This is done by manipulating the default settings, which are used to deploy the sandbox templates.

In order to appreciate the customization capabilities, let's start with a vanilla deployment, and then we have a look at the possible changes.

$ dbdeployer deploy replication 8.0.4
Installing and starting master
Database installed in $HOME/sandboxes/rsandbox_8_0_4/master
. sandbox server started
Installing and starting slave 1
Database installed in $HOME/sandboxes/rsandbox_8_0_4/node1
. sandbox server started
Installing and starting slave 2
Database installed in $HOME/sandboxes/rsandbox_8_0_4/node2
. sandbox server started
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_8_0_4
run 'dbdeployer usage multiple' for basic instructions'

A regular replication sandbox has one master and two slaves. Each slave is inside a directory called nodeX.

The resulting sandbox has a directory called master, two nodeX directories, a shortcut for the master called m, and two shortcuts for the slaves called s1 and s2. There are also two management scripts called initialize_slaves and check_slaves.

$ ls -l ~/sandboxes/rsandbox_8_0_4/
total 152
-rwxr--r-- 1 user staff 1500 Mar 5 06:21 check_slaves
-rwxr--r-- 1 user staff 1160 Mar 5 06:21 clear_all
-rwxr--r-- 1 user staff 1617 Mar 5 06:21 initialize_slaves
-rwxr--r-- 1 user staff 806 Mar 5 06:21 m
drwxr-xr-x 22 user staff 748 Mar 5 06:21 master
-rwxr--r-- 1 user staff 806 Mar 5 06:21 n1
-rwxr--r-- 1 user staff 804 Mar 5 06:21 n2
-rwxr--r-- 1 user staff 804 Mar 5 06:21 n3
drwxr-xr-x 23 user staff 782 Mar 5 06:21 node1
drwxr-xr-x 23 user staff 782 Mar 5 06:21 node2
-rwxr--r-- 1 user staff 855 Mar 5 06:21 restart_all
-rwxr--r-- 1 user staff 804 Mar 5 06:21 s1
-rwxr--r-- 1 user staff 804 Mar 5 06:21 s2
-rw-r--r-- 1 user staff 173 Mar 5 06:21 sbdescription.json
-rwxr--r-- 1 user staff 1127 Mar 5 06:21 send_kill_all
-rwxr--r-- 1 user staff 1296 Mar 5 06:21 start_all
-rwxr--r-- 1 user staff 1680 Mar 5 06:21 status_all
-rwxr--r-- 1 user staff 1087 Mar 5 06:21 stop_all
-rwxr--r-- 1 user staff 4598 Mar 5 06:21 test_replication
-rwxr--r-- 1 user staff 1315 Mar 5 06:21 test_sb_all
-rwxr--r-- 1 user staff 1100 Mar 5 06:21 use_all

Now, let's see how we can change this. We'll start by listing the current defaults

$ dbdeployer defaults show
# Internal values:
"version": "0.2.1",
"sandbox-home": "$HOME/sandboxes",
"sandbox-binary": "$HOME/opt/mysql",
"master-slave-base-port": 11000,
"group-replication-base-port": 12000,
"group-replication-sp-base-port": 13000,
"fan-in-replication-base-port": 14000,
"all-masters-replication-base-port": 15000,
"multiple-base-port": 16000,
"group-port-delta": 125,
"master-name": "master",
"master-abbr": "m",
"node-prefix": "node",
"slave-prefix": "slave",
"slave-abbr": "s",
"sandbox-prefix": "msb_",
"master-slave-prefix": "rsandbox_",
"group-prefix": "group_msb_",
"group-sp-prefix": "group_sp_msb_",
"multiple-prefix": "multi_msb_",
"fan-in-prefix": "fan_in_msb_",
"all-masters-prefix": "all_masters_msb_"

The values that we want to change are master-name, master-abbr, node-prefix, slave-prefix, and slave-abbr. We can export the defaults to a file, and import them after editing the values we want to change.

$ dbdeployer defaults export defaults.json
# Defaults exported to file defaults.json
$ vim defaults.json
$ dbdeployer defaults import defaults.json
Defaults imported from defaults.json into $HOME/.dbdeployer/config.json

Now dbdeployer is using the new defaults.

$ dbdeployer defaults show
# Configuration file: $HOME/.dbdeployer/config.json
"version": "0.2.1",
"sandbox-home": "/Users/gmax/sandboxes",
"sandbox-binary": "/Users/gmax/opt/mysql",
"master-slave-base-port": 11000,
"group-replication-base-port": 12000,
"group-replication-sp-base-port": 13000,
"fan-in-replication-base-port": 14000,
"all-masters-replication-base-port": 15000,
"multiple-base-port": 16000,
"group-port-delta": 125,
"master-name": "primary",
"master-abbr": "p",
"node-prefix": "branch",
"slave-prefix": "replica",
"slave-abbr": "r",
"sandbox-prefix": "msb_",
"master-slave-prefix": "rsandbox_",
"group-prefix": "group_msb_",
"group-sp-prefix": "group_sp_msb_",
"multiple-prefix": "multi_msb_",
"fan-in-prefix": "fan_in_msb_",
"all-masters-prefix": "all_masters_msb_"
We have now *primary* for *master*, *replica* for *slave*, *branch* for *node*, and the abbreviations for master and slave changed to *p* and *r* respectively.
Let's see how these defaults can play together when we run the same command as we did before for replication. We first remove the previous deployment.

$ dbdeployer delete rsandbox_8_0_4
List of deployed sandboxes:
Running $HOME/sandboxes/rsandbox_8_0_4/stop_all
# executing "stop" on $HOME/sandboxes/rsandbox_8_0_4
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
Running rm -rf $HOME/sandboxes/rsandbox_8_0_4
Sandbox $HOME/sandboxes/rsandbox_8_0_4 deleted

The deployment command is the same as before, but the output changes:

$ dbdeployer deploy replication 8.0.4
Installing and starting primary
Database installed in $HOME/sandboxes/rsandbox_8_0_4/primary
. sandbox server started
Installing and starting replica 1
Database installed in $HOME/sandboxes/rsandbox_8_0_4/branch1
. sandbox server started
Installing and starting replica 2
Database installed in $HOME/sandboxes/rsandbox_8_0_4/branch2
.. sandbox server started
initializing replica 1
initializing replica 2
Replication directory installed in $HOME/sandboxes/rsandbox_8_0_4
run 'dbdeployer usage multiple' for basic instructions'

This looks already as if our defaults have been adopted. Let's see the sandbox itself:

$ ls -l ~/sandboxes/rsandbox_8_0_4/
total 152
drwxr-xr-x 23 user staff 782 Mar 5 06:45 branch1
drwxr-xr-x 23 user staff 782 Mar 5 06:45 branch2
-rwxr--r-- 1 user staff 1515 Mar 5 06:45 check_replicas
-rwxr--r-- 1 user staff 1170 Mar 5 06:45 clear_all
-rwxr--r-- 1 user staff 1629 Mar 5 06:45 initialize_replicas
-rwxr--r-- 1 user staff 807 Mar 5 06:45 n1
-rwxr--r-- 1 user staff 806 Mar 5 06:45 n2
-rwxr--r-- 1 user staff 806 Mar 5 06:45 n3
-rwxr--r-- 1 user staff 807 Mar 5 06:45 p
drwxr-xr-x 22 user staff 748 Mar 5 06:45 primary
-rwxr--r-- 1 user staff 806 Mar 5 06:45 r1
-rwxr--r-- 1 user staff 806 Mar 5 06:45 r2
-rwxr--r-- 1 user staff 855 Mar 5 06:45 restart_all
-rw-r--r-- 1 user staff 173 Mar 5 06:45 sbdescription.json
-rwxr--r-- 1 user staff 1137 Mar 5 06:45 send_kill_all
-rwxr--r-- 1 user staff 1308 Mar 5 06:45 start_all
-rwxr--r-- 1 user staff 1700 Mar 5 06:45 status_all
-rwxr--r-- 1 user staff 1097 Mar 5 06:45 stop_all
-rwxr--r-- 1 user staff 4613 Mar 5 06:45 test_replication
-rwxr--r-- 1 user staff 1325 Mar 5 06:45 test_sb_all
-rwxr--r-- 1 user staff 1106 Mar 5 06:45 use_all

We see that the new defaults were used and the script names have changed. But the differences are deeper than this. Also the internal values in the scripts were changed accordingly.

$ ~/sandboxes/rsandbox_8_0_4/test_replication
# primary log: mysql-bin.000001 - Position: 14073 - Rows: 20
# Testing replica #1
ok - replica #1 acknowledged reception of transactions from primary
ok - replica #1 IO thread is running
ok - replica #1 SQL thread is running
ok - Table t1 found on replica #1
ok - Table t1 has 20 rows on #1
# Testing replica #2
ok - replica #2 acknowledged reception of transactions from primary
ok - replica #2 IO thread is running
ok - replica #2 SQL thread is running
ok - Table t1 found on replica #2
ok - Table t1 has 20 rows on #2
# Tests : 10
# failed: 0 ( 0.0%)
# PASSED: 10 (100.0%)
# exit code: 0

The test script calls the components with the names that we defined in the new defaults. Let's have a look at what the shortcuts for the master and slaves (now primary and replicas) do:

$ ~/sandboxes/rsandbox_8_0_4/p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

primary [localhost] {msandbox} ((none)) >

$ ~/sandboxes/rsandbox_8_0_4/r1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

replica1 [localhost] {msandbox} ((none)) >

Also the internal prompt has been adapted to the new naming.

Should we want to revert to the old behavior, we can just reset the defaults:

$ dbdeployer defaults reset
#File $HOME/.dbdeployer/config.json removed

The current replication sandbox is left untouched, but the next one will use the default values.

If we don't want to change the defaults permanently, there is an alternative. The --defaults flag allows us to change defaults on-the-fly just for the command we're running. For example, we could have achieved the same result, without editing the configuration file, using this command:

dbdeployer deploy replication 8.0.4 \
--defaults=master-name:primary \
--defaults=master-abbr:p \
--defaults=slave-prefix:replica \
--defaults=slave-abbr:r \

The syntax for --defaults requires the name of the variable and the new value, separated by a colon. The flag can be used as many times as needed.

Categories: Web Technologies

MySQL security for real users

Planet MySQL - Sun, 03/04/2018 - 20:30

Security features overview

One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:

  1. MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work properly.
  2. The "root" account now comes with a password defined during initialization. This is good news for security, but bad news for how the change was implemented.
  3. There is a new way of setting an options file for connection credentials: the mysql_config_editor paired with option --login-path allows users to store encrypted credentials for secure use. Also here, while we should rejoice for the added security, we can't help feeling that the implementation is yet again far from meeting users needs.
  4. There is an useful warning (introduced in MySQL 5.6) when using a password on the command line, telling users that it is a risk. Also in this case, we have a usability issue: while users care about their production deployments and use option files to avoid using passwords on the command line, there are, nonetheless, a lot of testing scripts, used in safe environment or with non-valuable data, where a password in the command line was not an issue, and the new warning simply screws up the result of those carefully crafted tests. This change, which can't be overcome without modifying the MySQL clients code, needs users to change their existing tests to adapt to the new behavior.
  5. MySQL 8 introduces roles, which simplify the accounts management. There are some minor usability issues, although in general the feature meets expectations.

This is the scenario of the main enhancements in MySQL since 5.6. Each one of them has some usability problems, some minor, some really bad.
We will first have a look at the problems mentioned above, and then examine the root cause for why they have arisen.

Usability issues

I start by noticing that some developers in the MySQL team have been working there for many years, starting with the time when MySQL was a different database and was used really differently.
In those times, managing the database meant that a human (the DBA) would run operations manually, take a look at the result, and adjust when needed. And then, when things went wrong, the same human explored the database system to find out what happened, took action, and went back to sleep.

Human-centered management leads to human problems: lazy DBA left their databases without password, using the root account, and exposing the server to uninspired attacks; they used passwords on the command line, without caring for options files (or without knowing about them.) Careless DBAs did not deal with anonymous users, leaving a dangerous backdoor in their server.

Some of the new functionalities introduced in the latest MySQL versions are aimed at this type of users: when you install MySQL, you get a message saying: your root password is ************, and the lazy DBAs have no option but to take note and use it. When they use the password on the command line, the annoying warning forces them to start using an options file or the mysql_config_editor.

This is all good, but the main problem here is that the DBAs of 10 years ago are on the verge of extinction. They are replaced by a new breed of DBAs who are not lazy, because they can't afford to be, and need to use dozens, hundreds, thousands of databases at once, using configuration management tools that don't require manual intervention, and actually abhor it. In the land of automation, some of the MySQL security enhancements are not seen as a solution, but as new problems.

Let's see an interesting example: docker containers.

Using Docker, MySQL images are deployed using a password on the command line. This is done for compatibility with the first implementation of the image maintained by the Docker team, where you deploy with this syntax:

docker run -e MYSQL_ROOT_PASSWORD=secret -d mysql

The MYSQL_ROOT_PASSWORD is a directive that becomes an environment variable inside the container, and the server uses it during initialization. As you can imagine, this is not recommended for a secure environment. Then, what's the MySQL team recommendation? They suggest the same strategy used for manual installation: set a directive MYSQL_RANDOM_ROOT_PASSWORD that results in a random password being generated, then collected by the DBA and used. Alternatively, the directive MYSQL_ONETIME_PASSWORD will force the root user to change the password on first connection.

The above suggestions were designed with the ancient DBA still in mind, while container deployment is even more automated than VMs, and it is based on the principle of immutable objects, i.e. containers that spring up from the cloud ready to run, with no configuration needed, and especially no configuration that requires someone (or some tool) to extract a new password from a log. I proposed a different solution, that would never show passwords on the command line and while it was implemented, but it still feels like a hack to circumvent an inadequate design.

As a result, the implementation inside the MySQL recommended Docker image uses "--initialize-insecure" to start the server. This is an implicit recognition of the bad design of the initialization feature. What was designed to overcome DBA's laziness becomes an obstacle towards automation.

We have a similar problem with mysql_config_editor: the tool will create a safe configuration file with credentials for multiple instances, but the password must be inserted manually. Consequently, this potentially useful feature doesn't get adopted, because it would be too difficult or impossible to automate properly.

We have seen that, of the security features that were introduced lately, only a few can be used safely in an automated environment, and all of them have at least one small usability quirk. I have talked about a confusing issue related to the removal of anonymous users where in their eagerness of removing the vulnerability the MySQL team removed also the "test" database, which was a consequence, not the cause of the problem. And I have recently talked about roles usability where there are still open problems, like the ability of telling roles from users which are apparently not considered a bug by the MySQL team.

All the above considerations led me to ask: how did we get to this point? There is an active community, and feedback is offered often with plenty of detail. How come we have such an abundance of usability issues? Don't the developers spend time with users at conferences to learn what they expect? Don't they read articles and blog posts about how a new feature meets expectations? Don't they talk to customers who have adopted new features? They certainly do. Then, why the usability problems persist?

What follows is my observation and speculation on this matter.

Disconnection between MySQL developers and users community

My experience working with system providers has put me in contact with many users. I have seen that in most cases users are very much protective of their current deployment, because it took them long time to get it right, and they don't upgrade unless they don't have another choice. I've seen users test the newer versions, realize that they would break some of their procedures, and defer the upgrade to better times that never come. I remember last year a user with a not so large set of servers was considering an upgrade to MySQL 5.6, while 5.7 had been GA for two years. The reason was a set of incompatibilities that made the upgrade too difficult.

For companies that deal with thousands of servers, the problem is similar, but exacerbated by the quantity of servers to upgrade and the need to do it without stopping operations. This latest requirement has made some users decide not to use GTID, because it required offline time for a master, and they hadn't had time enough to test the upgrade to MySQL 5.7 that would solve that problem.

For one reason or the other, many companies upgrade only two or three years after a given version became GA. And this is the main problem: until they use it in production, or at least test the version for a projected upgrade, users can't give valuable feedback, the one that is related to usage in production, and when they do, the version for which they provide feedback has been GA for long time, and can't be changed, while the next one is already close to GA, and as such will be untouchable.

The MySQL team gets feedback on a release from a handful of curious users who don't delay testing until the new version is GA, but don't provide the kind of important feedback that get the development team attention, such as deployment in production by large customers. In many cases, large customers are the ones that upgrade several years after GA, and by then their input is difficult to act upon.

We have then a curious situation, where the warnings given by the early software explorers are confirmed years later by the users to which the MySQL team listens more closely, but by then the next version of the server is already locked in a release schedule that nobody wants to alter to fix usability issues.

How can we solve this problem? Simple: listen to early software explorers and try to fix problems before GA.

Categories: Web Technologies

On InnoDB's FULLTEXT Indexes

Planet MySQL - Sun, 03/04/2018 - 10:28
I had recently written about InnoDB features that I try to avoid by all means if not hate: "online" DDL and persistent optimizer statistics. Time to add one more to the list - FULLTEXT indexes.

This feature had a lot of problems when initially introduced in MySQL 5.6. There was a nice series of blog posts about the initial experience with it by my colleague from Percona (at that times) : part I, part II, and part III. Many of the problems mentioned there were resolved or properly documented since that times, but even more were discovered. So, InnoDB FULLTEXT indexes may be used, with care, when MyISAM or other engines/means to add fulltext search is not an option. The list of bugs that are still important and must be taken into account is presented below.

What forced me to get back to this feature recently and hate it sincerely is one customer issue that led to this bug report: MDEV-14773  - "ALTER TABLE ... MODIFY COLUMN ... hangs for InnoDB table with FULLTEXT index". Note that I have to refer to MariaDB bug report here, as related upstream Bug #88844 is hidden from community (probably considered a shame, if not a security problem)! The bug is simple: if one applies any ALTER to the InnoDB table with FULLTEXT index, even not related that index and columns in in any way, chances are high that this ALTER may cause a kind of hang/infinite loop/conflict of the thread that tries to drop temporary table used by ALTER, as one of last steps, and FTS background optimize thread. Similar to other two problematic features, new background threads were introduced and their cooperation with other threads in InnoDB seems to be not that well designed/implemented.

There are many other bugs to take into account if you ever plan to add any single FULLTEXT index to your InnoDB table. Here is the list of the most important ones, mostly still "Verified" or open and ignored, that I collected during one of calm night shifts this week:
  • Bug #78048 - "INNODB Full text Case sensitive not working". This bug was fixed only recently, in MySQL 5.6.39, 5.7.21, and 8.0.4.
  • Bug #83776 - "InnoDB FULLTEXT search returns incorrect result for operators on ignored words". Still "Verified" on all GA versions and 8.0.x.
  • Bug #76210 - "InnoDB FULLTEXT index returns wrong results for key/value pair documents". This bug was reported by Justin Swanhart 3 years ago, quickly verified and then seems to be ignored.
  • Bug #86036 - "InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit". I reported this bug 10 months ago, and it was immediately "Verified". It seems FULLTEXT indexes are hardly useful in general for large InnoDB tables because of this limitation.
  • Bug #78977 - "Enable InnoDB fulltext index to use generated FTS_DOC_ID column". This is a feature request (still "Open") to get rid of this well known limitation/specific column.
  • Bug #86460 - "Deleted DOCID are not maintained during OPTIMIZE of InnoDB FULLTEXT tables". If you want to get rid of deleted DOC_IDs in the INNODB_FT_DELETED, better just run ALTER TABLE ... ENGINE=InnoDB.
  • Bug #75763 - "InnoDB FULLTEXT index reduces insert performance by up to 6x on JSON docs". yet another verified bug report by Justin Swanhart.
  • Bug #69762 - "InnoDB fulltext match against in boolean mode misses results on join". Let me quote last comment there:
    "Since innodb doesn't support fulltext search on columns without fulltext index, and it is very complicated to support search on columns in multiple fulltext indexes in optimizer, it won't be fixed.

    We admit it's a point innodb fulltext is not compatible with myisam."
  • Bug #85880 - "Fulltext query is too slow when each ngram token match a lot of documents". This bug is still "Open".
  • Bug #78485 - "Fulltext search with char * produces a syntax error with InnoDB". Yet another verified regression comparing to MyISAM FULLTEXT indexes. Nobody cares for 2.5 years.
  • Bug #80432 - "No results in fulltext search for top level domain in domain part of email ". It ended up as "Won't fix", but at least a workaround was provided by Oracle developer.
  • Bug #81819 - "ALTER TABLE...LOCK=NONE is not allowed when FULLTEXT INDEX exists". Online ALTER just does not work for tables with FULLTEXT indexes. This is a serious limitation.
  • Bug #72132 - "Auxiliary tables for InnoDB FTS indexes are always created in shared tablespace". This my bug report was fixed in .5.6.20+ and 5.7.5+, but the fact that this regression was not noted for a long time internally says a lot about the way the feature was developed and maintained.
  • Bug #83560  - "InnoDB FTS - output from mysqldump extremely slow and blocks unrelated inserts". I have yet to check the metadata locks set when the table with FULLTEXT index is used in various SQL statements, but from this "Verified" report it is clear that just lading a dump of a table with FULLTEXT indexes may work too slow for any large table.
  • Bug #71551 - "ft_boolean_syntax has no impact on InnoDB FTS". yet another inconsistency with MyISAM FULLTEXT indexes that was reported 4 years ago and "Verified", but still ignored after that.
  • Bug #83741 - "InnoDB: Failing assertion: lock->magic_n == 22643". Surely, debug assertions can be ignored, but in most cases they are in the code for a good reason. This failure was reported by Roel Van de Paar from Percona.
  • Bug #83397 - "INSERT INTO ... SELECT FROM ... fails if source has > 65535 rows on FTS". This "Verified" bug alone, reported by Daniël van Eeden, makes InnoDB FULLTEXT indexes hardly usable in production for large tables.
  • Bug #80296 - "FTS query exceeds result cache limit". The bug is "Closed" silently (by the bug reporter maybe, Monty Solomon?), but users report that recent enough versions like 5.6.35 and 5.7.17 are still affected. See also Bug #82971 (no fix for MySQL 5.6.x for sure).
  • Bug #85876 - "Fulltext search can not find word which contains "," or ".".  Still "Verified" for 1 months.
  • Bug #68987 - "MySQL crash with InnoDB assertion failure in file pars0pars.cc". Crash was reported in MySQL 5.6.10, not repeatable. Then (different?) assertion failure was reported in debug builds only in MySQL 5.6.21+, and verified. Not sure what's going on with this bug report...
  • Bug #83398 - "Slow and unexpected explain output on FTS". The fact that EXPLAIN may be slow when the table with FULLTEXT index is involved is now documented, so this report by Daniël van Eeden is closed.
  • Bug #81930 - "incorrect result with InnoDB FTS and subquery". This bug report about wrong results by Sergei Golubchik from MariaDB was immediately "Verified", but ignored since that time.
  • Bug #80347 - "mysqldump backup restore fails due to invalid FTS_DOC_ID (Error 182 and 1030)". There is a workaround based on mydumper/myloader at least...
To summarize, InnoDB FULLTEXT indexes is one of the most problematic InnoDB features for any production use because:
  • There are all kinds of serious bugs, from wrong results to hangs, debug assertions and crashes, that do not seem to get any internal priority and stay "Verified" for years.
  • There are performance regressions and missing features comparing to MyISAM FULLTEXT indexes, so migration may cause problems.
  • InnoDB FULLTEXT indexes are not designed to work with really large tables/result sets.
  • You should expect problems during routine DBA activities, like ALTERing tables or dumps and restores when any table with InnoDB FULLTEXT index is involved. 
If you still plan/have to use it, please, make sure to use the latest MySQL version, check the list above carefully and test/check the results of fulltext searches and routine DBA operations like altering the table. You may get a lot of surprises. Consider alternatives like Sphinx seriously.
Categories: Web Technologies

MySQL without the SQL - Oh My!

Planet MySQL - Sun, 03/04/2018 - 08:40
I will be speaking about the MySQL Document Store at the San Diego PHP Meetup Wednesday March 7th.  This is a great group and it is always a pleasure to head back to my hometown.

The the 8th-11th is the Southern California Linux Expo where I will be speaking again on the MySQL Document Store as part of the MySQL track and will be manning booth 617/619 with plenty of swag.

And then on March 17th I will be teaching Database Basics with MySQL at Chick Tech Austin.
Categories: Web Technologies

Announcing Scotch Box v3.5 and Scotch Box Pro v1.5: The Big Switcheroo

Planet MySQL - Sat, 03/03/2018 - 17:03

TL;DR: Scotch Box Pro is now the free version. Scotch Box Pro is now even more decked out!

It has been 9 months without a Scotch Box update! I don't really see that as a bad thing though - I see it as an accomplishment. It's been working perfectly fine and stable for a ton of people without having to upgrade the box. We've helped an enormous amount of people learn Vagrant the easy way and perfect their local development environments.

To date, we have:

  • Well over 2MM downloads of Scotch Box
  • Helped close nearly 500 Vagrant related GitHub issues
  • Released 11 tutorials on Vagrant
  • Release 2 Video Courses nearing almost 3 hours of content on Vagrant

With that being said, let's dive into what's new with Scotch Box.

So what's new?

Excited to announce some new updates:

  • Major updates to the free version
  • Major updates to the pro version
  • Additional video screencast help (coming soon if not released yet)
The Big Switcheroo

We never intended to make a killing on Scotch Box, and I know it sucks that I created a free and paid version last time. Scotch Box's main purpose is to improve my own personal work-flow, and then just release it to everyone else as well. I want to make sure I keep updating both versions and appreciate any support we've gotten.

So here's what is new:

  • Scotch Box Pro is now FREE and moved to Scotch Box v3.5
  • Then Scotch Box Pro v1.0 has been upgraded
Scotch Box v3.5 Updates

Now included in the box is all the awesomeness of the first Pro version:

* Ubuntu 16.04 * PHP 7.0 * MySQL 5.7 * Go lang in the box * PHPUnit in the box * Yarn in the box * Improved email catching automatically on with MailHog * Customize your own boxes in minutes * Generally higher versions of things (Node.js, Ruby, etc.)! * PHPUnit * MySQL 5. * Node via NVM * Extra Node packages (grunt, browserify, webpack) * Ruby via RBM

All major features are now included in the free version. This should address all major issues and bugs related to Scotch Box (like PHP-XML not working or something else). The original pro version was near flawless.

Major Differences

HEADS UP: There's a couple differences between these versions so the initial upgrade might be a little different:

  1. Database connections are a little different (Update your DB client and check the docs)
  2. Vagrantfile is a little different (update your Vagrantfile via git clone and / or check the docs)
  3. Email catching is a little different (check the docs)
Scotch Box Pro v1.5 Updates

Now, the regular pro version has also been upgraded. If you have already have Pro this is just a normal upgrade for you. I appreciate your support!

You get a ton of new higher features:

  • NEW OS: Ubuntu-17.10!
  • NEW PHP: PHP 7.2!
  • NEW APACHE: 2.4.29
  • NEW NGINX: 1.13.8
  • NEW RUBY via RVM: 2.5.0
  • NEW NODE via NVM: 8.9.4
  • Fixes a MongoDB and PHP bug
  • Makes Laravel routing finally work out of the box with NGINX version
  • Adds Drush (Launcher) even though you should do this through Composer these days
  • Updated WP-CLI version
  • Generally WAY higher versions of everything else
Other Pro Features

As always, people with Pro get additional features included:

  • Access to the Scotch Box NGINX version
  • NGINX version gets all the new features too
  • Access to the Scotch Box Build Scripts
  • Instructions to customize your own boxes in minutes
  • Additional Pro documentation
Install Instructions (BACK EVERYTHING UP FIRST)

Make sure you back everything up first! Scotch Box uses Vagrant Box in a non-traditional way and each update is starting from scratch

Free Version

"I have never installed Scotch Box (free) before":

git clone github.com/scotch-io/scotch-box my-project cd my-project vagrant up

"I have installed Scotch Box (free) before but have a new project":

git clone github.com/scotch-io/scotch-box my-project cd my-project vagrant box update vagrant up

"I have installed Scotch Box (free) before and have an existing project":

Back everything up! You've been warned.

# 1. Back everything up # 2. Manually update your Vagrantfile to the one at https://github.com/scotch-io/scotch-box # Run vagrant box update vagrant up # Re-import databases / backups. You're now on the latest version Pro Version

Back everything up! You've been warned.

# 1. Back everything up # 2. Manually update your Vagrantfile to the Pro Vagrantfile (found in the Scotch Box Pro password protected docs) # Run vagrant box update vagrant up # Re-import databases / backups. You're now on the latest version Update Database Connections

The free version now uses the Pro version credentials which is almost the same. An example of an application connection would be:

$connection = mysqli_connect('localhost', 'root', 'root', 'scotchbox');

An example connecting to desktop client would be:

# MySQL Stuff User: root Password: root Database: scotchbox Host: # SSH Stuff SSH Host: SSH User: vagrant SSH Passowrd: vagrant SSH Port: 2222 Debugging the Update

Whenever updating Vagrant and Scotch Box, sometimes weird things can happen.

SSH Known Host Conflict

If you can't SSH connect or get the database connection to work, try killing your SSH known_hosts:

rm ~/.ssh/known_hosts Weird Auth Issues

If you can't authenticate, try adding this to the Vagrantfile

config.ssh.username = "vagrant" config.ssh.password = "vagrant" Final Straw

If everything fails:

  • Uninstall Vagrant
  • Uninstall Virtualbox
  • Make sure to delete all meta info (~/.vagrant.d)
  • Reboot your machine
  • Make sure and your ports aren't being used by anything else
  • Reinstall Vagrant and Virtualbox

If that fails, open a GitHub issue!

Categories: Web Technologies

Node.JS vs Python

Echo JS - Sat, 03/03/2018 - 05:31
Categories: Web Technologies

V6: Color

CSS-Tricks - Fri, 03/02/2018 - 14:20

This is a lovely little post by Rob Weychert about color theory, hierarchy and how to implement those colors in a design system. It’s particularly interesting hearing what Rob has to say on HSL, which is an alternative way of setting the color of an element in CSS:

For color adjustment, HSL brings a level of granular control to the process that other color systems lack. And for implementation, Sass lets me assign color values to variables, which make system-wide iteration quick and painless.

Direct Link to ArticlePermalink

V6: Color is a post from CSS-Tricks

Categories: Web Technologies

Just Released: New Features!

Planet MySQL - Fri, 03/02/2018 - 13:24

I’m pleased to announce that VividCortex has just released a number of new features since our last product update. If you have any questions regarding any of the updates below, contact us!

Short URLs

You can now share links with your coworkers and collaborate in VividCortex using URLs short enough to be copied and pasted into emails and chat apps, letting you get work done faster with less hassle. Just click the Link icon in the top right of the screen and copy the URL:

When anyone with appropriate permissions clicks the link, they’ll be taken directly to the URL you were looking at when you grabbed the link. Click “Freeze Time Range” to let your coworkers see the time frame you were viewing, no matter when they click the link. For more details, take a look at our documentation here.

Events on Charts

An important part of monitoring your database is watching for changes in performance that occur after an event such as an upgrade, a code deployment, or a settings change. To make viewing the effects of these changes easier, we’ve added the ability to plot events on charts and sparklines throughout the app. Click the megaphone icon in the top right corner and select an event you’d like to see plotted:


Custom Events

To make Events on Charts even more useful, you can now send your own custom events to the VividCortex API, which can then be plotted to examine the effects of those changes. Some of our customers send an event to the API every time they have a code deployment, which is one of the most powerful use-cases. For more information about sending custom events, including sample code, check out our documentation.

Custom Dashboards

Custom Dashboards are now available to all customers, letting you create a collection of charts which display exactly the data you need to resolve your issues faster. You can read our original update about custom dashboards in this earlier post.

Credential Notifications

We’ve made it easier to see if VividCortex is having trouble connecting to your database because the credentials have changed or are no longer valid:

In Conclusion

We hope you take advantage of theses new features. If you have any questions or suggestions on how we can make VividCortex even better we'd love to hear from you. Contact us here anytime!

VividCortex does offer a free trial; the signup page can be found here: https://app.vividcortex.com/sign-up



Categories: Web Technologies

MySQL Community Awards 2018: Call for Nominations!

Planet MySQL - Fri, 03/02/2018 - 12:45

MySQL Community Awards take place, every year as usual, in Santa Clara, during the Percona Live Open Source Database Conference

The MySQL Community Awards is a community-based initiative. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based of past winners or their representatives, as well as known contributors.

It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self-criticizing committee.

The Call for Nominations is open. We are seeking the community’s assistance in nominating candidates in the following categories:

MySQL Community Awards: Community Contributor of the year 2018

This is a personal award; a winner would a person who has made contribution to the MySQL ecosystem. This could be via development, advocating, blogging, speaking, supporting, etc. All things go.

MySQL Community Awards: Application of the year 2018

An application, project, product etc. which supports the MySQL ecosystem by either contributing code, complementing its behavior, supporting its use, etc. This could range from a one man open source project to a large scale social service.

MySQL Community Awards: Corporate Contributor of the year 2018

A company who made contribution to the MySQL ecosystem. This might be a corporate which released major open source code; one that advocates for MySQL; one that help out community members by… anything.

For a list of previous winners, please see MySQL Hall of Fame.

Process of nomination and voting

Anyone can nominate anyone. When nominating, please make sure to provide a brief explanation on why the candidate is eligible to get the award. Make a good case!

The committee will review all nominations and vote; it typically takes two rounds of votes to pick the winners, and a lot of discussion.

There will be up to three winners in each category.

Methods of nomination:

  • Fill out this form
  • Send en email to mysql.community.awards [ at ] gmail.com
  • Assuming you can provide a reasonable description in 140 characters, tweet your nomination at #MySQLAwards.

Please submit your nominations no later than Friday, March 15 2018.

The committee

Members of the committee are:

  • Alexey Kopytov
  • Bill Karwin
  • Colin Charles
  • Daniël van Eeden
  • Eric Herman
  • Frédéric Descamps
  • Justin Swanhart
  • Mark Leith
  • René Cannaò
  • Santiago Lertora
  • Shlomi Noach
  • Simon Mudd

Emily Slocombe and Agustín Gallego are acting as co-secretaries; we will be non-voting (except for breaking ties).

The committee communicates throughout the nomination and voting process to exchange views and opinions.

The awards

Awards are traditionally donated by some party whose identity remains secret. We are still looking for a sponsor, email mysql.community.awards [ at ] gmail.com if you would like to sponsor the award goblets


This is a community effort; we ask for your support in spreading the word and of course in nominating candidates. Thanks!

Categories: Web Technologies

Percona XtraDB Cluster 5.7.21-29.26 Is Now Available

Planet MySQL - Fri, 03/02/2018 - 11:46

Percona announces the release of Percona XtraDB Cluster 5.7.21-29.26 (PXC) on March 2, 2018. Binaries are available from the downloads section or our software repositories.

Percona XtraDB Cluster 5.7.21-29.26 is now the current release, based on the following:

Starting from now, Percona XtraDB Cluster issue tracking system is moved from launchpad to JIRA. All Percona software is open-source and free.

Fixed Bugs
  • PXC-2039: Node consistency was compromised for INSERT INTO ... ON DUPLICATE KEY UPDATE workload because the regression introduced in Percona XtraDB Cluster 5.7.17-29.20made it possible to abort local transactions without further re-evaluation in case of a lock conflict.
  • PXC-2054 Redo optimized DDL operations (like sorted index build) were not blocked in case of a running backup process, leading to SST failure. To fix this, --lock-ddl option blocks now all DDL during the xtrabackup backup stage.
  • General code improvement was made in the GTID event handling, when events are captured as a part of the slave replication and appended to the Galera replicated write-set. This fixed PXC-2041 (starting async slave on a single node Percona XtraDB Cluster led to a crash) and PXC-2058 (binlog-based master-slave replication broke the cluster) caused by the incorrect handling in the GTID append logic.
  • An issue caused by non-coincidence between the order of recovered transaction and the global seqno assigned to the transaction was fixed ensuring that the updated recovery wsrep coordinates are persisted.
  • PXC-904: Replication filters were not working with account management statements like CREATE USER in case of Galera replication; as a result, such commands were blocked by the replication filters on async slave nodes but not on Galera ones.
  • PXC-2043: SST script was trying to use pv (the pipe viewer) for progress and rlimit options even on nodes with no pv installed, resulting in SST fail instead of just ignoring these options for inappropriate nodes.
  • PXC-911: When node’s own IP address was defined in the wsrep_cluster_address variable, the node was receiving “no messages seen in” warnings from its own IP address in the info log.

This release also contains fixes for the following CVE issues: CVE-2018-2565, CVE-2018-2573, CVE-2018-2576, CVE-2018-2583, CVE-2018-2586, CVE-2018-2590, CVE-2018-2612, CVE-2018-2600, CVE-2018-2622, CVE-2018-2640, CVE-2018-2645, CVE-2018-2646, CVE-2018-2647, CVE-2018-2665, CVE-2018-2667, CVE-2018-2668, CVE-2018-2696, CVE-2018-2703, CVE-2017-3737.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Categories: Web Technologies

A Concrete Guide to Dependency Injection - Andrew Embler

Planet PHP - Fri, 03/02/2018 - 09:47

If you've done any object-oriented development in PHP, you've probably heard the term Dependency Injection. Still fuzzy on the concept? It's probably simpler than you think. Let's demystify it a bit.
Categories: Web Technologies