Missing COLUMN_STATISTICS with mysqldump 8

[guix77]

Describe the bug
Drush now throws an error with mysqldump 8 for certain operations like drush sql-dump.

To Reproduce
Updated Ubuntu 18.04 LTS => 20.04 LTS

mysqldump --version
mysqldump  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
drush sql-dump > db.sql

Expected behavior
Dump the DB to a file.

Actual behavior

> mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'gd8' AND TABLE_NAME = 'batch';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

In SqlCommands.php line 242:
                                                                         
  Unable to dump database. Rerun with --debug to see any error message

Workaround
Not found so far

System Configuration

Q	A
Drush version?	10.2.2 and 8.3.2
Drupal version?	8.8.5 and 7.53
PHP version	7.3.12
OS?	Linux (Ubuntu 20.04 LTS)

[weitzman]

I dont think that query comes from drush.

We tested with mysql8 and it passed all tests #3830

[guix77]

Hi Moshe,

Very sorry for answering so late, also I should have checked before a manual mysqldump, the error was very explicit.

So I checked and indeed the error is here with a usual mysqldump so nothing caused by Drush indeed.

Thanks again!

[guix77]

For reference, mysqldump 8 is now expecting a information_schema.COLUMN_STATISTICS table.

On MariaDB there is no such column in information_schema: https://jira.mariadb.org/browse/MDEV-16555

This one seems pretty big and very surprising to me, mysqldump8 just broke the MariadDB compatibility and there's not even a fix in the new Ubuntu LTS.

Workaround: snipe/snipe-it#6800 (comment)

[guix77]

Sorry to reopen this...

In fact, while I can now run mysqldump from the command line, form Drush I still have the same issue.

Strange fact with the local fix I mentionned above:

• now in a Drupal 7 with Drush 8.3.2 I get no errors
• but still on Drupal 8 with Drush 10.2.2 / 9.6.1

Sure there's nothing to do ? I think WP-CLI included a fix (wp-cli/wp-cli-tests#71)

[netw3rker]

This appears to be a problem in /src/Sql/SqlMysql.php A defaults file is written to /tmp and is then used to initialize mysql and mysqldump mostly in order to prevent passing the username/password over the cli. here's the offending code:

            $contents = <<<EOT
#This file was written by Drush's Sqlmysql.php.
[client]
user="{$dbSpec['username']}"
password="{$dbSpec['password']}"
{$unixSocket}
EOT;

            $file = drush_save_data_to_temp_file($contents);

This is a problem because mysqldump8 expects that column to be there by default, and does not check the version of the mysql server it is dumping the DB from. therefore systems like drush need to disable that behavior when initializing mysql.

I fixed this by Adding in the columnstatistics setting to the file contents above and that seems to fix it. IE:

#This file was written by Drush's Sqlmysql.php.
[mysqldump]
column-statistics=0
[client]
user="{$dbSpec['username']}"
password="{$dbSpec['password']}"
{$unixSocket}

[netw3rker]

I created a PR for this: #4442

[netw3rker]

@weitzman I can see how this would escape the tests. What is required here is to test drush with a mysql8 client, attempting to dump a database from a mysql7 server. I'm betting that the drush tests only confirm mysql8 client dumping from mysql8 servers, and mysql7 clients dumping from mysql7 servers.

[greg-1-anderson]

If a user with mysqldump 8 locally creates their own my.cnf file that contains column-statistics=0 as shown above, does that fix this problem? Or does Drush's my.cnf that holds the username / password replace rather than extend the user's config file?

If the later, perhaps Drush should try to find the user's my.cnf and append it to its temporary my.cnf. It would be difficult for Drush to just know what settings to include in order to fix version mismatches or other issues with the user's configuration.

[netw3rker]

Unfortunately, It doesn't. when the --extras-file (i might have the name of that switch wrong, but you should know which one i'm referring to) is provided, that configuration overrides the my.cnf configuration.

[greg-1-anderson]

If we can find all of the applicable config files, then we could append their contents to the one we generate. That could fix problems other than the one described here.

[netw3rker]

If the later, perhaps Drush should try to find the user's my.cnf and append it to its temporary my.cnf. It would be difficult for Drush to just know what settings to include in order to fix version mismatches or other issues with the user's configuration.

Agreed. however, mysql doesn't have the same magic that php does where one can get the loaded_ini= or similar. I did stumble across a link on mysql's site the other day that showed you can specify version information in the [client] and [mysqldump] categories. That might be the easiest resolution.

[weitzman]

Would it help if we merged #4334?

[netw3rker]

reading the docs, that looks like the right thing to do. my only concern would be that it seems like it merges it between the system and user opt file reads.. so I guess it's possible that users might configure their system to break the client/dump in their user file leaving the drush config to not be able to ensure its own success. but to @greg-1-anderson 's point, is that something worth caring about?

[greg-1-anderson]

+1 on merging #4334. In theory, that fixes this problem and potentially others. I think the risk of that PR causing problems is low.

[weitzman]

I posted more background into #4334. I dont think that one will be merged as is.

Just so I'm clear - the error in this issue happens when you have a mysqldump8 that is connecting to a prior version of mysql? That is quite rare, no? And wouldn't you typically upgrade or downgrade the client or server to fix it?

[netw3rker]

the error in this issue happens when you have a mysqldump8 that is connecting to a prior version of mysql

yes. I'm sorta guessing here, but I suspect this probably also extends across forks, so things like percona, mariadb etc, that may not include the statistics table in their most recent versions would also be effected.

I think the problem is the most common workflow is someone installing mysql on their local to connect to a managed host server. Thus they won't have a lot of control over whether the server is a latest version or not. They are then left with managing that upgrade/downgrade transition locally.

There's also the point that it is possible to tweak your system so that the mysqldump client doesn't throw this error when connecting to previous versions of mysql, but the way we are providing default options prevents that tweak from going into effect.

If compatibility between these two things was so broken that we couldn't either manage that with a few switches or initialize things in a better way, I'd say documenting the steps to downgrade/switch versions, would be enough and be done. But here, we're talking about one switch, and a touch of 'we might not be providing defaults right anyways', so it's worth fixing.

[netw3rker]

@weitzman After reading through the comment thread in the issue that switched to the current setup, I would agree that switching back might be a problem. Also, contrary to what I said above, Alex had a point with "This will prefer the settings.php credentials over system, so it might be an awkward experience when drush can connect, but the system can't" (paraphrasing a bit)

So, We might be left with adding this switch in a [mysqldump > 8.0] esque tag.

[greg-1-anderson]

OK, so #4334 is not going to work, and my previous thought of looking for and merging the user's my.cnf file will face similar problems.

We also cannot simply add a column-statistics=0 setting in the config file that Drush generates, because we have no good way of knowing when it is appropriate to add this or any other setting.

I therefore think that what we need is a Drush setting that either directly specifies or points to a config file that contains all of the mysql settings that must be included in the mysql config file that Drush generates. This means that if a user adds some configuration such as column-statistics=0 to their my.cnf file, then they must also know to configure Drush to insert a similar setting.

Ugly, but I can't think of a reliable way for us to solve the problem automatically.

[netw3rker]

We also cannot simply add a column-statistics=0 setting in the config file that Drush generates, because we have no good way of knowing when it is appropriate to add this or any other setting.

yes, also, and even worse, if you do, mysqldump < 8 will throw an error saying it doesn't know what to do with that option.

I did track down the version opt group idea I mentioned earlier, but it has 2 problems: 1) it appears to only apply to mysqld, and 2) it is specific to minor versions. see: https://dev.mysql.com/doc/refman/8.0/en/option-files.html

Is it possible to do version detection on mysqldump, and then add this switch if it's 8 or higher?

[greg-1-anderson]

Is it possible to do version detection on mysqldump, and then add this switch if it's 8 or higher?

I think that there are too many things for Drush to special-case here. I'm -1 on building a list of ways that Drush can work around end-user configuration issues. We need a way to allow users to add the config that they need, e.g. column-statistics=0 if they want to mix a mysql8 client with a mysql7 server. It will get too complicated trying to figure out if the user has this version of mysql vs that version of Mariadb etc.; Drush should not be in this business.

[greg-1-anderson]

I didn't mean to close.

[weitzman]

Someone linked above to the version detection PR from WP-CLI. Its aint pretty but maybe we could do same. What do guys think? See https://github.com/wp-cli/wp-cli-tests/pull/71/files

[greg-1-anderson]

At least ^^ works equivalently with both Mysql and Mariadb.

I am a little wary of potential complexity growth in this area, but if we're feeling that enough folks are encountering this specific problem, then I suppose it's worthwhile adding it as an exception.

[weitzman]

Seems like drush sql:dump --extra-dump="--skip-column-statistics" would fix this, right? This option can be added to a site's drush config if the typing gets error-prone or tedious.

[netw3rker]

Its aint pretty but maybe we could do same.

no, no it's not :)

I guess their approach of grep'ing the results of --help does ensure that you don't have to figure out version names and such.

I have that open PR that I can add this to.

[netw3rker]

drush sql:dump --extra-dump="--skip-column-statistics"

I tried something similar to that the other day with drush sql-dump --extras="--skip-column-statistics" and it really complained. I think that the -d options in mysqldump don't apply when using an optfile. (I could be wrong about that though)

[guix77]

Hi,

Thank you guys for investigating this.

Something worth noting is that on Drush 8.3.2, it works if you have in /etc/my.cnf:

[mysqldump]
column-statistics=0

But not on Drush 10.2.2. So there's definitively something different around the way Drush invokes SQL commands (going to have a look at it now).

[guix77]

Seems I've found something: when reverting #2387 (Fixed .my.cnf messing up mysql connection + minor credential-leak fix) it works. It's not ignoring any more /etc/my.cnf so if you have column-statistics=0 in it, this fixes the problem. However reverting this PR should have undesired side-effects.

Another (ugly) "fix" would be to rename your local mysqlbackup file and create a script with the original name, while passing in option --column-statistics=0

[greg-1-anderson]

Drush 10 uses --default-file instead of --default-extra-file when calling mysqldump &c.; in my mind, this change was a mistake. Folks shouldn't be storing credentials in their my.cnf file in most circumstances. For those where it is unavoidable, Drush should have provided an option to switch to --default-file as an opt-in action.

If a local Mysql installation needs special configuration (e.g. column-statistics=0) in order to function, that configuration should go in my.cnf, so that it can fix all mysql clients equivalently.

Pantheon just ran into this exact bug, as our Mysql client on new containers was upgraded to Mysql 8 in preparation for a similar upgrade of all of the databases. However, the database upgrade hasn't happened yet. For Drush 8, we fixed this with an /etc/my.cnf file, but this doesn't work for Drush 10.

For the sake of completeness, the way to fix this via Drush configuration is:

Drush 8

$command_specific['sql:dump'] = array('extra' => '--column-statistics=0');

Drush 10

command:
  sql:
    dump:
      options:
        extra-dump: '--column-statistics=0'

As follow on work, I plan to introduce global config options to Drush 10 and Drush 8 that allow the user to select whether Drush uses --default-file or --default-extra-file. For backwards compatibility purposes, Drush 8 will default to respecting my.cnf, and Drush 10 will default to ignoring my.cnf, but I suggest that Drush 11 should go back to respecting my.cnf by default.

[weitzman]

That all sounds great. Thanks for sharing the config thats needed to work around this.

I'm of a different mind on I suggest that Drush 11 should go back to respecting my.cnf by default.. I'd rather default to isolation rather than mingling with the hosts' my.cnf. We're nowhere near Drush 11 so its a moot point right now.

[greg-1-anderson]

As long as there is an option, the default value is less important.

[mmolinari]

For the sake of completeness, the way to fix this via Drush configuration is:

Drush 8

$command_specific['sql:dump'] = array('extra' => '--column-statistics=0');

This didn't work on Drush 8 for me, while the following (in the alias array, and with a - instead of :) did:

$aliases['dev'] = array(
  ...
  'command-specific' => array(
    'sql-dump' => array('extra' => '--column-statistics=0'),
  ),
);

[bohemier]

I fixed it by inserting this in my drush.yml, adding --column-statistics=0 to my other options in extra-dump:

command:
  sql:
    dump:
      options:
        extra-dump: '--default-character-set=utf8mb4 --column-statistics=0'

Using drush 9.

[unpredictable-raunak]

Describe the bug
Drush now throws an error with mysqldump 8 for certain operations like drush sql-dump.

To Reproduce
Updated Ubuntu 18.04 LTS => 20.04 LTS

mysqldump --version
mysqldump  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
drush sql-dump > db.sql

Expected behavior
Dump the DB to a file.

Actual behavior

> mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'gd8' AND TABLE_NAME = 'batch';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

In SqlCommands.php line 242:
                                                                         
  Unable to dump database. Rerun with --debug to see any error message

Workaround
Not found so far

System Configuration

Q A
Drush version? 10.2.2 and 8.3.2
Drupal version? 8.8.5 and 7.53
PHP version 7.3.12
OS? Linux (Ubuntu 20.04 LTS)

I resolved this error by giving value of column-statics to 0

like this:

mysqldump --column-statistics=0 --host= --user= --password=

[RoSk0]

Was affected by this as well. I don't think Drush has anything to do with this. The reason for fail is when you use v8 client to talk to v5.7 server. Described here https://serverfault.com/questions/912162/mysqldump-throws-unknown-table-column-statistics-in-information-schema-1109 .

And yes, drush sql:dump --extra-dump='--column-statistics=0' is a workaround, but I don't think you would face it in the first place if you would have same version of client/server talking to each other.