Ortho's picture

Unfortunately, I've run into a problem where a database that is being generated with mysqldump is incompatible with mariadb due to MariaDB lack of support for any_value function.  Seems the latest version of MySQL exports with this automatically, and I cannot find a way to export in a compatible way.

I then tried removing MariaDB for the mysql 5.7 and cannot get it to do it smoothly.

Seems as though the "drop in" really isnt.

Jeremy Davis's picture

Whilst MariaDB 10.1 does have some features from MySQL 5.7, but is only fully compatible with MySQL 5.6. As such, some features from MySQL 5.7 (such as the "any_value" function - introduced in MySQL 5.7) are not available in MariaDB 10.1. I.e. you would have the same issue if you tried to use MySQL 5.6.

In theory this should have been resolved for v10.2+ (which should be fully compatible with MySQL 5.7) but it looks like they dropped the ball there...!

So you have a couple of choices.

Either you can adjust your code to be fully compliant with MySQL 5.6/MariaDB 10.1 (e.g. something like this or this).

Or alternatively, you could install a newer version of MySQL on TurnKey (and remove MariaDB - they will clash if installed together). So something like this. Please note though, that you will lose the auto-security updates, so you'll need to make sure that you keep on top of updates. Also, I'm not 100% sure if MySQL maintain multiple versions, or whether you'd need to always run the latest (which could have implications in production).

Hope that helps.

Ortho's picture

Thanks for the info.

I'd adjust my code, but its not my code that is causing this. Its literally mysqldump that is causing this. So there is no work around, other than installing mysql 5.7. 

Thanks for the linked instructions. I'll dig into it tomorrow morning to see if I can get it working.


Jeremy Davis's picture

A SQL dump of a DB is just a text file that includes SQL code to re-create your DB. So you could just manually adjust the SQL code (within the dump file) so it will be compliant with MySQL 5.6/MariaDB 10.1. If this is likely to occur again the future, then you could write a script to do the update for you (so you won't need to manually update the file in the future).

I can't help you much with the required SQL changes themselves because I'm really only a fairly novice SQL user. Although if you are unsure, please post and I'm happy to help as best I can.

I am pretty handy with bash though, so once you have the required changes worked out, I'm sure I could assist you to create a script (or at least provide some commands) to do the updates in the SQL dump file for you.

If you go the MySQL route, please let me know if you have any issues and I'll try to help out as best I can.

Ortho's picture


Sadly its a 1.6GB .sql file, and trying to figure out what needs changing and what doesn't isnt in my skill set (I'm far from a SQL expert).

I've tried removing MariaDB and install MySQL, and for the most part it is working, I'm just struggling to get certain mysql configurations setup in this environment. Mostly because I don't use debian a lot.

Jeremy Davis's picture

Yes I concur, editing 1.6GB of SQL by hand sounds like a bad idea at best! (Not to mention painful...)

Re your MySQL install, I'm glad to hear that it's (mostly) working for you. I'm no MySQL/SQL expert either, but I am very handy with Debian and my googlefu is pretty strong, so please feel free to ask about anything and I'll do my best to at least point you in the right direction.

Good luck with it.

Ortho's picture


So maybe there is one thing that you can help me with.  I've switched to the Magento turnkey, and I'm having one issue.

Everytime I clear the cache, the server creates the new folders in the magento var folder (/var/www/magento/var/cache), however they are always created as root, and are not loadable by the website.

How can I change apache/php so that it creates all folders as www-data user/group ?

Ortho's picture

Ok, after a little more checking, it seems to only happen when I run some commands from CLI. So php is running as root, not as www-data. The problem is that it doesn't seem like sudo is installed by default, and su doesnt allow passthrough of accounts without the known password (I'm not aware there is one for www-data).

Jeremy Davis's picture

When you use the CLI, any commands that you run, run as the user you are logged in as. So if you're logged in as root, then all commands will run as root. 3 options to deal with that spring to mind:

  • use 'su'
  • install (and then use) 'sudo'
  • continue to use root, then adjust permissions afterwards (manually, via a umask, or via a cron job)

I'll elaborate a little...

Use 'su'

The reason why 'su' doesn't work by default with the webserver user (www-data) is because by default www-data does not have a shell. But you can work around that pretty easily, by explicitly granting it a shell with the '-s' switch; like this:

su - www-data -s /bin/sh

Now you are in a session running as the webserver user. To exit back to the root user use the 'exit' command. Alternatively, if you only have a single command to run, you can do that in a one liner using the '-c' switch, like this:

su - www-data -s /bin/sh -c "command to run"

(Obviously replace "command to run" with the actual command you wish to run)

Install 'sudo'

Installing software from the Debian (or TurnKey) repos is super easy. Install sudo like this:

apt update
apt install sudo

Then you can sudo as the www-data user, like this:

sudo -u www-data command to run

(Obviously replace "command to run" with the actual command you wish to run). It's also worth noting that when you use sudo, unlike su, there is no option to run within a session, so you'll need to prefix every command with 'sudo -u www-data'.

Continue to use root, then adjust permissions afterwards

This is probably the most fiddly option but should work ok. If you are happy working from the commandline, then it supports the option to have your site quite locked down. Essentially it's a case of keep doing what you're doing, but whenever you run commands from the commandline you'll need to correct the permissions. The easy way to do that (and ensure that all the Magento functionality from within the WebUI always works) is grant webserver ownership to the whole Magento directory. E.g. like this:

chown -R www-data:www-data /var/www/magento

However, the "best practice" way to do that, is only grant write permissions to places that the webserver actually needs to write to. E.g. '/var/www/magento/var/cache' (and likely some other directories, e.g. for files uploaded via the Magento webUI). If you go that path, then even if a bad guy manages to break into your site, the only files they can change are files that the webserver user has write access to (i.e. the cache, etc). Whether that approach might make sense on your system will depend on who is using it and via which interface. Granting ownership for just specific locations is essentially the same as above, but only on the specific directory(ies?) you want to change ownership of. E.g.:

chown -R www-data:www-data /var/www/magento/var/cache /var/www/magento/another-dir

Bonus points - automated permission setting/fixing

As hinted above, you could also automate this last option. You could do that either via a cron job that runs every minute (i.e. a task that runs regularly and 'fixes' the permissions, probably as per above chown commands). Or alternatively, you could set a default group owner for the Magento files. As these defaults are recursive, setting that in the Magento doc root will ensure that all files/directories have the www-data group ownership.

It's worth noting that if you went this path, you will need to ensure that the directories and files have the required access via the group permissions (rather than the user level permissions). You can even set up ACL (access control lists) if you really want to dig into it, but it's likely overkill for this task...

I won't go into too much detail, but essentially you set an appropriate umask to ensure that pre-defined group permissions are set on files and directories. You'll need to ensure that the files/directories have the appropriate group permissions (by default any work you do as root, will belong to root user, so you need to ensure appropriate www-data group access). I.e. that the directories have '77x' permissions (e.g. '775') and files have '66x' permissions (e.g. '664'). If you're unfamiliar with 'nix octal permissions, read=4, write=2 and execute=1; the digits are user, group & everyone/global. FWIW the execute permission on files is exactly what you would expect, but on directories means something different, often referred to as "search permission". So to enter a directory, the user must have 'execute' permission of the directory.

TBH, I haven't played with this last method much, but if you're interested and need more pointers, please post.

Extra info

Also, you may already be aware of this, but it's probably well worth noting that TurnKey is Debian under the hood (v15.x = Debian 9/Stretch). So any info regarding Debian Stretch will likely apply to TurnKey v15.x too. As Ubuntu is also a Debian derivative, then often Ubuntu instructions will also apply. But please be aware that Ubuntu is NOT binary compatible with Debian (TurnKey is). So it is NOT recommend to add (and install from) Ubuntu specific repositories, such as Ubuntu PPAs. You might get away with it for a while, but eventually it will bite you! :)

Hope that helps.

Ortho's picture

Thanks Jeremy,

I ended up resolving it by creating a few alias's in my .bashrc, and adding the su www-data -s /bin/sh <command>.

After a little testing I recognized that the problem was when I was running magento commands via CLI.

I simply created an alias like:

  • alias mag=" su www-data -s /usr/bin/php /var/www/magento/bin/magento "
  • alias flushit="mag cache:flush"
  • alias indexit="mag indexer:reindex"
  • alias cacheit="flushit ; mag cache:enable; mag cache:clean full_page"
  • alias go="indexit; cacheit"

This allows me to run the most common commands that I need for testing changes to the website, without breaking permissions (I simply type 'go'). (I've actually added a good 20 Alias's, but it all works with the first alias.

Jeremy Davis's picture

Great solution, I like it. Thanks for posting back with that. Perhaps it's even worth us considering including something similar by default? I'm not sure how many will use it, but worthy of consideration.

Ortho's picture

If you are going to include them in the image, these are the entire list of alias's that I created (and have found consistently useful for Magento 2 over the past few years). I simply included this in root's .bashrc file.

# User specific aliases and functions
alias php="su www-data -s /usr/bin/php " #Runs php with matched user for apache2
alias rma="rm -R " #short form for remove command
alias mag="php /var/www/magento/bin/magento " #Shortcut to run Magento CLI
alias indexit="mag indexer:reindex" #Rebuilds Entire Index
alias indexgfx="mag indexer:reindex design_config_grid" #Index Graphical changes only
alias indexstock="mag indexer:reindex cataloginventory_stock" #Index Stock Changes Only
alias flushit="mag cache:flush" #Flush entire cache
alias go="indexit; cacheit" #Re-index all, and re-build the cache entirely
alias gfx="indexgfx; cachegfx" #Re-index and rebuild graphics related content only (for testing theme changes)
alias cacheit="flushit ; mag cache:enable; mag cache:clean full_page"
alias cachegfx="mag cache:clean config layout block_html full_page"
alias static="cd /var/www/magento/ ; mag setup:static-content:deploy -f" #Build all static content
alias statictheme="mag setup:static-content:deploy -f --exclude-theme="Magento/blank" --exclude-theme="Magento/backend" --exclude-theme="Magento/luma""
alias staticadmin="cd /var/www/magento/ ; mag  setup:static-content:deploy -f --exclude-theme="Magento/blank" --exclude-theme="Magento/luma"" #Build admin content only.
alias gr="mag cache:flush;rma /var/www/magento/pub/static/*;rma /var/www/magento/var/di/ /var/www/magento/var/generation/ /var/www/magento/var/page_cache/ /var/www/magento/var/view_preprocessed/ /var/www/magento/var/cache/;grunt clean:%THEMENAME%;grunt exec:%THEMENAME%;grunt less;gfx" #Requires Grunt to be installed and set ENV THEMENAME to the theme you are working on.
alias compiledi="cd /var/www/magento/ ; mag setup:di:compile" #Run compilation
alias cleanall="cd /var/www/magento/;mag cache:disable ; mag cache:flush; rma /var/www/magento/pub/static/*; cd /var/www/magento/ ; rma /var/www/magento/var/di/ /var/www/magento/var/generation/ /var/www/magento/var/page_cache/ /var/www/magento/var/view_preprocessed/ /var/www/magento/var/cache/" #Wipes out all cached/compiled data.
alias clearup="cleanall; mag cache:enable"
alias goall="rm -rf  /var/www/magento/pub/static/*; cleanall; go" #Same as cleanall but also wipes out static content for rebuild.
alias thumbs="mag catalog:images:resize" #Build thumbnails to speed up website loading.
alias permissions="cd /var/www/magento/ ; chown -R www-data:www-data *; find . -type f -print0|xargs -0 chmod 644 ; find . -type d -print0|xargs -0 chmod 755" #Fixes permissions and ownership.
#alias gogrunt="clearup;grunt clean;grunt exec;grunt less;grunt watch" #Install Grunt first.
#alias varnish="mag setup:config:set --http-cache-hosts=" #Install and setup Varnish if needed.
#alias checkvarnish="ps axf |grep varnish" #Check if Varnish is running.


Jeremy Davis's picture

I'm not sure that we'll use them all, nor when we might add them, but thanks so much for sharing. I really appreciate it.

Add new comment