Ошибка 1109 mysql - Не ошибается лишь тот, кто ничего не делает!

I’m using PHP and MySQL(Server version: 5.5.31-0ubuntu0.12.04.2) for my website when I run the following query it’s giving me the above error. I couldn’t get any clue behind this error. Can anyone help me in resolving this error and suggesting the changes if any to my existing query? For your reference I’m writing my query below:

DELETE
   ABC.theory_sheet_set,
   ABC.theory_sheet_questions
FROM
   ABC.theory_sheet_set AS theory_sheet_set,
   OCN.theory_sheet_questions AS theory_sheet_questions
WHERE
   theory_sheet_set.theory_sheet_set_id = theory_sheet_questions.theory_sheet_set_id
   AND theory_sheet_set.theory_sheet_id=".$theory_sheet_id

The error it gives is as follows:

MySQL Error: 1109 (Unknown table 'theory_sheet_set' in MULTI DELETE)
Session halted.

My database name is ABC. Actually all the table names are valid and all the tables involved in this query are present there into the database. Can you please help me in resolving this issue?

Dharman♦

30.4k22 gold badges84 silver badges132 bronze badges

asked Aug 21, 2013 at 6:28

If you use the alias names used later into the query at the beginning of of query(i.e. right after the word DELETE) then it will work finely. The only issue there was that it couldn’t be able to identify the table from your database as you have used alias names to refer those tables into your database. So in order to remove this bug you must use alias name s you used in the query after DELETE. The rectified query will look like following:

DELETE theory_sheet_set, theory_sheet_questions FROM ABC.theory_sheet_set AS theory_sheet_set, ABC.theory_sheet_questions AS theory_sheet_questions  WHERE theory_sheet_set.theory_sheet_set_id=theory_sheet_questions.theory_sheet_set_id AND  theory_sheet_set.theory_sheet_id="$theory_sheet_id

answered Aug 21, 2013 at 6:53

PHPLoverPHPLover

7,80337 gold badges103 silver badges190 bronze badges

there are syntax error, try on this

DELETE *
FROM
theory_sheet_set theory_sheet_set
INNER JOIN 
theory_sheet_questions theory_sheet_questions ON  
theory_sheet_set.theory_sheet_set_id = theory_sheet_questions.theory_sheet_set_id
WHERE theory_sheet_set.theory_sheet_id=".$theory_sheet_id

answered Aug 21, 2013 at 6:50

Low Chee MunLow Chee Mun

6101 gold badge4 silver badges9 bronze badges

Источник

When i run this code, it gives me error. From this code, there’s several task to do:
(1) update tble customer by setting the address to ’90 TYT’ if c_id= 1
(2) view order_no,status,c_id,item_total remarks.
(3) if item_total 0, then update table order_status by setting remarks = ‘UNAVAILABLE’, else select order_no,status,item_total,remarks where status = ‘waiting’.

Please help me fix the error. I’m new to SQL.

#drop procedure if exists usp_GetAnything;
delimiter //
create procedure usp_GetAnything()
begin

select c_id,lname,address,city
from customer;
update customer
set address = '90 TYT'
where c_id = 1;

select o.order_no,o.o_status,c.c_id,o.item_total,o.remarks
from customer c, order_status o
where c.c_id=o.c_id;

    if (o.item_total > 0) then 
    update order_status o
    set remarks = 'UNAVAILABLE'
    where order_no > '123';
    else  
    select order_no,o_status,item_total,remarks
    from order_status
    where o_status = 'waiting';

end if;

end

Источник

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

$> mysqldump --single-transaction --host host -u user -p db > db.sql
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM,
'$."number-of-buckets-specified"') FROM
information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'db' AND
TABLE_NAME = 'Absence';':
Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

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?

asked May 14, 2018 at 16:40

manifestormanifestor

5,8996 gold badges27 silver badges37 bronze badges

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:

mysqldump --column-statistics=0 --host=<server> --user=<user> --password=<password>

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

[mysqldump]
column-statistics=0

to a MySQL config file, go to /etc/my.cnf, ~/.my.cnf, or directly to /etc/mysql/mysql.cnf.

answered May 17, 2018 at 13:10

cristhiankcristhiank

8,0341 gold badge7 silver badges4 bronze badges

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.

answered Oct 25, 2018 at 22:57

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.

answered May 15, 2018 at 15:32

DodiXDodiX

4113 silver badges4 bronze badges

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:

plugins/wb_admin_export_options.py

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

  "column-statistics":["Writing ANALYZE TABLE statements to generate statistics histograms.", "FALSE", "BOOL", ("8.0.2", None)]

with this one:

"column-statistics":["Writing ANALYZE TABLE statements to generate statistics histograms (set 0 to disable).", "1", "INT", (None, None)]

Then remove the .pyo:

rm /Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export_options.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)

Note: you can download the patched file from this Gist.

answered May 3, 2020 at 15:58

JuananJuanan

3013 silver badges7 bronze badges

Easiest Work Around

When using Mysql Workbench 8.0

Best of luck!

answered Dec 13, 2018 at 7:10

ansonanson

1811 silver badge2 bronze badges

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:

Rename mysqldump:

mv /usr/local/bin/mysqldump /usr/local/bin/_mysqldump

Save the following shell script in its place:

#!/bin/sh

_mysqldump --column-statistics=0 $@

answered Jul 4, 2018 at 5:25

In addittion to pierlo https://serverfault.com/a/919403/586669

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:

mysqldump_nostatistics.cmd:

 @ECHO OFF
 "C:Program FilesMySQLMySQL Workbench 8.0 CEmysqldump.exe" %*  --column-statistics=0

mysqldump_nostatistics.sh:

#!/bin/sh

_mysqldump $@ --column-statistics=0

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.

answered Aug 4, 2020 at 11:40

RonaldRonald

511 silver badge1 bronze badge

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.

/usr/local/mariadb/mariadb-10.1.37-osx10.13-x86_64/bin/mysqldump

answered Feb 1, 2021 at 20:40

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.

answered Jan 23, 2019 at 23:04

Dean OrDean Or

2131 gold badge2 silver badges5 bronze badges

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

alias mysqldump="mysqldump --column-statistics=0"

answered Sep 20, 2021 at 13:17

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

But you have an option to do it by enabling delete-master-logs in version 8.0.22

--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.

mforsetti

2,6262 gold badges16 silver badges20 bronze badges

answered Nov 16, 2020 at 9:42

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:

Uwe Keim

2,4005 gold badges29 silver badges46 bronze badges

answered Dec 18, 2020 at 10:15

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

C:Program FilesMySQLMySQL Workbench 8.0 CEmysqldump --column-statistics=0  --user=USERNAME --host=REMOTE_HOST --protocol=tcp --port=3306 --default-character-set=utf8 DATABASE_NAME > c:tempdump.sql --password

Replace USERNAME, REMOTE_HOST, DATABASE_NAME with your names.

answered Jan 6, 2021 at 11:11

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.

answered Sep 7, 2021 at 19:01

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).

answered Dec 11, 2018 at 13:04

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,

C:xamppmysqlbin>mysqldump --column-statistics=0 --host=loalhost --user root --passwod
mysqldump: unknown variable 'column-statistics=0'

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

2019-01-21 11:31:30 1050 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2019-01-21 11:31:30 4176 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

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

Thomas

4,2055 gold badges21 silver badges28 bronze badges

answered Jan 21, 2019 at 7:23

On macOS I fixed this by overriding the bundled mysqldump:

Install mariadb, e.g. brew install mariadb
Navigate to MySQLWorkbench > Preferences > Administration
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.

answered Oct 24, 2021 at 21:31

jchookjchook

1416 bronze badges

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

[client]
column-statistics=0

answered May 18, 2022 at 15:29

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

answered Nov 8, 2022 at 12:27

For Ansible users use:

mysql_db:
 ...
 dump_extra_args: --column-statistics=0

In my case it ignores ~/.my.cnf and /etc/my.cnf

answered Dec 2, 2022 at 14:28

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:

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

This has been posted for future reference.

answered Dec 6, 2022 at 19:44

Copy the mysqldump command from the output window, remove the defaults file part, and add --column-statistics=0 with other option

Open a command window in the mysqldump.exe parent folder and run the command manually, piping to a file in my documents folder (you’ll get access denied errors if you just try to dump it to same folder).

Example:

C:Program FilesMySQLMySQL Workbench 8.0 CE>mysqldump.exe --user=USER--host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers --column-statistics=0 "mantis" > "C:usersmoiDocumentsdumpsmantis.sql"

answered Mar 21 at 10:16

Источник

Introduction

If you are using MySQL and dealing with the Unknown Table ‘column_statistics’ error (1109) while executing a query on INFORMATION_SCHEMA, you are in the right place. This post provides a step-by-step guide on how to fix this error.

Issue Summary

When executing a query such as:

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS

The server throws the following error message:

[1109] Unknown table ‘column_statistics’ in information_schema

Root Cause

The root cause of this error is that the COLUMN_STATISTICS table does not exist in INFORMATION_SCHEMA. This is because the COLUMN_STATISTICS table was introduced in version 8.0 of MySQL, while the previous version was 5.7.

Solution

The solution is to upgrade your MySQL version to the latest version 8.0. After upgrade, you will be able to query the COLUMN_STATISTICS table. The steps to upgrade MySQL is given below:

Backup existing database. This can be done by creating a dump by executing mysqldump database_name > database_name.sql

Download the latest version of MySQL from the official MySQL website.

Install the downloaded version of MySQL. Make sure to use the same configuration settings as the existing version.

Import the backup database into the new version. This can be done by running mysql -u root -p database_name < database_name.sql

The database should now have been imported into the new version of MySQL and the error should have been resolved.

FAQ

Q1: Is it possible to maintain the same version of MySQL and still fix this issue?

A1: No, this issue can only be fixed by upgrading MySQL to version 8.0 or later.

Q2: Is it safe to upgrade MySQL version to 8.0?

A2: Yes, upgrading to 8.0 is generally safe and doesn’t require any major change. However, it is always a good idea to backup your existing database before upgrading, as an extra precaution.

Источник

Вопрос:

Я использую PHP и MySQL (версия сервера: 5.5.31-0ubuntu0.12.04.2) для моего сайта, когда я запускаю следующий запрос, который дает мне вышеуказанную ошибку. Я не мог понять эту ошибку. Может ли кто-нибудь помочь мне в разрешении этой ошибки и предложить изменения, если они есть, в моем существующем запросе? Для вашей справки я пишу свой запрос ниже:

DELETE
ABC.theory_sheet_set,
ABC.theory_sheet_questions
FROM
ABC.theory_sheet_set AS theory_sheet_set,
OCN.theory_sheet_questions AS theory_sheet_questions
WHERE
theory_sheet_set.theory_sheet_set_id = theory_sheet_questions.theory_sheet_set_id
AND theory_sheet_set.theory_sheet_id=".$theory_sheet_id

Ошибка, которую он дает, выглядит следующим образом:

MySQL Error: 1109 (Unknown table 'theory_sheet_set' in MULTI DELETE)
Session halted.

Имя моей базы данных – ABC. Фактически все имена таблиц действительны, и все таблицы, участвующие в этом запросе, присутствуют там в базе данных. Не могли бы вы помочь мне в решении этой проблемы?

Ответ №1

Если вы используете имена псевдонимов, используемые позже в запросе в начале запроса (т.е. сразу после слова DELETE), он будет работать точно. Единственная проблема заключалась в том, что он не смог определить таблицу из вашей базы данных, поскольку вы использовали имена псевдонимов, чтобы ссылаться на эти таблицы в своей базе данных. Поэтому, чтобы удалить эту ошибку, вы должны использовать имя псевдонима s, которое вы использовали в запросе после DELETE. Выбранный запрос будет выглядеть следующим образом:

DELETE theory_sheet_set, theory_sheet_questions FROM ABC.theory_sheet_set AS theory_sheet_set, ABC.theory_sheet_questions AS theory_sheet_questions  WHERE theory_sheet_set.theory_sheet_set_id=theory_sheet_questions.theory_sheet_set_id AND  theory_sheet_set.theory_sheet_id="$theory_sheet_id

Ответ №2

есть синтаксическая ошибка, попробуйте это

DELETE *
FROM
theory_sheet_set theory_sheet_set
INNER JOIN
theory_sheet_questions theory_sheet_questions ON
theory_sheet_set.theory_sheet_set_id = theory_sheet_questions.theory_sheet_set_id
WHERE theory_sheet_set.theory_sheet_id=".$theory_sheet_id

Источник