Error 1146 42s02 at line 1 table zabbix images doesn t exist

How to resolve MySQL ‘1146 table doesn’t exist’ errors in your server

In our role as Support Engineers for web hosts, we manage servers with various services such as web, database, mail, control panels, FTP, etc.

MySQL is the most commonly used database server in Linux hosting and handling the databases and resolving the errors associated with it, is a common task that we perform.

A commonly noticed error in MySQL server is ‘1146 table doesn’t exist’. Today we’ll see what causes this ‘1146 table doesn’t exist’ error in MySQL and how to fix it.

Error : Table ‘mysql.innodb_index_stats’ doesn’t exist
status : Operation failed

What causes MySQL ‘1146 table doesn’t exist’ error

MySQL table errors happen due to many reasons, the major ones we’ve come across include:

1. InnoDB crash – When the InnoDB server crash due to any process load or user abuse, or if the server was not restarted properly, it can get corrupt and cause table errors to show up.
2. Missing ibdata file in the MySQL datadir – InnoDB has a data dictionary – the ibdata file and log files, which are crucial for InnoDB to function. If during migrations or restorations, these files go missing, it can prevent InnoDB tables from functioning right.
3. Improperly placed .frm files – In InnoDB, tables have ‘.frm’ files that define the table format. If these files get deleted or were missed to copy over to the proper database directory, then the tables can show errors.
4. Incorrect permissions and ownership of MySQL datadir – MySQL has a data directory, usually ‘/var/lib/mysql’ that stores the databases. If the permission and ownership of this directory is not adequate for MySQL to access it, errors would occur.
5. Corrupt tables or improper table names – If the database tables got corrupt due to improper server shut down or incomplete queries, or if the table name format is not correct, the ‘1146 table doesn’t exist’ error may show up.

How to fix MySQL ‘1146 table doesn’t exist’ error

Inorder to fix the error ‘1146 table doesn’t exist’, we adopt different techniques, after analyzing the root cause of the error.

1. Restart MySQL server – If the error has happened due to improper server shut down or MySQL service related errors, we restart the service and check if it fixes the issue. If the service doesn’t start properly, we further investigate and fix the error.
2. Repair the tables – MySQL has tools such as ‘myisamchk’ to repair corrupt databases and tables.
3. Backup restore – Restoring database backups is the final resort to get the tables back to working condition. We always configure and maintain the backups in our customers’ servers up to date, inorder to ensure that there is no data loss or down time due to unexpected crashes or errors.
4. Copy ibdata file – If the ‘ibdata’ file is missing, we copy it from the backup and restore it to the data directory for MySQL, after discarding the tablespace to avoid any corruptions or errors.
5. InnoDB crash recovery – In case where the backup is incomplete or ibdata file is also corrupt, we’ve still been able to recover the tables via our expert crash recovery methods. Read the post ‘Database crash rescue‘ to know more.

[ Use your time to build your business. We’ll take care of your servers. Hire Our server experts to resolve and prevent server issues. ]

At Bobcares, our 24/7 Web Support Specialists constantly monitor all the services in the server and proactively audit the server for any errors or corruption in them.

With our systematic debugging approach for service or other software errors, we have been able to provide an exciting support experience to the customers.

If you would like to know how to avoid downtime for your customers due to errors or other service failures, we would be happy to talk to you.

Источник

ERROR 1146 (42S02): Table ‘information_schema.SCHEMATA’ doesn’t exist #1047

Issue:

What seems to be going wrong?

docker exec -it bash
mysql -u root -p
show databases;

Expected behavior:

What should be happening instead?

see available databases or none? isn’t the container supposed to create it by default?

Reproduce:

How might we be able to reproduce the error?

Relevant Code:

The text was updated successfully, but these errors were encountered:

I resolved this error by running:
mysql_upgrade -u root -p —force

However, the next error whilst try to migrate the schema versions of Laravel is:
SQLSTATE[HY000] [2054] Server sent charset unknown to the client. default character set utf8mb4 collate utf8mb4_unicode_ci.

It seems the problem has to do with a bug in the driver in the version of PHP that comes with laradock?
https://bugs.php.net/bug.php?id=74461

This bug seems to be resolved in PHP1.7.5.

Hi, was having the same issue, i run the mysql_upgrade you posted and now i’m able to see databases. But i still have an issue of connecting to the database via sequel pro, using credentials i changed in .env

Are you facing same issue and have you been able to solve it?

Have you tried to using host=mysql?

If you are using the laradock repo you can spin up phpmyadmin:
docker-compose up -d nginx mysql phpmyadmin
then you can browse phpmyadmin at: http://localhost:8080

I have added 127.0.0.1 mysql in my hosts file, base on some post i read.
i just used phpmyadmin wth the following credentials and it worked:
username: laradock
password: secret
database: zoo (Collation: utf8mb4_0900_ai_ci)

Then i have a php file which i have this code in it.
$con = mysqli_connect(«127.0.0.1″,»laradock»,»secret»,»zoo»); var_dump($con);

this is the result i get:
Warning: mysqli_connect(): (HY000/2002): Connection refused in.

When i use «mysql» instead of «127.0.0.1», i get:
Warning: mysqli_connect(): Server sent charset (255) unknown to the client.

Try downgrading the MySQL version from 8.0 to 5.7 5083426 then rebuild the container.

Hi @Mahmoudz, I try downgrading MySQL to 5.7 and rebuild, but when up docker-machine I receive this:

I did the following to get mine working:

1. Removed image $ docker rmi laradock_mysql
2. Remove container $ docker rm laradock_mysql
3. Located DATA_SAVE_PATH=

/.laradock/data in .env file

Deleted mysql folder

Modified laradock/mysql/Dockerfile

1. Buil docker-compeer up -d mysql nginx

I hope I didn’t miss anything in the steps.

Changing the mysql container version to 5.7 from 8.0 didn’t fix this for me.

After lots of faffing I decided to start from scratch. Before building the containers for the first time ensure you have set the mysql version at laradock/mysql/Dockerfile to 5.7 as described at #1047 (comment).

Setting the version from mysql 8.0 to 5.7 caused lots of issues. At the moment it’s quicker to set the version before starting the build process.

solution:

/.laradock/data/mysql warning: it will delete your mysql exist data

docker-compose up —build mysql

question:

for this issue,i know i need clear mysql old data when i change mysql version, so i exec docker-compose down -v , but the error still exist, i realize the mysql container not use docker volume system , so i checked docker-compose.yml ,In there, i found a question:

for laradock old version, mysql use docker volume save data, example:

so, who is using the volume mysql ? I hope I didn’t miss anything for this config. is that a bug? @Mahmoudz

Источник

Как устранить неисправность InnoDB в базе данных MySQL

Проблема

Причина

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

Создайте дамп всех баз данных:

Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

Попробуйте создать копию:

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

Или при попытке сделать дамп через mysqldump

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

• узнать структуру таблиц
• иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

• файл .frm хранит в себе структуру таблицы;
• файл .ibd собственно данные

• выяснить структуру поврежденной таблицы;
• создать новую базу;
• создать в новой базе таблицу нужной структуры;
• скопировать данные в новую таблицу из старой;
• если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

Также желательно узнать кодировку старой базы:

Создаем новую базу:

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

Далее копируем данные:

• Очищаем автоматически созданный файл
• Копируем файл с данными с поврежденной таблицы
• Импортируем данные
• Проверяем корректность чтения данных

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.

Источник

While using an older VM with CentOS7 installed, I attempted to remove the default MySQL v5.5(ish) installation and replace it with a more recent MariaDB v10.5 for a Docker experiment. While trying to line up my DB version types due to a 16-character limit for the db usernames in older versions of MySQL, I ran into: “ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist”.

This was an odd one, as I even removed the original MySQL completely with:

(IMPORTANT: DON’T do this unless you have your existing data backed up with a mysqldump!! Read More here: https://mariadb.com/kb/en/making-backups-with-mysqldump/)

Then we need to add a recent version of MariaDB that works with CentOS7:

Next we update the cache after we added MariaDB to yum:

Now we install MariaDB from the repo we configured above:

Be sure to say “yes” to the GPG key, like so:

Now we need to start the newly installed mariadb.service so we can test it out and secure the MySQL-compatible database:

If you want mariadb.service to be started at boot, do this:

Now we need to make sure that we allow connection to port 3306, like so:

Everything seemed to go fine, until the next step…

While trying to harden the MariaDB upgrade installation with, the above command, I got the following error(s):

and/or

I searched in the “usual places” for an obvious fix. This seemed like it might be some kind of local Linux CentOS7 user permissions issue, at first. I went of on a few side quests and peered down the rabbit hole of Linux User permissions for MySQL packages… but I stopped myself. Anytime you see an “error on line 1” it is likely due to some kind of configuration problem, otherwise a “permission denied” or “no such user” error would be more likely.

So after I got my head right, I then tried to completely uninstall all of MariaDB, just to start from scratch (IMPORTANT: DON’T do this unless you have used mysqldump for your existing data as a back up!!)

After removing MariaDB, completely we need to start over. Just to be sure, we can reboot with:

After going through the entire installation process for a 2nd time, just as before (minus adding the MariaDB repo since that was done once already), I ran into the exact same issue again:

FAIL.

After spending awhile longer trying to understand what went wrong, I attempted the following successful “fix” to the error:

And I noticed this about 30% through the mysql_upgrade process:

…. and after that: Ka-ching!

My MySQL client tools and CLI access to the newly updated and clean installed MariaDB worked flawlessly.

I haven’t had to run the mysql_update command to solve a problem with setting MySQL/MariaDB to start & run in many years, but the error message and missing table issue was pretty obscure. Bonus tip: if you get “error access denied” during