Error 1109 unknown table applicant in multi delete

mysqldump throws: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

Every time I try to make a mysqldump I get the following error:

The result is a dump which is not complete. The strange thing is that the same command, executed from another host, works without throwing any errors. Did someone experienced the same problem?

I’m using mysql-client 8.0 and try to access a mysql 5-7 server — maybe that is the reason?

22 Answers 22

This is due to a new flag that is enabled by default in mysqldump 8. You can disable it by adding —column-statistics=0 . The command will be something like:

Check this link for more information. To disable column statistics by default, you can add

to a MySQL config file, go to /etc/my.cnf ,

/.my.cnf , or directly to /etc/mysql/mysql.cnf .

For those using MySQL Workbench, there is an «Advanced Options» button on the Data Export screen. The option «Use Column Statistics» can be disabled by setting to 0.

I have not confirmed, but the following information has been suggested to also be true: In Version 8.0.14 it’s missing. In Version 8.0.16 it’s doing this by default.

I spent the whole day looking for a solution, and signed up here just to share mine.

Yes, this error is due to version differences.

Just download the MySQL 5.7 ZIP Archive from here: https://dev.mysql.com/downloads/mysql/ and unzip it, then use the mysqldump.exe file from there.

If you are using MySQL Workbench, you will need to set a path to the mysqldump Tool you downloaded by going to Edit -> Preferences -> Administration (from left hand pane).

Hope this helps.

I know that I am late to the party but this was getting me crazy. If you want to use a recent MySQL Workbench (tried with the newest one as of today, MySQL Workbench 8.0.20) you can patch this file:

in macOS: ( /Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export_options.py ) Replacing this line:

Then remove the .pyo:

Finally, reload Workbench again and in the Data Export page, click on «Advanced options. » and you will see the column-statistics option again (set 0 to disable and click the Return button)

Easiest Work Around

When using Mysql Workbench 8.0

To make this answer easier, you can rename mysqldump , make a shell script in its place and call the renamed mysqldump with the —column-statistics=0 argument. Eg:

Save the following shell script in its place:

From within MySQL Workbench there is an option to set the path of the mysqldump executable. (Edit — Preferences — Administration)

So you can create a .cmd (on Windows) or a .sh file (on Linux or mac) as follows:

Note the order of the parameters (it is different from pierlo’s) : the dump command executed includes (or may include) a —defaults-file= option, and this has to be the first parameter.

Also The echo off is needed otherwise the workbench is unable to parse the command output correctly.

I had this problem using the latest mysql workbench (8.0.23) on OSX (11.1) with mariadb. I solved it by selecting the version of mysqldump found in the mariadb package.

I use XAMPP and MySQL Workbench does warn about a version mismatch. I set MySQL Workbench to point to the XAMPP’s mysql.exe and mysqldump.exe.

Go to Edit -> Preferences -> Administration and set the path for each.

This works at least for version 8.0.14. So for others you may want to avoid using the bundled version of mysql and mysqldump.

There a couple of answers above here that refer to renaming the mysqldump binary and creating a wrapper script. This is a terrible approach.

The correct method (in bash) is to alias the command in your .bashrc

To macOS you need the older version (8.0.13) to see the «column-statistics», because I test the version 8.0.14 and 8.0.15 and both not showing the «column-statistics».

From MySQL Workbench version 8.0.14 you don’t have the option to disable column-statistics

You can do that in version 8.0.13

• —delete-master-logs has the same effect as the RESET MASTER SQL command.
• RESET MASTER deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time.

For those using DBeaver check the Local Client is set to create the dump. See next images for reference:

Access to local client selection:

Local clients available:

I faced the same issue with MySQL workbench latest edition, I resolved it using the mysqldump command line

Replace USERNAME, REMOTE_HOST, DATABASE_NAME with your names.

Depending on your situation, you can get rid of mysql and install mariadb instead. This eliminates the new feature that was introduced in mysql 8.

In my situation, I’m using mac-OS. By the way, there was [mysqldump] column-statistics=0 fields in my.cnf file that is placed under the /usr/local/etc directory. Deleting that field fixed the issue. (not: mysql version is 5.7 and installed via homebrew).

I also had the same issue, it occurs when I merge multiple data tables to the existing schema from other schema and export merged data to self contained script file. I did try to change the column-statistics=0,but result was following,

So I hadn’t help. I analyze the MySQL log I found that

its complaining about the size of the innodb_buffer_pool_size. I did make it to 24MB. Then it works.

On macOS I fixed this by overriding the bundled mysqldump:

1. Install mariadb, e.g. brew install mariadb
2. Navigate to MySQLWorkbench > Preferences > Administration
3. Set the mysqldump path to the one you just installed, e.g. /opt/homebrew/bin/mysqldump

Note: to find the full path to mysqldump, you can run which mysqldump in your terminal.

Since i cannot comment the actual answer from user:cristhiank, i’m adding a slight variation of the actual answer. In my case i had to change it in /etc/my.cnf.d/client.cnf and i had to leave it in the [client] section so not adding a [mysqldump] section.

So for me this was working /etc/my.cnf.d/client.cnf

In MySQL Workbench 8.0.31 on Advanced Options tab now exists new option «Force«, use it to skip errors like mentioned above.

For Ansible users use:

In my case it ignores

/.my.cnf and /etc/my.cnf

If using a MariaDB backend with MySQL Workbench 8.0.31 CE use need to download the native MariaDB binaries for your system from https://mariadb.com/downloads/community/ and specify them in MySQL Workbench.

For example, if you are on 64-bit Microsoft Windows OS perform the following:

1. Goto https://mariadb.com/downloads/community/
2. On the above page, select your Operating System
3. Community Server —> Verison (latest GA) —> OS (MS Windows 64-bit) and click on download
4. Run the Install Shield just downloaded, and make sure «MariaDB Server -> Client Programs» is enabled.
5. Run up MySQL Workbench
6. Select the menu: Edit -> Preferences.
7. Select «Administration» in the left hand column
8. Update «Path to mysqldump Tool:» to be C:Program FilesMariaDB 10.10binmariadb-dump.exe
9. Update «Path to mysql Tool:» to be C:Program FilesMariaDB 10.10binmariadb.exe

No more performance statistic errors or any other divergent issues down the line between MySQL and MariaDB.

Источник

delete takes
    from takes as T inner join course as C on T.course_id = C.course_id
    where title like '%database%';

1 Answers

delete t
    from takes as t
    inner join course as c on t.course_id = c.course_id
    where title like '%database%';