Sql error 1146 sqlstate 42s02

The full error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'quotesapp.admin' doesn't exist (SQL: select count(*) as aggregate from `admin` where `username` = Admin)

I know the error is the mismatch between the name as it appears in the error log and how it’s defined everywhere else (in the database folder, but I am unable to solve the problem. I searched around and found this post, but even after I implemented the solution(shown below), I keep getting the same error.

I am using Laravel 5.2. I have an admins table in my database directory which looks like this:

class CreateAdminsTable extends Migration
{

    public function up()
    {
        Schema::create('admins', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
            $table->string('username')->unique();
            $table->string('password');
            $table->rememberToken();
        });
    }

    public function down()
    {
        Schema::drop('admins');
    }
}

The Admin model looks like this :

<?php

namespace App;

use IlluminateDatabaseEloquentModel;
use IlluminateAuthAuthenticatable; 

class Admin extends Model implements IlluminateContractsAuthAuthenticatable
{
    protected $table = 'admins';
    use Authenticatable;
}

?>

Here is the relevant route that is causing the error to be thrown:

Route::post('/admin/register', [
    'uses' => 'AdminController@postRegister',
    'as' => 'register'
]);

And here is the postRegister method

 public function postRegister(Request $request) {

    $this->validate($request, [
        'username' => 'required|unique:admin|max:30|min:3',
        'password' => 'required|min:5',
        'password_confirm' => 'required'           
    ]);

    $password = $request['password'];
    $passwordConfirm = $request['password_confirm'];

    if ($password !== $passwordConfirm) {
        return redirect()->back()->with(['fail' => 'password fields do not match!']);
    }

    $admin = new Admin();
    $admin->username = $request['username'];
    $admin->password = $request['password'];
    $admin->save();

    return redirect()->route('index');       

}

I have run php artisan migrate in composer, but I get the «nothing to migrate» response. I have tried refreshing the database via composer, but to no avail. The table ‘admins’ is showing up in phpmyadmin.

Update 1: Added full error message

The full error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'quotesapp.admin' doesn't exist (SQL: select count(*) as aggregate from `admin` where `username` = Admin)

I know the error is the mismatch between the name as it appears in the error log and how it’s defined everywhere else (in the database folder, but I am unable to solve the problem. I searched around and found this post, but even after I implemented the solution(shown below), I keep getting the same error.

I am using Laravel 5.2. I have an admins table in my database directory which looks like this:

class CreateAdminsTable extends Migration
{

    public function up()
    {
        Schema::create('admins', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
            $table->string('username')->unique();
            $table->string('password');
            $table->rememberToken();
        });
    }

    public function down()
    {
        Schema::drop('admins');
    }
}

The Admin model looks like this :

<?php

namespace App;

use IlluminateDatabaseEloquentModel;
use IlluminateAuthAuthenticatable; 

class Admin extends Model implements IlluminateContractsAuthAuthenticatable
{
    protected $table = 'admins';
    use Authenticatable;
}

?>

Here is the relevant route that is causing the error to be thrown:

Route::post('/admin/register', [
    'uses' => 'AdminController@postRegister',
    'as' => 'register'
]);

And here is the postRegister method

 public function postRegister(Request $request) {

    $this->validate($request, [
        'username' => 'required|unique:admin|max:30|min:3',
        'password' => 'required|min:5',
        'password_confirm' => 'required'           
    ]);

    $password = $request['password'];
    $passwordConfirm = $request['password_confirm'];

    if ($password !== $passwordConfirm) {
        return redirect()->back()->with(['fail' => 'password fields do not match!']);
    }

    $admin = new Admin();
    $admin->username = $request['username'];
    $admin->password = $request['password'];
    $admin->save();

    return redirect()->route('index');       

}

I have run php artisan migrate in composer, but I get the «nothing to migrate» response. I have tried refreshing the database via composer, but to no avail. The table ‘admins’ is showing up in phpmyadmin.

Update 1: Added full error message

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

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

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. Восстановление из резервной копии

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

Источник